数据仓库拉链表

最新推荐文章于 2023-05-11 22:20:37 发布

qq_38914431

最新推荐文章于 2023-05-11 22:20:37 发布

阅读量574

点赞数

文章标签：数据仓库

本文链接：https://blog.csdn.net/qq_38914431/article/details/108631055

版权

数据仓库拉链表

缓慢变化维
拉链表应用
周期性事实拉链表
- 测试数据
- 周期性事实拉链表实现

文章学习笔记内容来源：拉勾教育大数据开发高薪训练营。

记录一下数据仓库学习笔记，第一次接触数据仓库，这里记录一下拉链表，下面实现基于Hadoop、Hive。

缓慢变化维

观察的维度事实表数据随时间变化变化缓慢。处理维度表的历史变化信息的问题称为处理缓慢变化维的问题，简称SCD问题

保留原始值

数据的维度属性值不做更改，保留原始值
如商品在商场上架售卖的时间：一个商品在商场多次上架下架进行售卖，商品对应有商品的上架售卖时间。如果业务重点关注商品的首次上架时间，则采用该方式。

直接覆盖

修改维度属性为最新值，直接在原数据上覆盖，不保留历史数据。
如商品属性什么类型的商品：当商品的所属类型发生变化时，直接覆盖商品原来的所属类型。

增加新的属性列

在维度表中新增一列当前属性，原先属性列存放上旧的属性值，当前属性列存放当属性值，还可以增加一列记录变化的时间。
缺点：只能记录最后一次变化的信息。

商品ID	类型
U11111	电子类
商品ID	当前类型	旧类型	修改时间
U11111	通信类	电子类	2020-09-09 12:30:12

快照表

每天保留一份全量的数据
简单、高效。缺点是信息重复，浪费磁盘空间
适用范围：维表不能太大
使用场景多，范围广，一般情况下维度表都不大

拉链表

拉链表适用于：表的数据量大，而且数据会发生新增和变化，但是大部分是变化的（数据发生的变化百分比不大），并且数据是缓慢变化的。
拉链表主要是节省存储空间。

拉链表应用

拉链表处理数据逻辑流程

测试数据

城市片区测试数据 ods.ods_trade_shop_admin_org

id	name	parentid	level
0001		华北	1
0002	0001	石家庄	2
0003	0001	邯郸	2

商家测试数据表ods_trade_shops
shopId,userId,areaId,shopName,shopLevel,STATUS,createTime,modifyTime

2020-09-01数据初始化数据
100052,2,0002,VIxxx旗舰店,1,1,2020-06-28,2020-09-01 13:22:22
100053,3,0002,华为xxx旗舰店,1,1,2020-06-28,2020-09-01 13:22:22
100054,4,0002,小米xxx旗舰店,1,1,2020-06-28,2020-09-01 13:22:22
100056,6,0002,OPxxx自营店,1,1,2020-06-28,2020-09-01 13:22:22
2020-09-02采集到的数据修改了一条数据
100052,2,0002,Vivoxxx旗舰店,1,1,2020-06-28,2020-09-02 13:22:22
2020-09-03 采集到的数据新增了一条数据
100057,2,0003,oneplusxxx旗舰店,1,1,2020-06-03,2020-09-03 13:22:22
2020-09-04 采集到的数据变化了两条数据
100053,3,0003,Horrorxxx旗舰店,1,1,2020-06-28,2020-09-04 13:22:22
100056,6,0003,OPPOxxx自营店,1,1,2020-06-28,2020-09-04 13:22:22

把采集到数据存入Hive表ods_trade_shops

拉链表实现

拉链表创建

create table dim.dim_trade_shops_org(
shopid int, -- 商家ID
shopName string, -- 商家名称
cityId int, -- 商家所在城市ID
cityName string , -- 商家所在城市名称
regionId int , -- 商家所在片区ID 如华东、华南
regionName string, -- 商家所在片区名称 如华东、华南
start_dt string, -- 拉链表补充数据:数据生效日期
end_dt string-- 拉链表补充数据:数据失效日期
)STORED AS PARQUET;

1、初始化拉链表数据，这里的初始化为2020-09-01数据，这里start_date采用ods_trade_shops表createTime或者modifyTime，end_date默认为9999-12-31，这里的地域信息都查询的2020-09-01的数据

