数仓-维度建模之拉链表

2023-06-20

上一篇整体介绍了下在数仓建设中常用到的数据表类型，其中有一种比较特殊，是跟数据的历史状态相关联的。

在我从事的医疗大数据领域，有一个场景是需要考虑。

就是很多医院会改名，今天叫第一人民医院、过两年就叫上海市第一人民医院，再过几年可能叫红房子医院。这些随着时间推移的记录一般我们也会将其进行保存，那怎么保存这些变更记录呢，或者在数仓体系里面，这些属于变更记录的数据应该怎么来设计表更合适，有没有对应的方法论支撑呢？

这篇文章就是来说说这个问题的。

拉链表，学名叫缓慢变化维（Slowly Changing Dimensions），简称渐变维（SCD），俗称拉链表，是为了记录关键字段的历史变化而设计出来的一种数据存储模型，常见于维度表设计，在数据仓库相关的面试中，也经常有被问到。但是在工程实践中，拉链表真是太麻烦了，而且是在模型设计、初始化、ETL 开发、运维、日常取数等各个环节都很麻烦，而麻烦的设计通常都容易出错，或者对团队成员能力要求高些。

从这个角度来看，拉链表实际上是属于维度表的一部分。

我们先看一个示例，这就是一张拉链表，存储的是用户的最基本信息以及每条记录的生命周期。我们可以使用这张表拿到最新的当天的最新数据以及之前的历史数据。

注册时间	用户编号	手机号	t_start_date	t_end_date
2017-01-01	001	11111	2017-01-01	9999-12-31
2017-01-01	002	222222	2017-01-01	2017-01-01
2017-01-01	002	233333	2017-01-02	9999-12-31
2017-01-02	003	333333	2017-01-01	9999-12-31
2017-01-03	004	444444	2017-01-01	2017-01-01
2017-01-04	004	432432	2017-01-03	9999-12-31

我们暂且不对这张表做细致的讲解，实现案例二会专门来阐述怎么来设计、实现和使用它。

而这张表数据来源一般类似如下的表结构，并没有开始时间与结束时间。

注册时间	用户编号	手机号

对于变化数据的处理方案

我们常说，数据模型设计一定要切合实际业务需求。对于变化数据的处理，常见需求有以下三种：

需求一：保护第一个值

在广告投放的业务场景中，有个很重要的概念叫广告归因，这就是一个典型的必须保护第一个值的案例。就是说一个安装归属到渠道 1 后，就应该永远绑定在该渠道上。

该需求实现最简单，只需要追加新数据就好了。

需求二：保留最新值

当我们不需要记录历史变化的时候，就可以只保留最新值。比如用户修改了出生日期，有可能之前给的是系统默认值。

该需求处理会稍微复杂，需要 update 用户维表，同时如果有对于用户年龄相关的分析，还要重刷相关的事实表数据。

需求三：记录历史变化

我们需要回溯主体历史某一时点的状态的时候，就必须记录历史变化了。比如某一天，某业务员转岗了，那么部门业绩月度汇总的时候，就需要知道该业务员过去在哪些部门待过以及起始日期。

需求三处理起来比较麻烦，方案如下：

方案一：每天记录一份快照，以每天为切片。（冗余了大量数据）
方案二：增加新的列，比如只需要存最近 3 次变化，那么我们新增三列就好了。(只适合特定的场景)
方案三：增加新的行，核心属性变化一次，新增一条，同时新增 2 列（数据开始日期、数据截止日期）。–拉链表方案

这是多数人都能想到的处理思路，即拉链表。适用场景必须是缓慢变化，例如一张表有 10 亿数据，每天变化的只有几万、几十万才能称为缓慢变化，反之如果 10 亿的表每天有 7 亿都会发生变化，那这还适合用拉链表吗？

拉链表的优点是，相对于快照表可以极大的节省存储空间，缺点也很明显就是太麻烦了。

拉链表插入案例

案例一

这里用商品价格的变化作为例子，具体的开发过程要按实际的来，不能照搬代码，编程重要的是了解背后的思路和原理，而不是简单的复制粘贴。

原始表结构：

商品主键	开始时间	结束时间	价格
xxx	start_date	end_date	sale_price

-- 商品原始表这里取名goods_table
select spu_id,
       min(start_date) as start_date,
       end_date as end_date,
       sale_price
