数仓潮汐猎人|数据仓库企业数仓拉链表制作​

拉链表

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

下面就是一张拉链表&＃xff0c;存储的是用户的最基本信息以及每条记录的生命周期。我们可以使用这张表拿到最新的当天的最新数据以及之前的历史数据。

说明&＃xff1a;

• t_start_date 表示该条记录的生命周期开始时间&＃xff0c;t_end_date 表示该条记录的生命周期结束时间&＃xff1b;

• t_end_date &＃61; ‘9999-12-31’表示该条记录目前处于有效状态&＃xff1b;

• 如果查询当前所有有效的记录&＃xff0c;则select * from user where t_end_date &＃61; ‘9999-12-31′

• 如果查询2017-01-01的历史快照&＃xff0c;则select * from user where t_start_date <&＃61; ‘2017-01-01′ and end_date >&＃61; ‘2017-01-01’&＃xff0c;这条语句会查询到以下记录&＃xff1a;

拉链表的使用场景

在数据仓库的数据模型设计过程中&＃xff0c;经常会遇到下面这种表的设计&＃xff1a;

1.有一些表的数据量很大&＃xff0c;比如一张用户表&＃xff0c;大约10亿条记录&＃xff0c;50个字段&＃xff0c;这种表&＃xff0c;即使使用ORC压缩&＃xff0c;单张表的存储也会超过100G&＃xff0c;在HDFS使用双备份或者三备份的话就更大一些。

2.表中的部分字段会被update更新操作&＃xff0c;如用户联系方式&＃xff0c;产品的描述信息&＃xff0c;订单的状态等等。

3.需要查看某一个时间点或者时间段的历史快照信息&＃xff0c;比如&＃xff0c;查看某一个订单在历史某一个时间点的状态。

4.表中的记录变化的比例和频率不是很大&＃xff0c;比如&＃xff0c;总共有10亿的用户&＃xff0c;每天新增和发生变化的有200万左右&＃xff0c;变化的比例占的很小。

对于这种表的设计&＃xff1f;下面有几种方案可选&＃xff1a;

• 方案一&＃xff1a;每天只留最新的一份&＃xff0c;比如我们每天用datax抽取最新的一份全量数据到Hive中。

• 方案二&＃xff1a;每天保留一份全量的切片数据。

• 方案三&＃xff1a;使用拉链表。

方案一&＃xff1a;每天只留最新的一份

这种方案就不用多说了&＃xff0c;实现起来很简单&＃xff0c;每天drop掉前一天的数据&＃xff0c;重新抽一份最新的。优点很明显&＃xff0c;节省空间&＃xff0c;一些普通的使用也很方便&＃xff0c;不用在选择表的时候加一个时间分区什么的。缺点同样明显&＃xff0c;没有历史数据&＃xff0c;先翻翻旧账只能通过其它方式&＃xff0c;比如从流水表里面抽。

方案二&＃xff1a;每天保留一份全量的切片数据

每天一份全量的切片是一种比较稳妥的方案&＃xff0c;而且历史数据也在。缺点就是存储空间占用量太大太大了&＃xff0c;如果对这边表每天都保留一份全量&＃xff0c;那么每次全量中会保存很多不变的信息&＃xff0c;对存储是极大的浪费。当然我们也可以做一些取舍&＃xff0c;比如只保留近一个月的数据&＃xff1f;但是&＃xff0c;需求是无耻的&＃xff0c;数据的生命周期不是我们能完全左右的。

方案三&＃xff1a;拉链表

拉链表在使用上基本兼顾了我们的需求。首先它在空间上做了一个取舍&＃xff0c;虽说不像方案一那样占用量那么小&＃xff0c;但是它每日的增量可能只有方案二的千分之一甚至是万分之一。其实它能满足方案二所能满足的需求&＃xff0c;既能获取最新的数据&＃xff0c;也能添加筛选条件也获取历史的数据。所以我们还是很有必要来使用拉链表的。

拉链表的设计

在Mysql关系型数据库里的user表中信息变化。

在2017-01-01表中的数据是&＃xff1a;

在2017-01-02表中的数据是&＃xff0c;用户002和004资料进行了修改&＃xff0c;005是新增用户:

在2017-01-03表中的数据是&＃xff0c;用户004和005资料进行了修改&＃xff0c;006是新增用户:

如果在数据仓库中设计成历史拉链表保存该表&＃xff0c;则会有下面这样一张表&＃xff0c;这是最新一天&＃xff08;即2017-01-03&＃xff09;的数据&＃xff1a;

说明&＃xff1a;

• t_start_date 表示该条记录的生命周期开始时间&＃xff0c;t_end_date 表示该条记录的生命周期结束时间&＃xff1b;

• t_end_date &＃61; ‘9999-12-31’表示该条记录目前处于有效状态&＃xff1b;

• 如果查询当前所有有效的记录&＃xff0c;则select * from user where t_end_date &＃61; ‘9999-12-31′

