数据仓库 - 拉链表开发实践

一、什么是拉链表

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

二、拉链表开发

案例：客户数据拉链表

2x01 表设计

表	存储介质
T_CUSTOMER	MySQL
ODS_T_CUSTOMER	HIVE
TMP_CUSTOMER	MPP
DIM_CUSTOMER	MPP

操作系统数据表：T_CUSTOMER

字段	类型	备注
cid	int	主键
name	varchar
phone_number	varchar
create_date	date
update_date	date

 

ODS数据表：ODS_T_CUSTOMER (全量记录操作系统数据表的历史快照)

字段	类型	备注
cid	int
name	varchar
phone_number	varchar
create_date	date
update_date	date
sync_date	date	同步日期

临时数据表：TMP_CUSTOMER

** 根据ODS_T_CUSTOMER的sync_date字段抽取全量最新数据（包括新增，修改的数据）

字段	类型	备注
cid	int	主键
name	varchar
phone_number	varchar
create_date	date
update_date	date

数仓维度表：DIM_CUSTOMER

字段	类型	备注
sid	int	代理键
cid	int	源表主键
name	varchar
phone_number	varchar
create_date	date
update_date	date
valid_from	date	有效期起始日
valid_to	date	有效期结束日

2x02 流程

1. 利用数据同步工具，例如Sqoop，把操作系统数据表全量数据同步到ODS数据表。

2. 利用Spark抽取当天同步的数据到临时表TMP_CUSTOMER中。

3. 与维度表DIM_CUSTOMER进行JOIN操作，获取变化的记录，并存储。

2x03 实现

1. 同步操作忽略。。。

2. 抽取当天同步数据到临时表忽略。。。

3. 拉链表计算

2020-05-01 操作系统数据

cid	name	phone_number	create_date	update_date
1	A	12345678	2020-05-01	2020-05-01
2	B	22222222	2020-05-01	2020-05-01
3	C	33333333	2020-05-01	2020-05-01

 

2020-05-02 操作系统数据

cid	name	phone_number	create_date	update_date	备注
1	A	12345678	2020-05-01	2020-05-01
2	B	88888888	2020-05-01	2020-05-02	修改
3	C	33333333	2020-05-01	2020-05-01
4	D	55555555	2020-05-02	2020-05-02	新增

2020-05-03 操作系统数据 

cid	name	phone_number	create_date	update_date	备注
1	A	12345678	2020-05-01	2020-05-01
2	B	88888888	2020-05-01	2020-05-02
3	C	33333333	2020-05-01	2020-05-01	删除
4	D	77777777	2020-05-02	2020-05-03	修改

更新记录： 

-- 新纪录, 通过left join 寻找dim_customer表中为空的记录
-- 要么新增，要么数据已发生变化
WITH new_customer AS (
	SELECT
		t.cid,
		t. NAME,
		t.phone_number,
		t.create_date,
		t.update_date,
		to_char(CURRENT_TIMESTAMP  + '-1 day', 'yyyy-MM-dd') :: DATE AS valid_from,
		'2999-12-31' :: DATE AS valid_to
	FROM
		tmp_customer t
	LEFT JOIN dim_customer d ON t.cid = d.cid
	AND t.create_date = d.create_date
	AND t.update_date = d.update_date
	AND d.valid_to = '2999-12-31'
	WHERE
		d.cid IS NULL
),
 -- 更新维度表中记录，valid_to = 当前日期
update_new_customer (sid) AS (
	UPDATE dim_customer
	SET valid_to = to_char(CURRENT_TIMESTAMP  + '-1 day', 'yyyy-MM-dd') :: Date
	FROM
		new_customer
	WHERE
		dim_customer.cid = new_customer.cid
	AND dim_customer.valid_to = '2999-12-31' 
	RETURNING dim_customer.sid
),
-- 物理删除的记录
delete_customer AS (
	SELECT
		d.sid
	FROM
		dim_customer d
	LEFT JOIN tmp_customer t ON d.cid = t.cid AND d.valid_to = '2999-12-31'
	WHERE
		t.cid IS NULL
), 
 -- 更新维度表中记录，valid_to = 当前日期
