【数仓】拉链表（极限存储）

拉链表，我想做数仓的同学应该都是听过这个存储模式，拉链表的产生，源于维表存储中，如何存储和查询历史记录的问题

当然本文不是来介绍概念的（如果后面我觉得有需要，我会单独整理一下），主要是看了《大数据之路：阿里巴巴大数据实践》中提到了“极限存储”的概念，然后讨论怎么具体实现！

其实书中并没有说的很详细到底如何处理，我也搜了一些相关的文章，来帮助理解极限存储中月拉链表的存储和查询方法

首先感谢以下几篇很棒的博文：

拉链表是什么：https://www.kancloud.cn/grass1314521/data_warehouse_in_action/490400（作者：木东居士）

拉链表（一）：https://zhuanlan.zhihu.com/p/75070697（作者：天小天）

拉链表（二）：https://zhuanlan.zhihu.com/p/76254774（作者：天小天）

特别是作者：天小天直接给出了代码实现，真是受益匪浅~（当然 天小天 老师在博客中有几处笔误，我都会进行修正！）

本文是基于前人的文章做的补充和总结

一、概念

1、单表数据量低于百万级别（小）

比如存了一整年的数据，这张表就百万级别，那理论上来说，单机的数据库都能处理，就不要考虑什么分不分区，怎么分区的问题了，直接把所有数据往一张表里放就行了

2、单表数据量达到千万/亿/十亿级别（中）

这种情况基本上单表性能就不太行了，需要一些分布式数据库来做支撑，例如greenplum，或者clickhouse类似这种，数据存储有分布键的数据库

3、单表数据量达到百亿甚至更多（大）

这种时候，计算可能不能单纯的使用数据库引擎，需要用到大数据的计算框架，数据存储也需要使用物理隔离（按照日期做分区）

那么就遇到一个问题，如果使用生效时间和失效时间来作为分区键的话，那么存一年的话单表最多会有 365 * 364 / 2 = 66430个分区，当然你可以只存3个月让用户做查询，其他的归档到冷存储中

但是阿里提出了一种叫做“极限存储-月拉链表”的方式，来存储拉链表，一年最多只会产生12 * (1+(30 * 29) / 2) = 5232 个分区

二、具体实现

1、流程图

（该图模仿：天小天）

2、建表语句

CREATE TABLE IF NOT EXISTS user_binlog(
`id`                         BIGINT COMMENT '用户id',
`name`                       STRING COMMENT '姓名',
`phone`                      STRING COMMENT  '电话号码',
`sign_up_date`               DATETIME COMMENT '注册时间',
`modify_time`                DATETIME COMMENT '更新时间',
`type`                       STRING COMMENT '操作类型'
)
COMMENT 'binlog落地表'
PARTITIONED BY (pt_days STRING comment '日期分区字段');


CREATE TABLE IF NOT EXISTS user_link(
`id`                         BIGINT COMMENT '用户id',
`name`                       STRING COMMENT '姓名',
`phone`                      STRING COMMENT '电话号码',
`sign_up_date`               DATETIME COMMENT '注册时间',
`modify_time`                DATETIME COMMENT '更新时间'
)
COMMENT '拉链结果表'
PARTITIONED BY (
`start_date` STRING COMMENT '生效日期',
`end_date` STRING COMMENT '失效日期');


CREATE TABLE IF NOT EXISTS user_link_tmp(
`id`                         BIGINT COMMENT '用户id',
`name`                       STRING COMMENT '姓名',
`phone`                      STRING COMMENT '电话号码',
`sign_up_date`               DATETIME COMMENT '注册时间',
`modify_time`                DATETIME COMMENT '更新时间',
`start_date` STRING COMMENT '生效日期',
`end_date` STRING COMMENT '失效日期'
)
COMMENT '拉链处理临时表';

3、ETL流程（日常）

这里要注意下，每个月的2号，要处理1号的数据，和日常处理流程不一样，所以要有两个流程，一个是日常处理，另外一个就是当跨月了，如何处理？

我使用的例子，会是 天小天 老师的版本，但是我会对他的每一段sql加以图文的方式补充说明，帮助更多的同学理解~

接下来会以7月11日执行的SQL举例，详细介绍如何把binlog表的数据同步到拉链表中。其中的SQL涉及到先把binlog表中的数据同步到临时表，并把临时表写入到拉链表。

首先我们来思考下，拉链表中哪些分区的数据需要更改？

• （新增）今天失效的分区（把之前未失效，今天失效的数据更改）
• （修改）从未失效的分区中，剔除失效的数据
• （新增）今天未失效的分区

（1）、拉链表中未失效，但是今天失效的数据

