解决增量新增及更新中的拉链表

拉链表

• 拉链表的核心思想如下：

1.从ODS层获取增量数据(上一天新增和更新的数据)
2.拿着DWD原始拉链表数据 left join 增量数据 ,目的是修改原始拉链中历史数据的结束时间
3.拿着left join 的结果集 再 union all 增量数据,目的是获取到最新的拉链数据
4.把最新的拉链数据,优先保存到DWD对应的临时表中,目的是保证数据安全,即使中途失败原始拉链数据依然存在
5.使用insert+select 方式把临时表中数据灌入DWD拉链表中

一、dwd层的表结构和表数据如下：

create table dwd.dwd_t1(
    id int,
    name string,
    age int,
    enddate string
)partitioned by (startdate string)
row format delimited
fields terminated by ',';

id	name	age	enddate	startdate
1	张三	18	9999-99-99	2024-05-10
2	李四	19	2024-05-10	2024-05-02
3	王五	24	9999-99-99	2024-05-10

二、ods层的表结构和表数据如下：

create table ods.ods_t1(
    id int,
    name string,
    age int
)partitioned by (dt string)
row format delimited
fields terminated by ',';

id	name	age	dt
1	张三	18	2024-05-10
2	李四	19	2024-05-02
3	王五	24	2024-05-10
4	赵六	22	2024-05-11
1	张三	19	2024-05-11
2	李四	20	2024-05-11

三、需求：把ods层数据的2024-05-11以增量新增并更新的同步方式同步到dwd层中

1.先创建一张目标表的临时表, 用于放置计算后的结果

create table dwd.dwd_t1_tem(
    id int,
    name string,
    age int,
    enddate string
)partitioned by (startdate string) --临时表也可以不分区
row format delimited
fields terminated by ',';

2.执行sql修改历史数据的结束时间,并且把新增和更新的数据都导入到最新拉链表中

• 先创建一张目标表的临时表, 用于放置计算后的结果

create table dwd.dwd_t1_tem(
    id int,
    name string,
    age int,
    enddate string
)partitioned by (startdate string) --临时表也可以不分区
row format delimited
fields terminated by ',';

• 拉链表完整sql如下

with tbl1 as(
    -- 步骤1: 修改历史数据的结束时间
select
    dwd_t1.id ,
    dwd_t1.name ,
    dwd_t1.age ,
    if(ods_t1.id is null or dwd_t1.enddate != '9999-99-99',
        dwd_t1.enddate,
        ods_t1.dt
        ) as enddate,
    dwd_t1.startdate
from dwd.dwd_t1
    left join (select * from ods.ods_t1 where dt = '2024-05-11') as ods_t1
    on dwd_t1.id = ods_t1.id

    union all

    -- 步骤2: 获取新增和更新的数据
select
    id ,
    name ,
    age ,
    '9999-99-99' as enddate,
    dt as startdate
from ods.ods_t1
where dt = '2024-05-11'
)

-- 注意: 最新的拉链数据建议先保存到临时表中,保证数据安全
insert overwrite table dwd.dwd_t1_tem partition(startdate)
select * from tbl1;

-- 最后将临时表数据覆盖回目标表中
insert overwrite table dwd.dwd_t1 partition(startdate)
select * from dwd.dwd_t1_tem;

-- 将临时表删除或者清空数据
truncate table dwd.dwd_t1_tem;

• 注意：操作前后记得查询是否导入成功