数仓中拉链表(压缩存储)

一、拉链表介绍

1.什么是拉链表

拉链表：记录每条信息的生命周期，一旦一条记录的生命周期结束，就重新开始一条新的记录，并把当前日期放入生效开始日期。
如果当前信息至今有效，在生效结束日期中填入一个极大值（如9999-99-99），如下表（表1）：

image.png

2.为什么要做拉链表

拉链表适合于：数据会发生变化，但是大部分是不变的。
比如：订单信息从未支付、已支付、未发货、已完成等状态经历了一周，大部分时间是不变化的。如果数据量有一定规模，无法按照每日全量的方式保存。比如：1亿用户*365天，每天一份用户信息。（做每日全量效率低）
全量表如下表（表2）：

和拉链表（表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.拉链表制作流程图

订单当日全部数据和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

1. 初始化拉链表（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 

步骤5：整理为每日脚本

设置日期参数，整理为每日脚本，定时执行任务

链接：https://www.jianshu.com/p/cd8081701348