谈笑间学会数仓—拉链表&&流水表

小生love生活 2020-08-27

401

拉链表 && 流水表

前言

拉链表：维护历史状态，以及最新状态数据的一种表，拉链表根据拉链粒度的不同，实际上相当于快照，只不过做了优化，去除了一部分不变的记录而已,通过拉链表可以很方便的还原出拉链时点的客户记录。
流水表：对于表的每一个修改都会记录，可以用于反映实际记录的变更。

适用场景对比

拉链表通常是对账户信息的历史变动进行处理保留的结果，流水表是每天的交易形成的历史；流水表用于统计业务相关情况，拉链表用于统计账户及客户的情况数据仓库之拉链表（原理、设计以及在Hive中的实现）

在有些情况下，为了保持历史的一些状态，需要用拉链表来做，这样做目的在可以保留所有状态的情况下可以节省空间。

拉链表

拉链表应用场景

数据量大，表中某些字段有变化，但是变化的频率不是很高

业务需求，需要统计这种变化状态。

每天全量一份，浪费了存储空间，获取不到数据的变化。

这时，拉链表的作用就体现出来了，既节省空间，又满足了需求。

一般在数仓中通过增加begin_date,en_date来表示。

电商支付案例

如下例，后两列是start_date和end_date.

begin_date表示该条记录的生命周期开始时间，end_date表示该条记录的生命周期结束时间；

end_date = ‘9999-12-31’表示该条记录目前处于有效状态；

拉链表的查询

如果查询当前所有有效的记录，则select * from xxx where end_date = ‘9999-12-31′

如果查询2016-08-21的历史快照，则select * from xxx where begin_date <= ‘2020-08-21′ and end_date >= ‘2020-08-21’

拉链表更新操作

假设以天为维度，以每天的最后一个状态为当天的最终状态。

以一张订单表为例，如下是原始数据，每天的订单状态明细

根据拉链表我们希望得到的是

可以看出 1，2，3，4每个订单的状态都有，并且也能统计到当前的有效状态。

本例以hive数仓为例，只考虑到实现，与性能无关

拉链表操作呢？

主要分为两步：

一、拉链表数据初始化；

二、拉链表数据增量更新

CREATE TABLE ods_orders_info (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING
) row format delimited fields terminated by '\t'




CREATE TABLE ods_orders_info_di (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING
) PARTITIONED BY (day STRING)
row format delimited fields terminated by '\t'




CREATE TABLE dwd_orders_info_scd (
orderid INT,
createtime STRING,
modifiedtime STRING,
status STRING,
dw_start_date STRING,
dw_end_date STRING
) row format delimited fields terminated by '\t' ;

首先全量更新，我们先到2020-08-20为止的数据。初始化时间为2020-08-20的数据

INSERT overwrite TABLE ods_orders_di PARTITION (day = '2020-08-20')
SELECT orderid,createtime,modifiedtime,status
FROM ods_orders_info
WHERE createtime < '2020-08-21' and modifiedtime <'2020-08-21';

输出刷到dwd层拉链表中

INSERT overwrite TABLE dwd_orders_info_scd
SELECT orderid,createtime,modifiedtime,status,
createtime AS dw_start_date,
'9999-12-31' AS dw_end_date
FROM ods_orders_info_di
WHERE day = '2020-08-20';

结果如下

select * from dwd_orders_info_scd;
OK
1  2020-08-20  2020-08-20  创建 2020-08-20  9999-12-31
2  2020-08-20  2020-08-20  创建 2020-08-20  9999-12-31
3  2020-08-20  2020-08-20  创建 2020-08-20  9999-12-31

拉链表增量更新并校验数据

INSERT overwrite TABLE ods_orders_di PARTITION (day = '2020-08-21')
SELECT orderid,createtime,modifiedtime,status
FROM ods_orders_info
WHERE (createtime = '2020-08-21'  and modifiedtime = '2020-08-21') OR modifiedtime = '2020-08-21';


select * from ods_orders_di where day='2020-08-21';
OK
1  2020-08-20  2020-08-21  支付 2020-08-21
2  2020-08-20  2020-08-21  完成 2020-08-21
4  2020-08-21  2020-08-21  创建 2020-08-21

将数据放到增量表中，然后进行关联到一张临时表中，在插入到新表中

-- 拉链表关联增量数据，进行更新拉链表数据
DROP TABLE IF EXISTS dwd_orders_info_scd_tmp;
CREATE TABLE dwd_orders_info_scd_tmp AS
SELECT orderid,
createtime,
modifiedtime,
status,
dw_start_date,
dw_end_date
FROM (
    SELECT a.orderid,
    a.createtime,
    a.modifiedtime,
    a.status,
    a.dw_start_date,
    CASE WHEN b.orderid IS NOT NULL AND a.dw_end_date > '2020-08-21' THEN '2020-08-21' ELSE a.dw_end_date END AS dw_end_date
    FROM dwd_orders_info_scd a
    left outer join (
      SELECT * FROM ods_orders_info_di WHERE day = '2020-08-21'
    ) b ON (a.orderid = b.orderid)
    UNION ALL
    SELECT orderid,
    createtime,
    modifiedtime,
    status,
    modifiedtime AS dw_start_date,
    '9999-12-31' AS dw_end_date
    FROM ods_orders_info_di WHERE day = '2020-08-21'
) x
ORDER BY orderid,dw_start_date;


-- 将临时表数据插入原表中
INSERT overwrite TABLE dwd_orders_info_scd
SELECT * FROM dwd_orders_info_scd_tmp;

验证数据结果是否正确

select * from dw_orders_his;
OK
1  2020-08-20  2020-08-20  创建 2020-08-20  2020-08-20
1  2020-08-20  2020-08-21  支付 2020-08-21  2020-08-21
1  2020-08-20  2020-08-22  完成 2020-08-22  9999-12-31
2  2020-08-20  2020-08-20  创建 2020-08-20  2020-08-20
2  2020-08-20  2020-08-21  完成 2020-08-21  9999-12-31
3  2020-08-20  2020-08-20  创建 2020-08-20  2020-08-21
3  2020-08-20  2020-08-22  支付 2020-08-22  9999-12-31
4  2020-08-21  2020-08-21  创建 2020-08-21  2020-08-21
4  2020-08-21  2020-08-22  支付 2020-08-22  9999-12-31
5  2020-08-22  2020-08-22  创建 2020-08-22  9999-12-31

至此，拉链表初始化及更新已经全部做完了，一次初始化，后面都是跑增量更新的数据哦～

可能你会注意到，dwd、di、scd这些东东，这些都是建模规范撒，每家都不一样，不过差不多，后面有机会再整理吧。

流水表

记录表中所有改变的表。是数据仓库中记录数据的一个方式，它是记录数据的明细信息。

流水表对于表每修改都会记录用于反映实际记录变更而拉链表根据拉链粒度同实际上相当于快照只过做了优化，去除了部分变记录而已通过拉链表方便还原出拉链时点客户记录而流水表虽也能做得效率或者需要人工参与。

流水表是啥子呢？

就是所有的数据都进行全量保留，也可以狭义地理解为明细表吧，如果拿用户表为例，所有的用户操作和变更都进行保存，拉链表一般是按天进行获取数据变更的，那么天内的变动呢？其实也是搞不定的。

个人感觉如果把拉链表的分区做的足够小的话，接近每秒，那么其实也可以当做流水表咯，当然一般都不会这样搞咯～

最后吐槽两句，微信公众号的排版模式太尼玛坑了，以后比较复杂的文字，直接发博客地址了，不再进行排版了。

数据库

文章转载自小生love生活，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。