拉链表理解分析

链表理解分析"/>

拉链表理解分析

一、概述
拉链表是一种满足生产需求的表，主要用于历史记录。如下图

最后两列即为拉链，记录数据的生效时间与失效时间，同一个orderid的失效时间与下一次生效时间总是互相对应。
在数据仓库的设计过程中，由于一些表的数据量很大，即使压缩后仍有1~200G，加之hdfs储存备份副本，仍会占用大量的存储空间。
当用户更改状态时，表内数据（昵称、手机号等）可以会被一些操作如update覆盖掉，导致数据丢失。
有时需要统计每一天或者每个节点的状态数据、快照等。
部分表中的记录变化的比例和频率不是很大。

二、实例
这是我们每一天的数据表orders 。

这是贴源层的ods_orders表。

这是要求的数据表

========================================================
创建2021/11/25（第一天）的orders表

create table orders(
orderid int,id int, name string,status string,
create_date string,modified_date string 
)
row format delimited fields 
terminated by '\t';

将建好的2021-11-25的orders.txt文件导入orders表

select * from orders;1	1008	cq	创建	2021/11/25	2021/11/25
2	3023	zm	创建	2021/11/25	2021/11/25
3	3585	yy	创建	2021/11/25	2021/11/25

创建ods_orders表

create table ods_orders(
orderid int,id int, name string,status string,
create_date string,modified_date string
)
partitioned by (date string)
row format delimited fields terminated by '\t';

将orders表数据导入（增加date分区）

insert overwrite table ods_orders partition(day='2021-11-25')
select * from orders;select * from ods_orders;1	1008	cq	创建	2021/11/25	2021/11/25	2021-11-25
2	3023	zm	创建	2021/11/25	2021/11/25	2021-11-25
3	3585	yy	创建	2021/11/25	2021/11/25	2021-11-25

创建dw_orders

create table dw_orders(
orderid int,id int, name string,status string,
create_date string,modified_date string,
start_date string,end_date string
)row format delimited fields terminated by '\t';

将ods_orders表数据导入（增加start_date和end_date列）

insert overwrite table dw_orders
select orderid,id,name,status,create_date,
modified_date,create_date,'9999-12-31' 
from ods_orders
where date='2021-11-25';select * from dw_orders;1	1008	cq	创建	2021/11/25	2021/11/25	2021/11/25	9999-12-31
2	3023	zm	创建	2021/11/25	2021/11/25	2021/11/25	9999-12-31
3	3585	yy	创建	2021/11/25	2021/11/25	2021/11/25	9999-12-31

这些就是2021/11/25日记录的数据

将建好的2021-11-26的orders.txt文件导入orders表

select * from orders;1	1008	cq	创建	2021/11/25	2021/11/25
2	3023	zm	创建	2021/11/25	2021/11/25
3	3585	yy	创建	2021/11/25	2021/11/25
1	1008	cq	支付	2021/11/25	2021/11/26
2	3023	zm	支付	2021/11/25	2021/11/26
4	3297	th	创建	2021/11/26	2021/11/26
5	6824	mm	创建	2021/11/26	2021/11/26

将2021-11-26新增的数据导入ods_orders表（分区为2021-11-26）

insert overwrite table ods_orders partition(date='2021-11-26')
select * from orders
where modified_date='2021/11/26';

查看表

 select * from ods_orders;1	1008	cq	创建	2021/11/25	2021/11/25	2021-11-25
2	3023	zm	创建	2021/11/25	2021/11/25	2021-11-25
3	3585	yy	创建	2021/11/25	2021/11/25	2021-11-25
1	1008	cq	支付	2021/11/25	2021/11/26	2021-11-26
2	3023	zm	支付	2021/11/25	2021/11/26	2021-11-26
4	3297	th	创建	2021/11/26	2021/11/26	2021-11-26
5	6824	mm	创建	2021/11/26	2021/11/26	2021-11-26

查看分区

show partitions ods_orders;date=2021-11-25
date=2021-11-26

将修改内容导入dw_orders表
注：可以将新的dw_orders表分为两部分
一部分是更新已有的发生更改的数据
另一部分是添加新增的数据
最终结果为将两部分表拼接到一起并进行排序

第一部分：
根据已修改后的ods_orders表与未修改的dw_orders表进行比较，
得出更改的数据（因为只考虑更新数据，故以dw_orders为主采用left join）

