一、概述
拉链表是一种满足生产需求的表,主要用于历史记录。如下图
最后两列即为拉链,记录数据的生效时间与失效时间,同一个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-27
show 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-28
show 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
===================================
本文中由于时间限制 所有时间都采取固定时间写死
实际可以采用函数动态获取时间