拉链表的概念设计与实现

拉链表

一、概念

拉链表是针对数据仓库设计中表存储数据的方式而定义的，所谓拉链，就是记录历史。记录一个事物从开始，一直到当前状态的所有变化的信息。

用处： 解决持续增长且存在一定时间时间范围内重复的数据
场景： 数据规模庞大，新数据【在有限的时间】内存在多种状态变化
原来解决方案： 采用分区表，用户分区存储历史增量数据，缺点是重复数据太多
优点： 节约空间

二、拉链表的设计

以订单为例:

普通表存每天数据

order_id bigint,			-- 订单id
user_id bigint,				-- 订单创建时间 
order_modify_dt timestamp,	--状态更改时间 
order_money decimal(10,2),	--订单价格 
current_status int			--订单状态
1
2
3
4
5

每次存储某一天数据

拉链(分区分桶表)

order_id bigint,
user_id bigint,
order_create_dt timestamp,
order_modify_dt timestamp,
order_money decimal(10,2),
current_status int
1
2
3
4
5
6

将初始数据装如拉链表

用某一天部分数据更新拉链表，新订单生成，旧订单修改

三、拉链表的实现

创建普通表存原始数据

create table hive_zipper_order(
    order_id bigint,
    user_id bigint,
    order_modify_dt timestamp,
    order_money decimal(10,2),
    current_status int
)
row format delimited fields terminated by ',';
// 将数据文件导入原始表格
load data local inpath '/root/data/order_record.log'
overwrite into table hive_zipper_order;
1
2
3
4
5
6
7
8
9
10
11

创建拉链表

//操作历史全量数据用动态分区
set hive.support.concurrency=true;  			-- hive支持
set hive.enforce.bucketing=true;				-- hive强制分桶
set hive.exec.dynamic.partition.mode=nonstrict; --动态分区 分区表 	给首次将大量数据导入使用
set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; --事务管理器 
set hive.compactor.initiator.on=true; 			-- 表合并开启
set hive.compactor.worker.threads=1; 			-- 表合并线程必须为一
set hive.auto.convert.join=false;			 	-- 关闭 mapjoin，只能是reducejoin
set hive.merge.cardinality.check=false; 		-- 关闭检查数据列的基数(列值的差异性)
1
2
3
4
5
6
7
8
9

// 创建拉链表
drop table if exists hive_zipper_pc_order;
create table hive_zipper_pc_order(
    order_id bigint,
    user_id bigint,
    order_create_dt timestamp,
    order_modify_dt timestamp,
    order_money decimal(10,2),
    current_status int
) partitioned by(year int,month int,day int)
clustered by(order_create_dt) into 4 buckets
row format delimited fields terminated by ','
stored as orc tblproperties("transactional"="true");
1
2
3
4
5
6
7
8
9
10
11
12
13

开启动态分区，一次性挂载

// 对拉链表的数据进行聚合，获取订单信息的创建日期、修改日期和订单状态
with zip_src as (
	select order_id,user_id,order_money,
		min(order_modify_dt) as order_create_dt,
		max(order_modify_dt) as order_modify_dt,
		max(current_status) as current_status
	from hive_zipper_order
	group by order_id,user_id,order_money
)
// 将原始数据灌入拉链表
insert overwrite table hive_zipper_pc_order partition(year,month,day)
select 
	order_id,
	user_id,
	order_create_dt,
	order_modify_dt,
	order_money,
	current_status,
	year(order_create_dt) as year,
	month(order_create_dt) as month,
	day(order_create_dt) as day
from zip_src;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

拉链表查询

// 拉链表查询 查询之前必须先有这两句配置
set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
set hive.support.concurrency=true;
1
2
3

select * from hive_zipper_pc_order
where to_date(order_modify_dt)='2021-02-04'
order by order_modify_dt desc;
1
2
3

之后每天，增量添加

// 对于追加增量数据，将增量数据覆盖在原始数据表中
load data local inpath '/root/data/order_record_2021_02_05.log'
overwrite into table hive_zipper_order;
1
2
3

拉链处理增量数据(新增新数据，修改旧数据)

// 将原始数据表中的增量数据插入拉链表
// 利用源数据和目标表的order_id进行匹配，若匹配则更新现有订单信息，若不匹配则插入新订单
merge into hive_zipper_pc_order as O
using (
	select 
		order_id,
		user_id,
		order_create_dt,
		order_modify_dt,
		order_money,
		current_status,
		year(order_create_dt) as year,
		month(order_create_dt) as month,
		day(order_create_dt) as day
	from (
		select order_id,user_id,order_money,
			min(order_modify_dt) as order_create_dt,
			max(order_modify_dt) as order_modify_dt,
			max(current_status) as current_status
		from hive_zipper_order
		--where to_date(order_modify_dt)='2021-02-05'
		group by order_id,user_id,order_money
	)T
) as H
on O.order_id=H.order_id
when matched then 
update set order_modify_dt=H.order_modify_dt,current_status=H.current_status
when not matched then 
insert values(H.order_id,H.user_id,H.order_create_dt,H.order_modify_dt,H.order_money,H.current_status,H.year,H.month,H.day);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

验证拉链结果

select * from hive_zipper_pc_order
where to_date(order_modify_dt)>to_date(order_create_dt);
1
2

数据仓库_缓慢渐变维_拉链表（全揭秘）_拉链表中的代理主键-CSDN博客

拉链表_什么是拉链表-CSDN博客