1.Kettle介绍。

  ETL（Extract-Transform-Load的缩写，即数据抽取、转换、装载的过程），对于部门来说，经常会遇到大数据量的处理，转换，迁移，所以了解并掌握一种etl工具的使用，必不可少。

Kettle是一款国外开源的etl工具，纯java编写，绿色无需安装，数据抽取高效稳定,提供了图形化界面，使用很方便。Kettle中有两种脚本文件，transformation和job，transformation完成针对数据的基础转换，job则完成整个工作流的控制。

2.Kettle下载。

Kettle可以在http://2.2.2.240/bbs/网站下载kettle压缩包，因kettle为绿色软件，解压即可使用。这个压缩包，公司加了一些功能，跟官网下载的不太一样，建议使用公司论坛上的压缩包。

 资料网站：http://www.itisbi.com/。

注：解压路径尽量不要有中文。

.kettle:存放kettle的一些环境变量信息，资源库的信息。

jre6:存放java运行库文件（1.6版本）。

launcher：存放启动的文件以及启动的配置信息。

lib：存放kettle的库文件。

libext:存放用到的外部库文件。

libswt:存放用到的界面库文件。

plugins:存放插件文件。

resources:存放编写的转换和任务文件，以及日志和任务 

 配置信息。

ui：存放用到的的图片和配置信息。

Kitchen.bat:任务文件执行器（命令行模式）。

Pan.bat:转换文件执行器（命令行模式）。

set-pentaho-env.bat:设置kettle运行的环境变量。

Spoon.bat:打开设计工具（界面方式）。

工具的图标。

start.bat:启动存放在resources目录下的任务配置文件。

说明.txt:使用的规范和注意事项。

3.使用规范和注意事项。

3.1.配置数据库的全局变量。

 在.kettle目录下的kettle.properties文件配置数据库全局变量。

 属性：HostName,DatabaseName,PortNumber,UserName,Password。

 规则：地市名+数据库名+属性名=值,且命名时第一个单词以小写字母作为开头，后面的单词则用大写字母开头。

 如：昆山的ebcmks数据库，配置如下：

 在工具中使用方法为：${ksEbcmksHostName}。

3.2.文件命名规范（不要用汉字）。

 转换文件用操作数据库的表名(大写中文首字母)。

 任务文件用项目名。

 配置启动任务文件用项目名。

 日志文件用“kettlelog_项目名.log”。

3.3.文件存放位置。

 文件统一放到resources目录下。

 存放规则：项目名目录->（transformations目录，jobs目录）,config目录,logs目录；

 其中transformations目录主要存放转换文件，jobs目录存放任务文件，config目录存放配置启动任务文件（需要执行的任务文件配置信息）。

 如昆山：项目名目录为：ks,转换文件目录:transformations,任务文件目录：jobs，配置文件目录:config，日志文件目录:logs。

3.4.配置启动任务文件。

 配置文件统一放到：项目名目录->config目录下。

 文件后缀名为：bat（批处理文件）。

 编写规则：Kitchen.bat -file=%cd%\\resources\任务文件（从项目名目录开始） -level=日志等级 -log=resources\\logs\日志文件（以工具主目录为起始目录），之间用空格分割;

 如：Kitchen.bat -file=%cd%\\resources\\ks\\jobs\\ebcmks.kjb -level=Basic -log=resources\\logs\\kettlelog_ebcmks.log，

其中level等级包括（Basic, Detailed, Debug, Rowlevel, Error, Nothing）。

如下：

3.5.注意事项。

 ）：路径和文件名称不要使用中文。

 ）:在只进行新增操作时，如果两边的表字段数量不一样，应把“表输出”的specify database选中。

 ）：启动任务只需执行主目录下的start.bat文件。

 ）：在任务中选择一个交换时的目录应使用相对路径，如：应把“D:\\kettle\\kettle4.2.0\\resources\\ks\ransformations\\T_WORKFLOW_GZLCSL.ktr”修改为：

 “${KETTLE_HOME}\\resources\\ks\ransformations\\T_WORKFLOW_GZLCSL.ktr”,使用${KETTLE_HOME}全局变量。

 ）：在表里面新增字段后，如果在别的控件（如“表输入”）中没有找到新增的字段，

 此时应操作菜单Tool->Database->Clear Cache，把缓存清除掉。

 ）：在预览数据时，如果数据量比较大的情况下可能会报错，

 此时应把“表输入”的“记录数量”修改为100,正式转换时应把值修改为0。

4.Kettle部署使用。

4.1.启动kettle。

把下载的压缩文件压缩到D盘根目录。

双击kettle4.2.0目录下的Spoon.bat文件，出现kettle欢迎界面如图1。

