拉链表理解分析

最新推荐文章于 2024-07-23 08:57:02 发布
最新推荐文章于 2024-07-23 08:57:02 发布
阅读量1k 收藏 1
点赞数
文章标签： hive 大数据
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/chou_qian/article/details/121545912
版权

一、概述
拉链表是一种满足生产需求的表，主要用于历史记录。如下图
在这里插入图片描述
最后两列即为拉链，记录数据的生效时间与失效时间，同一个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

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

愁千
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
漫谈数据仓库之拉链表（原理、设计以及在Hive中的实现）
weixin_34388207的博客
07-12 3689
2019独角兽企业重金招聘Python工程师标准>>> ...
oracle 拉链表如何分区,关于数据仓库历史拉链表新更新方法的说明
weixin_29233857的博客
04-06 981
原始资料：数据仓库保留了大量的历史数据，这是区别于其他数据库的显著特点之一。历史数据能够使数据仓库重现任意时点的现场，对时间维度上的数据分析工作提供了重要的手段。保留历史数据最常见的办法就是使用历史拉链表。历史拉链表仅当生产数据发生变更时，才相应地发生变更，既能有效保留历史数据的变动信息，又不浪费存储空间。以数据仓库中每日增量更新的订单历史拉链表(假设表名为EVT_ORDER_HIST)为例，假设...
数据仓库数据模型之：极限存储--历史拉链表
iteye_2280的博客
08-15 2533
在数据仓库的数据模型设计过程中，经常会遇到这样的需求： 1. 数据量比较大;2. 表中的部分字段会被update,如用户的地址，产品的描述信息，订单的状态等等;3. 需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态，   比如，查看某一个用户在过去某一段时间内，更新过几次等等;4. 变化的比例和频率不是很大，比如，总共有1000万的会员，每天新增和发生变...
拉链表和宽表的优劣势
最新发布
m0_68623631的博客
07-23 947
是一种用于数据仓库的表结构，记录了数据随时间变化的历史状态。每次数据发生变化时，都会在拉链表中插入一条新记录，而旧记录保持不变，仅标记其有效时间区间。
拉链表
什么最重要？算法！！
03-15 1141
转载网址：http://blog.sina.com.cn/s/blog_4ea178ad0101bdps.html
拉链表的应用
qq_37069064的博客
11-15 452
拉链表的需求 复制： 1.1 数据量比较大; 1.2表中的部分字段会被update,如用户的地址，产品的描述信息，订单的状态等等; 1.3 需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态，比如，查看某一个用户在过去某一段时间内，更新过几次等等; 1.4 变化的比例和频率不是很大，比如，总共有1000万的会员，每天新增和发生变化的有10万左右;如果对这边表每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费; 综上所述：引入'拉链..
什么是拉链表
qq_39889944的博客
12-06 378
本文简单介绍了拉链表
复合主键做历史拉链表
hahweiwen的博客
08-18 1034
分享技巧之前，先普及两个概念： 1）什么是维度？ 维度是维度建模的基础和灵魂。在维度建模中，讲度量称为“事实”，将环境描述为“维度”，维度是用于分析事实所需要的多样环境。例如，在分析新增过程中，可以通过平台、手机厂商、版本号等维度描述用户的属性。 维度所包含的标识维度的列成为维度属性。维度属性是查询约束条件、分组和报表标签生成的基本来源，是数据易用性的关键。例如，在sql查询中，获取连信有头像和风控合法用户，是通过约束用户头像状态属性和风控级别属性来实现的；统计连信不同手机厂商的每日新增用户数，是通过用户手
拉链表案例 - 完整.zip
10-04
拉链表，也被称为链接列表，是计算机...通过学习这个案例，读者将能够深入理解拉链表的工作原理，掌握其基本操作，并能够运用到实际编程问题中。这不仅有助于提升编程技能，也是理解和解决更复杂数据结构问题的基础。
深入讲解拉链表，还怕面试官问？.docx
11-12
拉链表是一种数据库表结构，主要用于存储数据的历史版本，以便在需要时能够快速恢复到某个特定时间点的状态。...在面试中，对拉链表的理解和运用展示出对数据处理和优化的深入认识，是展现技术实力的重要方面。
什么是拉链表？
热门推荐
qq_40745994的博客
10-30 1万+
一，什么是拉链表 拉链表是针对数据仓库设计中表存储数据的方式而定义的，顾名思义，所谓拉链，就是记录历史。记录一个事物从开始，一直到当前状态的所有变化的信息。 二，用在什么地方 在数据仓库的数据模型设计过程中，经常会遇到这样的需求： 数据量比较大; 表中的部分字段会被update,如用户的地址，产品的描述信息，订单的状态等等; 需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态， 比如，查看某一个用户在过去某一段时间内，更新过几次等等; 变化的比例和频率不是很大，比如
数据仓库拉链表：存储与优化
数据仓库拉链表的设计旨在高效地存储和追踪数据的变化，从而节省存储空间并记录数据的历史演变。 1. **数据仓库特点**： 数据仓库的主要目的是提供决策支持，它通常包含来自多个业务系统的整合数据，并且强调数据...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值