详细记录拉链表的实现过程

面试中被问到了，想了会儿思路混乱没答好，还是理解的不够深刻，重新好好理解记录一下~

拉链表的用途，主要是用来在数仓中记录业务库数据的全部历史信息和当前最新信息，也就是用来实现对渐变维的记录。数仓中对渐变维的记录通常有三种实现方式：

1）直接更新，也就是用业务库中发生变化的数据覆写数仓中的数据，这种实现方式可以保持数仓与业务库数据保持一致，缺点是只能保存当前最新的数据，对这条数据修改之前的历史数据不做维护，一般很少采用这种实现方式，除非历史数据本身就是错误的，没有使用价值。

2）通过在数仓中对经常发生变化的字段新增列来保存历史记录，比如用两个列来记录用户居住地这个属性，分别命名为previous_home和current_ home，当业务库中用户的居住地信息发生变化时，用current_home列来记录变化后的值，用previous_home列记录变化之前的值。这种记录方式能维护的历史变化记录数受限于新增列的个数，适合只需要保存最近的几个历史记录的业务场景。

3）也是常用的拉链表，拉链表可以实现对所有历史数据的维护，通过两个时间字段（例如start_date和end_date）实现对历史数据和当前最新数据的区分。

下面详细记录一下拉链表的实现过程

比如开始业务库中的数据：

第一次全量导入数仓ods层，然后以create_time作为拉链表中的start_date，9999-01-01作为拉链表中end_date字段，导入到dwd层形成最初始的拉链表，像下面这样：

此时所有的记录end_date都为9999-01-01，表示为当前最新数据。

假设过了一天，业务库中的数据发生了变化，变成了下面这样：

也就是新增了赵六，同时李四的address修改为了北京，这个时候业务库数据导入ods层的时候就不必全量导入，只导入在28号这天发生了更改的数据，这里的更改包括新增和修改，导入条件设置为update_time between '2022-12-28 00:00:00' and '2022-12-28 23:59:59'，此时同步到ods层的数据再以update_time为拉链表中start_date，同样以9999-01-01为拉链表中end_date字段，又得到了一张新的拉链表像下面这样：

此时这张拉链表就是只包含最新的记录信息，而上面那张拉链表中包含的既可能有最新的信息，也可能有历史的记录信息，此时我们需要把原始的那张拉链表中的end_date也就是数据的有效期修改一下，如果这条数据被更新了，那就将时间修改为发生更改的前一天，假设原始拉链表为A，新的拉链表为B：

select
	A.id,
	A.name,
	A.address,
	A.create_time,
	A.update_time,
	A.start_date,
	# B.id is not null说明匹配到的数据有更新，A.end_date='9999-01-01'限制只修改更新的这条数据在原始拉链表中最近的生效的这条记录，而不修改它的所有历史记录
	if(B.id is not null and A.end_date='9999-01-01', DATE_SUB(B.update_time, interval 1 day),'9999-01-01') as end_date
from A left join B on A.id = B.id;
1
2
3
4
5
6
7
8
9
10

得到的结果：

最后将修改过end_date的原始拉链表和上面那张新的拉链表做个union all操作就得到了最终的这张结果拉链表：

这张结果拉链表中既保存了业务库中最新的记录数据，又维护了李四修改前的历史数据。

后续对拉链表的更新都是只同步每天发生更新的数据，然后把这部分数据做成一个新的拉链表，再修改原始拉链表中的有效期end_date字段，然后两表union all就行了。

为了方便测试，数据都是在mysql中跑的，使用hive时稍微修改下部分语法和函数就行了，思路还是一样的。