hive中的缓慢变化维之拉链表

一、前言

数据仓库中数据的同步策略：

1）增量同步：增量同步适用于那些数据量比较大，并且之前的数据不发生变化的数据，储存的是新增加的数据；

2）全量同步：存储的是所有的数据，适用于那些数据量不是很大但是每天都会发生变化的数据，在数据仓库中，如果按照天进行分区，那么每个分区储存的数据就是截止到当天分区的所有的数据，此时要想查询所有数据的话只需要查询当前最新一天分区数据即可；

3）新增及变化同步：存储创建时间和操作时间都是今天的数据，适用于那些数据量比较大，既会有新增也会有变化的数据；

4）特殊同步策略：对某些公众都认定的比如说日期维度，地区维度等，我们只需要在数仓最开始导入一次全量的数据即可，因为时间和地区这些数据都是客观存在且不会有第二种解释的数据。

二、缓慢变化维

缓慢变化维指的是：维度表里面的数据并非是始终不变的，总会随着时间发生变化，但是数据量变化的大小相对来说比较缓慢，例如接下来将要举例说明的用户表，数据每天都在新增，历史数据每天也都在变化着，对缓慢变化维这部分数据使用拉链表来解决无疑是最好的选择。话不多说，我们直接开始。

三、如何解决

假设我们有一个原始表用来存储学生信息的，这里面的数据每天都在发生变化，既有新增也有变化，我们来直接用例子说明。

1.因为在数仓项目中每种数据都需要有一个明确的更新策略，一般情况下拉链表是用来处理新增及变化的数据，所以这里的原始数据层的表每天更新策略为新增及变化，当然在接下来的例子中也能体现出这一点。

2.数仓项目的第一天ods层原始数据：

create table ods_student
(
    id       int,
    name     string,
    age      int,
    class_id int,
    phonenum string
);

insert into ods_student
values (1001, '张三', 13, 61, '133xxxx1111'),
       (1002, '李四', 14, 72, '144xxxx1111'),
       (1003, '王五', 15, 53, '155xxxx1111'),
       (1004, '赵六', 16, 51, '166xxxx1111');

数据是下面这样的

 3. 拉链表的初始化(假设数仓的第一天是"2021-05-20")

数仓的第一天要对拉链表进行一个初始化，记录当前信息的开始时间以及结束时间(即有效时间段)

首先，这里的拉链表我们创建的是一个分区表，每个分区存储的是当天过期的数据，还有一个永远达不到的时间"9999-99-99"这个分区存放的是截止到当前时间全量最新的数据

create table dwd_student
(
    id         int,
    name       string,
    age        int,
    class_id   int,
    phonenum   string,
    start_date string,
    end_date   string
)
    partitioned by (dt string);


--TODO 数仓第一天数据进行初始化【假设第一天为 2021-05-20】第一天数据不存在过期数据，全部存在‘9999-99-99’分区里面
insert into table dwd_student partition (dt = '9999-99-99')
select id,
       name,
       age,
       class_id,
       phonenum,
       '2021-05-20',
       '9999-99-99'
from ods_student;

这里的初始化也就是将原始数据里面添加了两个字段(开始时间和结束时间)，用来记录数据的有效时间段，数据如下

 第一天的数据我们无需额外进行处理，只要进行一个初始化即可

4.当时间到了第二天，ods层又新到了一些新增即变化的数据之后，我们开始进行拉链

我们为了便于观看代码，这里重新建立一张表，里面存储的是ods层第二天新增即变化的数据，就不在开始的ods_student里面放了

create table ods_student2
(
    id       int,
    name     string,
    age      int,
    class_id int,
    phonenum string
);

insert into ods_student2
values (1001, '张小三', 13, 61, '133xxxx1111'),
       (1004, '赵六', 16, 52, '166xxxx1111'),
       (1005, '钱七', 17, 42, '177xxxx1111');

 第二天新来的数据是这样的

 

 我们对比一下第一天的数据来看：

