拉链表的制作过程_拉链表的实现过程

背景：

对于一些维度表，数据量特别大，每天又会有新增或者修改的数据，但是这部分数据总数据量的比重不大。如果做成每日全量数据，会导致大量的重复数据，占用存储资源；如果做成全量最新，则会缺少历史数据。针对这种情况，可以考虑使用拉链表。

设计思路：

每行数据加上两个字段，开始时间，结束时间。初始值，开始时间设置为当天，结束时间设为9999-99-99；新增数据，开始时间设置为当天，结束时间设为9999-99-99；修改数据，原数据的开始时间不变，结束时间改为修改当天，修改的数据开始时间设置为修改当天，结束时间设置为9999-99-99，如此重复。

详细过程：

1）、准备一张用户表，第一天为初始化数据，保存全量用户；后续每日增量数据为新增或修改数据。

create table ods_user(
    user_id      string
   ,user_name    string
)
partition by (
dt string
);
1
2
3
4
5
6
7

2）、准备用户维度的拉链表，分区字段dt；dt = 9999-99-99，存放全量最新数据；dt = 其他日期，存放在当天失效的数据，例如，2022-01-01新增了一条数据，start_date = 2022-01-01，end_date = 9999-99-99，这条数据在2022-01-02有改动，则生成两条数据，第一条：start_date = 2022-01-01，end_date = 2022-01-02，dt = 2022-01-02，第二条：start_date = 2022-01-02,end_date =9999-99-99 ，dt = 9999-99-99

create table dim_user(
    user_id      string
   ,user_name    string
   ,start_date   string
   ,end_date     string
)
partition by (
dt string
);
1
2
3
4
5
6
7
8
9

3）、数据分为两部分。全量最新和修改
假设ods_user表在2022-01-01初始化，写入全量用户数据

--初始化，全量数据写入9999-99-99分区
insert overwrite dim_user partition (dt = 9999-99-99)
select 
    user_id
   ,user_name
   ,'2022-01-01'  as start_date
   ,'9999-99-99'  as end_date
   ,'9999-99-99'  as dt
from ods_user
where dt = 2022-01-01
1
2
3
4
5
6
7
8
9
10

2022-01-02增量任务，写入新增和修改用户数据

--准备数据集，未修改的数据+新增和修改的数据
with tmp as(
select 
    old.user_id      as old_user_id
   ,old.user_name    as old_user_name
   ,old.start_date   as old_user_satrt_date
   ,old.end_date     as old_user_end_date
   ,new.user_id      as new_user_id
   ,new.user_name    as new_user_name
   ,new.start_date   as new_user_satrt_date
   ,new.end_date     as new_user_end_date
from (
select 
    user_id
   ,user_name
   ,start_date
   ,end_date
from dim_user
where dt = '9999-99-99') old
full outer join(
select 
    user_id
   ,user_name
   ,'2022-01-02'  as start_date
   ,'9999-99-99'  as end_date
from ods_user
where dt = '2022-01-02')new
on old.user_id = new.user_id
)
--全量最新数据 union all 历史数据
insert overwrite dim_user partition (dt)
select 
     nvl(new_user_id,old_user_id)          as user_id
    ,nvl(new_user_name,old_user_name)      as user_name
    ,nvl(new_start_date,old_start_date)    as start_date
    ,nvl(new_end_date,odl_end_date)        as end_date
    ,'9999-99-99'                          as dt
from tmp
union all
--历史过期数据
select 
     old_user_id    as user_id
    ,old_user_name  as user_name
    ,old_satrt_date as start_date
    ,'2022-01-02'   as end_date --修改当天
    ,'2022-01-02'   as dt
from tmp 
where old_user_id is not null
and new_user_id is not null
;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

使用方法：

1）、选择最新的全量数据，限制dim_user的dt = 9999-99-99
2）、某个时间点的全量切片，时间点 >= start_date 并且 时间点 <= end_date.