什么是拉链表？详解数据仓库中的历史表设计与实现

创作时间:

作者:

@小白创作中心

什么是拉链表？详解数据仓库中的历史表设计与实现

引用

CSDN

https://blog.csdn.net/KevinDeng35/article/details/128015357

拉链表（也称为历史表）是数据仓库中处理历史数据变更的一种重要技术手段。它能够记录一个事物从开始到当前状态的所有变化信息，广泛应用于需要查看历史快照信息的场景。本文将详细介绍拉链表的概念、使用场景、设计原理以及在Hive中的具体实现方法。

拉链表的使用场景

在数据仓库的数据模型设计过程中，经常会遇到以下几种表的设计需求：

有一张用户表，数据量很大（例如50多个字段，约10亿条记录），即使使用ORC压缩，单张表的存储也会超过100G，HDFS中三副本存储会更大。
表中的部分字段会被更新，如用户联系方式、产品描述信息、订单状态等。
需要查看某一个时间点或时间段的历史快照信息，比如查看某一订单在历史某一时间点的状态。
表中的记录变化的比例和频率不是很大，例如总共有10亿用户，每天新增和发生变化的约200万左右，变化比例很小。

针对上述需求，常见的解决方案有三种：

方案一：每天只保留最新的一份数据，优点是节省空间且使用方便，但缺点是没有历史数据，查看历史数据需要通过其他方式。
方案二：每天保留一份全量的切片数据，优点是保留了历史数据，但缺点是存储空间消耗巨大。
方案三：使用拉链表。

为什么使用拉链表

拉链表在使用上兼顾了存储空间和数据查询的需求：

存储空间：虽然不像方案一那样占用空间少，但每日的增量可能只有方案二的千分之一甚至是万分之一。
数据查询：既能获取最新的数据，也能通过添加筛选条件获取历史数据。

拉链表的设计和实现

以电商网站的用户表为例，说明拉链表的设计原理：

t_start_date：表示该条记录的生命周期开始时间。
t_end_date：表示该条记录的生命周期结束时间，如果为'9999-12-31'则表示该条记录目前处于有效状态。

查询示例：

查询当前所有有效记录：SELECT * FROM user WHERE t_end_date = '9999-12-31'
查询2017-01-02的历史快照：SELECT * FROM user WHERE t_start_date <= '2017-01-02' AND t_end_date >= '2017-01-02'

在Hive中实现拉链表

在大数据场景下，基于HDFS和Hive的数据仓库架构中实现拉链表：

数据源准备：

ODS层的用户全量表（用于初始化）
每日用户更新表（可通过监听MySQL变化、对比切片数据、使用流水表等方式获取）

表结构设计：

ODS层用户表

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

ODS层用户更新表

CREATE EXTERNAL TABLE ods.user_update (
  user_num STRING COMMENT '用户编号',
  mobile STRING COMMENT '手机号码',
  reg_date STRING 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
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
STORED AS ORC
LOCATION '/dws/user_his';

实现SQL语句：
假设已经初始化了2017-01-01的日期，需要更新2017-01-02的数据：

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;