拉链表的制作过程

背景：

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

设计思路：

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

详细过程：

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

createtable ods_user(
    user_id      string
   ,user_name    string
)partitionby(
dt string
);

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

createtable dim_user(
    user_id      string
   ,user_name    string
   ,start_date   string
   ,end_date     string
)partitionby(
dt string
);

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

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
fullouterjoin(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
unionall--历史过期数据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 isnotnulland new_user_id isnotnull;

使用方法：

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