• 如果查询2017-01-01的历史快照&＃xff0c;则select * from user where t_start_date <&＃61; ‘2017-01-01′ and end_date >&＃61; ‘2017-01-01’&＃xff0c;这条语句会查询到以下记录&＃xff1a;

拉链表的实现与更新

Hive中实现拉链表

我们需要一张ODS层的用户全量表。至少需要用它来初始化。每日的用户更新表。而且我们要确定拉链表的时间粒度&＃xff0c;比如说拉链表每天只取一个状态&＃xff0c;也就是说如果一天有3个状态变更&＃xff0c;我们只取最后一个状态&＃xff0c;这种天粒度的表其实已经能解决大部分的问题了。

获取每日的用户增量

1. 监听Mysql数据的变化&＃xff0c;比如说用Canal&＃xff0c;最后合并每日的变化&＃xff0c;获取到最后的一个状态。

2. 假设我们每天都会获得一份切片数据&＃xff0c;我们可以通过取两天切片数据的不同来作为每日更新表&＃xff0c;这种情况下我们可以对所有的字段先进行concat&＃xff0c;再取md5&＃xff0c;这样就ok了。

3. 流水表&＃xff0c;有每日的变更流水表。

表结构 ods层的user表

CREATE EXTERNAL TABLE ods.user (user_num STRING COMMENT &＃39;用户编号&＃39;,mobile STRING COMMENT &＃39;手机号码&＃39;,reg_date STRING COMMENT &＃39;注册日期&＃39;
COMMENT &＃39;用户资料表&＃39;
PARTITIONED BY (dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY &＃39;\t&＃39; LINES TERMINATED BY &＃39;\n&＃39;
STORED AS ORC
LOCATION &＃39;/ods/user&＃39;;
)


ods层的user_update表

CREATE EXTERNAL TABLE ods.user_update (user_num STRING COMMENT &＃39;用户编号&＃39;,mobile STRING COMMENT &＃39;手机号码&＃39;,reg_date STRING COMMENT &＃39;注册日期&＃39;
COMMENT &＃39;每日用户资料更新表&＃39;
PARTITIONED BY (dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY &＃39;\t&＃39; LINES TERMINATED BY &＃39;\n&＃39;
STORED AS ORC
LOCATION &＃39;/ods/user_update&＃39;;
)


拉链表

CREATE EXTERNAL TABLE dws.user_his (user_num STRING COMMENT &＃39;用户编号&＃39;,mobile STRING COMMENT &＃39;手机号码&＃39;,reg_date STRING COMMENT &＃39;用户编号&＃39;,t_start_date ,t_end_date
COMMENT &＃39;用户资料拉链表&＃39;
ROW FORMAT DELIMITED FIELDS TERMINATED BY &＃39;\t&＃39; LINES TERMINATED BY &＃39;\n&＃39;
STORED AS ORC
LOCATION &＃39;/dws/user_his&＃39;;
)


更新

假设已经初始化了2017-01-01的日期&＃xff0c;然后需要更新2017-01-02那一天的数据

INSERT OVERWRITE TABLE dws.user_his
SELECT * FROM
(SELECT A.user_num,A.mobile,A.reg_date,A.t_start_time,CASEWHEN A.t_end_time &＃61; &＃39;9999-12-31&＃39; AND B.user_num IS NOT NULL THEN &＃39;2017-01-01&＃39;ELSE A.t_end_timeEND AS t_end_timeFROM dws.user_his AS ALEFT JOIN ods.user_update AS BON A.user_num &＃61; B.user_num
UNIONSELECT C.user_num,C.mobile,C.reg_date,&＃39;2017-01-02&＃39; AS t_start_time,&＃39;9999-12-31&＃39; AS t_end_timeFROM ods.user_update AS C
) AS T


补充

拉链表和流水表

流水表存放的是一个用户的变更记录&＃xff0c;比如在一张流水表中&＃xff0c;一天的数据中&＃xff0c;会存放一个用户的每条修改记录&＃xff0c;但是在拉链表中只有一条记录。这是拉链表设计时需要注意的一个粒度问题。我们当然也可以设置的粒度更小一些&＃xff0c;一般按天就足够。

查询性能

链表当然也会遇到查询性能的问题&＃xff0c;比如说我们存放了5年的拉链数据&＃xff0c;那么这张表势必会比较大&＃xff0c;当查询的时候性能就比较低了&＃xff0c;个人认为两个思路来解决&＃xff1a;

1. 在一些查询引擎中&＃xff0c;我们对start_date和end_date做索引&＃xff0c;这样能提高不少性能。

2. 保留部分历史数据&＃xff0c;比如说我们一张表里面存放全量的拉链表数据&＃xff0c;然后再对外暴露一张只提供近3个月数据的拉链表。

推荐阅读&＃xff1a;

那些被问懵逼的数仓面试题

58基于Flink构建实时数仓实践

干货&＃xff0c;OLAP数仓从百万到百亿级数据量实时分析