关于数据仓库:数据仓库之拉链表

拉链表 是针对数据仓库设计中表存储数据的形式而定义的，顾名思义，所谓拉链，就是记录历史。记录一个事物从开始，始终到以后状态的所有变动的信息。

上面就是一张拉链表，存储的是用户的最根本信息以及每条记录的生命周期。咱们能够应用这张表拿到最新的当天的最新数据以及之前的历史数据。

阐明：

• t_start_date 示意该条记录的生命周期开始工夫，t_end_date 示意该条记录的生命周期完结工夫；
• t_end_date = ‘9999-12-31’ 示意该条记录目前处于无效状态；
• 如果查问以后所有无效的记录，则select * from user where t_end_date = '9999-12-31'
• 如果查问 2017-01-01 的历史快照，则select * from user where t_start_date <= '2017-01-01' and end_date >= '2017-01-01'，这条语句会查问到以下记录：

拉链表的应用场景

在数据仓库的数据模型设计过程中，常常会遇到上面这种表的设计：

1. 有一些表的数据量很大，比方一张用户表，大概 10 亿条记录，50 个字段，这种表，即便应用 ORC 压缩，单张表的存储也会超过 100G，在 HDFS 应用双备份或者三备份的话就更大一些。
2. 表中的局部字段会被 update 更新操作，如用户联系方式，产品的形容信息，订单的状态等等。
3. 须要查看某一个工夫点或者时间段的历史快照信息，比方，查看某一个订单在历史某一个工夫点的状态。
4. 表中的记录变动的比例和频率不是很大，比方，总共有 10 亿的用户，每天新增和发生变化的有 200 万左右，变动的比例占的很小。

对于这种表的设计？上面有几种计划可选：

• 计划一：每天只留最新的一份，比方咱们每天用 datax 抽取最新的一份全量数据到 Hive 中。
• 计划二：每天保留一份全量的切片数据。
• 计划三：应用拉链表。

为什么应用拉链表

计划一：每天只留最新的一份

这种计划就不必多说了，实现起来很简略，每天 drop 掉前一天的数据，从新抽一份最新的。
长处很显著，节俭空间，一些一般的应用也很不便，不必在抉择表的时候加一个工夫分区什么的。
毛病同样显著，没有历史数据，先翻翻旧账只能通过其它形式，比方从流水表外面抽。

计划二：每天保留一份全量的切片数据

每天一份全量的切片是一种比拟稳当的计划，而且历史数据也在。
毛病就是存储空间占用量太大太大了，如果对这边表每天都保留一份全量，那么每次全量中会保留很多不变的信息，对存储是极大的节约。
当然咱们也能够做一些取舍，比方只保留近一个月的数据？然而，需要是无耻的，数据的生命周期不是咱们能齐全左右的。

计划三：拉链表

拉链表在应用上根本兼顾了咱们的需要。
首先它在空间上做了一个取舍，虽说不像计划一那样占用量那么小，然而它每日的增量可能只有计划二的千分之一甚至是万分之一。
其实它能满足计划二所能满足的需要，既能获取最新的数据，也能增加筛选条件也获取历史的数据。
所以咱们还是很有必要来应用拉链表的。

拉链表的设计

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

在 2017-01-01 表中的数据是：

在 2017-01-02 表中的数据是，用户 002 和004材料进行了批改，005是新增用户:

在 2017-01-03 表中的数据是，用户 004 和005材料进行了批改，006是新增用户:

如果在数据仓库中设计成历史拉链表保留该表，则会有上面这样一张表，这是最新一天（即 2017-01-03）的数据：

阐明：

• t_start_date 示意该条记录的生命周期开始工夫，t_end_date 示意该条记录的生命周期完结工夫；
• t_end_date = '9999-12-31'示意该条记录目前处于无效状态；
• 如果查问以后所有无效的记录，则select * from user where t_end_date = '9999-12-31'
• 如果查问 2017-01-01 的历史快照，则select * from user where t_start_date <=‘2017-01-01′ and end_date >= '2017-01-01'。

拉链表的实现与更新

Hive 中实现拉链表

1. 咱们须要一张 ODS 层的用户全量表。至多须要用它来初始化。
2. 每日的用户更新表。

而且咱们要确定拉链表的工夫粒度，比如说拉链表每天只取一个状态，也就是说如果一天有 3 个状态变更，咱们只取最初一个状态，这种天粒度的表其实曾经能解决大部分的问题了。

获取每日的用户增量

监听 Mysql 数据的变动，比如说用 Canal，最初合并每日的变动，获取到最初的一个状态。
假如咱们每天都会取得一份切片数据，咱们能够通过取两天切片数据的不同来作为每日更新表，这种状况下咱们能够对所有的字段先进行 concat，再取md5，这样就 ok 了。
流水表，有每日的变更流水表

表构造

ods层的 user 表

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

ods层的 user_update 表

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

拉链表

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

更新

假如曾经初始化了 2017-01-01 的日期，而后须要更新 2017-01-02 那一天的数据

INSERT OVERWRITE TABLE dws.user_his
SELECT * FROM
(
    SELECT A.user_num,
           A.mobile,
           A.reg_date,
           A.t_start_time,
           CASE
                WHEN A.t_end_time = '9999-12-31' AND B.user_num IS NOT NULL THEN '2017-01-01'
                ELSE A.t_end_time
           END AS t_end_time
    FROM dws.user_his AS A
    LEFT JOIN ods.user_update AS B
    ON A.user_num = B.user_num
UNION
    SELECT C.user_num,
           C.mobile,
           C.reg_date,
           '2017-01-02' AS t_start_time,
           '9999-12-31' AS t_end_time
    FROM ods.user_update AS C
) AS T

补充

拉链表和流水表

流水表寄存的是一个用户的变更记录，比方在一张流水表中，一天的数据中，会寄存一个用户的每条批改记录，然而在拉链表中只有一条记录。
这是拉链表设计时须要留神的一个粒度问题。咱们当然也能够设置的粒度更小一些，个别按天就足够。

查问性能

链表当然也会遇到查问性能的问题，比如说咱们寄存了 5 年的拉链数据，那么这张表势必会比拟大，当查问的时候性能就比拟低了，集体认为两个思路来解决：

1. 在一些查问引擎中，咱们对 start_date 和 end_date 做索引，这样能进步不少性能。
2. 保留局部历史数据，比如说咱们一张表外面寄存全量的拉链表数据，而后再对外裸露一张只提供近 3 个月数据的拉链表。