拉链表及常见问题

拉链表的使用场景

1. 存储空间大：数据同步时，数据量规模较大，按照每日全量的方式保存效率很低
2. 字段变更：上亿数据量，表中部分字段更新
3. 查询历史状态
4. 统计历史行为次数
   这些场景下就需要使用拉链表了，它是数仓的一种存储方式，记录历史状态，从一个事件的开始一直到当前状态的所有变化，维护历史状态以及当前最新状态数据。

拉链表的实现逻辑

新增两个字段 ：start_dt生效时间,end_dt失效时间
end_dt='9999-12-31’记录该条记录当前处于有效状态

举个栗子

2021-08-25新增三笔待支付订单，此时三比订单的开始时间和结束时间分别是：2021-08-25 、9999-12-31

2021-08-26第二笔订单完成了支付，那么我们需要执行的操作有两个：

1. 将第二笔订单的待支付状态数据end_dt修改为2021-08-25
2. 新增一条支付完成状态数据，start_dt=‘2021-08-26’ end_dt=‘9999-12-31’

代码实现

insert overwrite table dwd.order_info partition(date_id='2021-08-16')
select 
	t1.order_id
	,t1.order_status
	,t1.create_time
	,t1.update_time
	,t1.start_dt
	,case when t1.end_dt='9999-12-31' and t2.order_id is not null then t1.date_id else t1.end_dt end as end_dt
from
dwd.order_info t1
left join ods.order_info_20210816 t2
on t1.order_id = t2.order_id
where t1.date_id='2021-08-15'

union all

select 
	t1.order_id
	,t1.order_status
	,t1.create_time
	,t1.update_time
	,to_date(update_time) as start_dt
	,'9999-12-31' as end_dt
	from ods.order_info_20210816 t1

结果验证

• 如果查询当前所有有效记录select * from user where dt=‘9999-12-31’
• 如果查询2021-08-15的历史快照 select * from user where start_dt<=‘2021-08-15’ and end_dt>=‘2021-08-15’

拉链表优化：

• 对生效开始时间和结束时间做索引
• 保留部分历史数据，结合业务比如只对外提供近3个月的数据

拉链表如果某一天的数据是错误的该如何处理？

数据回滚到错误数据的前一天，然后重跑相关任务及下游数据