select
t1.orderid,
t1.id,
t1.name,
t1.status,
t1.create_date,
t1.modified_date,
t1.start_date,
case when t2.orderid is not null and t1.end_date>'2021/11/26' then '2021/11/26' else t1.end_date 
end end_date
from dw_orders t1
left join 
(select 
orderid ,modified_date 
from ods_orders 
where date='2021-11-26') t2
on t1.orderid=t2.orderid;1	1008	cq	创建	2021/11/25	2021/11/25	2021/11/25	2021/11/26
2	3023	zm	创建	2021/11/25	2021/11/25	2021/11/25	2021/11/26
3	3585	yy	创建	2021/11/25	2021/11/25	2021/11/25	9999-12-31

第二部分：
新增数据修改好start和end日期即可

select 
orderid,
id,
name,
status,
create_date,
modified_date,
modified_date start_date,
'9999-12-31' end_date
from ods_orders 
where date='2021-11-26';1	1008	cq	支付	2021/11/25	2021/11/26	2021/11/26	9999-12-31
2	3023	zm	支付	2021/11/25	2021/11/26	2021/11/26	9999-12-31
4	3297	th	创建	2021/11/26	2021/11/26	2021/11/26	9999-12-31
5	6824	mm	创建	2021/11/26	2021/11/26	2021/11/26	9999-12-31

最终 合并两张表 并排序即可

select 
t.orderid,t.id,t.name,t.status,t.create_date,
t.modified_date,t.start_date,t.end_date from 
(
select
t1.orderid,
t1.id,
t1.name,
t1.status,
t1.create_date,
t1.modified_date,
t1.start_date,
case when t2.orderid is not null and t1.end_date>'2021/11/26' then '2021/11/26' else t1.end_date 
end end_date
from dw_orders t1
left join 
(select 
orderid ,modified_date 
from ods_orders 
where date='2021-11-26') t2
on t1.orderid=t2.orderid
union all
select 
orderid,
id,
name,
status,
create_date,
modified_date,
modified_date start_date,
'9999-12-31' end_date
from ods_orders 
where date='2021-11-26'
) as t
order by orderid,start_date;1	1008	cq	创建	2021/11/25	2021/11/25	2021/11/25	2021/11/26
1	1008	cq	支付	2021/11/25	2021/11/26	2021/11/26	9999-12-31
2	3023	zm	创建	2021/11/25	2021/11/25	2021/11/25	2021/11/26
2	3023	zm	支付	2021/11/25	2021/11/26	2021/11/26	9999-12-31
3	3585	yy	创建	2021/11/25	2021/11/25	2021/11/25	9999-12-31
4	3297	th	创建	2021/11/26	2021/11/26	2021/11/26	9999-12-31
5	6824	mm	创建	2021/11/26	2021/11/26	2021/11/26	9999-12-31

将这些数据插入dw_orders表

insert overwrite table dw_orders
select 
t.orderid,t.id,t.name,t.status,t.create_date,
t.modified_date,t.start_date,t.end_date from 
(
select
t1.orderid,
t1.id,
t1.name,
t1.status,
t1.create_date,
t1.modified_date,
t1.start_date,
case when t2.orderid is not null and t1.end_date>'2021/11/26' then '2021/11/26' else t1.end_date 
end end_date
from dw_orders t1
left join 
(select 
orderid ,modified_date 
from ods_orders 
where date='2021-11-26') t2
on t1.orderid=t2.orderid
union all
select 
orderid,
id,
name,
status,
create_date,
modified_date,
modified_date start_date,
'9999-12-31' end_date
from ods_orders 
where date='2021-11-26'
) as t
order by orderid,start_date;select * from dw_orders;1	1008	cq	创建	2021/11/25	2021/11/25	2021/11/25	2021/11/26
1	1008	cq	支付	2021/11/25	2021/11/26	2021/11/26	9999-12-31
2	3023	zm	创建	2021/11/25	2021/11/25	2021/11/25	2021/11/26
2	3023	zm	支付	2021/11/25	2021/11/26	2021/11/26	9999-12-31
3	3585	yy	创建	2021/11/25	2021/11/25	2021/11/25	9999-12-31
4	3297	th	创建	2021/11/26	2021/11/26	2021/11/26	9999-12-31
5	6824	mm	创建	2021/11/26	2021/11/26	2021/11/26	9999-12-31

这些就是2021/11/26日记录的数据
由于后面日期的原理与此处类似后续日期数据不做解释只填写代码及结果

