【Hive】浅谈拉链表

枯藤老树昏鸦

空调WiFi西瓜

葛优同款沙发

夕阳西下

我就搁那一趴

这天真的是越来越热了🌞

🌈🌈🌈

今天我们主要是hive的拉链表

拉链表不管是在工作中还是面试中

都是被问到的概率都蛮高的哈

先养眼，再学习

❤️

今天还是小姐姐镇楼

在数据仓库或数据集市中，我们大多数都是选择用分区对数据进行一个处理（也有用分桶处理）在这里我们讲的是拉链表。

[toc]

1. 什么是拉链表，拉链表的用法

    a. 拉链表的使用场景

    b. 为什么使用拉链表

        - 方案一

        - 方案二

        - 方案三

2. 拉链表的设计和实现

    a. 在hive中实现拉链表

        - ods层的user表

        - ods层的user_update表

        - 拉链表

        - 实现语句

3. 补充

    a. 流水表和拉链表

    b. 查询性能

4. 总结

1. 首先什么是拉链表？

拉链表是针对数仓设计中表存储数据的方式而定义的。

也就是记录历史，即从开始到现在的所有变化信息。 

demo：这是一张拉链表，存储的是用户最基本的信息，以及每条记录的生命周期，可通过拉链表获取到最新和历史数据。

a. 拉链表的使用场景

在数仓的模型设计中，会有这些要求

1. 有一些表数据量太大

2. 表中部分字段要被update操作

3. 需要查看某一时间端的历史数据

4. 新增和更新的量占全表的比例很小

so，该怎么设计。。。

（先看方案，下面有方案的利弊讲解）

方案一：每天只保留一份最新的，再全量到hive中

方案二：每天保留全量的切片数据

方案三：使用拉链表

b. 为什么使用拉链表

方案一：

简单粗暴，每天drop前一天的数据，保存最新的
优点：节省空间，简单明了
缺点：无历史数据

方案二：

保存一份全量，这样历史数据也在
优点：所有的数据都有
缺点：相当占空间

方案三

拉链表：
优点：既能获取最新数据，也能获取历史数据

2. 拉链表的设计和实现

举个🌰，观察下user表的信息变化

这是2017-01-01的数据

这是2017-01-02的数据，对用户002，004做了修改，新增005

这是2017-01-03的数据，对用户004，005做了修改，新增006

如果将以上的数据变成拉链表存储数据的话，效果如下

说明

1. t_start_date表示该条记录生命周期开始时间，t_end_date表示该条记录的生命周期结束时间

2. t_end_date='9999-12-31'表示该条数据还是有效数据

3. 如果要查询所有有效的数据，select * from user where t_end_date='9999-12-31

4. 如果查询2017-01-02的历史数据，则select * from user where t_start_date <= '2017-01-02' and t_end_date' >= '2017-01-02'

a.在Hive中实现拉链表

因为hive是只能进行delete和insert操作，不支持update操作。

so，我们来实现拉链表

1. 我们需要一张ods的全量表，至少初始化

2. 每天用户更新表 --> 既然要确定拉链表的时间粒度，就需要确认每天的用户增量

3. 我们可以监听mysql数据变化，比如用Canal，最后合并每日的变化，获取到最后的一个状态

4. 假设我们每天都获取到一份切片数据，我们可以通过取两天切片数据的不同来作为每日更新表，这时，我们需要将所有字段先concat，再md5，这就ok

5. 流水表，有每天的变更流水表

下面我们来开始实现拉链表吧，哈哈哈哈

1. 建表 --> ods层的user表

ods层的用户资料切片表结构

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';
)

2. 建表 --> 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';
)

3. 建表 --> 创建拉链表

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';
)

4. 下面要装逼了，开始实现语句！！！

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

3. 补充

拉链表和流水表

（这两个还是是情况视业务而定吧，最优的不表示是最适合的）

流水表：存放的是一个用户的变更记录，比如在一张流水表中，一天的数据中，会存放一个用户的每条修改信息
拉链表：只有一条记录
so，这就是我们要考虑的粒度问题，一般都默认为天

查询性能

既然把拉链表吹的这么牛逼，现在该说说缺点了

拉链表

当然会遇到查询性能问题，数据量越大，查询效率也会有所降低，有两种思路

1. 在一些查询引擎中，我们对start_date和end_date做索引。

2. 保留部分历史数据，比如一张表我们存放全量的拉链表，然后再对外提供3个月的拉链表

4.总结

1. 使用拉链表不一定要有end_date，即失效日期，但加上后，会优化很多查询

2. 可以加上当前状态标识，能快速定位到当前状态

3. 拉链表可以增加一些其他内容，因为我们存储的是一个状态，如果我们状态里面加一个字段，比如修改次数，那么拉链表的作用会更大

以上就是对拉链表的认识和学习

大家如果可以

推荐大家对拉链表最好能够了解并讲出来！

我是狗子

祝你幸福