kettle从零开始到生产使用

一、前言

生产环境中，需要每天执行定时任务，把某个数据库的某些数据处理成需要的形式后、同步到另一个数据库；

由于是不同的数据库，并且生产环境端口与权限比较严格，所以没有使用sql的存储过程来同步数据；

而是使用kettle+crontab实现的。

在此总结下生产环境中kettle的使用方法。

二、准备工作

1.kettle相关

(1)kettle，是 PDI 以前的名称，PDI 的全称是Pentaho Data Integeration。

首先要下载kettle，本人使用的是7.1版本的(pdi-ce-7.1.0.0-12.zip)；下载地址：
https://sourceforge.net/projects/pentaho/files/Data%20Integration/

下载完成后，解压，会有一个data-intergration文件夹，进入后，运行Spoon.bat就可以打开kettle界面了。

备注：使用kettle前，需要先把jdk装好。

(2)接下来，如果是windows环境下，可以使用everything软件搜索.kettle文件夹，位置一般在C:\Users\用户名\.kettle；

可以在.kettle文件夹下创建一个kettle.properties文件，在这个properties文件中，可以配置一些常用的参数，例如数据库连接等参数，如下：

KJB_PATH=C:/home/admin/kettle/kjb
KTR_PATH=C:/home/admin/kettle/ktr

SOURCE_URL=10.123.123.1
SOURCE_PORT=3306
SOURCE_DATABASE=prod?autoReconnect=true&failOverReadOnly=false&connectTimeout=0&socketTimeout=0&allowMultiQueries=true&maxReconnects=100&zeroDateTimeBehavior=convertToNull
SOURCE_USERNAME=root
SOURCE_PASSWORD=root


TARGET_URL=10.123.123.2
TARGET_PORT=3306
TARGET_DATABASE=prod2?autoReconnect=true&failOverReadOnly=false&connectTimeout=0&socketTimeout=0&allowMultiQueries=true&maxReconnects=100&zeroDateTimeBehavior=convertToNull
TARGET_USERNAME=root2
TARGET_PASSWORD=root2

这样配置后，后续写kettle的ktr与kjb文件时，就可以用这些参数了。

linux下安装kettle后，可以用find命令搜索.kettle文件夹的位置，一般在安装后的文件夹中

(3)kettle工具中，可以新建2种类型的文件：

转换(后缀为ktr)，可以执行具体的操作；例如使用表输入、表输出。
作业(后缀为kjb)，可以给多个转换文件ktr编排执行顺序，也可以给其它作业文件kjb编排执行顺序。

(4)linux系统中，假设ktr与kjb文件准备好了，下一步可以编写一个sh文件，通过sh文件来执行kjb文件。（windows环境下则可以编写一个bat文件）

(5)linux系统中，如果想让这个sh定时执行，就可以使用crontab。(windows环境就用windows定时任务；不过服务器一般都是linux的，windows也就是本地自测、偶尔调用一次，一般不用设置定时任务)

2.crontab相关
(1)crontab是linux系统中的定时任务功能，可以定时执行sh文件。

(2)crontab基本命令：
service crond start //启动服务
service crond stop //关闭服务
service crond restart //重启服务
service crond reload //重新载入配置

(3)crontab -l
这个命令可以查看当前已配置的定时任务

(4)crontab -e
这个命令可以编辑当前已配置的定时任务

(5)crontab定时任务时间格式

分 时 日 月 星期
00 11 *  *  *

//每个周一到周五的9点执行一次、11点执行一次
//00 9,11 *  *  1-5

(6)crontab的定时任务的注释符号：#

(7)crontab相关网址
https://blog.csdn.net/cm_0205/article/details/100210584
https://www.cnblogs.com/kenshinobiy/p/7685229.html

三、生产使用示例

1.假设要从一个系统的student表中查询信息，进行简单处理后，保存到另一个系统的sync_student表中。

2.运行Spoon.bat，打开kettle。

