拉链表

user_his表

userid	username	phone	start_date	end_date
1001	zhangsan	13066666666	2020-09-01	9999-12-31
1002	lisi	13055555555	2020-09-01	9999-12-31
1003	wangwu	13077777777	2020-09-01	9999-12-31

user_update表

userid	username	phone
1001	zhangsan	13088888888
1002	lisi	13044444444

1. 将更新表所有数据插入到user_his 中, 并将起始日期设置为今天, 截止日期设置为9999-12-31
2. 用历史表 left join 更新表, 如果能够join上, 那么将历史表中截止日期改为昨天, 使用overwrite覆盖掉

创建user_his表和user_update表并插入数据

create table if not exists user_his(
userid string,
username string,
userphone string,
start_date string,
end_date string
)
stored as textfile;

create table if not exists user_update(
userid string,
username string,
userphone string
)
stored as textfile;

truncate table user_his;
insert into table user_his values
('1001','zhangsan','13066666666','2020-09-01','9999-12-31'), 
('1002','lisi','13055555555','2020-09-01','9999-12-31' ),
('1003','wangwu','13077777777','2020-09-01','9999-12-31' )
;

insert into table user_update values
('1001','zhangsan','13088888888'),
('1002','lisi','13044444444');

更新user_his

insert overwrite table user_his 
select * 
from 
(
select *,
'2020-09-14' start_date,
'9999-12-31' end_date
from user_update
union all 
select 
uh.userid userid,
uh.username username,
uh.userphone userphone,
uh.start_date start_date,
case when uu.userid is not null and uh.end_date='9999-12-31' then '2020-09-13' else uh.end_date end end_date
from user_his uh left join user_update uu on uh.userid=uu.userid
) t
;