with tmp as (
select 
   t1.shopid,
   t1.shopname,
   t2.id as cityid,
   t2.orgname as cityname,
   t3.id as regionid,
   t3.orgname as regionname,
   case when t1.modifyTime is not null
     then substr(t1.modifyTime, 0, 10)
     else substr(t1.createTime, 0, 10)
   end as start_dt,
   '9999-12-31' as end_dt
from (select shopId, shopName, areaId,modifyTime,createTime
    from ods.ods_trade_shops where dt='2020-09-01') t1   -- 查询初始化数据         
   left join  -- 查询城市
   (select id, parentId, orgname, orglevel
    from ods.ods_trade_shop_admin_org
    where orglevel=2 and dt='2020-09-01') t2  
   on t1.areaid = t2.id
   left join  -- 查询区域
   (select id, parentId, orgname, orglevel
    from ods.ods_trade_shop_admin_org
    where orglevel=1 and dt='2020-09-01') t3 
   on t2.parentid = t3.id)
insert overwrite table dim.dim_trade_shops_org
select shopid,shopname,cityid,cityname,regionid,regionname,start_dt,end_dt from tmp;

在这里插入图片描述

2、处理第二日的数据，此后每日数据按照此逻辑处理，这里的地域信息都查询的2020-09-01的数据

-- 查询城市区域
with org as(
 select t2.id as cityid, t2.orgname as cityname,
 t3.id as regionid,t3.orgname as regionname
 from (select id, parentId, orgname, orglevel from ods.ods_trade_shop_admin_org where orglevel=2 and dt='2020-09-01') t2  
left join
  (select id, parentId, orgname, orglevel from ods.ods_trade_shop_admin_org where orglevel=1 and dt='2020-09-01') t3 
 on t2.parentid = t3.id
)
-- 进行写表
insert overwrite table dim.dim_trade_shops_org
-- 查询当天采集的数据
select 
   t1.shopid,
   t1.shopname,
   o.cityid,
   o.cityname,
   o.regionid,
   o.regionname,
   case when t1.modifyTime is not null
     then substr(t1.modifyTime, 0, 10)
     else substr(t1.createTime, 0, 10)
   end as start_dt,
   '9999-12-31' as end_dt
   from (select shopId, shopName, areaId,modifyTime,createTime
    from ods.ods_trade_shops where dt='2020-09-02') t1 
 left join org o on o.cityid = t1.areaId
 union all
-- 处理历史数据。历史数据包括：有修改、无修改的数据
-- 连接上，说明数据被修改
-- 未连接上，说明数据未被修改
 select 
   b.shopid,
   b.shopname,
   o.cityid,
   o.cityname,
   o.regionid,
   o.regionname,
   b.start_dt,
   case when B.end_dt='9999-12-31' and a.shopid is not null
     then date_add('2020-09-02', -1)
     else b.end_dt
  end as end_dt      
 from (select shopId, shopName, areaId,modifyTime,createTime
    from ods.ods_trade_shops where dt='2020-09-02') a
   right join dim.dim_trade_shops_org b
   on a.shopid = b.shopid
  left join org o on o.cityid = b.cityid;

在这里插入图片描述

最后的查询到2020-09-04的数据为

拉链表数据回滚

拉链表的数据进行回滚，恢复到指定日期 rollback_date 那一天的数据
end_date < rollback_date，即结束日期 < 回滚日期。表示该行数据在 rollback_date 之前产生，这些数据需要原样保留。
start_date <= rollback_date <= end_date，即开始日期 <= 回滚日期 <= 结束日期。这些数据是回滚日期之后产生的，但是需要修改。将end_date 改为 9999-12-31
其他数据不用管

将数据回滚到2020-09-02的时刻

insert overwrite table dim.dim_trade_shops_org
select shopid,shopname,cityid,cityname,regionid,regionname,start_dt,end_dt
 from dim.dim_trade_shops_org where end_dt < '2020-09-02'
union
select shopid,shopname,cityid,cityname,regionid,regionname,start_dt,'9999-09-01' as end_dt
from dim.dim_trade_shops_org
where start_dt <= '2020-09-02' and end_dt >= '2020-09-02';

查询结果符合预期

周期性事实拉链表

