数仓之拉链表-从需求到实现

某天领导提了个需求，需要按某段时间，比如按天为单位，来记录订单的状态，需要从时间上能对状态进行跟踪，比如2020-01-01 下单的某一订单，2020-01-02 客户支付，2020-01-03 ~ 2020-01-05之间这笔单在运输途中，2020-01-06用户退单了这么一过程，这咋弄呢？

面对问题，真男人不能怂，有了问题就解决问题吧。先来看看我们手里有什么数据呢？从业务开发兄弟那边得到订单数据放在DB里，每条订单只有一条订单记录和若干条状态流水。ok，这下有了数据就该我们动脑子想想怎么办了。

首先是用一张表还是多张表来存呢，如果是用多张表来存，就得有个分表规则，按时间分的话，每个周期一张表，没法确定要查的某一订单在哪张表，就没法查询某一笔订单的流转状态了。按订单分就更不行了，这么多订单，表数量要爆了。

好，既然不能分多张表，那就用一张表试试。 每条订单的状态如果有状态变更就记录一条数据，这样不就可以了吗，另外，通过解析DB binlog的方式，对于每个周期增量数据也可以搞定。 那么现在问题来了：

1. 如果上一个周期（按天为例）的数据在下一个周期中发生了变化，怎么样才能把这些变化体现出来呢。
2. 下一个周期的新数据怎么加入到这张表中呢。为了便于描述，称这张表为历史拉链表。

假设需求是2020-01-01来的，那就可以将之前的订单数据导入到一张全量表里，这里自然可以想到这样一张表当然是放在ODS层好了，就叫ods_orders 吧。这里modifiedtime 也是DB里的字段，created状态的订单modifiedtime等于createdtime。

2020-01-01 之前 ods_orders 的全量数据如下

orderid	uid	amount	state	createtime	modifiedtime
1	111	100	created	2019-10-01	2019-10-01
2	222	200	created	2019-11-01	2019-11-01
3	333	300	created	2019-12-01	2019-12-01

通过解析binlog可以得到 2020-01-02 ods_orders 的增量数据

orderid	uid	amount	state	creattime	modifiedtime
1	111	100	created	2019-10-01	2019-01-02
4	444	400	created	2020-01-01	2020-01-01
5	555	500	created	2020-01-01	2020-01-01

可以看出，02-01这天，1号订单被支付了，又新增了4和5 两笔订单。就这两张表我们似乎并不能完成需求。好，我们回到刚刚谈论的，既然用一张表就要实现这个需求，前面的两张表都是简单记录了数据源的数据。既然差一张表，那我们就将这张表建立出来吧。怎么建呢，如果要看到状态流转，似乎还需要某个状态对应的事件端吧，对，那么就用start_time 和 end_time来记录吧，对于最后一个状态的订单end_time 就给他来个最大的吧 9999-12-31，应该没那个公司能活这么久了，毕竟人类都还不一定存在这么久。

建好表，将历史数据导入，如下

orderid	uid	amount	state	modifiedtime	start_time	end_time
1	111	100	created	2019-10-01	2019-10-01	9999-12-31
2	222	200	created	2019-11-01	2019-11-01	9999-12-31
3	333	300	created	2019-12-01	2019-12-01	9999-12-31

既然对数据有了自己的处理逻辑 ，这张表就不能放在ods层了。我们将其放在dwd层吧，下面就用 dwd_orders 来指这张表。

那现在怎么把 2020-01-01 ods_orders 里数据融入进来呢，这个就容易了，把 dwd_orders 表用orderid 字段左连接 ods_orders, 取出 ods_orders orderid 不为空的 且 end_time > 2020-01-01 这样就处理好了历史数据中改动过的本分，对于新增的部分就直接从ods_orders 中的增量数据导入到dwd_orders 中就行,当然这里需要用到modifiedtime 来做starttime, 用于划分不同的状态。这样就完成需求了。

光说不练假把式，现在又到了上号时间 。

代码实现

2020-06-20 的全量数据

001,2020-06-20,2020-06-20,创建
002,2020-06-20,2020-06-20,创建
003,2020-06-20,2020-06-20,支付

建表并导入数据。
首先是ods_orders:

-- ods_orders 增量数据表
use test;
DROP TABLE test.ods_orders;
CREATE TABLE test.ods_orders
(
    orderid      INT,
    createtime   STRING,
    modifiedtime STRING,
    status       STRING
) PARTITIONED BY (dt STRING) row format delimited fields terminated by ',';
-- 导入2020-06-20全量的数据
load data local inpath '/root/order1.dat' into table test.ods_orders
    partition (dt = '2020-06-20');
select *from ods_orders;

再上dwd_orders