select * from orders;1	1008	cq	创建	2021/11/25	2021/11/25
2	3023	zm	创建	2021/11/25	2021/11/25
3	3585	yy	创建	2021/11/25	2021/11/25
1	1008	cq	支付	2021/11/25	2021/11/26
2	3023	zm	支付	2021/11/25	2021/11/26
4	3297	th	创建	2021/11/26	2021/11/26
5	6824	mm	创建	2021/11/26	2021/11/26
1	1008	cq	发货	2021/11/25	2021/11/27
3	3585	yy	支付	2021/11/25	2021/11/27
5	6824	mm	支付	2021/11/26	2021/11/27
6	2022	kk	创建	2021/11/27	2021/11/27
7	2303	tf	创建	2021/11/27	2021/11/27

insert overwrite table ods_orders partition(date='2021-11-27')
select * from orders
where modified_date='2021/11/27';select * from ods_orders;1	1008	cq	创建	2021/11/25	2021/11/25	2021-11-25
2	3023	zm	创建	2021/11/25	2021/11/25	2021-11-25
3	3585	yy	创建	2021/11/25	2021/11/25	2021-11-25
1	1008	cq	支付	2021/11/25	2021/11/26	2021-11-26
2	3023	zm	支付	2021/11/25	2021/11/26	2021-11-26
4	3297	th	创建	2021/11/26	2021/11/26	2021-11-26
5	6824	mm	创建	2021/11/26	2021/11/26	2021-11-26
1	1008	cq	发货	2021/11/25	2021/11/27	2021-11-27
3	3585	yy	支付	2021/11/25	2021/11/27	2021-11-27
5	6824	mm	支付	2021/11/26	2021/11/27	2021-11-27
6	2022	kk	创建	2021/11/27	2021/11/27	2021-11-27
7	2303	tf	创建	2021/11/27	2021/11/27	2021-11-27show partitions ods_orders;date=2021-11-25
date=2021-11-26
date=2021-11-27

insert overwrite table dw_orders
select 
t.orderid,t.id,t.name,t.status,t.create_date,
t.modified_date,t.start_date,t.end_date from 
(
select
t1.orderid,
t1.id,
t1.name,
t1.status,
t1.create_date,
t1.modified_date,
t1.start_date,
case when t2.orderid is not null and t1.end_date>'2021/11/27' then '2021/11/27' else t1.end_date 
end end_date
from dw_orders t1
left join 
(select 
orderid ,modified_date 
from ods_orders 
where date='2021-11-27') t2
on t1.orderid=t2.orderid
union all
select 
orderid,
id,
name,
status,
create_date,
modified_date,
modified_date start_date,
'9999-12-31' end_date
from ods_orders 
where date='2021-11-27'
) as t
order by orderid,start_date;select * from dw_orders;1	1008	cq	创建	2021/11/25	2021/11/25	2021/11/25	2021/11/26
1	1008	cq	支付	2021/11/25	2021/11/26	2021/11/26	2021/11/27
1	1008	cq	发货	2021/11/25	2021/11/27	2021/11/27	9999-12-31
2	3023	zm	创建	2021/11/25	2021/11/25	2021/11/25	2021/11/26
2	3023	zm	支付	2021/11/25	2021/11/26	2021/11/26	9999-12-31
3	3585	yy	创建	2021/11/25	2021/11/25	2021/11/25	2021/11/27
3	3585	yy	支付	2021/11/25	2021/11/27	2021/11/27	9999-12-31
4	3297	th	创建	2021/11/26	2021/11/26	2021/11/26	9999-12-31
5	6824	mm	创建	2021/11/26	2021/11/26	2021/11/26	2021/11/27
5	6824	mm	支付	2021/11/26	2021/11/27	2021/11/27	9999-12-31
6	2022	kk	创建	2021/11/27	2021/11/27	2021/11/27	9999-12-31
7	2303	tf	创建	2021/11/27	2021/11/27	2021/11/27	9999-12-31

=====================================================

select * from orders;1	1008	cq	创建	2021/11/25	2021/11/25
1	1008	cq	支付	2021/11/25	2021/11/26
1	1008	cq	发货	2021/11/25	2021/11/27
1	1008	cq	完成	2021/11/25	2021/11/28
2	3023	zm	创建	2021/11/25	2021/11/25
2	3023	zm	支付	2021/11/25	2021/11/26
2	3023	zm	支付	2021/11/25	2021/11/28
3	3585	yy	创建	2021/11/25	2021/11/25
3	3585	yy	支付	2021/11/25	2021/11/27
4	3297	th	创建	2021/11/26	2021/11/26
4	3297	th	支付	2021/11/26	2021/11/28
5	6824	mm	创建	2021/11/26	2021/11/26
5	6824	mm	支付	2021/11/26	2021/11/27
6	2022	kk	创建	2021/11/27	2021/11/27
7	2303	tf	创建	2021/11/27	2021/11/27
7	2303	tf	支付	2021/11/27	2021/11/28
8	1024	llx	创建	2021/11/28	2021/11/28
9	2012	cc	创建	2021/11/28	2021/11/28

