大数据-245 离线数仓 - Hive 拉链表入门实战：缓慢变化维 SCD 类型、建表加载

缓慢变化维

缓慢变化维（SCD，Slowly Changing Dimensions），在现实世界中，维度的属性随着时间的流失发生缓慢的变化（缓慢是相对事实表而言，事实表数据变化的速度比维度表快）。

处理维度表的历史变化信息的问题称为处理缓慢变化维的问题，简称SCD问题，处理缓慢变化维的方法有以下几种常见方式：

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

缓慢变化维的类型

SCD 类型 0：不处理变化

  • 特点：属性变化时，不记录变化，只保留最新值。
  • 适用场景：维度的属性对历史分析无影响。
  • 优点：实现简单。
  • 缺点：无法保存历史信息。

SCD 类型 1：覆盖变化

  • 特点：属性变化时，直接覆盖旧值。
  • 适用场景：只需要保留最新的维度信息，历史数据无关紧要。
  • 优点：占用存储空间小，查询效率高。
  • 缺点：无法追溯历史信息，丢失数据变更记录。
  • 示例：客户地址发生变化，仅更新地址字段。

SCD 类型 2：保留历史记录

  • 特点：为每一次变化创建一条新记录，同时可以通过标识字段或时间戳区分当前数据和历史数据。

实现方式：

  • 增加版本号：新增一个版本号字段表示记录版本。

  • 增加有效时间区间：新增开始和结束时间字段表示记录的有效期。

  • 适用场景：需要保留所有历史信息，支持基于时间的回溯查询。

  • 优点：完整记录历史变化。

  • 缺点：数据量增加，查询复杂度可能提高。

  • 示例：客户地址发生变化，保留旧地址记录，新建一条记录保存新地址。

SCD 类型 3：有限的历史记录

  • 特点：为变化的属性设置额外的字段，仅保留有限的历史信息（如最近一次变化）。
  • 适用场景：只需要保存一部分历史信息，对存储空间要求较低。
  • 优点：减少数据量，存储需求低。
  • 缺点：历史记录有限，无法满足更复杂的回溯分析。
  • 示例：添加”旧地址”和”当前地址”两个字段。

SCD 类型 4：历史表

  • 特点：将历史记录存储在单独的历史表中，主表中只保留当前数据。
  • 适用场景：需要完整保存历史信息，同时希望主表保持精简。
  • 优点：主表数据简单，查询当前值效率高。
  • 缺点：需要额外的历史表，查询历史信息时复杂度增加。
  • 示例：主表存储客户的最新地址，历史表存储地址变更记录。

SCD 类型 6：混合型

  • 特点：结合类型 1、2 和 3，既保留最新值，又保留有限的历史信息，还可以保存完整的历史记录。
  • 适用场景：需要兼顾历史记录和当前值查询效率。
  • 优点：兼具多种类型的优点。
  • 缺点：实现较为复杂。
  • 示例：主表记录最新信息，同时增加版本号和时间戳以追溯历史。

保留原始值

维度属性值不做更改，保留原始值。

直接覆盖

修改维度属性为最新值，直接覆盖，不保留历史信息。

增加新属性列

在维度表中新增加新的一列，原先属性列存放上一版本的属性值，当前属性列存放当前版本属性值。

快照表

每天保留一份全量数据，简单高效。缺点是信息重复，浪费磁盘空间。适用的维表不能太大。

拉链表

拉链表适用于：表的数据量大，而且数据会发生新增和变化，但是大部分是不变的，且是缓慢变化的。

适用场景：

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

维表拉链表应用案例

创建表

CREATE DATABASE test;

-- 用户信息（分区表）
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 ',';

数据文件（userinfo.dat）

001,13551111111,2020-03-01,2020-06-20
002,13561111111,2020-04-01,2020-06-20
003,13571111111,2020-05-01,2020-06-20
004,13581111111,2020-06-01,2020-06-20
002,13562222222,2020-04-01,2020-06-21
004,13582222222,2020-06-01,2020-06-21
005,13552222222,2020-06-21,2020-06-21
004,13333333333,2020-06-01,2020-06-22
005,13533333333,2020-06-21,2020-06-22
006,13733333333,2020-06-22,2020-06-22
001,13554444444,2020-03-01,2020-06-23
003,13574444444,2020-05-01,2020-06-23
005,13555554444,2020-06-21,2020-06-23
007,18600744444,2020-06-23,2020-06-23
008,18600844444,2020-06-23,2020-06-23

加载数据

-- 创建中间表(非分区表)
DROP TABLE IF EXISTS test.tmp1;
CREATE TABLE test.tmp1 AS
SELECT * FROM test.userinfo;

-- 设置 tmp1 非分区表的字段分隔符为 ','
ALTER TABLE test.tmp1 SET SERDEPROPERTIES('field.delim' = ',');

-- 向中间表加载数据
LOAD DATA LOCAL INPATH '/opt/wzk/userinfo.dat' INTO TABLE test.tmp1;

-- 启用非严格模式的动态分区
SET hive.exec.dynamic.partition.mode = nonstrict;

-- 从中间表向分区表加载数据
INSERT INTO TABLE test.userinfo
PARTITION (dt)
SELECT * FROM test.tmp1;

错误速查

症状根因定位修复
读者看完仍分不清 SCD 与拉链表把”概念分类”和”工程实现”混写了明确：拉链表通常是 SCD Type 2 的一种实现
SCD Type 0 与”保留原值”前后矛盾Type 0 误写成”保留最新值”Type 0 改为”不随源数据变化而更新，始终保留初始值”
LOAD DATA 后字段错位或分隔失败中间表默认 SerDe 与源文件分隔符不一致建 tmp1 时直接指定分隔符，避免依赖 CTAS 后再改 SerDe
动态分区插入报错未开启非严格动态分区或列顺序不匹配检查 hive.exec.dynamic.partition.mode 并开启 nonstrict
查询到的数据不是”拉链表历史”只完成原始维表装载，没有真正做历史闭链增补 userhis 的初始化和每日增量拉链 SQL
历史查询结果重复或失真未说明当前记录结束日期哨兵值明确当前记录 end_date=‘9999-12-31’ 或业务最大日期
快照表与拉链表选型不清没有给出成本/查询复杂度对比增加选型规则：小表高可读性优先快照表，大表低变更且要追历史优先拉链表