一、什么是拉链表
拉链表是针对数据仓库设计中表存储数据的方式而定义的,顾名思义,所谓拉链,就是记录历史。记录一个事物从开始,一直到当前状态的所有变化的信息。
二、拉链表开发
案例:客户数据拉链表
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数据库中没有获得期望结果。还得继续定位。