关于数据仓库中变化历史数据处理方式：全量表、快照表、拉链表 |
您所在的位置：网站首页 › hive查看历史sql › 关于数据仓库中变化历史数据处理方式：全量表、快照表、拉链表 |
关于数据仓库中变化历史数据处理方式：全量表、快照表、拉链表
|
关于数据仓库中变化历史数据处理方式：全量表、快照表、拉链表
一、全量表二、快照表三、拉链表
对于数据仓库中变化的历史数据存储，一般有三种方式：全量表、快照表、拉链表 一、全量表直接全量导入，覆盖旧数据，保存每天所有的最新状态的数据 优点： 效率比较高,可以查看最新数据 缺点： 查看不到历史数据 二、快照表按日分区，记录截止数据日期的全量数据，一天一个分区 优点： 效率比较高,可以查看最新数据也可以查看历史数据 缺点： 占用的磁盘空间比较大 例： 有一个user表，每天更新上传昨天的最新数据，比如是今天日期 0914，上传表数据为 user_day0913，明天上传表数据 user_day0914，后天上传表数据 user_daay0915，… 三、拉链表记录截止数据日期的全量数据 记录一个事物从开始，一直到当前状态的所有变化的信息；拉链表每次上报的都是历史记录的最终状态，是记录在当前时刻的历史总量；当前记录存的是当前时间之前的所有历史记录的最后变化量（总量）；只有一个分区；实现： 通过两张表，一张历史数据表，一张更新表，使用join操作关联 优点： 占用磁盘空间适中,可以查看最新数据也可以查看历史数据 缺点： 效率相对较低 实例解析： user_his useridusernameuserphonestart_dateend_date1001zhangsan130666666662020-09-019999-12-311002lisi130555555552020-09-019999-12-311003wangwu130777777772020-09-019999-12-31user_update useridusernameuserphone1001zhangsan130888888881002lisi13044444444实现思路： 将更新表所有数据插入到user_his 中, 并将起始日期设置为今天, 截止日期设置为9999-12-31用历史表 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 表数据如下所示： |
【本文地址】
今日新闻 |
推荐新闻 |