数仓之缓慢变化维处理方式

一 什么是缓慢变化维

  缓慢变化维：维度建模的数据仓库中，有一个概念叫Slowly Changing Dimensions，中文一般翻译成“缓慢变化维”，经常被简写为SCD。缓慢变化维的提出是因为在现实世界中，维度的属性并不是静态的，它会随着时间的流逝发生缓慢的变化。这种随时间发生变化的维度我们一般称之为缓慢变化维，并且把处理维度表的历史变化信息的问题称为处理缓慢变化维的问题，有时也简称为处理SCD的问题。

二 缓慢变化维处理方式

  这里主要介绍缓慢变化维用到的最多的2种处理方式，分别是全量快照和拉链表

1 全量快照

  每天保留一份全量快照，简单而有效，开发和维护成本低；开发和维护成本低，且方便理解和使用。数据使用方只需要限定日期即可以获取当天的快照数据，任意一天的事实快照和维度快照通过维度的自然键进行关联即可。缺点是浪费存储空间，尤其是当数据的变化比例比较低时。

1）建表语句

  ods_user_info表为ods层每天从业务库同步过来新增和变化的用户表数据，dim_user_info_full为dim层的用户全量快照维度表，每天一个全量分区。

drop table if exists ods_user_info;
create external table ods_user_info( 
    `id` string COMMENT '用户id',
    `name`  string COMMENT '姓名',
    `birthday` string COMMENT '生日',
    `gender` string COMMENT '性别',
    `email` string COMMENT '邮箱',
    `user_level` string COMMENT '用户等级',
    `etl_time` string COMMENT 'ETL时间',
    `create_time` string COMMENT '创建时间',
    `update_time` string COMMENT '修改时间'
) COMMENT '用户表'
PARTITIONED BY (`dt` string);

drop table if exists dim_user_info_full;
create external table dim_user_info_full(
    `id` string COMMENT '用户id',
    `name` string COMMENT '姓名', 
    `birthday` string COMMENT '生日',
    `gender` string COMMENT '性别',
    `email` string COMMENT '邮箱',
    `user_level` string COMMENT '用户等级',
    `etl_time` string COMMENT 'ETL时间',
    `create_time` string COMMENT '创建时间',
    `update_time` string COMMENT '修改时间'
) COMMENT '用户全量快照'
PARTITIONED BY (`dt` string);

2）sql实现

  将每天新增和变化的数据于历史数据合并到一起，然后去重取只保留最新的数据。

insert overwrite table dim_user_info_full partition(dt=date_add(current_date(),-1))
select id
    ,name
    ,birthday
    ,gender
    ,email
    ,user_level
    ,create_time
    ,update_time
from(select id
        ,name
        ,birthday
        ,gender
        ,email
        ,user_level
        ,create_time
        ,update_time
        ,row_number() over(partiton by id order by update_time desc) rn
    from (
        select id
            ,name
            ,birthday
            ,gender
            ,email
            ,user_level
            ,create_time
            ,update_time
        from ods_user_info 
        where dt = date_add(current_date(), -1)
        union all 
        select id
            ,name
            ,birthday
            ,gender
            ,email
            ,user_level
            ,create_time
            ,update_time
        from dim_user_info_full
        where dt = date_add(current_date(), -2)
        ) a 
    )b 
where rn = 1;

注意：实际工作中会定义变量来代替date_add(current_date(),-1)

2 拉链表

  拉链表的意义就在于能够更加高效的保存维度信息的历史状态。
拉链表是记录每条信息的生命周期，一旦一条记录的生命周期结束，就重新开始一条新的记录，并把当期日期放入生效开始日期，如果当前信息至今有效，再生效结束日期种填入一个极大值

1）建表语句

  ods_user_info表为ods层每天从业务库同步过来的新增和变化用户表数据，dim_user_info为dim层的用户拉链表

