数据仓库之缓慢变化维度处理

最新推荐文章于 2024-05-07 10:56:59 发布

LongRui888

最新推荐文章于 2024-05-07 10:56:59 发布

阅读量1k

点赞数 1

分类专栏：大数据、hadoop、spark

本文链接：https://blog.csdn.net/sqlserverdiscovery/article/details/109453701

版权

大数据、hadoop、spark 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

当维度数据发生变化时，有多种处理方式，一般会用缓慢变化维类型2来处理，也就是当维度属性发生变化时，新生成1行，同时添加开始日期，结束日期。

比如: 业务系统中的用户表：

userId	mobile	regDate
111	12345	2020/1/1
222	11111	2020/2/1

用户111在3月2号登录系统，修改手机号为 12222，那么缓慢变化维类型2来处理后，数据仓库中的用户表：

userId	mobile	regDate	startDate	endDate
111	12345	2020/1/1	2020/1/1	2020/3/1
222	11111	2020/2/1	2020/2/1	9999/12/31
111	22222	2020/1/1	2020/3/2	9999/12/31

可以看到用户111新增加了一条记录，开始日期和结束日期记录了这条数据生效日期。

1、要达到这种效果，有什么前提条件？
在网上很多讲数据仓库的文章，或者维度建模的书里没有提到，要用缓慢变化维类型2来处理，至少业务系统里要记录了用户表的变更日志或者变更流水，比如用户在某一天，可能手机号改了2次，那么这2次修改会对应2条日志或记录（比如：user_update_log表）。

如果在业务系统没有记录这种变化，如果有 mdm主数据管理系统，记录了这种变化情况，也是可以的。
但如果既没有在业务系统内记录变更流水，也没有mdm系统，那就比较尴尬，这种情况下只能从主数据变更文档中提炼变化，生成变化流水，在进行后面的处理。

2、分析要求维度变化的粒度是什么？
上面讲的实际上是业务过程，用户可以修改自己的信息，完成业务操作后，业务系统会记录某人在何时做了什么变化。
上面说到如果一天修改了2次，就会有2条记录，但实际分析的时候就按照天来看数据，不需要细分到一天中的上下午或者分小时来看，所以维度属性一天内变化多次，我们只取最后一次修改的值。

比如：用户 111，在3月2号上午，把手机号从 12345 改成了 123456，到了下午又改成了 22222，那么在业务系统里这个用户的mobile是22222，而到了数据仓库中，这个用户的mobile也是22222，一天内的中间结果，我们不需要记录。

3、如何生成维度变化数据？
这里假设数据都是放到hive表中的，注意使用的是 hive中的sql语法（hql）：

create table ods.user(
userid int,
mobile string,
regDate string,
comment '用户表'
partitioned by (dt string)
row format delimited fields terminated by '\t' lines terminated by '\n'
stored as orc
location '/ods/user';
)

create table ods.user_update_log(
userid int,
mobile string,
regDate string
comment '用户更新日志表'
partitioned by (update_date string)
row format delimited fields terminated by '\t' lines terminated by '\n'
stored as orc
location '/ods/user_update_log';
)

缓慢变化维度表（拉链表）
create table dim.user_arv(
userId int ,
mobile string,
regDate string,
startDate string,
endDate string
row format delimited fields terminated by '\t' lines terminated by '\n'
stored as orc
location '/dim/user_arv';
)

通过如下的sql，生成渐变维度表 user_arv
insert into dwd.user_arv
select *
from
(
select
d.userId,
   d.mobile,
   d.startDate,
   case when d.endDate = '9999-12-31' then {$current_date}-1 else d.endDate end as endDate
from dwd.user_arv d
left join ods.user_update_log as o
on d.userId = o.userId
   and b.userid is not null
       and o.update_date = {$current_date}
union
select
d.userId,
   d.mobile,
   {$current_date} startDate, --生效日期
   '9999-12-31' as endDate
from dwd.user_arv d
) t

4、那么维度表新增一条数据，对事实表有什么影响呢？
（1）对于传统数据仓库来说，用户表结构为：
ID，userId，mobile，regDate，startDate，endDate
   其中 ID为新增加的代理键来作为主键，在业务系统中，当维度数据发生变化后，新增了一条数据，之后业务系统中会记录ID值作为外键，来关联用户表。

   但是这种方式，在实际处理时，会有一定的问题，就是开发业务系统时，一般不会考虑后续数据仓库的建设要求，虽然会记录变更日志，但在用户表不会生成新的记录，用户表里永远是用户最新的信息。
   那这种情况下，就要用下面的方法来处理。

（2）如果在系统不记录代理键ID，可以在etl里先把原始业务数据抽取过来后，按照如下方式生成事实表：
insert into dwd.order
   select
   u.ID, --作为外键，关联用户表
       ...
   from ods.原始业务表 t
   inner join 用户表 u
   on t.业务日期 >= u.开始日期 and t.业务日期 <= u.结束日期
   and t.userId = u.userId

（3）不增加代理键
表结构如下：
userId，mobile，regDate，startDate，endDate
   不用增加ID作为代理键来作为新的主键，严格来讲主键就是 userId、startDate、endDate这3个字段的组合，在事实表中也还是用 userId 来关联用户表，但是需要增加关联条件:

   select ...
   from 事实表 t
   inner join 用户表 u
   on t.业务日期 >= u.开始日期 and t.业务日期 <= u.结束日期
   and t.userId = u.userId

5、此外，业务系统中也可以使用类似的设计(不增加代理键的方式)
比如，数据从客户端抽取上来后，其中包括原始价格，但这个价格不是厂商的价格，所以，要按照抓取的产品id+业务日期，在价格组数据中查找对应有效期内的价格，价格组的表结构如下：

prodId，price，startDate，endDate

可以按照这个方式做关联，取得产品的价格字段：
select
a.prodId,
b.price --匹配到的价格组
   ...
from 抓取数据 a
left join 价格组 b
on a.prodId = b.prodId
   and a.业务日期 between b.startDate and b.endDate