实时同步，增量ETL方案分享（源表含时间戳，做逻辑删除）

数据源、中间库、目的库均为MySql，ETL工具为kettle(General Availability Release-Version 6.0.1.0-386)

1.源表有一个updated_time时间戳字段，java代码中每更新一条数据，就会用当前时间更新该时间戳。源表只做逻辑删除，即数据的status字段值置为0。

2.我的方案如下：

2.1暂定源表到目的表不做任何清洗（如字段脱敏打星处理，字典映射M替换成男等）。

2.2新建一张etl_log表，一张stage表，一张目的表dest。dest、stage表与源表表结构一致。

etl_log建表语句：

CREATE TABLE `etl_log` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

  `procedure_name` varchar(32) DEFAULT NULL COMMENT '调用过程名称 (扩展字段，用于存储过程走ETL的方案)',

  `updated_count` int(11) DEFAULT NULL COMMENT '更新数据条数',

  `begin_time` datetime DEFAULT NULL COMMENT '开始加载时间',

  `end_time` datetime DEFAULT NULL COMMENT '结束加载时间',

  `cost_dur` varchar(32) DEFAULT NULL COMMENT '加载时间(秒)',

  `updated_time` datetime DEFAULT NULL COMMENT '本次同步中源头表最大的更新时间',

  `table_name` varchar(32) DEFAULT NULL COMMENT '同步的主要表名（可为空，或者多个以，分隔）',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='etl日志表';

2.3

第1步，找出源头表中指定数据，它们的更新时间大于etl_log表中记录的对应同步表中最大更新时间（一开始etl_log表是空表，根据ifnull(max_updatedtime,0)函数，可以获取源表所有数据）。

第2步，将第1步抽出来的数据塞到stage表。

第3步，获取第2步抽出来的数据总条数和最大的updated_time，塞入到etl_log表

第4步，用kettle的“更新/插入”组件，关联stage与dest的主键id，更新剩余字段（该步等同于oracle中的merge或mysql中的replace，有新增的则新增，有更新的走更新，因源表不做物理删除，不存在被删除数据，故可用此组件实现表同步）

3.上述步骤中，第1第2步为一个转换，第3第4为一个转换，将这两个转换整合成一个job，3个文件详见附件。

4.

模拟测试的脚本：

update demo.student t set t.updated_time=CURTIME(),t.name=replace(t.name,'小明','王小明') where id=1;

INSERT INTO  demo.student(NAME,CREATED_TIME,UPDATED_TIME) VALUES('NEWDATA',CURTIME(),CURTIME());

INSERT INTO  demo.student(NAME,CREATED_TIME,UPDATED_TIME) VALUES('NEWDATA',CURTIME(),CURTIME());

5.一些说明、优化、思考、疑问：

5.1或许我还可以给etl_log增加一个字段记录本轮更新的所有数据id号，存成如"1,2,3“这样，便于故障跟踪，主要sql为：

select group_concat(id), count(1) ,max(updated_time) ,'表名' from demo.student_stage

但似乎这步骤有些多余，万一本轮同步的数据很多，group_concat(id)得出的数值会非常大

5.2我的stage表没有做全删全增，因考虑到数据量大，实时同步会带来性能的损耗。

5.3似乎"更新/插入"组件效率较低？有什么替换方案吗？

6.关于调度，我是部署在linux上，用crontab+kettle自带的kitchen来完成的

6.1job的start里边不放置任何调度

6.2用crontab来集成kitchen完成调度，下图是一个1分钟跑1次同步的例子

注意etl.sh里要写#!/bin/sh source /etc/profile，不然crontab调用的时候会不成功。我一开始的sh语句，没有这段话。结果单独跑sh的时候可以实现数据同步，放在crontab的时候就会失败，这是为什么？

6.3下图是调度跑起来之后的进程及crontab的调度日志，从图中可以看出的确1分钟调用了1次，每次kitchen会跑3个进程，同步完成后会自动关掉进程（因之前6.1步骤中作业定时调度中未设置调度），下1分钟，又会开启新的3个进程。问一下，下图蓝框里"but got status 0x0047"是什么意思呢？

6.4关闭调度

关闭调度可以通过清除crontab来实现，命令是crontab -r(但这种会清除所有调度。假设命令crontab etlcron.txt这里的etlcron.txt里有多条调度，它们都会被清除掉，较好的方式就是编辑etlcron.txt，移除不需要的调度)

6.5其它可视化调度

由6.4也看出，调度的监控不是很友好，需要一个便于启停调度的图形化工具。听说kettle企业版有提供图形化的调度，不过我用的是非企业版本。在ukettle社区找到rundeck

http://www.ukettle.org/forum.php?mod=viewthread&tid=256&highlight=rundeck

回头再研究一下，感谢写作者小白。

我也是刚开始摸索kettle，里边还有邮件通知，日志监控，这些东西我都还没有摸熟悉，感觉到它的强大。

另，

大家怎么监控ETL作业？如何监控kettle中 当前总的有效作业数， 正在运行的作业数，等待运行的作业数，报错的作业数等等。有什么好方案可以分享一下吗？

转载请注明出处！

不知道如何在这放附件

附件可在这下载http://www.ukettle.org/forum.php?mod=viewthread&tid=636&page=1&extra=#pid4961