曾静的技术博客

但行好事,莫问前程.

嗨,我是曾静 (@devzeng),目前暂居深圳。


这是我用来记录平日学习笔记的地方,欢迎您的访问.

使用Kettle导入Excel数据

ETL(Extraction, Transformation, and Loading),在日常的工作中我们经常会遇到各种数据的处理,转换,迁移。比如将Excel的数据导入到数据库,将SQLServer里面的数据转换后存到Oracle,将数据库的数据提取到文本等。

最开始都是使用写代码然后进行处理,多了几次之后就觉得麻烦了。后来了解到Kettle这个工具,首先无需安装直接就能使用,支持图形化的GUI设计界面,然后可以以工作流的形式流转,在做一些简单或复杂的数据抽取、质量检测、数据清洗、数据转换、数据过滤等方面有着比较稳定的表现,通过熟练的使用能在数据处理方面减少不少的工作量,提高工作效率。

kettle-logo.png

下载安装

Kettle是使用Java编写的,所以需要安装Java的运行环境。Kettle支持跨平台能在各种系统下使用,下面以在MacOS上面为例介绍如何配置。

1、环境准备

在命令行执行java -version,查看当前是否安装JDK。如果出现如下的内容:

java version "1.8.0_91"
Java(TM) SE Runtime Environment (build 1.8.0_91-b14)
Java HotSpot(TM) 64-Bit Server VM (build 25.91-b14, mixed mode)

表示Java环境已经安装,如果没有的话需要先安装JDK。

#####(1)下载安装JDK

到Oracle的官网上下载最新的版本JDK,网址如下:

http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html

选择需要的版本下载即可,下载完成直接双击下一步安装:

jdk-download.png

(2)配置环境变量

安装完成JDK之后,一般会自动安装在/Library/Java/JavaVirtualMachines/这个目录下面,可以通过/usr/libexec/java_home这个查看路径。

vim ~/.bash_profile

在里面新增如下记录(当前安装的JDK版本是1.8):

export JAVA_8_HOME=`/usr/libexec/java_home -v 1.8`
export JAVA_HOME=$JAVA_8_HOME

保存,然后使用source ~/.bash_profile

2、下载

打开Kettle官网:http://community.pentaho.com/projects/data-integration/ 在网页的下面有下载的入口,当前的稳定版本是7.1:

kettle-download.png

3、启动

下载完成后解压压缩包,在命令行进入到kettle的目录(文件夹的名字一般是data-integration)。然后执行./spoon.sh即可启动Kettle。

hello-kettle.png

使用示例

近期工作中经常需要协助同事将Excel里面的数据进行处理之后保存到SQLServer数据里面去,最开始是使用Python脚本解析Excel然后生成SQL语句到服务器上面执行的,每次有字段的调整都要改代码(代码如下):

def parse_excel_file(file, index=1):
    workbook = xlrd.open_workbook(file)
    sheets = workbook.sheets()
    sheets_data = []
    for sheet in sheets:
        print "[解析]", sheet.name
        sheet_data = []
        for i in range(index, sheet.nrows):
            sheet_row_data = []
            for j in range(0, sheet.ncols):
                cell = sheet.cell(i, j)
                # 数据类型 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
                ctype = cell.ctype 
                # 数据转换
                cvalue = cell.value
                if ctype == 2:
                    if isinstance(cvalue, float):
                        cvalue = str(long(cvalue))
                    elif isinstance(cvalue, int):
                        cvalue = str(cvalue)
                elif ctype == 1:
                    cvalue = cvalue.encode('UTF-8')
                sheet_row_data.append(cvalue)
            sheet_data.append(sheet_row_data)
        sheets_data.append(sheet_data)
    return sheets_data

下面以如何使用Kettle通过配置的方式简化操作,具体的操作步骤如下:

1、创建数据转换

执行./spoon.sh后启动Kettle的界面,在主对象树 -> 转换菜单上面单击右键新建转换。

kettle-demo-01.png

2、配置数据转换

#####(1)从左边的核心对象里面选中控件直接拖到右边的区域,各个控件之间可以用箭头连起来(按住Shift直接拖即可)

kettle-demo-03.png

#####(2)双击Excel输入配置解析

kettle-demo-04.png

1) 点击浏览选择Excel文档的路径(xls格式),选择完成后点击增加

2)选择工作表的选项卡,在此页面配置要解析的Sheet和起始行列信息。

kettle-demo-05.png

3)选择字段的选项卡,在此页面点击获取来自头部数据的字段,Kettle会自动获取表头生成字段信息。

kettle-demo-06.png

#####(3)使用JavaScript代码自动生成主键

由于业务方需要在将数据保存到数据库的时候需要指定一个主键,这里可以直接使用JavaScript的代码来自动生成一个UUID作为主键。

kettle-demo-07.png

#####(4)输出数据到目标数据库

1)配置数据源

kettle-demo-08.png

配置完成之后点击测试,如果报错(缺少驱动包),需要下载对应数据库的驱动包放到Kettle目录下面的lib目录下。

kettle-demo-08.png

2)配置目标表和字段映射

配置好数据源后需要配置目标表的信息,如表名、数据库字段对应等。

kettle-demo-08-2.png

点击输入字段映射可以将输入的数据字段和目标表的数据库字段进行一一对应起来。

3、运行

配置完成后点击左上角的运行按钮直接就可以运行转换任务。

kettle-demo-run.png

参考资料

1、Kettle 官网

2、kettle转换中使用javascript例子整理(1)

3、kettle JavaScript脚本

最近的文章

CentOS安装Hadoop

系统环境下载软件: (1)JDK (2)Hadoop (3)MySQL (4)Hive (5)HBase (6)Zookeeper1、服务器配置(1)修改主机名将192.168.13.1、192.168.13.2 和 192.168.13.3 这三台机器分别命名为hadoop-master、hadoop-slave1 和 hadoop-slave2。1) 修改/etc/sysconfig/network文件修改HOSTNAME=localhost.localdomain为HOS...…

Note继续阅读
更早的文章

使用Dockerfile构建Docker镜像

Docker中有个非常重要的概念叫做——镜像(Image)。Docker 镜像是一个特殊的文件系统,除了提供容器运行时所需的程序、库、资源、配置等文件外,还包含了一些为运行时准备的一些配置参数(如匿名卷、环境变量、用户等)。镜像不包含任何动态数据,其内容在构建之后也不会被改变。镜像的定制实际上就是定制每一层所添加的配置、文件。如果我们可以把每一层修改、安装、构建、操作的命令都写入一个脚本,用这个脚本来构建、定制镜像,那么之前提及的无法重复的问题、镜像构建透明性的问题、体积的问题就都会解决。...…

Note继续阅读