数仓分层:
*根据实际情况分类,也有可能叫其他名字
首先,先得搞明白什么情况下我们需要用到拉链表?
例如:同一条数据我们需要记录不同时间的两个状态。
之前的效果:
订单id | 创建时间 | 订单状态 | start_date | end_date |
---|---|---|---|---|
o001 | 2022-01-01 | 已支付 | 2022-01-01 | 9999-12-31 |
o001 | 2022-01-01 | 已发货 | 2022-01-02 | 9999-12-31 |
最终效果:
使用两个时间字段用来标记一条数据的不同状态。
订单id | 创建时间 | 订单状态 | start_date | end_date |
---|---|---|---|---|
o001 | 2022-01-01 | 已支付 | 2022-01-01 | 2022-01-01 |
o001 | 2022-01-01 | 已发货 | 2022-01-02 | 9999-12-31 |
那么,怎么把之前第一条记录的时间改为历史状态的时间呢?
实现思路:
1、第一次拉链表就是全量导入历史数据即可;
2、之后就是循环拉链表导入数据,将昨天新增和更新的数据增量采集到ODS层分区表的昨天的分区中【start_date:就是昨天的日期】;
3、将历史拉链表和第2步新采集的表,进行left join,如果右表中的id不为null,那么就证明,这条数据是更新的,那么就把之前数据的end_time,时间修改为昨天的时间就可以了(这里我们用到if 判断函数,if(条件,true的结果,false的结果));
4、将新采集的表和第3步对有数据更新的表进行union all;
5、为了保证安全性,一般都是先建立一个临时拉链表,成功之后,只需把临时拉链表的数据覆盖到拉链表就可以了。
代码实现(为方便大家理解,简单的代码仅供参考):
insert overwrite table dwd.fact_shop_order_tmp partition (start_date)
select
*
from (
--1、ods表的新分区数据(有新增和更新的数据)
select
id,
create_time,
order_status,
'9999-99-99' end_date,
'2022-01-02' as start_date
from ods.t_shop_order
where dt='2022-01-02'
--合并
union all
-- 2、历史拉链表数据,并根据up_id判断更新end_time有效期
select
fso.id,
fso.create_time
fso.order_status,
--3、更新end_time:如果没有匹配到变更数据,或者当前已经是无效的历史数据,则保留原始end_time过期时间;否则变更end_time时间为前天(昨天之前有效)
if (tso.id is null or fso.end_date<'9999-99-99', fso.end_date, date_add(tso.dt, -1)) end_time,
-- if(条件,true的结果,false的结果)
-- if(tso.id is not null and fso.end_date = '9999-99-99',date_add(tso.dt, -1), fso.end_date)
fso.start_date
-- 历史拉链表
from dwd.fact_shop_order fso
-- 左连接当前最新数据:左表有结果就有,右表没有就为null
-- 如果左表有并且右表也有:这条数据发生了更新
left join (
select * from ods.t_shop_order where dt='2022-03-20'
) tso
on fso.id=tso.id
) his
order by his.id, start_date;