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
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)
,定期对拉链表做备份(如一个月做一次备份);
如需回滚,直接在备份的拉链表上重跑增量数据。处理简单