-- 先清空临时表的数据。
-- 理论上这张表已经是清空的。
-- 这里清空主要是防止异常清空，导致上一批次没有清空临时表
truncate table user_link_tmp;


-- binlog表中，type类型为update或者delete的数据，证明在拉链表中，原数据已经失效了
-- 将拉链表中需要改为失效的数据的失效时间改为'2019-07-10',并把数据写入到临时表中
-- 其中start_date>='2019-07-01'是因为7月1日之前未失效的数据会写入到开始时间为7月1日的分区中，
-- 所以查开始分区只要查当月的即可
-- 当然如果数据量大，用in可能性能不够好，可以使用inner join
insert into table user_link_tmp
select 
    id,
    name,
    phone,
    sing_up_date,
    modify_time,
    start_date,
    '2019-07-10' as end_date
from user_link 
where start_date<='2019-07-09' 
and start_date>='2019-07-01' 
and end_date='9999-12-31'
and id in
(
    select 
        id 
    from user_binlog 
    where pt_days='2019-07-10' 
    and type in ('update','delete') 
    group by id
);

结束分区用应该用end_date='9999-12-31'

我举个例子：

假设我在7月6号，跑完了7月5号的数据，表目录如下

 这时候发现7月3号的数据有问题

这时候我要重跑7月3号的数据（重新处理binlog里7月3号的数据），重新启动7月4号的流程，但是start_date<='2019-07-02' and start_date>='2019-07-01' and end_date > '2019-07-02'，这样会有问题的！会取出如下图红框框出来的数据！那就和7月4号那天跑的sql读入的数据不一样了！所以综上，end_date='9999-12-31'

当然，有另一种补数据方法不用考虑这些时间，就是把7月3号之后生成的数据删了，首先开始时间3号以后的数据要全部删除，开始时间小于2号的，要删除结束时间大于2号，但是结束时间等于9999-12-31不能删，所以如下：

start_date>='2019-07-03' or
(start_date<='2019-07-02' and end_date>='2019-07-03' and end_date<= '9999-12-30')

的分区数据全部删了

 好，所以到这一步（user_link_tmp）临时表中有数据如下：

 （2）、拉链表中未失效，并且今天也未失效的数据

-- 将原始拉链表中未失效的数据原样写入到临时表中
-- 此步骤的目的是从原有分区中删除失效的数据
-- 即在把临时表的数据覆盖到拉链表中时会把失效的数据从原有未失效分区中删除。
-- 这里用了not in，当然考虑性能，也可以使用left join来过滤
insert into table user_link_tmp
select 
	id
    name,
    phone,
    sing_up_date,
    modify_time,
    start_date,
    end_date
from user_link
where start_date<='2019-07-09' 
and start_date>='2019-07-01' 
and end_date='9999-12-31'
and id not in
(
    select 
        id 
    from user_binlog 
    where pt_days='2019-07-10' 
    and type in ('update','delete') 
    group by id
);

 好，所以到这一步（user_link_tmp）临时表中有数据如下（新增的我用红框框出来）：

（3）、拉链表中没有，今天新增未失效的数据

-- 将新增的数据写入到临时表中。
-- 并且开始时间为当前批次日期，结束日期为最大日期
-- 这里用max(modify_time)来关联时间最大的那一条也可以(因为一天内，用户可能修改多次，所以有可能来多条id相同的数据)
-- 当然也可以用窗口函数row_number取最大的那一条，当然
insert into table user_link_tmp
select 
	a.id
    a.name,
    a.phone,
    a.sing_up_date,
    a.modify_time,
    '2019-07-10' as start_date,
    '9999-12-31' as end_date
from
(
    select 
		id,
        name,
        phone,
        sing_up_date,
        modify_time
    from user_binlog
    where pt_days='2019-07-10'
) a
inner join
(
    select 
        id,
        max(modify_time)
    from user_binlog
    where pt_days='2019-07-10'
    and type in ('insert','update') 
    group by id
) b
on a.id=b.id 
and a.modify_time=b.modify_time
;

  好，所以到这一步，日常流程中（user_link_tmp）临时表已经处理完（新增的我用红框框出来）：

 （3）、overwrite到拉链表

会把删除拉链表中对应的分区（临时表中有的分区）

-- 将临时表中的数据覆盖到拉链表中。
insert overwrite table user_link partition(start_date,end_date)
select 
	id,
    name,
    phone,
    sing_up_date,
    modify_time,
    start_date,
    end_date
from user_link_tmp;

-- 删除临时表中的数据
truncate table user_link_tmp;

4、ETL流程（跨月）

