【大数据hive】hive 拉链表设计与实现

一、前言

做过电商开发的同学对订单的业务应该不陌生，比如对一条订单数据来说，通常会有一个类似于status的字段来标识这个订单的完整的生命周期，从存储的数据来看，一张表只需要存储这一条数据即可。

但是对于数据分析来看，为了跟踪这个订单的全生命周期的完整过程来说，这并不是一个很好的设计，假如说订单到已支付但未发货，而且在未发货这一步停留的时间很长，对于大数据分析场景来说，这就是一个重要的分析场景，但对于mysql存储的订单表来说，这就有些冗余了。这也就是说，mysql在设计表的时候，是会充分考虑冗余数据量带来的性能问题。

二、拉链表业务背景

我们知道，Hive在实际工作中主要用于构建离线数据仓库，定期的从各种数据源中同步采集数据到Hive中，经过分层转换提供数据给上层其他应用使用。

例如：有一个定时任务每天从MySQL中同步最新的订单信息、用户信息、店铺信息等到数据仓库中，从而进行订单分析、用户分析等。

如下图所示，为一个数仓简单的业务流程图；

2.1 数据同步引发的问题

有下面这样一张用户表tb_user，有过开发经验的同学对类似的订单表应该不陌生，比如每次注册完一个用户后，该表中就会产生一条新的数据，记录了该用户的id、手机号码、用户名、性别、地址等信息。

关于该表在业务中的具体使用场景如下：

• 每天都会有用户注册，产生新的用户信息；
• 每天都需要将MySQL中的用户数据同步到Hive数据仓库中；
• 需要对用户的信息做统计分析，例如统计新增用户的个数、用户性别分布、地区分布、运营商分布等指标；

数据同步的过程大概长下面这样

比如说，在2021-01-01这一天，MySQL中有10条用户信息；

然后通过中间程序（或其他方式）同步到下面的Hive表中了；

现在，假如在 2021-01-02 这一天，在前一天的基础上，MySQL中新增了2条用户注册数据，并且其中有1条用户数据发生更新，

• 新增两条用户数据011和012；
• 008的addr发生了更新，从gz更新为sh；

到了2021-01-03这天，Hive需要对2号的数据进行同步更新处理，此时问题来了：

新增的数据会直接加载到Hive表中，但是更新的数据如何存储在Hive表中？

2.1.1 解决方案1

在Hive中用新的addr覆盖008的老的addr，直接更新

这么做的优点是：实现最简单，使用起来最方便，但缺点也是很明显的，没有历史状态，008的地址是1月2号在sh，但是1月2号之前是在gz的，如果要查询008的1月2号之前的addr就无法查询，也不能使用sh代替；

2.1.2 解决方案2

每次数据改变，根据日期构建一份全量的快照表，每天一张表

这样做的优点是：记录了所有数据在不同时间的状态， 缺点：冗余存储了很多没有发生变化的数据，导致存储的数据量过大；

2.1.3 解决方案3

构建拉链表，通过时间标记发生变化的数据的每种状态的时间周期，如下图表中数据所示，它大意就是，当一条数据中的关键业务标识字段发送了变化，将新增加一条数据，将这条数据的过期时间设置的非常大，作为这条数据的边界，同样主键的数据再次过来的时候，在新增的一条记录中只需要记录变化的字段即可；关于拉链表，下文将做详细的讲述；

三、拉链表设计与原理

3.1 功能与应用场景

拉链表专门用于解决在数据仓库中数据发生变化如何实现数据存储的问题。

拉链表的设计是将更新的数据进行状态记录，没有发生更新的数据不进行状态存储，用于存储所有数据在不同时间上的所有状态，通过时间进行标记每个状态的生命周期，查询时，根据需求可以获取指定时间范围状态的数据，默认用9999-12-31等最大值来表示最新状态。

如下图所示，记录了某些订单的完整生命周期；

3.2 实现步骤

用下面这张图来说明其完整的实现过程

具体来说，操作步骤如下：

3.2.1 Step1

增量采集变化数据，放入增量表中。

3.2.2 Step2

将Hive中的拉链表与临时表的数据进行合并，合并结果写入临时表。

3.2.3 Step3

将临时表的数据覆盖写入拉链表中。

3.3 操作演示

准备一份原始数据，内容如下

3.3.1 创建一张表并加载数据

--创建拉链表
create table dw_zipper(
                          userid string,
                          phone string,
                          nick string,
                          gender int,
                          addr string,
                          starttime string,
                          endtime string
) row format delimited fields terminated by '\t';

--加载模拟数据
load data local inpath '/usr/local/soft/selectdata/zipper.txt' into table dw_zipper;

执行过程

检查数据是否加载进去

3.3.2 模拟增量数据变化

下面为两条新增的数据，以及一条变化的数据

创建一张增量表，并加载数据

create table ods_zipper_update(
                                  userid string,
                                  phone string,
                                  nick string,
                                  gender int,
                                  addr string,
                                  starttime string,
                                  endtime string
) row format delimited fields terminated by '\t';

load data local inpath '/usr/local/soft/selectdata/update.txt' into table ods_zipper_update;

执行过程

检查数据是否加载成功

3.3.3 合并数据

创建一张临时表

create table tmp_zipper(
                           userid string,
                           phone string,
                           nick string,
                           gender int,
                           addr string,
                           starttime string,
                           endtime string
) row format delimited fields terminated by '\t';

执行过程

合并拉链表与增量表

insert overwrite table tmp_zipper
select
    userid,
    phone,
    nick,
    gender,
    addr,
    starttime,
    endtime
from ods_zipper_update
union all
--查询原来拉链表的所有数据，并将这次需要更新的数据的endTime更改为更新值的startTime
select
    a.userid,
    a.phone,
    a.nick,
    a.gender,
    a.addr,
    a.starttime,
    --如果这条数据没有更新或者这条数据不是要更改的数据，就保留原来的值，否则就改为新数据的开始时间-1
    if(b.userid is null or a.endtime < '9999-12-31', a.endtime , date_sub(b.starttime,1)) as endtime
from dw_zipper a  left join ods_zipper_update b
                            on a.userid = b.userid ;

执行上面的sql

覆盖拉链表

insert overwrite table dw_zipper
select * from tmp_zipper;

执行过程

执行完成后，检查拉链表的数据，可以看到新增了2条数据，同时对于相同的那条数据做了时间上的更新；