拉链表的说明_拉链表开链闭链

概念讲解

在数据仓库的数据模型设计过程中，经常会遇到这样的需求：
1. 数据量比较大;
2. 表中的部分字段会被update,如用户的地址，产品的描述信息，订单的状态等等;
3. 需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态，
   比如，查看某一个用户在过去某一段时间内，更新过几次等等;
4. 变化的比例和频率不是很大，比如，总共有1000万的会员，每天新增和发生变化的有10万左右;
5. 如果对这边表每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费;

拉链历史表，既能满足反应数据的历史状态，又可以最大程度的节省存储;

举个简单例子，比如有一张订单表,6月20号有3条记录：

到6月21日，表中有5条记录：

到6月22日，表中有6条记录：

数据仓库中对该表的保留方法：

1. 只保留一份全量，则数据和6月22日的记录一样，如果需要查看6月21日订单001的状态，则无法满足；

2. 每天都保留一份全量，则数据仓库中的该表共有14条记录，但好多记录都是重复保存，没有任务变化，如订单002,004，数据量大了，会造成很大的存储浪费；

如果在数据仓库中设计成历史拉链表保存该表，则会有下面这样一张表：

说明：

1. dw_begin_date表示该条记录的生命周期开始时间，dw_end_date表示该条记录的生命周期结束时间；

2. dw_end_date = '9999-12-31'表示该条记录目前处于有效状态；

3. 如果查询当前所有有效的记录，则select * from order_his where dw_end_date = '9999-12-31'

4. 如果查询2012-06-21的历史快照，则select * from order_his where dw_begin_date <= '2012-06-21' and end_date >= '2012-06-21'，这条语句会查询到以下记录：

和源表在6月21日的记录完全一致：

可以看出，这样的历史拉链表，既能满足对历史数据的需求，又能很大程度的节省存储资源；

拉链表实现例子

-- 源数据表的结构
 
create table if not exists dws.dws_saas_user_basics_da
(
user_id int comment '客户id', 
is_parent int comment '是否是父帐号', 
is_payed int comment '是否付费',
fee_type_bg int comment '计费类型', 
func_type int comment '功能版本', 
start_time date comment '套餐开始时间', 
end_time date comment '套餐结束时间'
) COMMENT '客户信息表'
PARTITIONED BY (dt int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
STORED AS ORC
 
 
-- 拉链表的结构
 
create table if not exists dws.dws_saas_user_his_da
(
user_id int comment '客户id, 只是父账号id', 
fee_type int comment '计费类型', 
func_type int comment '功能版本', 
start_time date comment '当前计费类型开始时间', 
end_time date comment '当前计费类型结束时间'
) COMMENT '客户计费类型拉链表(父账号)'
PARTITIONED BY (dt int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
STORED AS ORC
 
 
-- 首先得到修改的和新增的数据
 
create table temp.temp_user_update_da as 
select 
T1.user_id, 
T1.fee_type_bg, 
T1.func_type
from 
dws.dws_saas_user_basics_da as T1 inner join dws.dws_saas_user_basics_da as T2
on T1.dt = ${today} and T2.dt = ${last_day} and T1.is_payed = 1 and T2.is_payed = 1 and T1.is_parent = 1 and T2.is_parent = 1 
where 
T1.dt = ${today} and T2.dt = ${last_day} and T1.is_payed = 1 and T2.is_payed = 1 and T1.is_parent = 1 and T2.is_parent = 1 
and T1.fee_type_bg != T2.fee_type_bg
UNION ALL 
select 
T1.user_id, 
T1.fee_type_bg, 
T1.func_type
from 
dws.dws_saas_user_basics_da as T1 left join dws.dws_saas_user_basics_da as T2
on T1.dt = ${today} and T2.dt = ${last_day} and T1.is_payed = 1 and T2.is_payed = 1 and T1.is_parent = 1 and T2.is_parent = 1 
where 
T1.dt = ${today} and T2.dt = ${last_day} and T1.is_payed = 1 and T2.is_payed = 1 and T1.is_parent = 1 and T2.is_parent = 1 
and T2.user_id is null
 
 
 
-- 然后做成拉链表, 注意在正式运行之前需要先初始化拉链表
-- 然后拉链表dws.dws_user_his_da去和temp.temp_user_update_da关联将已经更新的数据的结束时间设置为当天的时间，将没有更新的数据还是设置为之前的时间
-- 将新增的数据的开始时间设置为当天的时间, 结束时间设置为9999-12-31
 
insert overwrite table dws.dws_user_his_da(dt=${today})
select 
T1.user_id, 
T1.fee_type_bg, 
T1.func_type, 
CASE
WHEN T1.end_time = '9999-12-31' AND T2.user_id IS NOT NULL THEN '${today}'
ELSE T1.end_time
END AS end_time
from 
dws.dws_user_his_da as T1 left join temp.temp_user_update_da as T2 
on T1.dt = ${last_day} and T1.user_id = T2.user_id
where 
T1.dt = ${last_day} 
 
UNION ALL
 
select 
T1.user_id, 
T1.fee_type_bg, 
T1.func_type, 
'${today}' AS start_time,
'9999-12-31' AS end_time
from 
temp.temp_user_update_da as T1
 
 
 
 
-- 初始化语句
 
insert overwrite table dws.dws_saas_user_his_da PARTITION(dt=${init_day})
select 
T1.user_id, 
T1.fee_type_bg, 
T1.func_type, 
T1.start_time,
'9999-12-31' AS end_time
from 
dws.dws_saas_user_basics_da as T1 
where T1.dt = ${init_day} and T1.is_payed = 1 and T1.is_parent = 1;