缓慢变化维 - 理论与实践
2019-09-26 本文已影响0人
别停下思考
月亮的变化.jpg
介绍
概念
Slowly Changing Dimensions
在现实世界中,维度的属性并不是静态的,它会随着时间的流失发生缓慢的变化。这种随时间发生变化的维度我们一般称之为缓慢变化维,并且把处理维度表的历史变化信息的问题称为处理缓慢变化维的问题,有时也简称为处理SCD的问题。
应用场景
支持对于历史数据的追溯,Inmon对于数仓的定义已成为公认。而数仓是这么考虑的:在企业管理和决策中面向主题的、集成的、与时间相关的、不可修改的数据集合。
处理方式
缓慢变化维.png如何使用
-- 通过对开始和结束时间的限定,获取指定时间点数据情况
select name
from tableA
where begin_time<='2019-06-02' and end_time>='2019-06-02'
存在的问题
-
数据重复
处理数据时,总会有各种各样的情况导致数据更新失败或错误。这个时候,需要对应的脚本支持数据重跑。在缓慢变化维中,由于保留历史数据,如何保证重跑数据时保障数据正确性、不会大的变化。
-
存储与性能
缓慢变化维记录整个历史数据,数据会以增量的方式进行扩张。随着时间推移,数据就会越来越多。查询的效率也会随之面临挑战。
-
应用的边界
数据的变化是不可避免的,在处理数据时哪些是必须要使用,那么可用可不用。如何选择
实践应用
基础数据
变化-基础数据.png处理方式a
目标 记录数据变化轨迹,支持数据重跑
-- 更新已有数据
insert overwrite table temp_db.temp_scd_checking
select
a.id
,a.name
,a.money
,a.status
,a.begin_time
,case when b.id is not null and a.end_time='9999-09-09' then date_sub('${dealDate}',1) else a.end_time end as end_time
from temp_db.temp_scd_checking as a
left join (
select
id
from temp_db.temp_scd_source_data
where p_dt='${dealDate}'
) as b on a.id=b.id
;
-- 插入新数据
insert overwrite table temp_db.temp_scd_checking
select
case when a.id is not null then a.id else b.id end as id
,case when a.id is not null then a.name else b.name end as name
,case when a.id is not null then a.money else b.money end as money
,case when a.id is not null then a.status else b.status end as status
,case when a.id is not null then '${dealDate}' else b.begin_time end as begin_time
,case when a.id is not null then '9999-09-09' else b.end_time end as end_time
from (
select
id
,name
,money
,status
,p_dt
from temp_db.temp_scd_source_data
where p_dt='${dealDate}'
) as a
full join (
select
*
from temp_db.temp_scd_checking
where end_time='9999-09-09'
) as b on a.id=b.id
-- 历史去除重复(by: sunhj)
union all
select
id
,name
,money
,status
,begin_time
,end_time
from temp_db.temp_scd_checking
where end_time<>'9999-09-09'
group by
id
,name
,money
,status
,begin_time
,end_time
;
结果
重跑结果(0602-0604)
方式a重跑.png处理方式b
目标 方式a中处理效果太差,时间、脚本都太长。进行优化
-- 支持历史重刷 (by sunhj)
insert overwrite table temp_db.temp_scd_checking
select
a.id
,a.name
,a.money
,a.status
,a.begin_time
,case when b.id is not null and a.end_time='9999-09-09' then date_sub('${dealDate}',1) else a.end_time end as end_time
from
(
select
id
,name
,money
,status
,begin_time
,case when begin_time=date_sub('${dealDate}',1) then '9999-09-09' else end_time end as end_time
from temp_db.temp_scd_checking
where begin_time<'${dealDate}'
) as a
left join (
select
id
from temp_db.temp_scd_source_data
where p_dt='${dealDate}'
) as b on a.id=b.id
;
-- 更新数据 (by sunhj)
insert overwrite table temp_db.temp_scd_checking
select
case when a.id is not null then a.id else b.id end as id
,case when a.id is not null then a.name else b.name end as name
,case when a.id is not null then a.money else b.money end as money
,case when a.id is not null then a.status else b.status end as status
,case when a.id is not null then a.begin_time else b.begin_time end as begin_time
,case when a.id is not null then a.end_time else b.end_time end as end_time
from (
select
id
,name
,money
,status
,'${dealDate}' as begin_time
,'9999-09-09' as end_time
from temp_db.temp_scd_source_data
where p_dt='${dealDate}'
) as a
full join temp_db.temp_scd_checking as b on a.id=b.id and b.end_time='9999-09-09'
;
结果略.
优劣对比
处理方式a | 处理方式b | |
---|---|---|
代码简洁与可读性 | 高 | 低 |
执行效率 | 高 | 低(基于hive,增加近80%的时间延迟) |
数据重跑 | 支持 | 支持 |
重跑脏数据 | 无 | 有 |
总结: 建议方式b处理