update_delete_customer (sid) AS (
	UPDATE dim_customer
	SET valid_to = to_char(CURRENT_TIMESTAMP  + '-1 day', 'yyyy-MM-dd'):: Date
	FROM
		delete_customer
	WHERE
		dim_customer.sid = delete_customer.sid
	AND dim_customer.valid_to = '2999-12-31' 
	RETURNING dim_customer.sid
)
SELECT COUNT(1) FROM new_customer;

插入新记录： 

WITH new_customer AS (
	SELECT
		t.cid,
		t. NAME,
		t.phone_number,
		t.create_date,
		t.update_date,
		to_char(CURRENT_TIMESTAMP  + '-1 day', 'yyyy-MM-dd') :: DATE AS valid_from,
		'2999-12-31' :: DATE AS valid_to
	FROM
		tmp_customer t
	LEFT JOIN dim_customer d ON t.cid = d.cid
	AND t.create_date = d.create_date
	AND t.update_date = d.update_date
	AND d.valid_to = '2999-12-31'
	WHERE
		d.cid IS NULL
),
-- 插入新记录
INSERT INTO dim_customer (
	cid,
	NAME,
	phone_number,
	create_date,
	update_date,
	valid_from,
	valid_to
) SELECT
	cid,
	NAME,
	phone_number,
	create_date,
	update_date,
	valid_from,
	valid_to
FROM
	new_customer;

 

 根据以上逻辑：

2020-05-02 维表数据

sid	cid	name	phone_number	create_date	update_date	valid_from	valid_to	备注
1	1	A	12345678	2020-05-01	2020-05-01	2020-05-01	2999-12-31	新增
2	2	B	22222222	2020-05-01	2020-05-01	2020-05-01	2999-12-31	新增
3	3	C	33333333	2020-05-01	2020-05-01	2020-05-01	2999-12-31	新增

2020-05-03 维表数据

sid	cid	name	phone_number	create_date	update_date	valid_from	valid_to	备注
1	1	A	12345678	2020-05-01	2020-05-01	2020-05-01	2999-12-31	不变
2	2	B	22222222	2020-05-01	2020-05-01	2020-05-01	2020-05-02	修改（源数据变化，修改）
3	3	C	33333333	2020-05-01	2020-05-01	2020-05-01	2999-12-31	不变
4	2	B	88888888	2020-05-01	2020-05-02	2020-05-02	2999-12-31	新增 (源数据变化，新增)
5	4	D	55555555	2020-05-02	2020-05-02	2020-05-02	2999-12-31	新增

2020-05-04 维表数据

sid	cid	name	phone_number	create_date	update_date	valid_from	valid_to	备注
1	1	A	12345678	2020-05-01	2020-05-01	2020-05-01	2999-12-31	不变
2	2	B	22222222	2020-05-01	2020-05-01	2020-05-01	2020-05-02	不变
3	3	C	33333333	2020-05-01	2020-05-01	2020-05-01	2020-05-03	修改 (源数据删除)
4	2	B	88888888	2020-05-01	2020-05-02	2020-05-02	2999-12-31	不变
5	4	D	55555555	2020-05-02	2020-05-02	2020-05-02	2020-05-03	修改（源数据变化，修改）
6	4	D	77777777	2020-05-02	2020-05-03	2020-05-03	2999-12-31	新增 (源数据变化，新增)

** 为何要分两个脚本？开发过程中发现一个奇怪的问题合并在一个脚本执行，valid_to字段的值不正确，CTE中的update语句在insert语句后执行。这个问题还没找到原因。

解答：根据官方文档说明，with statement 和 主 statement都是同步执行的，它们看到的底层快照其实是一样的。当使用with执行数据更新操作时，需要加上RETURNING语句，作为子查询间的联系。在原生的postgresql成功，但是在MPP数据库中没有获得期望结果。还得继续定位。