3.新建一个转换(ktr)
(1)点击左侧"主对象树"->“转换”->“转换1”，找到"DB连接"，右击，新建。
(2)选择连接类型，例如MySql；连接方式，Native(JDBC)；然后输入数据库连接参数，参考如下：

连接名称：source
主机名称：${SOURCE_URL}
数据库名称：${SOURCE_DATABASE}
端口号：${SOURCE_PORT}
用户名：${SOURCE_USERNAME}
密码：${SOURCE_PASSWORD}

●这里的$符，指的就是kettle.properties中配置的内容
●密码那一栏，虽然看不见，但是也可以输入为$形式的
然后，可以点击测试，没有问题后，点击确认。
(3)仿照上一步，创建另一个数据库的数据库连接，参考如下：

连接名称：target
主机名称：${TARGET_URL}
数据库名称：${TARGET_DATABASE}
端口号：${TARGET_PORT}
用户名：${TARGET_USERNAME}
密码：${TARGET_PASSWORD}

(4)从左侧的"核心对象"->“输入"中，找到"表输入”，拖到右侧工作区。
(5)双击"表输入"，打开界面，“数据库连接"选择"source”(上方配置的连接名)，然后写sql，样例如下：

select user_id,user_name,
CASE gender 
     WHEN '男' THEN '1' 
     WHEN '女' THEN '0'
     ELSE '2'  
     END
     as sex
from student     
where create_date > ${nowbeforethree}

●这里的${nowbeforethree}是一个变量，它的值在kjb中可以设置，下面会讲到。
写完后，确定。
(6)在左边"核心对象"->“输出"中找到"插入/更新”，拖动到右侧工作区。
(7)选中"表输入"，按住shift，从"表输入"拖动到"插入/更新"上，用线连起来。
(8)双击"插入/更新"，打开界面，进行设置：
选择数据库连接为"target"(上方配置的)，目标表为"sync_student"，提交记录数量为"1000"(默认100)；
下面是2个主要设置：
用来查询的关键字：一般写一行就够了，其中"表字段"指的是目标表的字段，例如user_id；比较符选=；流里的字段，指的是表输入中执行sql得到的结果流，也选user_id。整个意思是当流里的user_id等于表里的user_id时，执行update操作，如果流里的user_id在表里不存在，则执行insert操作。
更新字段：可以点击"获取和更新字段"，就会自动填写一部分(一般表里和流里的字段名是一样的，不一样的话，sql加as)；也可以自己选表字段与流字段的对应关系，"更新"栏一般选Y即可。
编辑完成后，点击确定。
(9)在同步数据时，某行数据可能因为一些原因insert/update失败，为了定位是哪一行，继续进行以下步骤。
(10)点击左侧"核心对象"->“转换”，找到"增加常量"，拖入右侧工作区。
(11)按住shift，从"插入/更新"拖到"增加常量"，用线连起来，会出现一个选项，选第二个"错误处理步骤"。
(12)单击线中间的红色X号，出现对话框，填写内容样例如下：

错误数列名：errnum
错误描述列名：errdesc
错误列的列名：errname
错误编码列名：errcode

这样配置后，当kettle执行insert/update、某些行数据出错时，就会产生一个错误流，流到"增加常量"步骤，其中errnum表示有几行错误了；errdesc表示错误描述，会有错误的原因；errname是错误列的列名，如果是某列导致的错误，就会有名字，如果是整个行的错误，就会为null；errcode表示错误码，指名是哪一类型的错误。
(13)双击"增加常量"，进行以下设置：

名称：tableName
类型：String
长度：32
值：${error_table}
设为空串？否