insert overwrite table ods_orders partition(date='2021-11-28')
select * from orders
where modified_date='2021/11/28';select * from ods_orders;1	1008	cq	创建	2021/11/25	2021/11/25	2021-11-25
2	3023	zm	创建	2021/11/25	2021/11/25	2021-11-25
3	3585	yy	创建	2021/11/25	2021/11/25	2021-11-25
1	1008	cq	支付	2021/11/25	2021/11/26	2021-11-26
2	3023	zm	支付	2021/11/25	2021/11/26	2021-11-26
4	3297	th	创建	2021/11/26	2021/11/26	2021-11-26
5	6824	mm	创建	2021/11/26	2021/11/26	2021-11-26
1	1008	cq	发货	2021/11/25	2021/11/27	2021-11-27
3	3585	yy	支付	2021/11/25	2021/11/27	2021-11-27
5	6824	mm	支付	2021/11/26	2021/11/27	2021-11-27
6	2022	kk	创建	2021/11/27	2021/11/27	2021-11-27
7	2303	tf	创建	2021/11/27	2021/11/27	2021-11-27
1	1008	cq	完成	2021/11/25	2021/11/28	2021-11-28
2	3023	zm	支付	2021/11/25	2021/11/28	2021-11-28
4	3297	th	支付	2021/11/26	2021/11/28	2021-11-28
7	2303	tf	支付	2021/11/27	2021/11/28	2021-11-28
8	1024	llx	创建	2021/11/28	2021/11/28	2021-11-28
9	2012	cc	创建	2021/11/28	2021/11/28	2021-11-28show partitions ods_orders;date=2021-11-25
date=2021-11-26
date=2021-11-27
date=2021-11-28

insert overwrite table dw_orders
select 
t.orderid,t.id,t.name,t.status,t.create_date,
t.modified_date,t.start_date,t.end_date from 
(
select
t1.orderid,
t1.id,
t1.name,
t1.status,
t1.create_date,
t1.modified_date,
t1.start_date,
case when t2.orderid is not null and t1.end_date>'2021/11/28' then '2021/11/28' else t1.end_date 
end end_date
from dw_orders t1
left join 
(select 
orderid ,modified_date 
from ods_orders 
where date='2021-11-28') t2
on t1.orderid=t2.orderid
union all
select 
orderid,
id,
name,
status,
create_date,
modified_date,
modified_date start_date,
'9999-12-31' end_date
from ods_orders 
where date='2021-11-28'
) as t
order by orderid,start_date;select * from dw_orders;1	1008	cq	创建	2021/11/25	2021/11/25	2021/11/25	2021/11/26
1	1008	cq	支付	2021/11/25	2021/11/26	2021/11/26	2021/11/27
1	1008	cq	发货	2021/11/25	2021/11/27	2021/11/27	2021/11/28
1	1008	cq	完成	2021/11/25	2021/11/28	2021/11/28	9999-12-31
2	3023	zm	创建	2021/11/25	2021/11/25	2021/11/25	2021/11/26
2	3023	zm	支付	2021/11/25	2021/11/26	2021/11/26	2021/11/28
2	3023	zm	支付	2021/11/25	2021/11/28	2021/11/28	9999-12-31
3	3585	yy	创建	2021/11/25	2021/11/25	2021/11/25	2021/11/27
3	3585	yy	支付	2021/11/25	2021/11/27	2021/11/27	9999-12-31
4	3297	th	创建	2021/11/26	2021/11/26	2021/11/26	2021/11/28
4	3297	th	支付	2021/11/26	2021/11/28	2021/11/28	9999-12-31
5	6824	mm	创建	2021/11/26	2021/11/26	2021/11/26	2021/11/27
5	6824	mm	支付	2021/11/26	2021/11/27	2021/11/27	9999-12-31
6	2022	kk	创建	2021/11/27	2021/11/27	2021/11/27	9999-12-31
7	2303	tf	创建	2021/11/27	2021/11/27	2021/11/27	2021/11/28
7	2303	tf	支付	2021/11/27	2021/11/28	2021/11/28	9999-12-31
8	1024	llx	创建	2021/11/28	2021/11/28	2021/11/28	9999-12-31
9	2012	cc	创建	2021/11/28	2021/11/28	2021/11/28	9999-12-31

===================================
本文中由于时间限制 所有时间都采取固定时间写死
实际可以采用函数动态获取时间