拉链表
- 定义:用来记录历史变化,相比每天存储全量数据,可大幅减少数据冗余,可以基于历史变化,统计分析历史变化信息
- 使用场景: 用于记录维度变化场景,记录维度变化,根据维度变化记录,统计聚合,加成生成不同时期历史指标
Hive 拉链表实现
实现原理
- 首次(T+1: 2023-04-01) 同步业务系统全量数据到ods_user_info,并确定记录数据变化时标记字段(如日期变更)
- 将首次全量数据导入到拉链表 dws_user_info_history 信息,记录开始日期(T+1),和截至日期(9999-12-31)
- 记录当天更新数据,存储到另一张ods_user_info_update,用于与拉链表进行关联
- 然后进行第二次拉链表制作,如果更新表数据跟拉链表关联上,则截至日期置为T+1 (2023-04-01),若关联不上,则仍然是有效数据；然后再将更新数据ods_user_info_update 与关联后的数据进行union
- 生成最终拉链表
例子
ods 表
-- ods表 DROP TABLE IF EXISTS ods_user_info; create table if not exists ods_user_info( `id` string COMMENT '用户id', `name` string COMMENT '姓名', `register_date` string COMMENT '注册日期' )COMMENT '用户信息 表' PARTITIONED BY(dt STRING); INSERT OVERWRITE TABLE ods_user_info PARTITION(dt = '2023-04-01') select name,email,'2023-04-01' from sdi_hljy_hrr_rzzp_jlxt.sdi_bs_employee– 更新增量表
-- 增量表 DROP TABLE IF EXISTS ods_user_info_update; create table if not exists ods_user_info_update( `id` string COMMENT '用户id', `name` string COMMENT '姓名', `register_date` string COMMENT '注册日期' )COMMENT '用户信息更新表' PARTITIONED BY(dt STRING); INSERT OVERWRITE TABLE ods_user_info_update PARTITION(dt = '2023-04-02') select name,email,'2023-04-02' from sdi_hljy_hrr_rzzp_jlxt.sdi_bs_employee limit 10拉链表
DROP TABLE IF EXISTS dws_user_info_history; create table if not exists dws_user_info_history( `id` string COMMENT '用户id', `name` string COMMENT '姓名', `register_date` string COMMENT '注册日期' , t_start_date string COMMENT '开始日期' , t_end_date string COMMENT '结束日期' ) COMMENT '用户信息更新表'首次插入
INSERT INTO TABLE dws_user_info_history select id,name,'2023-04-01','2023-04-01','9999-12-31' from ods_user_info后续更新拉链
INSERT OVERWRITE TABLE dws_user_info_history SELECT A.id, A.name, A.register_date, A.t_start_date, -- 更新表关联上,则该条记录失效 CASE WHEN A.t_end_date = '9999-12-31' AND B.id IS NOT NULL THEN '2023-04-01' -- 未关联上,则仍然有效 ELSE A.t_end_date END AS t_end_date FROM dws_user_info_history A LEFT JOIN ods_user_info_update B on A.id = B.id -- 与新增的数据进行合并 UNION SELECT C.id, C.name, C.register_date, '2023-04-02' AS t_start_date, '9999-12-31' AS t_end_date FROM ods_user_info_update C
