拉链表
1.为什么要做拉链表
拉链表适用于同步新增和变化的数据!对于周期型事实表,采用同步新增和变化的方式!
使用场景:
1)比如订单状态:
三天内该订单状态发生了变化,但是同一个事物内进行的
2)人的出行轨迹
2.拉链表在hive中使用
以同步order_info为例
以下为Mysql中数据的记录形式
2020-02-23
2020-02-24
如果该mysql的表是每天全量同步, 如果不采用拉链表,存在在一张hive的表中,有同一条信息的多条不同时期的状态!比较凌乱,而且很难分别不同的记录之间的顺序关系!
可以采用拉链表,拉(拉取原始数据)链(以链条的形式)表,展示同一条信息,在不同日期的状态,以及同一条信息的随着时间状态的演变顺序!
3.拉链表的形式
(1)查询拉链表中的某天的历史切片
举例查询2020-02-24日在拉链表中的历史切片数据,
代表要查询2020-02-24之前插入的,当前在表中状态是最新的数据!
(2)hive拉拉表制作
1.2020-02-24从mysql同步过来的数据(假如是第一次同步),10个订单数据
2 .2020-02-24从mysql同步过来的数据(假如是第一次同步),3条订单数据
2020-02-25的数据,8号与9号的订单修改了,操作时间为2020-02-25,11号是新增订单
3.创建临时表,结构和目标拉链表一致
create table 临时表名 like ods_order_info
create table dwd_order_info_his_tmp like dwd_order_info_his;
4.混合数据
先插入到临时表,再从临时表查询,覆盖写到拉链表
a)讲拉链表中的所有数据查询出,如果历史数据没有发生变化,不需要修改如果历史数据,和今日新增和变化的数据的id一致,此时需要把历史数据中的end_date从9999-99-99改为当前同步数据的日期-1
b)讲今日的新增和变化数据直接插入到临时表
sql:
insert overwrite TABLE dwd_order_info_his_tmp
SELECT id, total_amount, order_status, user_id, payment_way, out_trade_no, create_time, operate_time,a.dt as start_date,
'9999-99-99' end_date
FROM gmall.ods_order_info a
where dt='2020-02-25'
union all
SELECT oi.id, total_amount, order_status, user_id, payment_way, out_trade_no, create_time, operate_time,
start_date,
if(t.id is null,end_date,date_sub(t.dt,1))end_date
FROM gmall.dwd_order_info_his oi left join
(select id ,dt from ods_order_info where dt='2020-02-25') t
on oi.id=t.id and oi.end_date='9999-99-99'
10.将临时表的数据插入到新表
insert overwrite TABLE dwd_order_info_his select * from dwd_order_info_his_tmp order by id
1.历史的拉链表数据与今天同步过来的数据进行left join 条件为order_id相等并且结束时间有效,今天的order_id不为空的说明是变化的数据,为空的就是没有变化的和新增的数据
2.if(t.id is null,end_date,date_sub(dt,1))end_date 将变化的end_date改为昨天时间,没有变化仍然不变
3.将历史表所有变化的订单的end_time修改为前一天作为结束时间,说明该订单的这个状态的已经结束了
4.将修改后的历史订单与今天同步(新增的和更新的)进行union all 得到就是某个订单历史状态及目前的状态
5.将临时表dwd_order_info_his_tmp数据覆盖dwd_order_info_his
最终看到 8号与9号订单的历史变化是: 2020-02-24订单状态位1,当天就结束了,到了2020--02-25订单状态变为3
可以明显的看到周期事物表中某个事物的周期变化性