-- 历史数据拉链表
DROP TABLE test.dwd_orders;
CREATE TABLE test.dwd_orders
(
    orderid      INT,
    createtime   STRING,
    modifiedtime STRING,
    status       STRING,
    start_date   STRING,
    end_date     STRING
) row format delimited fields terminated by ',';
-- 将历史数据导入历史拉链表
INSERT overwrite TABLE test.dwd_orders
SELECT orderid, createtime, modifiedtime, status, createtime AS start_date, '9999-12-31' AS end_date
FROM test.ods_orders
WHERE dt = '2020-06-20';

到这里ods_orders 和 dwd_orders 的记录是一样的。现在2020-06-21的记录进入ods_orders
数据文件order2.dat

001,2020-06-20,2020-06-21,支付
004,2020-06-21,2020-06-21,创建
005,2020-06-21,2020-06-21,创建 

load data local inpath 
'/root/order2.dat' 
into table test.ods_orders partition(dt='2020-06-21');

下面就是核心和高潮了

insert overwrite table test.dwd_orders
-- 先处理新增数据，用modifiedtime 做状态划分
-- 对于ods_orders 和dwd_orders 都存在记录这里就把 ods_orders 中新的状态记录在了modifiedtime 之后
--  即 从start_date开始这个记录就是新状态了，
SELECT orderid, createtime, modifiedtime, status, modifiedtime AS start_date, '9999-12-31' AS end_date
FROM test.ods_orders
where dt = '2020-06-21' 
union all
-- 再处理dwd_orders中的已有数据，当旧数据没有状态变更时就是没有匹配上，end_time 就不用改
-- 匹配上了就把这end_time 改为上一个周期
select A.orderid,
       A.createtime,
       A.modifiedtime,
       A.status,
       A.start_date,
       case 
       		when B.orderid is not null and A.end_date > '2020- 06-21' then '2020-06-20' 			
       		else A.end_date 
       		end end_date
from dwd_orders A
         left join (select * from ods_orders where dt = '2020-06- 21') B on A.orderid = B.orderid;

到这里，就出现了一张可以体现历史数据状态流转的表了，我们可以叫它历史数据拉链表，毕竟他就像拉链一样，将自己前一天的数据和ods_orders中的数据结合在一起并往前不断推进。如果你觉得拉链表就干这，那就小看他了。现在又来了个需求，2020-10-08 国庆假期回来，业务线开发兄弟发现代码有bug，导致数据错乱了，这样必然导致拉链表里数据也不太对了，需要会会滚到上一个版本，即放假之前2020-09-30 那天的数据，虽然这锅是兄弟单位的，但是我们的业务收到影响了，我们不得不改啊，哎，怎么改了，难不成要把数据从上线第一天开始重跑一边，先不说数据有没有放这么久，即使存了这么久，一天天这样跑出来，都天荒地老海枯石烂了。既然不能重新跑那该咋整，是时候展现真正的技术了，既然拉链表可以往前拉，那他就可以往后退了嘛。

现在我们有三个时间：拉链表的end_time start_time 和需要会回滚到的那一天假设为roll_date。
对于end_time < roll_time 说明这个订单状态流转在roll_time 之前就结束了，可以保留这些数据

select userid, mobile, regdate, start_date, end_date, 
'1' as tagfrom test.userhis where end_date < '2020-06-22';

对于start_time <= roll_time <= end_time 说明这个订单不应该把end_time 改为 roll_time ，那么将end_time 改为 9999-12-31

select userid, mobile, regdate, start_date, '9999-12-31' as end_date, '2' as tag 
from test.userhis where start_date <= '2020-06-22' and end_date >= '2020-06-22';

再把这两种数据写到一个tmp表，当然这里需逐天回退。

drop table test.tmp; 
create table test.tmp as 
select userid, mobile, regdate, start_date, end_date, 
	'1' as tagfrom test.userhis where end_date < '2020-06-22' 
union all select userid, mobile, regdate, start_date, '9999-12-31' as end_date, 
	'2' as tag from test.userhis where start_date <= '2020-06-22' and end_date >= '2020-06- 22'; 

-- 查看一下结果先
select * from test.tmp cluster by userid, start_date; 

这样一天天地回滚到需要的那一天。
当然对于回滚还有一种方法，定期保存历史拉链表的快照，然后保存该快照之后的增量数据ods_orders。需要回退时，直接找到目标日前的前一个历史拉链表快照，然后结合ods_orders重做就可以了。

ok，到此这个需求就算搞定了，现在上总结。
对于处理缓慢变化维问题，针对不同场景可以采用不同方案。
如商品品类，当品类发生变化直接覆盖原值。
对于不太大的维表也可以每天保存快照。
对于表很大，但是变化的字段不多，且变化比较慢，而且需要保留变化历史的就需要用到拉链表了。

对于拉链表，上面的解释过程可以看出有一些局限性

1. 订单刷新周期类的状态跟新就没法保存的。
2. 如果原订单数据没有状态修改时间，就需要一个机制来确保抽取到每个周期（天）的数据。

后记：
之前做后端开发时重心都在db事务，锁，并发和sql效率上，现在觉得用hql来实现各种业务也是如此有趣，keep looking, don’t settle。