这样配置后，名称为tableName，会在流中增加一个字段tableName；值为error_table，后续kjb文件中会设置一个参数error_table为"student"，表示这个kjb对student表进行了操作，出错时就知道是对哪个表的操作出错了。
(14)从左侧找到"核心对象"->“输出"中的"表输出”，拖到右侧工作区。
(15)按住shift，从"增加常量"连线到"表输出"。
(16)双击"表输出"，选择数据库连接为"target"，提交记录数量"1000"，目标表"student_errorlog"，勾选"指定数据库字段"，然后切换到"数据库字段"，填写内容参考如下：

表字段            流字段
tableName        tableName
error_user_id    user_id
errnum           errnum
errdesc          errdesc
errname          errname
errcode          errcode

这个配置的意思是，"target"连接的数据库中，有一个自己创建的表student_errlog，当kettle执行insert/update出错时，就会把错误信息存入这张表，就能定位到是哪行数据同步失败了(user_id)。
(17)到此，转换ktr编写完成，共4个步骤，3条连线；可以保存一下了。

4.新建一个作业(kjb)
(1)从左侧"核心对象"->“通用"中，把"start”、“转换”、“设置变量”、“成功"拖入右侧工作区，各一个。
(2)按住shift连线，start->设置变量->转换->成功
(3)双击"设置变量”，变量范围"当前作业有效"，内容如下：

变量名             值                                     变量有效范围
nowbeforethree    date_sub(curdate(),interval 3 DAY)     当前作业有效
error_table       student                                当前作业有效

设置后，后续执行转换时，就会将$中的变量名替换为值。
(4)双击"转换"，选择转换文件的路径，就是刚才写好的ktr文件，例如:

Transformation: ${KTR_PATH}\student2SyncStudent.ktr

其中$部分是kettle.properties配置的，当然对应的ktr文件要放在那个位置。
(5)这样，作业文件kjb也就写好了；实际生产上应该会有多个转换与其它作业也需要执行，安排好执行顺序、连好线就可以了。

5.创建sh文件
为了在linux上执行kettle的kjb文件，在此创建一个sh文件。内容样例如下：

LOG_DATE='date +%Y%m%d'
LOG_PATH=/home/admin/kettle/logs/student_${LOG_DATE}.log
KJB_PATH=/home/admin/kettle/kjb
/home/admin/kettle/data-intergration/kitchen.sh -file=${KJB_PATH}/student2SyncStudent.kjb >> ${LOG_PATH} 2>&1

这个sh实际是通过kettle的kichen.sh执行了kjb文件，并把日志输出到了指定位置。
可以单独执行一次这个sh，就相当于单独执行了一次kjb。

6.使用crontab设置定时执行
(1)由于生产需要这个数据同步的操作每天都定时执行，所以要使用crontab。
(2)执行命令

crontab -e

进入定时任务编辑模式，输入一行新文本，样例如下：

00 11 * * * /home/admin/kettle/shell/student2SyncStudent.sh >> /home/admin/kettle/logs/cron.log 2>&1

这个sh会每天11点执行一次。

由于student2SyncStudent.sh已经打印日志了，所以这个其实不会打日志到cron.log；除非student2SyncStudent.sh执行失败。

然后保存：

:wq

可以检查一下：

crontab -l

如果看到了自己新添加的定时任务，就说明设置成功了。

7.到此，整个步骤就结束了，使用kettle+crontab实现了不同数据库的定时同步数据功能。

四、相关笔记

1.kettle自己也可以实现定时任务，不过需要一直开启kettle程序才可以；上方样例中没有那样做，只是使用了kettle的kitchen.sh执行kjb而已，定时功能是crontab实现的。
2.sql存储过程也能实现数据同步，不过对于不同位置的数据库、不同软件的数据库之间数据同步，可能实现较为复杂。
3.生产环境中，跑内容较多的140多万条数据同步到其它库，用时40分钟左右。内容较少的(某几天的增量数据)，一般5-10分钟左右。
4.生产环境中，kettle可以将某个库的全量信息每天更新到其它库，导出报表用；也可以在同一个库中多个表之间处理数据用；也可以用于主备库。一般是以天为单位，适用于实时性要求不高的数据。