一、拉链表介绍
1.什么是拉链表
拉链表：记录每条信息的生命周期，一旦一条记录的生命周期结束，就重新开始一条新的记录，并把当前日期放入生效开始日期。
如果当前信息至今有效，在生效结束日期中填入一个极大值（如9999-99-99），如下表（表1）：
image.png
2.为什么要做拉链表
拉链表适合于：数据会发生变化，但是大部分是不变的。
比如：订单信息从未支付、已支付、未发货、已完成等状态经历了一周，大部分时间是不变化的。如果数据量有一定规模，无法按照每日全量的方式保存。比如：1亿用户*365天，每天一份用户信息。（做每日全量效率低）
全量表如下表（表2）：
image.png
和拉链表（表1）对比可以看出拉链表的优势。
3.如何是用拉链表
通过，生效开始日期<=某个日期 且 生效结束日期>=某个日期，能够得到某个时间点的数据全量切片。
例如：
select * from dw.t_order_info_his where start_date<='2020-01-01' and end_date>='2020-01-01'
4.拉链表形成过程
image.png
5.拉链表制作流程图
image.png
订单当日全部数据和mysql中每天变化的数据拼接在一起，形成一个新的临时拉链表数据。用临时的拉链表覆盖旧的拉链表数据。（这就解决了hive表中数据不能更新的问题）
6.拉链表制作过程代码层面
步骤一：初始化拉链表
1）生成原始订单表dw.t_order_info并插入数据（2020-01-01开始到2020-01-02两天数据）
create table dw.t_order_info(
`id` string COMMENT '订单编号',
`total_amount` decimal(10,2) COMMENT '订单金额',
`order_status` string COMMENT '订单状态',
`create_time` string COMMENT '创建时间',
`operate_time` string COMMENT '操作时间'
) COMMENT '订单表'
stored as parquet；
insert into online.t_order_info (id,total_amount,order_status,create_time,operate_time)values('1',100,'0','2020-01-01','2020-01-01');
insert into online.t_order_info (id,total_amount,order_status,create_time,operate_time)values('2',100,'0','2020-01-01','2020-01-01');
insert into online.t_order_info (id,total_amount,order_status,create_time,operate_time)values('3',100,'1','2020-01-01','2020-01-01');
insert into online.t_order_info (id,total_amount,order_status,create_time,operate_time)values('2',100,'1','2020-01-01','2020-01-02');
insert into online.t_order_info (id,total_amount,order_status,create_time,operate_time)values('4',100,'1','2020-01-02','2020-01-02');
insert into online.t_order_info (id,total_amount,order_status,create_time,operate_time)values('5',100,'1','2020-01-02','2020-01-02');
2）建立拉链表dw.t_order_info_his
create table dw.t_order_info_his(
`id` string COMMENT '订单编号',
`total_amount` decimal(10,2) COMMENT '订单金额',
`order_status` string COMMENT '订单状态',
`create_time` string COMMENT '创建时间',
`operate_time` string COMMENT '操作时间',
`start_date` string COMMENT '有效开始日期',
`end_date` string COMMENT '有效结束日期'
) COMMENT '订单拉链表'
stored as parquet
- 初始化拉链表（2020-01-01数据）
insert into dw.t_order_info_his
select id,total_amount,order_status,create_time,operate_time,'2020-01-01' as start_date,'9999-99-99' end_date from dw.t_order_info a
where a.operate_time='2020-01-01'
步骤二：制作当日变动数据（包括新增，修改）每日执行
1）根据原始订单表dw.t_order_info的操作时间可得到变化记录
select
*
from dw.t_order_info
where operate_time='2020-01-02'
步骤三：合并变动信息，并追加新增信息，插入到临时表中
1）建立拉链临时表
create table dw.t_order_info_his_tmp(
`id` string COMMENT '订单编号',
`total_amount` decimal(10,2) COMMENT '订单金额',
`order_status` string COMMENT '订单状态',
`create_time` string COMMENT '创建时间',
`operate_time` string COMMENT '操作时间',
`start_date` string COMMENT '有效开始日期',
`end_date` string COMMENT '有效结束日期'
) COMMENT '订单拉链临时表'
stored as parquet
2)合并变动信息插入到临时表中
insert overwrite table dw.t_order_info_his_tmp
select * from
(
select
id,
total_amount,
order_status,
create_time,
operate_time,
'2020-01-02' start_date,
'9999-99-99' end_date
from dw.t_order_info where operate_time='2020-01-02'
union all
select oh.id,
oh.total_amount,
oh.order_status,
oh.create_time,
oh.operate_time,
oh.start_date,
if(oi.id is null, oh.end_date, date_add(oi.operate_time,-1)) end_date
from dw.t_order_info_his oh left join
(
select
*
from dw.t_order_info
where operate_time='2020-01-02'
) oi
on oh.id=oi.id and oh.end_date='9999-99-99'
)his
order by his.id, start_date;
步骤四：把临时表覆盖给拉链表
insert overwrite table dw.t_order_info_his
select * from dw.t_order_info_his_tmp;
查询dw.t_order_info_his表，可知得到我们想要的拉链表了，如下图：
select * from dw.t_order_info_his
image.png
步骤5：整理为每日脚本
设置日期参数，整理为每日脚本，定时执行任务
网友评论