每个月2号（假设是7月2号）要处理的事情：

• （新增）将（6月）上个月所有未失效的数据结算为失效（结束日期统一写7月1号）
• （新增）将（6月）上个月所有未失效，并且今天也未失效的数据，写入开始时间为2019-07-01，结束时间为9999-12-31
• （新增）将7月1号新增的用户写入开始时间为2019-07-01，结束时间为9999-12-31
• （删除）将上一个月，结束时间为9999-12-31分区删除干净

（1）、上个月所有未失效数据结算为失效

truncate table user_link_tmp;

-- 把拉链表所有6月30日未失效的数据失效日期改为7月1日
insert into table user_link_tmp;
select
    id, 
    name,
    phone,
    sing_up_date,
    modify_time,
    start_date,
    '2019-07-01' as end_date
from user_link
where start_date<='2019-06-30' 
and start_date>='2019-06-01' 
and end_date='9999-12-31'

临时表中的数据如下：

（2）、上个月未失效，并且今天也未失效

-- 把6月未失效，并且今天也未失效的数据，改为开始日期7月1日，失效日期9999-12-31
insert into table user_link_tmp
select 
    id,
    name,
    phone,
    sing_up_date,
    modify_time,
    '2019-07-01' as start_date,
    '9999-12-31' as end_date
from user_link
where start_date<='2019-06-30' 
and start_date>='2019-06-01' 
and end_date='9999-12-31'
and id not in
(
    select 
        id 
    from user_binlog 
    where pt_days='2019-07-01' 
    and type in ('update','delete') 
    group by id
);

临时表中的数据如下：

（3）、今天新增的数据

-- 把7月1日新的数据写入到临时表中
insert into table user_link_tmp
select 
    a.id,
    a.name,
    a.phone,
    a.sing_up_date,
    a.modify_time,
    '2019-07-01' as start_date,
    '9999-12-31' as end_date
from
(
    select 
        id,
        name,
        phone,
        sing_up_date,
        modify_time
    from user_binlog
    where pt_days='2019-07-01'
) a
inner join
(
    select 
        id,
        max(modify_time)
    from user_binlog
    where pt_days='2019-07-01'
    and type in ('insert','update') 
    group by id
) b
on a.id=b.id
and a.modify_time=b.modify_time

临时表中的数据如下：

（4）、插入数据到拉链表，删除6月份未失效的数据分区

-- 将临时表中的数据覆盖到拉链表中。
insert overwrite table user_link partition(start_date,end_date)
select 
    id,
    name,
    phone,
    sing_up_date,
    modify_time,
    start_date,
    end_date
from user_link_tmp;

-- 删除临时表中的数据
truncate table user_link_tmp;

-- 删除6月份所有结束时间为9999-12-31分区的数据
alter table user_link_tmp drop if exists partition(stat_date>='2019-06-01' , start_date<'2019-07-01', end_date='9999-12-31' );

5、关于查询

（1）、查询所有未失效的数据

结束时间取9999-12-31，开始时间（注意跨月）：今日减1天，取月份，拼接01和31

比如7月2号，那开始时间就是在7月1号和7月31号之间

比如7月1号，那开始时间就是在6月1号和6月31号之间

当然如果条件只写结束时间取9999-12-31，查出来的数据也不会有问题，只是要看explain，分区裁剪合不合理

（2）、具体查某条数据在某天的数据

比如id=100的数据，在6月10号是什么状态？

过滤条件如下：

start_date>=20190601 and start_date<=20190610 and end_date >20190610

（3）、拉链表跟行为表join

分为2种情况

行为表的数据只有一天：

可以先通过上面的查询，将拉链表中的部分数据抽取出来作为一张临时表，接着通过主键（比如本文例子中就是id）关联

行为表的数据有多天：

这就涉及到不等值join，可以去看我另一篇文章，但是要注意并不是所有的框架都支持非等值join，比如hive好像就不行（所以还是推荐一天一天处理）

【数据库】join的用法和场景（包含非等值于非等值）：https://blog.csdn.net/lsr40/article/details/99569049

6、注意

如果数据要重跑，得从重跑的那天，按照顺序一直跑到当前的日期！

所以拉链表消耗的性能还是比较多的，维表数据量不多的场景下别折磨自己~

好，菜鸡一只，如果哪里写的有误，请各位看官一定要批评指正！！千万千万不要哪里写错了，误导了新人！！

太久太久没写博客了，拉链表这个其实很早就想写了，但是一方面是需要自己花些时间整理消化，另一方面最近确实比较忙，岗位变动，有些不适应，压力突然大了不少，自己还是要多努力，船到桥头自然直把~