拉链表

缓慢变化维

缓慢变化维(SCD;Slowly Changing Dimensions)。在现实世界中，维度的属性随着时间的流失发生缓慢的变化(缓慢是相对事实表而言，事实表数据变化的速度比维度表快)。
处理维度表的历史变化信息的问题称为处理缓慢变化维的问题，简称SCD问题。处理缓慢变化维的方法有以下几种常见方式:

  • 保留原值
  • 直接覆盖
  • 增加新属性列
  • 快照表
  • 拉链表

保留原始值

维度属性值不做更改，保留原始值。
如商品上架售卖时间:一个商品上架售卖后由于其他原因下架，后来又再次上架，此种情况产生了多个商品上架售卖时间。如果业务重点关注的是商品首次上架售卖时间，则采用该方式。

直接覆盖

修改维度属性为最新值，直接覆盖，不保留历史信息。 如商品属于哪个品类:当商品品类发生变化时，直接重写为新品类。

增加新属性列

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

快照表

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

拉链表

拉链表适合于:表的数据量大，而且数据会发生新增和变化，但是大部分是不变的 (数据发生变化的百分比不大)，且是缓慢变化的(如电商中用户信息表中的某些用 户基本属性不可能每天都变化)。主要目的是节省存储空间。

适用场景:

  • 表的数据量大
  • 表中部分字段会被更新
  • 表中记录变量的比例不高
  • 需要保留历史信息

周期性事实表

比如订单表，创建之后会有状态变化。

  • 只保留一份全量。数据和6月22日的记录一样，如果需要查看6月21日订单001的状态，则无法满足;
  • 每天都保留一份全量。在数据仓库中可以在找到所有的历史信息，但数据量大了，而且很多信息都是重复的，会造成较大的存储浪费;
  • 使用拉链表保存历史信息，历史拉链表，既能满足保存历史数据的需求，也能节省存储资源。

前提条件：

  • 订单表的刷新频率为一天，当天获取前一天的增量数据;
  • 如果一个订单在一天内有多次状态变化，只记录最后一个状态的信息;
  • 订单状态包括三个:创建、支付、完成;
  • 创建时间和修改时间只取到天，如果源订单表中没有状态修改时间，那么抽取增量就比较麻烦，需要有个机制来确保能抽取到每天的增量数据;

拉链表的实现

hive 的实现

原始数据

实现思路

  1. 初始化数据，生效时间是当天，失效时间永久
  2. 新增的时候，查看历史表是否有id一样的数据，
    有的话把历史数据的失效时间改为昨天（hive弱支持事务，每次新增都需要覆盖整张表）
  3. 添加新增数据，开始时间当日，结束时间永久
  4. 每日重复2、3

具体实现

  • 建表与数据导入 
    -- 原数据表  
DROP TABLE IF EXISTS test.userinfo;   
CREATE TABLE test.userinfo(  
  userid STRING COMMENT '用户编号',   
  mobile STRING COMMENT '手机号码',   
  regdate STRING COMMENT '注册日期')  
COMMENT '用户信息'  
PARTITIONED BY (dt string)  
row format delimited fields terminated by ',';  
    
-- 拉链表(存放用户历史信息)  
-- 拉链表不是分区表;多了两个字段start_date、end_date   
DROP TABLE IF EXISTS test.userhis;  
CREATE TABLE test.userhis(  
	userid STRING COMMENT '用户编号',   
  mobile STRING COMMENT '手机号码',   
  regdate STRING COMMENT '注册日期',   
  start_date STRING,  
	end_date STRING)  
COMMENT '用户信息拉链表'  
row format delimited fields terminated by ',';  
    
-- 动态分区数据加载:分区的值是不固定的，由输入数据确定  
-- 创建中间表(非分区表)  
drop table if exists test.tmp1;  
create table test.tmp1 as  
select * from test.userinfo;  
    
desc formatted test.tmp1;  
desc formatted test.userinfo;  
    
-- tmp1 非分区表，使用系统默认的字段分割符'\001'  
ALTER table test.tmp1 set serdeproperties("field.delim"=',');  
    
-- 加载数据到临时表  
load data local inpath '/data/lagoudw/data/userinfo.dat' into table test.tmp1;  
    
-- 动态分区非严格模式  
set hive.exec.dynamic.partition.mode = nonstrict;  
    
-- 动态插入分区表  
insert overwrite table test.userinfo  
partition(dt)  
select * from test.tmp1;
  • 更新拉链表数据 
    -- 1、初始化拉链表，导入第一天的数据.   
insert overwrite table test.userhis  
select   
	userid, mobile, regdate, dt as start_date, '9999-12-31' as end_date  
from test.userinfo where dt='$first_date';  
    
-- 2、构建拉链表(userhis), 每日增量数据   
-- userinfo(do_date) + userhis => userhis  
-- userinfo: 新增数据   
-- userhis: 历史数据  
    
insert overwrite table test.userhis  
    
-- 找出拉链表中需要设置为过期的数据，更新它们  
SELECT A.userid, A.mobile, A.regdate, A.start_date,   
	if(B.userid is not null and A.end_date='9999-12-31',   
     date_add('$do_date', -1),  
     A.end_date) end_date  
