拉链表

概述

过程:

1. 新建初始表, 增加开始时间, 结束时间, 两个字段
2. 新建临时表, 包含开始时间, 与结束时间
3. 修改初始表(旧表)中变化数据对应的结束时间后, 将初始表(旧表)数据插入到临时表中, 之后UNION ALL 当天的新增与变化数据.
4. 将临时表覆盖初始表(或旧表)的数据

核心是生成临时表的两步:

1. 只要修改旧表中变化数据的日期(当天日期-1)
2. 然后UNION ALL 当天变化和新增的数据

制作过程

数据准备：

DELETE  FROM user_info WHERE id<100;

UPDATE user_info SET `operate_time`=NULL

①建表

创建拉链表：

drop table if exists dwd_dim_user_info_his;
create external table dwd_dim_user_info_his(
    `id` string COMMENT '用户id',
    `name` string COMMENT '姓名',
    `birthday` string COMMENT '生日',
    `gender` string COMMENT '性别',
    `email` string COMMENT '邮箱',
    `user_level` string COMMENT '用户等级',
    `create_time` string COMMENT '创建时间',
    `operate_time` string COMMENT '操作时间',
    `start_date`  string COMMENT '有效开始日期',
    `end_date`  string COMMENT '有效结束日期'
) COMMENT '订单拉链表'
stored as parquet
location '/dwd/dwd_dim_user_info_his/'
tblproperties ("parquet.compression"="lzo");

创建临时拉链表：

drop table if exists dwd_dim_user_info_his_tmp;
create external table dwd_dim_user_info_his_tmp(
    `id` string COMMENT '用户id',
    `name` string COMMENT '姓名',
    `birthday` string COMMENT '生日',
    `gender` string COMMENT '性别',
    `email` string COMMENT '邮箱',
    `user_level` string COMMENT '用户等级',
    `create_time` string COMMENT '创建时间',
    `operate_time` string COMMENT '操作时间',
    `start_date`  string COMMENT '有效开始日期',
    `end_date`  string COMMENT '有效结束日期'
) COMMENT '订单拉链临时表'
stored as parquet
location '/dwd/dwd_dim_user_info_his_tmp/'
tblproperties ("parquet.compression"="lzo");

②向拉链表导入初始数据（第一天的数据）

insert overwrite TABLE  dwd_dim_user_info_his
select
    *,'9999-99-99'
FROM ods_user_info
where dt='2020-03-29'

③之后每一次导入时，都需要把当日新增的数据和历史数据进行合并，合并后先导入到临时表，再导入到原始表

合并：①为新增和变化的数据，添加startdate=新增和变化的日期，enddate=9999-99-99

​ ②历史表中发生变化的数据，enddate=当前日期-1

​ ③历史表没有发生变化的数据，保持原貌

insert overwrite TABLE  dwd_dim_user_info_his_tmp
select
    old.id,
    old.name, old.birthday, old.gender, old.email, old.user_level,
    old.create_time, old.operate_time, old.start_date,
    if (`new`.id is not null and old.end_date='9999-99-99',date_sub('2020-03-30',1),old.end_date)
FROM dwd_dim_user_info_his old
left join
(select
    *
FROM ods_user_info
where dt='2020-03-30') new
on old.id=new.id
UNION all
select
    id, name, birthday, gender, email, user_level, create_time,
    operate_time, '2020-03-30', '9999-99-99'
FROM ods_user_info
where dt='2020-03-30'

④将临时表数据导入覆盖到原始表

insert overwrite table dwd_dim_user_info_his select * from dwd_dim_user_info_his_tmp

提高查询效率

• 为start_date与end_date字段增加索引
• 保存整个表的数据, 但是只导出部分数据, 例如近3个月的数据

拉链表的应用场景与好处可以参考

Talking about the zipper table of data warehouse (principle, design and implementation in Hive) - Programmer Sought