Kettle（三）拉链表

拉链表，顾名思义，就是记录一个事物从开始，一直到当前状态的所有变化的信息。

1、示例

例如下面的流水表，code是会更新的字段。

那么对应的拉链表设计如下：其中uid是自增主键。

需要更详细了解可以参考 漫谈数据仓库之拉链表（原理、设计以及在Hive中的实现）

2、Kettle实现分析

上一节的参考文章用SQL实现，因为不用优化所以比较混乱；Kettle的速度比较慢，所以要理清楚每一个步骤，不要有多余的步骤拖慢本来就不快的速度：

拉链表数据分两种情况：
1、某个id首次录入，那么显然start_time就是update_time，end_time = 9999/12/31 0:00:00;
2、某个id更新code，那么当前的code，其end_time需要更改为update_time；另外要新建一条记录，对应更新的code，start_time就是update_time，end_time = 9999/12/31 0:00:00;

其实，无论是首次录入还是更新，流水表的记录方式都是一样的。所以拉链表更新就是两个操作，更新原有id的code和插入新id的数据.

需要注意，我这里设计的时间都是日期格式，流水表数据抽取的话要先进行去重，因为会有同一天多次修改的情况。

3、Kettle实现步骤

假设要更新今天(2020-09-01)的数据。

3.1 更新

首先，仅需要更新一部分数据，拉链表zipper_table最新状态和流水表source今天最新都存在的数据，SQl代码如下：

SELECT
	id 
FROM
	( SELECT *, CURDATE( ) end_time FROM source WHERE update_time >= CURDATE( ) ) a
	JOIN ( SELECT id FROM zipper_table WHERE end_time = '9999-12-31 00:00:00' ) b 
	ON a.id = b.id

但是这样要将zipper_table数据抽出，所以应该直接使用更新组件，忽略掉查找不到id的失败条目。
上面那种做法会出现将某id历史记录全部修改end_time的错误，还是要按套路走。

3.2 插入

这里选用表输出组件，因为当天的所有source数据都要插入，我觉得速度会比插入快。

3.2 流程

直接job调度两个转换，只是增加了一个查询当天更新记录的步骤，如果为零就直接记录日志，不执行。

这里有两个步骤，而且必须是先更新后插入，这里要强调一下。
本来用job调度两个转换就可以了，但是我觉得因为需要的数据都是一样的，为了避免重复操作数据库，所以我直接在同一个转换中完成两个步骤，但是我并不确定会有速度上的提升。另外看到插入步骤增加了一个阻塞数据的组件，是用来控制插入步骤要等待更新完成。
全部流程如下：

4、Kettle实现细节

4.1 更新

注意表输入一定要对id进行排序，不然的话记录集连接组件不能正常工作。
因为更新数据要将end_time设为当天，但是没找到合适的组件操作，所以直接在SQL增加。

4.2 插入

5、速度测试

测试数据大概是source表数据141000条，其中当天的更新的数据10000条，当天需要插入的数据1000条。

5.1 csv数据导入

约13000条/秒。

5.2 首次导入数据

就是将130000条数据增加一个end_time字段，更新到zipper_table。约8000条/秒。

5.3 更新

更新选择忽略查询失败速度大概每秒十几条左右，实际的速度可能更低。

5.4 插入

接近每秒100条

6、总结与经验

我这里不再进行优化，因为优化这件事是需要根据具体问题进行的。比如我这设计的方式，就是适合每天更新（就是原有id更新的code）的量不多的情况，如果某个项目每天需要更新的数据特别多，每秒十几条已经完全不可接受，那就要考虑具体的优化措施。比如，对需要更新的id先删除再做插入，或者使用其他组件，更换流程，等等。

经验1 MYSQl 的时间格式，timestamp和date、datetime范围是不一样的。date的时间范围是 ‘1000-01-01’ – ‘9999-12-31’，而timestamp范围是 ‘1970-01-01 00:00:01’ UTC – ‘2038-01-19 03:14:07’ UTC。
经验2 记录集连接之前需要排序

经验3 数据输入后可以增加常量，但是想要增加变量，比如时间，可以直接在SQL里面加。
经验4 更新组件，如果勾选忽略查询失败是可以多对少更新的。就是有一万条记录，但是与目标库的同id的只有1000条，但是又不想提前处理知道是哪1000条，忽略查询失败是可以只更新这1000条数据的。