【sql学习之拉链表】

1.拉链表理解

记录历史。记录一个事物从开始，一直到当前状态的所有变化的信息。

字段说明：

start_dt：该条记录的生命周期开始时间
end_dt：该条记录的生命周期结束时间
end_dt=’9999/12/31’表示该条记录目前处于有效状态
如果查询当前所有有效记录，select * from table where end_date=’9999/12/31’
如果查询2021/10/22的历史快照，select * from table where start_dt <=’2021/10/22’ and end_dt >=’2021/10/22’

2021/10/22的历史快照：

2. 拉链表实现方式

以在hive中为例（hive表一般只能进行删除和添加操作，不能进行update）
需要确定拉链表的时间粒度，比如拉链表每天只取一个状态，那如果一天中有3个状态变更，只取最后一个状态。

2.1 需要的信息

① 数据源信息：
数据全量信息，需要用来初始化；
② 每日更新信息（在分区表中添加更新标识字段或创建每日的更新数据表）
每日更新信息的获取方式：
在每日切片数据基础上，取第二天数据与前一天的不同数据，标记为更新。如何比较不同，可对所有字段先进行concat，取其md5进行比较，md5值不相等时，说明有更新。(hive环境需配置md5.jar包)
（取两天的切片数据，以md5字段做关联（full join），若第二天数据的md5不为空，说明为最新生效树，否则为失效数据。）

达到的效果：
对数据生效状态进行标记（生效/失效），删除及修改前数据为“失效”，新增及修改后数据为“生效”

数据变更形式：
数据不变、数据新增、数据删除、数据修改（修改=删除➡新增）
若要找出具体状态，可以用编码字段做关键字段关联（一般编码字段不会进行修改）

（取两天的切片数据，以编码字段做关联（full join），结合编码和md5判断数据变更形式）

2.2 添加标识字段（以编码字段关联）

①查看2021/10/24日数据

② 2021/10/25日数据

③ 两个分区日期对比下，在新一日数据中添加变更标识
1:删除，2：新增，3：修改，0：不变

实现方式：

① 前后两个分区日期的数据对比
② 使用full join （Mysql不支持，hive支持）
③ 关联字段为编码obj_cd，在标识数据变更状态时，进行全字段比较，这里是用concat函数将字段全部合并，生产环境中，一个表可能有几十或几百个字段，合并后的字符串长度会比较长。可以使用Md5函数，将长字符串转化为36位编码。（hive中需导入Md5函数的jar包）

2.3 根据标识字段，创建拉链表

① 首先历史信息存在
历史数据，其启用时间为2021/10/24，结束时间9999/12/31

② 根据变更标识字段，最新一日的更新信息为：

③ 将更新数据与历史数据做关联，更新拉链表起止时间，并追加新数据：

实现方式：

① 在历史数据中标记有变更的数据，更新结束时间end_dt
② 拼接更新数据（修改后数据 + 新增）