本篇博客,博主为大家带来的是关于数据仓库中一个非常重要的知识点缓慢变化维的讲解!
码字不易,先赞后看
例如:用根据用户维度,统计不同出生年份的消费金额占比。(80后、90后、00后)。
而期间,用户可能去修改用户数据,例如:将出生日期改成了 1992年。此时,用户维度表就发生了变化。当然这个变化相对事实表的变换要慢。但这个用户维度表的变化,就是缓慢变化维。
这个用户的数据不是一直不变,而是有可能发生变化。例如:用户修改了出生日期、或者用户修改了住址。
以下为解决缓慢变化维问题的几种办法:
例如: 用户维度表
修改前:
修改后:
数据仓库系统的目标之一是正确地表示历史。典型代表就是拉链表。
保留历史的数据,并插入新的数据。
例如: 用户维度表
修改前:
修改后:
用不同的字段来保存不同的值,就是在表中增加一个字段,这个字段用来保存变化后的当前值,而原来的值则被称为变化前的值。总的来说,这种方法通过添加字段来保存变化后的痕迹。
例如: 用户维度表
修改前:
修改后:
另外建一个表来保存历史记录,这种方式就是将历史数据与当前数据完全分开来,在维度中只保存当前最新的数据。
用户维度表
用户维度历史表
这种方式的优点是可以同时分析当前及前一次变化的属性值,缺点是只保留了最后一次变化信息。
数据仓库的数据模型设计过程中,经常会遇到这样的需求:
用户的地址,产品的描述信息,品牌信息等等;
查看某一个产品在历史某一时间点的状态
查看某一个用户在过去某一段时间内,更新过几次等等
总共有1000万的会员,每天新增和发生变化的有10万左右
相信大家看到这里,可能对拉链表技术已经实现的效果可能不太清楚,下面将通过一个案例为大家进行演示实现拉链表的具体操作。
需求: 有一个商品表:
2019年12月20日的数据如下所示:
商品的状态,会随着时间推移而变化,我们需要将商品的所有变化的历史信息都保存下来。如何实现呢?
12月20日商品拉链表的数据:
12月20日的数据是全新的数据导入到dw表
dw_start_date表示某一条数据的生命周期起始时间,即数据从该时间开始有效(即生效日期)
dw_end_date表示某一条数据的生命周期结束时间,即数据到这一天(不包含)(即失效日期)
dw_end_date为9999-12-31,表示当前这条数据是最新的数据,数据到9999-12-31才过期
12月21日商品拉链表的数据
可以发现:
12月22日商品拉链表的数据
操作步骤:
生效日期(dw_start_date) 失效日期(dw_end_date)
编写SQL处理当天最新的数据(新添加的数据和修改过的数据)
编写SQL处理dw层历史数据,重新计算之前的dw_end_date
MySQL创建商品表
-- 创建数据库
create database if not exists demo;
-- 创建商品表
create table if not exists `demo`.`t_product_2`(
goods_id varchar(50), -- 商品编号
goods_status varchar(50), -- 商品状态
createtime varchar(50), -- 商品创建时间
modifytime varchar(50) -- 商品修改时间
);
Hive ODS层建表
-- 创建表
create database if not exists `demo`;
-- 创建ods层表
create table if not exists `demo`.`ods_product_2`(
goods_id string, -- 商品编号
goods_status string, -- 商品状态
createtime string, -- 商品创建时间
modifytime string -- 商品修改时间
)
partitioned by (dt string)
row format delimited fields terminated by ',' stored as TEXTFILE;
Hive dw层创建拉链表
-- 创建拉链表
create table if not exists `demo`.`dw_product_2`(
goods_id string, -- 商品编号
goods_status string, -- 商品状态
createtime string, -- 商品创建时间
modifytime string, -- 商品修改时间
dw_start_date string, -- 生效日期
dw_end_date string -- 失效日期
)
row format delimited fields terminated by ',' stored as TEXTFILE;
全量导入2019年12月20日数据
1、MySQL数据库导入12月20日数据(4条数据)
insert into `demo`.`t_product_2`(goods_id, goods_status, createtime, modifytime) values
('001', '待审核', '2019-12-18', '2019-12-20'),
('002', '待售', '2019-12-19', '2019-12-20'),
('003', '在售', '2019-12-20', '2019-12-20'),
('004', '已删除', '2019-12-15', '2019-12-20');
2、使用Kettle进行全量同步MySQL数据到Hive ods层表
关于如何使用Kettle同步数据的操作博主已经在上面一篇博客大数据实战【千亿级数仓】阶段二详细说明了,感兴趣的朋友可以去看看。
创建Hive分区
-- 创建分区
alter table `demo`.`ods_product_2` add if not exists partition (dt='${dt}');
表输入
SELECT
*
FROM t_product_2
where modifytime <= '${dt}'
3、编写SQL从ods导入dw当天最新的数据
-- 从ods层导入dw当天最新数据
insert overwrite table `demo`.`dw_product_2`
select
goods_id, -- 商品编号
goods_status, -- 商品状态
createtime, -- 商品创建时间
modifytime, -- 商品修改时间
modifytime as dw_start_date, -- 生效日期
'9999-12-31' as dw_end_date -- 失效日期
from
`demo`.`ods_product_2`
where
dt = '2019-12-20';
增量导入2019年12月21日数据 1、MySQL数据库导入12月21日数据(6条数据)
UPDATE `demo`.`t_product_2` SET goods_status = '待售', modifytime = '2019-12-21' WHERE goods_id = '001';
INSERT INTO `demo`.`t_product_2`(goods_id, goods_status, createtime, modifytime) VALUES
('005', '待审核', '2019-12-21', '2019-12-21'),
('006', '待审核', '2019-12-21', '2019-12-21');
2、使用Kettle开发增量同步MySQL数据到Hive ods层表
Hive创建分区
-- 创建分区
alter table `demo`.`ods_product_2` add if not exists partition (dt='${dt}');
表输入读取MySQL数据
SELECT
*
FROM t_product_2
where modifytime = '${dt}'
3、编写SQL处理dw层历史数据,重新计算之前的dw_end_date
-- 重新计算dw层拉链表中的失效时间
select
t1.goods_id, -- 商品编号
t1.goods_status, -- 商品状态
t1.createtime, -- 商品创建时间
t1.modifytime, -- 商品修改时间
t1.dw_start_date, -- 生效日期(生效日期无需重新计算)
case when (t2.goods_id is not null and t1.dw_end_date > '2019-12-21')
then '2019-12-21'
else t1.dw_end_date -- 小的是以前修改的,不用修改,只修改9999-12-31的数据
end as dw_end_date -- 更新生效日期(需要重新计算)
from
`demo`.`dw_product_2` t1
left join
(select * from `demo`.`ods_product_2` where dt='2019-12-21') t2
on t1.goods_id = t2.goods_id
4、合并当天最新的数据和历史数据到dw层
insert overwrite table `demo`.`dw_product_2`
select
t1.goods_id, -- 商品编号
t1.goods_status, -- 商品状态
t1.createtime, -- 商品创建时间
t1.modifytime, -- 商品修改时间
t1.dw_start_date, -- 生效日期(生效日期无需重新计算)
case when (t2.goods_id is not null and t1.dw_end_date > '2019-12-21')
then '2019-12-21'
else t1.dw_end_date
end as dw_end_date -- 更新生效日期(需要重新计算)
from
`demo`.`dw_product_2` t1
left join
(select * from `demo`.`ods_product_2` where dt='2019-12-21') t2
on t1.goods_id = t2.goods_id
union all
select
goods_id, -- 商品编号
goods_status, -- 商品状态
createtime, -- 商品创建时间
modifytime, -- 商品修改时间
modifytime as dw_start_date, -- 生效日期
'9999-12-31' as dw_end_date -- 失效日期
from
`demo`.`ods_product_2` where dt='2019-12-21' -- 只有新增和修改的数据
order by dw_start_date, goods_id;
到这里,我们的拉链表的操作就完了,接下来让我们来进行一些查询来验证效果。
查询拉链表
1、获取2019-12-20日的历史快照数据
select * from demo.dw_product_2 where dw_start_date <= '2019-12-20' and dw_end_date > '2019-12-20' order by goods_id;
2、获取最新的商品快照数据
select * from demo.dw_product_2 where dw_end_date = '9999-12-31' order by goods_id;
感谢看到这里的朋友,为你们的学习精神点赞?
本篇博客为大家详细介绍了大数据数据仓库中一个非常重要的概念——缓慢变化维,以及讲述了一个简单的入门案例。
如果以上过程中出现了任何的纰漏错误,烦请大佬们指正?
受益的朋友或对大数据技术感兴趣的伙伴记得点赞关注支持一波?