0


拉链表的制作过程

背景:

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

设计思路:

每行数据加上两个字段,开始时间,结束时间。初始值,开始时间设置为当天,结束时间设为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.

标签: big data sql hive

本文转载自: https://blog.csdn.net/zzf135/article/details/124893938
版权归原作者 ddf482 所有, 如有侵权,请联系我们删除。

“拉链表的制作过程”的评论:

还没有评论