离线数仓拉链表

1、创建表加载数据（准备工作）

-- 用户信息

DROP TABLE IF EXISTS test .userinfo ;

CREATE TABLE test .userinfo (

userid STRING COMMENT ' 用户编号 ' ,

mobile STRING COMMENT ' 手机号码 ' ,

regdate STRING COMMENT ' 注册日期 ' )

COMMENT ' 用户信息 '

PARTITIONED BY (dt string)

row format delimited fields terminated by ',' ;

-- 拉链表（存放用户历史信息）

-- 拉链表不是分区表；多了两个字段 start_date 、 end_date

DROP TABLE IF EXISTS test .userhis ;

CREATE TABLE test .userhis (

userid STRING COMMENT ' 用户编号 ' ,

mobile STRING COMMENT ' 手机号码 ' ,

regdate STRING COMMENT ' 注册日期 ' ,

start_date STRING,

end_date STRING)

COMMENT ' 用户信息拉链表 '

row format delimited fields terminated by ',' ;

 

 

-- 数据 (/data/lagoudw/data/userinfo.dat)

001 , 13551111111 , 2020 - 03 - 01 , 2020 - 06 - 20

002 , 13561111111 , 2020 - 04 - 01 , 2020 - 06 - 20

003 , 13571111111 , 2020 - 05 - 01 , 2020 - 06 - 20

004 , 13581111111 , 2020 - 06 - 01 , 2020 - 06 - 20
002 , 13562222222 , 2020 - 04 - 01 , 2020 - 06 - 21

004 , 13582222222 , 2020 - 06 - 01 , 2020 - 06 - 21

005 , 13552222222 , 2020 - 06 - 21 , 2020 - 06 - 21

004 , 13333333333 , 2020 - 06 - 01 , 2020 - 06 - 22

005 , 13533333333 , 2020 - 06 - 21 , 2020 - 06 - 22

006 , 13733333333 , 2020 - 06 - 22 , 2020 - 06 - 22

001 , 13554444444 , 2020 - 03 - 01 , 2020 - 06 - 23

003 , 13574444444 , 2020 - 05 - 01 , 2020 - 06 - 23

005 , 13555554444 , 2020 - 06 - 21 , 2020 - 06 - 23

007 , 18600744444 , 2020 - 06 - 23 , 2020 - 06 - 23

008 , 18600844444 , 2020 - 06 - 23 , 2020 - 06 - 23

2 、拉链表的实现

userinfo( 分区表 ) => userid 、 mobile 、 regdate => 每日变更的数据（修改的 + 新增的） / 历史数据（第一天）

userhis （拉链表） => 多了两个字段 start_date / end_date
 

-- 步骤：

-- 1 、 userinfo 初始化（ 2020-06-20 ）。获取历史数据

001 , 13551111111 , 2020 - 03 - 01 , 2020 - 06 - 20

002 , 13561111111 , 2020 - 04 - 01 , 2020 - 06 - 20

003 , 13571111111 , 2020 - 05 - 01 , 2020 - 06 - 20

004 , 13581111111 , 2020 - 06 - 01 , 2020 - 06 - 20

-- 2 、初始化拉链表（ 2020-06-20 ）。 userinfo => userhis

insert overwrite table test .userhis

select userid, mobile, regdate, dt as start_date, '9999-12-31' as

end_date

from test .userinfo

where dt= '2020-06-20' ;

-- 3 、次日新增数据（ 2020-06-21 ）；获取新增数据

002 , 13562222222 , 2020 - 04 - 01 , 2020 - 06 - 21

004 , 13582222222 , 2020 - 06 - 01 , 2020 - 06 - 21

005 , 13552222222 , 2020 - 06 - 21 , 2020 - 06 - 21

-- 4 、构建拉链表 (userhis) （ 2020-06-21 ）【核心】 userinfo(2020-06-21) +

userhis => userhis

-- userinfo: 新增数据

-- userhis ：历史数据

-- 第一步：处理新增数据【 userinfo 】（处理逻辑与加载历史数据类似）

select userid, mobile, regdate, dt as start_date, '9999-12-31' as

end_date

from test .userinfo

where dt= '2020-06-21' ;

-- 第二步：处理历史数据【 userhis 】（历史包括两部分：变化的、未变化的）

-- 变化的： start_date: 不变； end_date ：传入日期 -1

-- 未变化的：不做处理

-- 观察数据

select A .userid , B .userid , B .mobile , B .regdate , B .start_Date , B .end_date

from ( select * from test .userinfo where dt= '2020-06-21' ) A

right join test .userhis B

on A .userid =B .userid ;

-- 编写 SQL ，处理历史数据

select B .userid ,

B .mobile ,

B .regdate ,

B .start_Date ,

case when B .end_date = '9999-12-31' and A .userid is not null

then date_add( '2020-06-21' , - 1 )

else B .end_date

end as end_date

from ( select * from test .userinfo where dt= '2020-06-21' ) A

right join test .userhis B

on A .userid =B .userid ;

-- 最终的处理（新增 + 历史数据）

insert overwrite table test .userhis

select userid, mobile, regdate, dt as start_date, '9999-12-31' as

end_date

from test .userinfo

where dt= '2020-06-21'

union all

select B .userid ,

B .mobile ,

B .regdate ,

B .start_Date ,

case when B .end_date = '9999-12-31' and A .userid is not null

then date_add( '2020-06-21' , - 1 )

else B .end_date

end as end_date

from ( select * from test .userinfo where dt= '2020-06-21' ) A

right join test .userhis B

on A .userid =B .userid ;

3 、拉链表的回滚

 

由于种种原因需要将拉链表恢复到 rollback_date 那一天的数据。此时有：

end_date < rollback_date ，即结束日期 < 回滚日期。表示该行数据在 rollback_date 之前产生，这些数据需要原样保留

start_date <= rollback_date <= end_date ，即开始日期 <= 回滚日期 <= 结束日期。这些数据是回滚日期之后产生的，但是需要修改。将end_date 改为 9999-12-31

其他数据不用管

按以上方案进行编码：

1 、处理 end_date < rollback_date 的数据，保留

select userid, mobile, regdate, start_date, end_date, '1' as tag from test .userhis where end_date < '2020-06-22' ;

 

2 、处理 start_date <= rollback_date <= end_date 的数据，设置 end_date=9999-12-31

select userid, mobile, regdate, start_date, '9999-12-31' as end_date, '2' as tag from test .userhis

where start_date <= '2020-06-22' and end_date >= '2020-06-22' ;

 

3 、将前面两步的数据写入临时表 tmp （拉链表）

drop table test .tmp ;

create table test .tmp as

select userid, mobile, regdate, start_date, end_date, '1' as tag

from test .userhis

where end_date < '2020-06-22'

union all

select userid, mobile, regdate, start_date, '9999-12-31' as end_date, '2'

as tag

from test .userhis

where start_date <= '2020-06-22' and end_date >= '2020-06-22' ;

select * from test .tmp cluster by userid, start_date;

 

逐天回滚，检查数据；

 

方案二：保存一段时间的增量数据 (userinfo) ，定期对拉链表做备份（如一个月做一次备份）；

如需回滚，直接在备份的拉链表上重跑增量数据。处理简单