HiveSQL实战积累_数据仓库全量表&增量表&流水表&拉链表

1.基础概念

全量表：全量表就是每天拉取原表中的所有数据，每个dt分区存储全部的数据，进行任何数据查询都只需要使用dt='yyyy-mm-dd'即可。
增量表：增量表就是每天拉取当天的更新数据，至于如何处理每天的更新数据，又将增量表分为流水表和拉链表。
流水表：流水表就是每天拉取当天的更新数据，然后将这些更新数据直接不做计算存储到表中，每个dt分区存储的就是更新数据。
拉链表：拉链表就是每天拉取当天的更新数据，然后将这些数据进行聚合计算，实现对历史状态的维护。通常拉链表需要start_date、end_date、dp、dt四个字段来维护数据。

2.拉链表案例

2022-10-01的订单底表：

order_id	status	modified_date
100001	创建	2022-10-01
100002	创建	2022-10-01
100003	创建	2022-10-01

2022-10-02的订单底表：

order_id	status	modified_date
100001	完成	2022-10-02
100002	创建	2022-10-01
100003	创建	2022-10-01
100004	创建	2022-10-02

2022-10-02加工完的订单拉链表：

order_id	status	start_date	end_date	dp	dt
100001	创建	2022-10-01	2022-10-02	EXPIRED	2022-10-02
100002	创建	2022-10-01	9999-12-31	ACTIVE	9999-12-31
100003	创建	2022-10-01	9999-12-31	ACTIVE	9999-12-31
100001	完成	2022-10-02	9999-12-31	ACTIVE	9999-12-31
100004	创建	2022-10-02	9999-12-31	ACTIVE	9999-12-31

数据仓库中拉链表的start_date表示该条记录的生命周期开始时间，end_date表示该条记录的生命周期结束时间，dp则表示该条数据是否处于有效状态，dt一般会与end_date字段写入同样的数据。

从拉链表中获取有效数据只能用如下两种写法：

1
2
3

select * from chain_table_name where dp = 'ACTIVE';

select * from chain_table_name where start_date <= sysdate(-1) and end_date > sysdate(-1);

从拉链表中获取历史上某一天的当时的有效数据，比如2022-10-01，只能用如下写法：

1	select * from chain_table_name where start_date <= '2022-10-01' and end_date > '2022-10-01';

3.拉链表加工过程

3.1从订单底表中取出增量数据

首先需要从订单底表中取出当天的更新数据放入一张流水表中，假设订单底表名为order_bottom，流水增量表名为order_inc，执行sql如下：

INSERT overwrite TABLE order_inc PARTITION (dt = '2022-10-02') 
SELECT 
	order_id,
	status
FROM
	order_bottom
WHERE
	modified_date = '2022-10-02';

3.2将流水增量表与拉链表中的有效数据关联

将流水表中的增量数据与拉链表中的有效数据进行关联，将已经存在的数据设置为失效数据，同时将所有增量数据添加为新的有效数据。假设关联得到的临时表名为order_tmp，最终拉链表名为order_chain，执行sql如下：

DROP TABLE IF EXISTS order_tmp;
CREATE TABLE order_tmp AS
SELECT 
	order_id,
	status,
	start_date,
	end_date,
	dp,
	dt
FROM (
  --将已存在的有效订单记录设置为失效
	SELECT 
  	a.order_id,
		a.status,
		a.start_date,
		CASE 
  		WHEN b.order_id IS NOT NULL AND a.end_date > '2022-10-02' THEN '2022-10-02'
  		ELSE a.end_date 
  	END AS end_date,
  	CASE 
  		WHEN b.order_id IS NOT NULL AND a.end_date > '2022-10-02' THEN 'EXPIRED'
  		ELSE a.dp 
  	END AS dp,
		CASE 
  		WHEN b.order_id IS NOT NULL AND a.end_date > '2022-10-02' THEN '2022-10-02'
  		ELSE a.dt 
  	END AS dt
  FROM
  	(SELECT * FROM order_chain WHERE dp = 'ACTIVE') a
  	left jion (SELECT * FROM order_inc WHERE dt = '2022-10-02') b
  	on a.order_id = b.order_id;
  
  UNION ALL
  
  --将增量数据全部添加并设置为有效
  SELECT
  	order_id,
  	status,
  	dt as start_date,
  	'9999-12-31' as end_date,
  	'ACTIVE' as dp,
 		'9999-12-31' as dt
  FROM
  	order_inc
  WHERE
  	dt = '2022-10-02'
) c;

3.3用临时表替换当前拉链表的有效分区和新增无效分区

--替换有效分区
INSERT overwrite TABLE order_chain PARTITION (dp = 'ACTIVE') 
SELECT * FROM order_tmp WHERE dp = 'ACTIVE';

--新增无效分区
INSERT overwrite TABLE order_chain PARTITION (dp = 'EXPIRED', dt = '2022-10-02') 
SELECT * FROM order_tmp WHERE dp = 'EXPIRED';

4.拉链表使用疑惑

4.1拉链表与流水表的区别

其实跟着上面过一遍拉链表的加工过程就知道区别了，两者都是增量表。流水表是简单的新增每天的更新数据，添加一天数据并不会影响到之前的数据。而拉链表在拿到更新数据之后，还要按照主键进行聚合，修改旧数据的状态。

还有一点要注意的就是拉链表设置的时间粒度问题，比如一个订单状态可能在一天内改变了两次以上，那么流水表会记录订单的每条修改记录。而拉链表在按照主键聚合的加工过程中，一个订单只取最后的状态作为拉链表中的有效状态，而同一天中的前几次修改记录是直接删除，还是直接以失效状态放入拉链表中可以自行设计。

4.2拉链表归档：

从上面拉链表的加工过程可以知道，拉链表每次新增数据时，都要读取dp=’ACTIVE’有效分区的全量数据来与增量数据进行关联，当数据越来越多，这个计算过程的耗时也会越来越大，我们不可能任其数据量无限增长。那么对于拉链表一般会定期进行归档，就是将end_date=’9999-12-31’但是入库时间较早的一些数据放入’HISTORY’分区中。

一般自行结合业务场景估计数据大概多久后就不会再出现状态变化，以此来设计归档周期，如3个月、6个月等。如果想从归档后的拉链表中获取完整的全量有效数据，就必须使用select * from chain_table_name where start_date <= sysdate(-1) and end_date > sysdate(-1)。

5.数据仓库表类型选择

bdm缓冲数据模型、fdm基础数据模型、gdm公共数据模型、adm聚合数据模型。fdm层一般要求在保留历史数据的同时，尽可能少的占用存储空间，且要求可以高效快速地获取历史上任意一天的快照数据，所以一般选用流水表或拉链表来构建fdm模型。gdm层在数据量不大的情况下可以选用全量表，在数据量较大时也要使用流水表或拉链表。

注意使用全量表时，一定要指定dt分区，因为一个分区就是全量数据。