图1

进入主界面如图2。

题2

4.2.创建转换文件。

4.2.1.使用标识字段实现新增数据。

如：传输人员的轨迹信息。

单击菜单项的文件->新建->转换，创建一个转换文件；保存文件名为：WZXX路径为主目录下的resources\\ks\ransformations\\WZXX.ktr；右键选择“转换设置”，把转换名字修改为：轨迹信息；如图3. 

图3

把左边菜单“输入”下的“表输入”，通过鼠标左键拖到主界面中，并双击“表输入”，

如图4。

图4

单击图4界面中的“新建”按钮,创建源数据连接信息，弹出框如图5。

Connection Name:ebcmks。(连接库的名称)

Connection Type:MS Sql Server。(数据库类型)

Host Name:通过Ctrl+Alt+Space(空格)，选择ksEbcmksHostName（也可直接输入）。（数据库的服务器IP地址）

Database Name:${ksEbcmksDatabaseName}。（数据库名称）

Port Number:${ksEbcmksPortNumber}。（数据库端口号）

User Name:${ksEbcmksUserName}。（数据库用户名）

Password:${ksEbcmksPassword}。（数据库密码）

说明：

使用${value}表示的value值，都是在kettle.properties文件中配置的变量名。

配置格式为：ksEbcmksDatabaseName=ebcmks。

配置好上面的信息之后，可以用“Test”按钮进行测试，如果连接成功，单击“OK”按钮。

图5

在图4中的Sql面板中输入“select * from t_emop_wzxx where isNUll(flag,'0')<>'1'”

可以通过“预览”按钮，查看数据，如果数据量比较大的情况下可能会报错，此时应把“表输入”的“记录数量”修改为100,正式转换时应把值修改为0，单击“OK”。

与“表输入”同理，把左边菜单中的“表输出”用鼠标拖到主界面中，按住Shift键，用鼠标把“表输入”与“表输出”连接起来，如图5。

图5

“表输出”的功能就是往表里面新增数据，在图5中双击“表输出”，在弹出的窗口中单击“新建”按钮，创建目标数据连接信息（与源目标连接信息同理）,配置信息如下，如图6：

Connection Name:datacenter_ks。

Connection Type:MS Sql Server。

Host Name:通过Ctrl+Alt+Space(空格)，选择ksDatacenter_ksHostName（也可直接输入）。Database Name:${ksDatacenter_ksDatabaseName}。

Port Number:${ksDatacenter_ksPortNumber}。

User Name:${ksDatacenter_ksUserName}。

Password:${ksDatacenter_ksPassword}。

图6

表输出配置信息如下（图7）：

数据库连接选为：datacenter_ks。

目标表选择：t_emop_wzxx。

Specify database fileds前面的勾打上。

图7

单击图7中的Database fileds选项卡，如图8，单击Enter filed mapping,在弹出框中单击“猜一猜”，会把相同的字段匹配上，单击“确定”。

图8

把左边菜单中的“脚本”下的“执行SQL脚本”拖到主界面中，并通过鼠标把“表输出”与“执行SQL脚本”连接起来，如图9。

图9

“执行SQL脚本”的功能就是执行里面写sql语句，双击“执行SQL脚本”，配置如下信息（图10）：

数据库连接：ebcmks。

Sql面板中输入：update t_emop_wzxx set flag='1' where uuid='?'。

“执行每一行”前面的勾打上。

“变量替换”前面的勾打上。

 参数中：选中UUID。

图10

运行该转换文件，单击左上角的按钮启动，此时下面会显示执行结果信息，如图11。

图11

4.2.2.使用时间戳实现新增或修改数据。

如：传输前一天的数据。

单击菜单项的文件->新建->转换，创建一个转换文件；保存文件名为：XCJC路径为主目录下的resources\\ks\ransformations\\XCJC.ktr；右键选择“转换设置”，把转换名字修改为：现场检查；如图12，“字段选择”在左边“转换”菜单下，“插入或更新”在左边“输出”菜单下。 

图12

“表输入”配置信息，只查询前昨天的数据，如图13。

图13

“字段选择”的功能可以把前面的字段重命名，把与目标库不同的字段名，重命名为目标库的字段名；其配置信息，如图14，

图14

“插入或更新”的功能是根据关键字找对应的记录，如果找不到则执行新增，否则执行更新，其配置信息，如图15，用来查询的关键字就是用来比较的字段，通过“获取和更新字段”按钮，来获取更新字段；更新字段就是更新目标表的字段，表字段：目标表的字段；流字段：前一步骤传过来的字段；Update:是否更新。

图15

4.2.3.使用时间比较实现新增或修改数据。

