一、前言
生产环境中,需要每天执行定时任务,把某个数据库的某些数据处理成需要的形式后、同步到另一个数据库;
由于是不同的数据库,并且生产环境端口与权限比较严格,所以没有使用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相关网址
三、生产使用示例
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可以将某个库的全量信息每天更新到其它库,导出报表用;也可以在同一个库中多个表之间处理数据用;也可以用于主备库。一般是以天为单位,适用于实时性要求不高的数据。