关于数据仓库中变化历史数据处理方式：全量表、快照表、拉链表

对于数据仓库中变化的历史数据存储，一般有三种方式：全量表、快照表、拉链表

一、全量表

直接全量导入，覆盖旧数据，保存每天所有的最新状态的数据
优点： 效率比较高,可以查看最新数据
缺点： 查看不到历史数据

二、快照表

按日分区，记录截止数据日期的全量数据，一天一个分区
优点： 效率比较高,可以查看最新数据也可以查看历史数据
缺点： 占用的磁盘空间比较大
例： 有一个user表，每天更新上传昨天的最新数据，比如是今天日期 0914，上传表数据为 user_day0913，明天上传表数据 user_day0914，后天上传表数据 user_daay0915，…

三、拉链表

记录截止数据日期的全量数据

• 记录一个事物从开始，一直到当前状态的所有变化的信息；
• 拉链表每次上报的都是历史记录的最终状态，是记录在当前时刻的历史总量；
• 当前记录存的是当前时间之前的所有历史记录的最后变化量（总量）；
• 只有一个分区；

实现： 通过两张表，一张历史数据表，一张更新表，使用join操作关联
优点： 占用磁盘空间适中,可以查看最新数据也可以查看历史数据
缺点： 效率相对较低

实例解析：

user_his

userid	username	userphone	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	userphone
1001	zhangsan	13088888888
1002	lisi	13044444444

实现思路：

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

SQL语句：

①创建 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     --将新数据的结束日期设为 '9999-12-31'
from user_update
union all   --联合表，查询拉链表 user_his 中是否存在更新表中的数据，left join，根据userid查询，如果userid存在，那么说明这条数据就是需要更新的数据，需要将结束日期更为昨天的日期 '2020-09-13'，此条数据就是历史数据
select 
uh.userid userid,   --这里子查询每个字段的别名都需要与user_his中的字段名一致，不然会找不到该字段
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  --将查询到的所有的数据（更新的和为更新的）统一覆盖，全量导入到 user_his中
;

更新完成，再查询 user_his 表数据如下所示：