拉链表和快照表怎么更新数据(数据仓库的拉链表)

本文主要给大家展示“如何为数据仓库企业制作拉链表”，简单易懂，组织清晰，希望能帮你解决疑惑。让边肖带领大家学习学习《数据仓库企业如何制作拉链表》一文。

拉链表

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

下面是一个张力链表，存储了用户最基本的信息和每条记录的生命周期。我们可以用这个表格得到当天的最新数据和之前的历史数据。

描述：

T_start_date表示该记录的生命周期开始时间，t_end_date表示该记录的生命周期结束时间；

T _ end _ date=' 9999-12-31 '表示该记录当前处于有效状态；

从用户中选择*其中t _ end _ date=' 9999-12-31 '

如果查询2017-01-01的历史快照，选择* from user，其中t _ start _ date=' 2017-01-01 '和end _ date=' 2017-01-01 '，此语句将查询以下记录：

00-1010在数据仓库的数据模型设计过程中，我们经常会遇到如下的表格设计：

1.有些表有大量的数据，例如一个用户表，大约有10亿条记录和50个字段。在这个表中，即使使用ORC压缩，单个表的存储量也会超过100G，如果在HDFS使用双备份或者三备份的话会更大。

2.表格中的某些字段将通过更新进行更新，如用户联系信息、产品描述信息、订单状态等。

3.您需要检查某个时间点或时间段的历史快照信息，例如，检查订单在某个历史点的状态。

4.表中记录变化的比例和频率都不是很大。比如用户总数10亿，每天新增用户和变化约200万，占变化的比例很小。

对于这种桌子的设计？以下选项可用：

方案一：每天只留最新的一份。例如，我们每天使用datax将最新的完整数据提取到Hive中。

方案二：每天保留完整的切片数据。

方案三：使用拉链手表。

00-1010，不用说，实现起来非常简单。每天丢弃前一天的数据，获取新的副本。优势显而易见。节省空间，一些常用的也很方便。选择表时不需要添加时间分区什么的。缺点也很明显。没有历史数据，老账只能通过其他方式上缴，比如从地下水位上划。

拉链表的使用场景

每天一整片是一个相对安全的计划，历史数据也有。缺点是存储空间太大。如果你每天都保持这个表的满量，那么每次都会有很多不变的信息以满量保存，这是对存储的极大浪费。当然，我们也可以做一些取舍，比如，只保留近一个月的数据？但是需求不要脸，数据的生命周期是我们无法控制的。

方案一：每天只留最新的一份

拉链手表基本上考虑到了我们在使用中的需求。首先，它在空间上做出选择。虽然没有方案一占用的空间大，但其日增量可能只有方案二的千分之一甚至十分之一。其实可以满足第二种方案的要求，既可以获取最新数据，又可以添加过滤条件，获取历史数据。所以我们仍然有必要使用拉链手表。

00-1010 MySQL关系数据库中用户表的信息变化。

2017-01-01表中的数据为：

2017-01-02表中的数据是用户002和004的数据已经修改，005是新用户：

2017-01-03表中的数据是用户004和005的数据已经修改，006是新用户：

如果将数据仓库设计成历史拉链表来保存这个表，就会有一个这样的表，就是最近的一天(即20

17-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中实现拉链表

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

获取每日的用户增量

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

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

3. 流水表，有每日的变更流水表。

表结构 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个月数据的拉链表。

以上是“数据仓库企业数仓拉链表如何制作”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注行业资讯频道！