from
  (select spu_id,
          start_date,
          if(sale_price = lead_sale_price,lead_end_date,end_date) as end_date,
          sale_price
   from
     ( select spu_id,
              start_date,
              end_date,
              sale_price,
              lead(sale_price,1,null) over(partition by spu_id order by end_date) as lead_sale_price,
              lead(end_date) over(partition by spu_id order by end_date) as lead_end_date
      from goods_table ) t) t	
group by spu_id,
         end_date,
         sale_price ;

以上SQL的整体思路是：将最新的商品记录插入历史拉链表中，然后我们通过HIVE的窗口行数，按照end_date排序，然后分别取下一条的sale_price和end_date，然后再判断本条的价格和下一条的价格是否相等，如果是一样的，那么就把end_date改为下一条的end_date,最后做去重处理，然后就得到我们想要的数据了。

案例二

以上面的用户表为例，我们要实现用户的拉链表。在实现它之前，我们需要先确定一下我们有哪些数据源可以用。

我们需要一张ODS层的用户全量表。至少需要用它来初始化。
每日的用户更新表。

ods层的user表

现在我们来看一下我们ods层的用户资料切片表的结构：

CREATE EXTERNAL TABLE ods.user (
  user_num STRING COMMENT '用户编号',
  mobile STRING COMMENT '手机号码',
  reg_date STRING COMMENT '注册日期'
COMMENT '用户资料表'
PARTITIONED BY (dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
STORED AS ORC
LOCATION '/ods/user';
)

ods层的user_update表

然后我们还需要一张用户每日更新表，前面已经分析过该如果得到这张表，现在我们假设它已经存在。

CREATE EXTERNAL TABLE ods.user_update (
  user_num STRING COMMENT '用户编号',
  mobile STRING COMMENT '手机号码',
  reg_date STRING COMMENT '注册日期'
COMMENT '每日用户资料更新表'
PARTITIONED BY (dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
STORED AS ORC
LOCATION '/ods/user_update';
)

拉链表

现在我们创建一张拉链表：

CREATE EXTERNAL TABLE dws.user_his (
  user_num STRING COMMENT '用户编号',
  mobile STRING COMMENT '手机号码',
  reg_date STRING COMMENT '用户编号',
  t_start_date ,
  t_end_date
COMMENT '用户资料拉链表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
STORED AS ORC
LOCATION '/dws/user_his';
)

实现sql语句

然后初始化的sql就不写了，其实就相当于是拿一天的ods层用户表过来就行，我们写一下每日的更新语句。

现在我们假设我们已经已经初始化了2017-01-01的日期，然后需要更新2017-01-02那一天的数据，我们有了下面的Sql。

然后把两个日期设置为变量就可以了。

INSERT OVERWRITE TABLE dws.user_his
SELECT * FROM
(
    SELECT A.user_num,
           A.mobile,
           A.reg_date,
           A.t_start_time,
           CASE
                WHEN A.t_end_time = '9999-12-31' AND B.user_num IS NOT NULL THEN '2017-01-01'
                ELSE A.t_end_time
           END AS t_end_time
    FROM dws.user_his AS A
    LEFT JOIN ods.user_update AS B
    ON A.user_num = B.user_num
UNION
    SELECT C.user_num,
           C.mobile,
           C.reg_date,
           '2017-01-02' AS t_start_time,
           '9999-12-31' AS t_end_time
    FROM ods.user_update AS C
) AS T

补充

好了，我们分析了拉链表的原理、设计思路、并且在Hive环境下实现了一份拉链表，下面对拉链表做一些小的补充。

拉链表和流水表
流水表存放的是一个用户的变更记录，比如在一张流水表中，一天的数据中，会存放一个用户的每条修改记录，但是在拉链表中只有一条记录。

这是拉链表设计时需要注意的一个粒度问题。我们当然也可以设置的粒度更小一些，一般按天就足够。

查询性能
拉链表当然也会遇到查询性能的问题，比如说我们存放了5年的拉链数据，那么这张表势必会比较大，当查询的时候性能就比较低了，个人认为两个思路来解决：

在一些查询引擎中，我们对start_date和end_date做索引，这样能提高不少性能。
保留部分历史数据，比如说我们一张表里面存放全量的拉链表数据，然后再对外暴露一张只提供近3个月数据的拉链表。

漫谈数据仓库之拉链表（原理、设计以及在Hive中的实现）数仓拉链表木东居士木东居士的博客-CSDN博客

数据仓库实践-拉链表设计 - 知乎 (zhihu.com)