from test.userhis A   
	left join   
	(select * from test.userinfo where dt='$do_date') B   
	ON A.userid=B.userid  
    
union all  
    
-- 当日新增数据  
select   
	userid, mobile, regdate, dt as start_date, '9999-12-31' as end_date  
from test.userinfo where dt='$do_date';
  • 拉链表的使用 
    -- 查看拉链表中最新数据  
SELECT * from test.userhis where end_date>="9999-12-31" order by userid;  
    
-- 查看拉链表中给定日期数据("2020-06-22")  
SELECT * from test.userhis where start_date<='2020-06-22'   
and end_date>="2020-06-22" order by userid;  
    
-- 查看拉链表中给定日期数据("2020-06-21")  
SELECT * from test.userhis where start_date<='2020-06-21'   
and end_date>="2020-06-21" order by userid;  
    
-- 查看拉链表中给定日期数据("2020-06-20")  
SELECT * from test.userhis where start_date<='2020-06-20'   
and end_date>="2020-06-20" order by userid;

  • 拉链表的回滚
    由于种种原因需要将拉链表恢复到 rollback_date 那一天的数据。此时有:

    • end_date < rollback_date，即结束日期 < 回滚日期。表示该行数据在 rollback_date 之前产生，这些数据需要原样保留
    • start_date <= rollback_date <= end_date，即开始日期 <= 回滚日期 <= 结束日 期。这些数据的失效日期在回滚日期之后, 所以需要修改。将end_date 改为 9999-12-31
    • rollback_date < start_date, 即回滚日期 < 开始日期, 这部分数据需要删除
    -- 1. 忽略回滚日期之后产生的数据 start_date > rollback_date  --> when start_date<=rollback_date  
-- 2. end_date < rollback_date 原封不动, end_date >= rollback_date ， end_date = 永久有效（9999-12-31）	--> if(end_date < rollback_date, end_date, 9999-12-31) end_date  
    
insert overwrite table test.userhis  
select userid, mobile, regdate, start_date,  
	if(end_date<'$rollback_date', end_date, '9999-12-31') end_date  
from test.userhis  
where start_date <= '$rollback_date';
    • 方案二 保存一段时间的增量数据(userinfo)，定期对拉链表做备份(如一个月做一 次备份);如需回滚，直接在备份的拉链表上重跑增量数据。处理简单
  • 周期性订单表的实现
    • 与维表不同，订单事实表的记录数非常多
    • 订单有生命周期;订单的状态不可能永远处于变化之中(订单的生命周期一般在 15天左右)
    • 订单是一个拉链表，而且是分区表
    • 分区的目的:订单一旦终止，不会重复计算
    • 分区的条件:订单创建日期;保证相同的订单在用一个分区
    -- 订单事实表(拉链表)  
DROP TABLE IF EXISTS dwd.dwd_trade_orders;   
create table dwd.dwd_trade_orders(  
`orderId`			 	int,  
  ...  
`createTime`   string,  
`modifiedTime` string,  
`start_date`   string,  
`end_date`     string  
) COMMENT '订单事实拉链表'  
partitioned by (dt string)  
STORED AS PARQUET;  
    
-- 这里需要处理动态分区  
-- 动态插入分区表的时候，最后一个字段是分区依据，别名对结果没有影响  
set hive.exec.dynamic.partition.mode=nonstrict;  
set hive.exec.dynamic.partition=true;  
    
INSERT OVERWRITE TABLE dwd.dwd_trade_orders  
partition(dt)  
    
-- 导入新数据，使用修改时间或创建时间作为开始时间，分区使用开始时间，假设一条订单15天内就会结束更新  
select  
	orderId,  
	...  
	createTime,  
	modifiedTime,   
	case when modifiedtime is not null   
			then from_unixtime(unix_timestamp(modifiedtime, 'yyyy-MM-dd HH:mm:ss'), 'yyyy-MM-dd')  
			else from_unixtime(unix_timestamp(createTime, 'yyyy-MM-dd HH:mm:ss'), 'yyyy-MM-dd')  
	end as start_date,  
	'9999-12-31' end_date,  
	from_unixtime(unix_timestamp(createTime, 'yyyy-MM-dd HH:mm:ss'), 'yyyy-MM-dd') dt  
from ods.ods_trade_orders   
where dt='$do_date'  
    
union all  
    
select  
	T1.orderId,  
	...  
	T1.createTime,  
	T1.modifiedTime,   
	T1.start_date,  
	case when T2.orderId is not null and T1.end_date='9999-12-31'   
				then date_add('$do_date', -1)  
				else T1.end_date  
				end as end_date,  
	T1.dt  
from (select orderId, ...	createTime,modifiedTime, start_date, end_date, dt   
	from dwd.dwd_trade_orders   
	where dt>date_add('$do_date', -15)) T1   --选取15天内的数据，查看是否需要更新状态  
left join   
	(select orderId  
	from ods.ods_trade_orders   
	where dt='$do_date') T2  
on T1.orderId=T2.orderId;