如：根据修改时间来判断数据是否需要传输，只有当源数据的修改时间大于目标数据的修改时间时就修改这些数据或源数据是新增数据时就新增这些数据到目标数据库。

单击菜单项的文件->新建->转换，创建一个转换文件；保存文件名为：WRYJBXX路径为主目录下的resources\\ks\ransformations\\WRYJBXX.ktr；右键选择“转换设置”，把转换名字修改为：污染源基本信息；如图16。

“插入”：在左边“输入”菜单下。

“数据库查询”：在左边“查询”菜单下。

“过滤记录”：在左边“Flow”菜单下。

“空操作”：在左边“Flow”菜单下。

“插入或更新”：在左边“输出”菜单下。

图16

表输入配置信息，如图17。

图17

数据库查询就是把前一步骤的数据通过关键字与别的库中的记录进行比较，如图18，

等价于下面查询语句括号里面的内容：

Select wrybh,wrymc,

(select wrybh from datacenter_ks.t_wry_jbxx where wrybh=wry.wrybh and xgsj>=wry.xgsj) as newwrybh

from t_wry_jbxx wry。

查询所需的关键字：与上边括号中where后边的查询条件相同。

查询表返回的值：与上边括号的值的别名相同。

图18

“过滤记录”就是对前面传过来的值进行判断，如图19。 

当NEWWRYBH为空时执行插入/更新操作，否则执行空操作。 

图19

“空操作（什么也不做）”表示一个提示信息，不执行任何操作。

“插入或更新”如图20。

图20

4.2.4.使用对某一数据值比较实现新增或修改数据。

如：通过目标数据表的BH的最大值，作为源数据的比较值，来更新或修改数据。

单击菜单项的文件->新建->转换，创建一个转换文件；保存文件名为：GZLCSL路径为主目录下的resources\\ks\ransformations\\GZLCSL.ktr；右键选择“转换设置”，把转换名字修改为：工作流程实例；如图21。

图21

获取编号最大值，配置信息如图22,查询的是目标数据库的信息。

图22

表输入，配置信息如图23。

Sql面板的语句为：“SELECT * FROM T_WORKFLOW_GZLCSL WHERE BH>?”。

替换Sql语句里的变量：前面的勾打上。

从步骤插入数据：选择获取编号最大值。

执行每一行：前面的勾打上。

图23

插入或更新，配置信息如图24。

图24

4.3.创建任务文件。

单击菜单项的文件->新建->作业，创建一个任务文件；保存文件名为：ebcmks路径为主目录下的resources\\ks\\jobs\\ebcmks.kjb；右键选择“作业设置”，把Job名字修改为：昆山转换任务；配置如图25的信息。

Start:在左边“通用”菜单下。

Transformation,Transformation2,Transformation3,Transformation4:在左边“通用”菜单下。

Success：在左边“通用”菜单下。

图25

Start配置信息如图26。

重复：前面的勾如果打上，表示任务会循环执行。

类型：任务执行方式。

Time of day:1.

表示每天凌晨1点执行。

图26

Transformation配置信息如图27。

Job名称修改为：轨迹信息，其中转换文件名的格式为：${KETTLE_HOME}\\resources\项目名\ransformations\转换文件；

如：${KETTLE_HOME}\\resources\\ks\ransformations\\WZXX.ktr。

Transformation2，Transformation3,Transformation4同理，

job名称，转换文件名分别设为：

Transformation2->job名称：现场检查，转换文件名称：${KETTLE_HOME}\\resources\\ks\ransformations\\XCJC.ktr。

Transformation3->job名称：污染源基本信息，转换文件名称：${KETTLE_HOME}\\resources\\ks\ransformations\\WRYJBXX.ktr。

Transformation4->job名称：工作流程实例，转换文件名称：${KETTLE_HOME}\\resources\\ks\ransformations\\GZLCSL.ktr。

图27

执行结果如图28。

图28

4.4.命令行启动任务。

配置启动任务文件。

在resources\\config目录下新建ebcmks.bat文件。

内容为：

Kitchen.bat -file=%cd%\\resources\\ks\\jobs\\ebcmks.kjb -level=Basic -log=resources\\logs\\kettlelog_ebcmks.log

-file：为需要执行的任务文件，格式为：%cd%\\resources\项目名\\jobs\任务文件。

-level: 日志的等级。

-log:  日志文件，格式为：resources\\logs\日志文件。

之间用空格分隔。

如果有多个任务文件需要执行，需写多个配置文件。

配置好上面信息其保证正确后，就可以运行主目录下的start.bat文件，启动任务。

也可以在服务器中作任务计划，定时执行start.bat文件即可。下载本文