drop table if exists ods_user_info;
create external table ods_user_info( 
    `id` string COMMENT '用户id',
    `name`  string COMMENT '姓名',
    `birthday` string COMMENT '生日',
    `gender` string COMMENT '性别',
    `email` string COMMENT '邮箱',
    `user_level` string COMMENT '用户等级',
    `etl_time` string COMMENT 'ETL时间',
    `create_time` string COMMENT '创建时间',
    `update_time` string COMMENT '修改时间'
) COMMENT '用户表'
PARTITIONED BY (`dt` string);

drop table if exists dim_user_info;
create external table dim_user_info(
    `id` string COMMENT '用户id',
    `name` string COMMENT '姓名', 
    `birthday` string COMMENT '生日',
    `gender` string COMMENT '性别',
    `email` string COMMENT '邮箱',
    `user_level` string COMMENT '用户等级',
    `etl_time` string COMMENT 'ETL时间',
    `create_time` string COMMENT '创建时间',
    `update_time` string COMMENT '修改时间',
    `start_date`  string COMMENT '有效开始日期',
    `end_date`  string COMMENT '有效结束日期'
) COMMENT '用户拉链表';

2）拉链表的制作过程

a 初始化拉链表

  假设拉链表初始化日期为2023年6月30号，将dt = '2023-06-30’的ods层数据写入到用户拉链表dim_user_info，此时ods_user_info在dt = '2023-06-30为业务数据的全量数据

insert overwrite table dim_user_info
select id,
    name,
    birthday,
    gender,
    email,
    user_level,
    from_unixtime(unix_timestamp()) as etl_time,
    create_time,
    update_time,
    '2023-06-30' as start_date,
    '9999-12-31' as end_date
from ods_user_info
where dt = '2023-06-30'

b 旧数据与新数据关联

  dim_user_info与ods_user_info表（保存这每天从业务数据抽取到的新增和变化的数据）关联，新增的数据添加start_date和end_data字段，修改变化记录的end_date

insert overwrite table dim_user_info
select * from 
(
    -- 新增的数据，start_date为T-1，end_date为'9999-12-31',表示为有效数据
    select 
        id,
        name,
        birthday,
        gender,
        email,
        user_level,
        from_unixtime(unix_timestamp()) as etl_time,
        create_time,
        operate_time,
        cast(date_add(current_date(),-1) as string) as start_date,
        '9999-12-31' end_date
    from ods_user_info where dt=date_add(current_date(),-1)
    union all 
    select 
        a.id,
        a.name,
        a.birthday,
        a.gender,
        a.email,
        a.user_level,
        a.etl_time
        a.create_time,
        a.operate_time,
        a.start_date,
        -- 旧数据与新数据关联上，说明是变化的数据，需要修改变化用户的结束时间
        if(b.id is not null and a.end_date='9999-12-31', date_add(b.dt,-1), a.end_date) end_date
    from dim_user_info a 
    left join 
    (
        select
            *
        from ods_user_info
        where dt=date_add(current_date(),-1)
    ) b on di.id=b.id
     and a.start_date < date_add(current_date(),-1)  -- 当天重复执行，可以保证数据的幂等性
)m
order by m.id, start_date;

注意：实际工作中会定义变量来代替date_add(current_date(),-1)，正常情况下是处理T-1的数据，当某天发现ods层数据有问题时，需要回滚，这时候就不能用date_add(current_date(),-1)了。

3） 拉链表的使用

  下表是7月2号的数据

a 获取历史切片数据

select * from dim_user_info
where start_date<= '2023-07-01' and end_date >= '2023-07-01';

b 获取最新数据

select * from dim_user_info
where end_date = '9999-12-31'

3）拉链表优点

  可以有效地处理维度表中的历史数据变化，轻松地查询某个特定时间点的维度信息。
  保留所有数据的同时节省了空间存储

4）拉链表缺点

  数据出错后不好维护，如果发现某一天ods数据有问题，需要一天一天去回滚到目标日期，然后从目标日期再一天天一直跑到最新一天的数据

三 总结

  数据量大，而且数据会发生变化，但是大部分是不变的（即缓慢变化维），否则可以考虑使用每天一分区全量快照维度表，结合数据生命周期，定期清理数据。