不难发现第二天("2021-05-21")的数据中"张三"的名字发生变化改成了"张小三" ，"赵六"的班级发生变化，第一天在51班，第二天转班到了52班，同时还新增了用户"钱七"，此时真正考验我们的是如何来写这个SQL实现最终的我们需要的效果，下面我们画张图来形象的向大家展示

 先给大家解释一下整张图以及标记的各个字段的意思，因为我们创建拉链表有一个意图就是能看到某些数据的历史记录，所以我们不能只拿出最新全量的数据，我们还需要拿到以前发生变化的数据，所以我们直接将dwd_student表和第二天新增及变化的数据进行full join，然后进行修改取出我们所需要的数据；

其中左边黄色部分的old指的是前一天dwd_student表格，右边绿色部分new指的是第二天ods新来的数据(不知道大家发现没有，这里的new和old也是有学问的哦！！因为第二天ods层来的数据相比第一天的dwd层数据比较新，，所以有此命名)。

其中A部分：old表里面有而new表没有，我们始终要贯穿这么一个理念new表 是基于old表发生新增及变化的数据，所以new表没有，指的是到了第二天这部分数据没有发生变化。

C部分：old表里面有，new表里面也有的数据，再次强调，更新策略是新增及变化，old和new都有的数据说明是第一天发生变化的数据，只有发生变化的数据才会在第二天也会出现。

B部分new表里面有而old表里面没有的数据，这部分数据即是第二天新增的数据。

到此，我们已经将各部分数据来源了解清楚，接下来直接上SQL

with tmp as
         (
             select
                 --TODO 求出最新一天的所有最新数据
                 nvl(new.id, old.id)                                                   new_id,
                 nvl(new.name, old.name)                                               new_name,
                 nvl(new.age, old.age)                                                 new_age,
                 nvl(new.class_id, old.class_id)                                       new_class_id,
                 nvl(new.phonenum, old.phonenum)                                       new_phonenum,
                 nvl(new.start_date, old.start_date)                                   new_start_date,
                 nvl(new.end_date, old.end_date)                                       new_end_date,
                 --TODO 求出前一天的数据到今天已经发生变化了的数据，之后进行拉链需要将每天过期的数据单独分在当天
                 `if`(new.id is not null and old.id is not null, old.id, null)         old_id,
                 `if`(new.id is not null and old.id is not null, old.name, null)       old_name,
                 `if`(new.id is not null and old.id is not null, old.age, null)        old_age,
                 `if`(new.id is not null and old.id is not null, old.class_id, null)   old_class_id,
                 `if`(new.id is not null and old.id is not null, old.phonenum, null)   old_phonenum,
                 `if`(new.id is not null and old.id is not null, old.start_date, null) old_start_date,
                 cast(`if`(new.id is not null and old.id is not null, date_sub(new.start_date, 1),
                           null) as string)                                            old_end_date
             from (
                      select id,
                             name,
                             age,
                             class_id,
                             phonenum,
                             start_date,
                             end_date
                      from dwd_student
                  ) old
                      full join
                  (
                      select id,
                             name,
                             age,
                             class_id,
                             phonenum,
                             '2021-05-21' start_date,
                             '9999-99-99' end_date
                      from ods_student2
                  ) new
                  on old.id = new.id
         )
--TODO 这里要用overwrite,否则会出现重复数据
insert
overwrite
table
dwd_student
partition
(
dt
)
select new_id,
       new_name,
       new_age,
       new_class_id,
       new_phonenum,
       new_start_date,
       new_end_date,
       new_end_date dt
from tmp
union all
select old_id,
       old_name,
       old_age,
       old_class_id,
       old_phonenum,
       old_start_date,
       old_end_date,
       old_end_date dt
from tmp
where old_id is not null;

这里用到了hive的动态分区，所以上面将数据插入前要设置一下开启动态分区，否则会报错

set hive.exec.dynamic.partition.mode=nonstrict;

数据插入后最终效果：

 不难看出，"2020-05-20"发生变化的数据即过期数据存到当天分区，其余新增的以及没有变化的数据即全量最新数据全部都存放在"9999-99-99"这个分区里面。

转载请标明原出处，谢谢大家！