Hive 之拉链表

什么是拉链表？

一张存储历史数据的表，记录数据由 “生” 到 “死” 的过程，用于处理缓慢变化维。

好处是拉链表可以保存每条数据的所有历史记录，轨迹十分清晰。

如何实现拉链？

我们在拉链表中常常通过定义 start_time 和 end_time 这两个字段来记录数据当前是否还在有效期内。

具体实现逻辑：

假如用户当前某个字段的值发生了变化，例如：昵称被用户修改了。我们需要进行下面两个操作。

新增数据：
因为发生了变化，此时就会产生一条新的数据，start_time 值为数据更新的时间，而 end_time 的值一般设为 9999-12-31（不过期的时间）。

过期数据：
我们需要将用户的上一条数据设置为过期状态，也就是说用户上一条数据的 end_time 字段的值应该被设置为新数据开始时间的上一秒，让它成为过去式。

通过为新增数据设置过期时间，为过期数据设置结束时间的方式，我们完成对数据的更新操作，同时还可以保存历史数据，这就是拉链表的作用。

拉链表实现示例

1. 我们在 Hive 中 dwd 层创建拉链表，并插入示例数据。

create table if not exists dwd.dwd_zip(
id int, -- 主键
name string,
gender string,
addr string,
start_time string,
end_time string)
row format delimited fields terminated by '\t';

insert into dwd.dwd_zip values
(001,"zhangsan","男","上海","2022-10-20","9999-12-31"),
(002,"lisi","男","北京","2022-10-20","9999-12-31"),
(003,"wangwu","男","天津","2022-10-20","9999-12-31"),
(004,"laoqin","男","常德","2022-10-20","9999-12-31"),
(005,"zc","男","平江","2022-10-20","9999-12-31"),
(006,"lihua","男","长沙","2022-10-20","9999-12-31");
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

插入后数据如下所示：

2. 我们在 Hive 中 ods 层设置增量表 update_zip，并插入示例数据。

create table if not exists ods.update_zip(
id int, -- 主键
name string,
gender string,
addr string,
start_time string,
end_time string)
row format delimited fields terminated by '\t';

insert into ods.update_zip values
(001,"张三","男","上海","2022-10-21","9999-12-31"),
(099,"wuziyi","女","武汉","2022-10-21","9999-12-31");
1
2
3
4
5
6
7
8
9
10
11
12

插入后数据如下所示：

3. 在 ods 层中的增量表中可以看到我们修改了一行数据，增加了一行数据，现在我们通过拉链表的方式对其进行更新。

4. 实现 SQL

create table dwd.tmp_zip as
select
  id,
  name,
  gender,
  addr,
  start_time,
  end_time
from
  ods.update_zip -- 增量表
union all -- 合并历史表
select
  a.id,
  a.name,
  a.gender,
  a.addr,
  a.start_time,
  if(b.id is null or a.end_time < '9999-12-31',a.end_time,date_sub(b.start_time,1)) end_time
from
  dwd.dwd_zip a -- 历史拉链表
left join
  ods.update_zip b -- 增量数据表
on
  a.id = b.id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

重点部分：

if(b.id is null or a.end_time < '9999-12-31',a.end_time,date_sub(b.start_time,1)) end_time
1

通过左连接判断是否新增或者该数据是否为历史记录（即：已经被设置为过期的数据）。

如果没有进行新增或为历史记录，就返回 end_time 原值。

否则就对原拉链表数据的结束时间进行修改，值为增量数据的开始时间减1，将它设置为过期状态。

最终结果如下所示：

拉链表通常情况下的粒度为天。