周期性事实拉链表：数据随时间在一段时间内变化。如在淘宝上面买商品，下单后，会有一条订单记录。这条订单记录的状态会随时间进行更新，但这条数据的订单状态[下单、商家确认、发货、收货、确认收货等]，只会在在一月的时间内发生变化，此后这条数据将不再发生任何变化了。要保存这种周期性变化的数据课可以采用周期性事实拉链表，进行实现。
周期性事实拉链表：采用分区表实现。一条数据只在一段时间内进行变化，此后将不再发生任何变化，可以根据这条数据的创建时间进行分区，根据数据的修改时间进行采集。

测试数据

数据随时间进行周期性发生变化，一段时间后，数据就不再发生变化
订单创建日期修改时间订单编号订单状态

如2020-09-01数据
2020-09-01 12:00:01 2020-09-01 12:00:01 001 创建订单
2020-09-01 12:00:01 2020-09-01 12:00:01 002 创建订单
2020-09-01 12:00:01 2020-09-01 12:00:01 003 支付完成
如2020-09-02数据
2020-09-01 12:00:01 2020-09-02 001 12:00:02 支付完成
2020-09-01 12:00:01 2020-09-02 002 12:00:02 支付完成
2020-09-02 12:00:02 2020-09-02 004 12:00:02 创建订单
如2020-09-03数据
2020-09-03 12:00:02 2020-09-03 004 12:00:03 支付完成
2020-09-03 12:00:03 2020-09-03 005 12:00:03 创建订单
2020-09-03 12:00:03 2020-09-03 005 12:00:03 创建订单

周期性事实拉链表实现

根据采集的数据创建表

CREATE TABLE ods.ods_orders(
orderid STRING, -- 订单ID
createtime STRING, -- 创建时间
modifiedtime STRING, -- 修改时间
status STRING -- 状态
) PARTITIONED BY (dt STRING) -- 根据当日采集数据日期进行分区
row format delimited fields terminated by ',';

创建拉链表，周期性事实拉链表根据创建时间进行分区，一条订单数据订单状态只在一段时间发生变化，此后不再发生变化

CREATE TABLE dwd.dwd_orders(
orderid STRING,
createtime STRING,
modifiedtime STRING,
status STRING,
start_date STRING,
end_date STRING
)partitioned by (dt string) -- 周期性事实拉链表根据创建时间进行分区
STORED AS PARQUET;

1、进行拉链表数据初始化

INSERT OVERWRITE TABLE dwd.dwd_orders partition(dt) -- 根据创建时间动态分区
select 
orderid,
createtime,
modifiedtime,
status,
from_unixtime(unix_timestamp(createtime, 'yyyy-MM-dd HH:mm:ss'),'yyyy-MM-dd') as start_date,
'9999-12-31' as end_date,
from_unixtime(unix_timestamp(createtime, 'yyyy-MM-dd HH:mm:ss'),'yyyy-MM-dd') as dt
from ods.ods_orders t where t.dt ='2020-09-01';

2、处理第二日的数据，此后每日数据按照此逻辑处理

INSERT OVERWRITE TABLE dwd.dwd_orders partition(dt) -- 根据创建时间动态分区
select 
orderid,
createtime,
modifiedtime,
status,
from_unixtime(unix_timestamp(modifiedTime, 'yyyy-MM-dd HH:mm:ss'),'yyyy-MM-dd') as start_date,
'9999-12-31' as end_date,
from_unixtime(unix_timestamp(modifiedTime, 'yyyy-MM-dd HH:mm:ss'),'yyyy-MM-dd') as dt
from ods.ods_orders t where t.dt ='2020-09-02'
union all
select 
a.orderid,
a.createtime,
a.modifiedtime,
a.status,
a.start_date,
case when b.orderid is not null and a.end_date > '2020-09-02'
     then date_add('2020-09-02', -1)
     else a.end_date end as end_date,
from_unixtime(unix_timestamp(a.createtime, 'yyyy-MM-dd HH:mm:ss'),'yyyy-MM-dd') as dt
from (select * from dwd.dwd_orders s where s.dt > date_add('2020-09-02',-30)) a 
left join (select * from ods.ods_orders t where t.dt ='2020-09-02') b
on a.orderid = b.orderid;