kettle使用笔记(mysql操作篇)

时间:2024-03-31 21:05:07

注:老规矩在不泄露公司信息的前提下操作

4 kettle与mysql

4.1 mysql-mysql数据操作

4.1.1 全库迁移

此操作的目的是一次性将源数据库中的表全部抽取到目标库中,本例子的流程是将ip为XXXXXXXX的mysql数据库中的XXX库中的302张表(每张收10条数据)一次性抽取到pc机的mysql的test库中。整体流程如下:批量获取源数据库的表名和ddl,并且替换掉不能识别的字段类型将其赋值给tablename和ddl两个变量,然后利用传递过来的表名和ddl在目标库批量建表和导入数据。整个流程由2个Job和4个Transformation组成。如下图所示。

首先,“提取表名和ddl”(Transformation),替换掉kettle无法识别的字段类型和报错关键字。如图4.1所示。此处需要注意问题如下:

(1)    字符串替换:将Kettle把DATETIME和TIMESTAMP识别成的UNKNOWN替换为VARCHAR(100)。

(2)    关键字替换:将password,level,end等关键字加上` `。

(3)    本地mysql字符集设置:将kettle和mysql都设置为utf8,不然会影响中文的插入。

kettle使用笔记(mysql操作篇)

图4.1 获取表名和ddl并替换报错字符

然后,“表名称变量和ddl变量”(Transformation)的设置,流程如图4.2所示,将上一步复制到记录的抽取结果取出来然后设置为变量。

kettle使用笔记(mysql操作篇)

图4.2 表名称和ddl变量设置

然后是“入户表结构创建”(Transformation)。将变量传输过来的ddl以sql脚本的形式执行在本地创建表。流程如图4.3所示。选择好对应的数据库,勾选变量替换。

kettle使用笔记(mysql操作篇)

图4.3 入库表结构创建

接下来是“每张表抽10条数”(Transformation),从tablename变量中获取到表名,然后从每张表中取10条数,输出给对应目标库的目标表。流程如图4.4-4.5所示。

kettle使用笔记(mysql操作篇)

图4.4 表输入设置

kettle使用笔记(mysql操作篇)

图4.5 表输出设置

然后,建立创建表的JOB,流程如下所示。其中kettle使用笔记(mysql操作篇)代表不管前一个节点是否执行成功,后一个节点依然会被执行。kettle使用笔记(mysql操作篇)表示上一个节点如果执行报错的话,跳过此环节,接着执行下一个循环。如图4.6所示。

kettle使用笔记(mysql操作篇)

图4.6 创建表

最后是批量建表和导入数据Job创建,整体流程如图4.7所示。点击“创建表”在高级选项卡中勾选“对每一个输入都执行一次”表示要执行变量中的每一条数据。如图4.8所示。

kettle使用笔记(mysql操作篇)

图4.7 全库迁移

kettle使用笔记(mysql操作篇)

图4.8 创建表节点设置

到此整个全库迁移流程创建完毕,执行结果成功301张表,其中一张是kettle读取过来的ddl与源表不一致报错,需要单独抽取。

4.1.2 增量抽取

增量抽取的目的是解决源数据库中表数据实时更新的问题,增量更新的方法有两种,一种是采用当前表中的时间字段的最大值或者上一次增量后产生的sysdate();另一种是采用触发器等工具,把变化的数据捕获,然后进行插入更新。

4.1.2.1 时间增量

本例通过时间去增量抽取。以xxxxx库中的orders表为实验对象,首先通过max_date节点获取目标库的系统时间,然后传输给表输入2,筛选出大于此时间的数据,然后存到目标数据库。流程如图4.9所示。“Max_date”节点和“表输出2”节点的设置如图4.10-4.11所示。

kettle使用笔记(mysql操作篇)

图4.9 增量抽取流程

kettle使用笔记(mysql操作篇)

图4.10 获取目标表系统时间

kettle使用笔记(mysql操作篇)

图4.11 筛选更新变化数据

4.1.2.2 触发器增量

会对源数据库产生影响,不做介绍

 

4.1.3 定时任务开发

结合时间增量抽取的例子,本章对kettle定时任务的开发做讲解,整个流程由一个JOB和两个Transformation组成。首先用“抽取历史数据”将历史数据抽取到目标库。如图4.12所示。

kettle使用笔记(mysql操作篇)

图4.12 抽取历史数据

然后,取目标表的最新时间为时间参数,增量抽取更新数据到目标表,同时将更新数据写一份到本地text文件。如图4.13所示。

kettle使用笔记(mysql操作篇)

图4.13 采用maxdate参数抽取更新数据

最后,新建一个Job,定时执行增量抽取JOB并将执行结果以邮件的形式发送到指定邮箱。如图4.14所示。

kettle使用笔记(mysql操作篇)

图4.14 定时执行增量抽取作业

为了方便测试,设置定时为每天16:03开始增量抽取作业。如图4.15所示。

kettle使用笔记(mysql操作篇)

图4.15 定时执行

但是这样还是要人为的点击“执行”kettle使用笔记(mysql操作篇)按钮才能执行job作业,linux系统可以采用crontab 执行kettle的调用命令。Windows下可以采用bat脚本+任务计划程序的方式来实现自动化执行。过程如下:

首先,编辑调用Kitchen.bat的.bat文件。如图4.16所示。

kettle使用笔记(mysql操作篇)

图4.16 Kitchen.bat的调度

然后,创建windows计划任务,如图4.17所示。测试结果:定时每天16:02执行计划任务,16:03执行增量抽取操作,并发送邮件到指定的邮箱。如图4.18-4.19所示。

kettle使用笔记(mysql操作篇)

图4.17 定时计划

kettle使用笔记(mysql操作篇)

图4.18 计划任务定时执行                    图4.19 定时任务反馈邮件

转载于:https://my.oschina.net/zhouwang93/blog/1619833