0
0

拉链表详解

拉链表产生背景

在数据仓库的数据模型设计过程中，经常会遇到这样的需求：

1、数据量比较大；

2、表中的部分字段会被update,如用户的地址，产品的描述信息，订单的状态等等；

3、需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态，比如，查看某一个用户在过去某一段时间内，更新过几次等等；

4、变化的比例和频率不是很大，比如，总共有1000万的会员，每天新增和发生变化的有10万左右；

5、如果对这边表每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费；

对于这种表有几种方案可选：

  • 方案一：每天只留最新的一份，比如我们每天用Sqoop抽取最新的一份全量数据到Hive中。
  • 方案二：每天保留一份全量的切片数据。
  • 方案三：使用拉链表。

以上方案对比

方案一

这种方案就不用多说了，实现起来很简单，每天drop掉前一天的数据，重新抽一份最新的。

优点很明显，节省空间，一些普通的使用也很方便，不用在选择表的时候加一个时间分区什么的。

缺点同样明显，没有历史数据，先翻翻旧账只能通过其它方式，比如从流水表里面抽。

方案二

每天一份全量的切片是一种比较稳妥的方案，而且历史数据也在。

缺点就是存储空间占用量太大太大了，如果对这边表每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费，这点我感触还是很深的…

当然我们也可以做一些取舍，比如只保留近一个月的数据？但是，需求是无耻的，数据的生命周期不是我们能完全左右的。

拉链表

拉链表在使用上基本兼顾了我们的需求。

首先它在空间上做了一个取舍，虽说不像方案一那样占用量那么小，但是它每日的增量可能只有方案二的千分之一甚至是万分之一。

其实它能满足方案二所能满足的需求，既能获取最新的数据，也能添加筛选条件也获取历史的数据。

所以我们还是很有必要来使用拉链表的。

拉链表概念

拉链表是一种数据模型，主要是针对数据仓库设计中表存储数据的方式而定义的，顾名思义，所谓拉链，就是记录历史。记录一个事物从开始，一直到当前状态的所有变化的信息。拉链表可以避免按每一天存储所有记录造成的海量存储问题，同时也是处理缓慢变化数据（SCD2）的一种常见方式。

百度百科的解释：拉链表是维护历史状态，以及最新状态数据的一种表，拉链表根据拉链粒度的不同，实际上相当于快照，只不过做了优化，去除了一部分不变的记录，通过拉链表可以很方便的还原出拉链时点的客户记录。

拉链表算法

1、采集当日全量数据到ND(NowDay当日)表；

2、可从历史表中取出昨日全量数据存储到OD(OldDay上日)表；

3、两个表进行全字段比较，(ND-OD)就是当日新增和变化的数据，也就是当天的增量，用W_I表示；

4、两个表进行全字段比较，(OD-ND)为状态到此结束需要封链的数据，需要修改END_DATE，用W_U表示；

5、将W_I表的内容全部插入到历史表中，这些是新增记录，start_date为当天，而end_date为max值，可以设为’9999-12-31‘；

6、对历史表进行W_U部份的更新操作，start_date保持不变，而end_date改为当天，也就是关链操作，历史表（OD）和W_U表比较，START_DATE，END_DATE除外，以W_U表为准，两者交集将其END_DATE改成当日，说明该记录失效。

拉链表示例1

举个简单例子，比如有一张订单表：

6月20号有3条记录：
订单创建日期订单编号订单状态2012-06-20001创建订单2012-06-20002创建订单2012-06-20003支付完成
到6月21日，表中有5条记录：
订单创建日期订单编号订单状态2012-06-20001创建订单2012-06-20002创建订单2012-06-20003支付完成2012-06-21004创建订单2012-06-21005创建订单
到6月22日，表中有6条记录：
订单创建日期订单编号订单状态2012-06-20001创建订单2012-06-20002创建订单2012-06-20003支付完成2012-06-21004创建订单2012-06-21005创建订单2012-06-22006创建订单
数据仓库中对该表的保留方法：

1、只保留一份全量，则数据和6月22日的记录一样，如果需要查看6月21日订单001的状态，则无法满足；

2、每天都保留一份全量，则数据仓库中的该表共有14条记录，但好多记录都是重复保存，没有任务变化，如订单002,004，数据量大了，会造成很大的存储浪费；

如果在数据仓库中设计成历史拉链表保存该表，则会有下面这样一张表：
订单创建日期订单编号订单状态dw_bigin_datedw_end_date2012-06-20001创建订单2012-06-202012-06-202012-06-20001支付完成2012-06-219999-12-312012-06-20002创建订单2012-06-209999-12-312012-06-20003支付完成2012-06-202012-06-212012-06-20003已发货2012-06-229999-12-312012-06-21004创建订单2012-06-219999-12-312012-06-21005创建订单2012-06-212012-06-212012-06-21005支付完成2012-06-229999-12-312012-06-22006创建订单2012-06-229999-12-31
说明：

1、dw_begin_date表示该条记录的生命周期开始时间，dw_end_date表示该条记录的生命周期结束时间；

2、dw_end_date = '9999-12-31’表示该条记录目前处于有效状态；

3、如果查询当前所有有效的记录，则select * from order_his where dw_end_date = ‘9999-12-31’；

4、如果查询2012-06-21的历史快照，则select * from order_his where dw_begin_date <= ‘2012-06-21’ and end_date >= ‘2012-06-21’，这条语句会查询到以下记录：
订单创建日期订单编号订单状态dw_bigin_datedw_end_date2012-06-20001支付完成2012-06-219999-12-312012-06-20002创建订单2012-06-209999-12-312012-06-20003支付完成2012-06-202012-06-212012-06-21004创建订单2012-06-219999-12-312012-06-21005创建订单2012-06-212012-06-21
和源表在6月21日的记录完全一致：
订单创建日期订单编号订单状态2012-06-20001创建订单2012-06-20002创建订单2012-06-20003支付完成2012-06-21004创建订单2012-06-21005创建订单
可以看出，这样的历史拉链表，既能满足对历史数据的需求，又能很大程度的节省存储资源；

拉链表示例2：

在历史表中对人的一生的记录可能就这样几条记录，避免了按每一天记录客户状态造成的海量存储的问题：
人名开始日期结束日期状态client1900010119070901H在家client1907090119130901A小学client1913090119160901B初中client1916090119190901C高中client1919090119230901D大学client1923090119601231E公司client1960123129991231H退休在家
上面的每一条记录都是不算末尾的，比如到19070901，client已经在A，而不是H了。所以除最后一条记录因为状态到目前都未改变的，其余的记录实际上在结束日期那天，都不在是该条记录结束日期那天的状态。这种现象可以理解为算头不算尾。

拉链表实现方式

1、定义两个临时表，一个为当日全量数据，另一个为需要新增或更新的数据；

CREATE VOLATILE TABLE VT_xxxx_NEW AS xxxx WITHNODATAONCOMMIT PRESERVE ROWS;CREATE VOLATILE SETTABLE VT_xxxx_CHG,NO LOG AS xxxx WITHNODATAONCOMMIT PRESERVE ROWS;

2、获取当日全量数据

INSERTINTO VT_xxxx_NEW(xx)SELECT(xx,cur_date, max_date)FROM xxxx_sorce;

3、抽取新增或有变化的数据，从xxxx_NEW临时表到xxxx_CHG临时表；

INSERTINTO VT_xxxx_CHG(xx)SELECT xx FROM VT_xxxx_NEW
WHERE(xx)NOTIN(select xx from xxxx_HIS where end_date='max_date');

4、更新历史表的失效记录的end_date为max值

UPDATE A1 FROM xxxx_HIS A1, VT_xxxx_CHG A2
SET End_Date='current_date'WHERE A1.xx=A2.xx AND A1.End_Date='max_date';

5、将新增或者有变化的数据插入目标表

INSERTINTO xxxx_HIS SELECT*FROM VT_xxxx_CHG;

以商品数据为例

存在商品表 t_product，表结构如下：
列名类型说明goods_idvarchar(50)商品编号goods_statusvarchar(50)商品状态（待审核、待售、在售、已删除）createtimevarchar(50)商品创建日期modifytimevarchar(50)商品修改日期
2019年12月20日的数据如下所示：
goods_idgoods_statuscreatetime****modifytime001待审核2019-12-202019-12-20002待售2019-12-202019-12-20003在售2019-12-202019-12-20004已删除2019-12-202019-12-20
商品的状态，会随着时间推移而变化，我们需要将商品的所有变化的历史信息都保存下来。

方案一: 快照每一天的数据到数仓

该方案为：每一天都保存一份全量，将所有数据同步到数仓中，很多记录都是重复保存，没有任何变化。

12月20日（4条数据）

goods_idgoods_statuscreatetime****modifytime001待审核2019-12-182019-12-20002待售2019-12-192019-12-20003在售2019-12-202019-12-20004已删除2019-12-152019-12-20

12月21日（10条数据）

goods_idgoods_statuscreatetime****modifytime以下为12月20日快照数据001待审核2019-12-182019-12-20002待售2019-12-192019-12-20003在售2019-12-202019-12-20004已删除2019-12-152019-12-20以下为12月21日快照数据001待售（从待审核到待售）2019-12-182019-12-21002待售2019-12-192019-12-20003在售2019-12-202019-12-20004已删除2019-12-152019-12-20005（新商品）待审核2019-12-212019-12-21006（新商品）待审核2019-12-212019-12-21

12月22日（18条数据）

goods_idgoods_statuscreatetime****modifytime以下为12月20日快照数据001待审核2019-12-182019-12-20002待售2019-12-192019-12-20003在售2019-12-202019-12-20004已删除2019-12-152019-12-20以下为12月21日快照数据001待售（从待审核到待售）2019-12-182019-12-21002待售2019-12-192019-12-20003在售2019-12-202019-12-20004已删除2019-12-152019-12-20005待审核2019-12-212019-12-21006待审核2019-12-212019-12-21以下为12月22日快照数据001待售2019-12-182019-12-21002待售2019-12-192019-12-20003已删除（从在售到已删除）2019-12-202019-12-22004待审核2019-12-212019-12-21005待审核2019-12-212019-12-21006已删除（从待审核到已删除）2019-12-212019-12-22007待审核2019-12-222019-12-22008待审核2019-12-222019-12-22

MySQL数仓代码实现

MySQL初始化

在MySQL中 lalian 库和商品表用于到原始数据层

-- 创建数据库createdatabaseifnotexists lalian;-- 创建商品表createtableifnotexists`lalian`.`t_product`(
    goods_id varchar(50),-- 商品编号
    goods_status varchar(50),-- 商品状态
    createtime varchar(50),-- 商品创建时间
    modifytime varchar(50)-- 商品修改时间);

在MySQL中创建ods和dw层来模拟数仓

-- ods创建商品表createtableifnotexists`lalian`.`ods_t_product`(
    goods_id varchar(50),-- 商品编号
    goods_status varchar(50),-- 商品状态
    createtime varchar(50),-- 商品创建时间
    modifytime varchar(50),-- 商品修改时间
    cdat varchar(10)-- 模拟hive分区)defaultcharacterset='utf8';-- dw创建商品表createtableifnotexists`lalian`.`dw_t_product`(
    goods_id varchar(50),-- 商品编号
    goods_status varchar(50),-- 商品状态
     createtime varchar(50),-- 商品创建时间
     modifytime varchar(50),-- 商品修改时间
     cdat varchar(10)-- 模拟hive分区)defaultcharacterset='utf8';

增量导入12月20号数据

原始数据导入12月20号数据（4条）

insertinto`lalian`.`t_product`(goods_id, goods_status, createtime, modifytime)values('001','待审核','2019-12-18','2019-12-20'),('002','待售','2019-12-19','2019-12-20'),('003','在售','2019-12-20','2019-12-20'),('004','已删除','2019-12-15','2019-12-20');

注意：由于这里使用的MySQL来模拟的数仓所以直接使用insert into的方式导入数据，在企业中可能会使用hive来做数仓使用 kettle 或者 sqoop 或 datax 等来同步数据。

# 从原始数据层导入到ods 层insertinto lalian.ods_t_product
select*,'20191220'from lalian.t_product ;# 从ods同步到dw层insertinto lalian.dw_t_product
select*from lalian.ods_t_product where cdat='20191220';

查看dw层的运行结果

select*from lalian.dw_t_product where cdat='20191220';

goods_idgoods_statuscreatetimemodifytimecdat1待审核2019/12/182019/12/20201912202待售2019/12/192019/12/20201912203在售2019/12/202019/12/20201912204已删除2019/12/152019/12/2020191220
增量导入12月21数据

原始数据层导入12月21日数据（6条数据）

UPDATE `lalian`.`t_product` SET goods_status = '待售', modifytime = '2019-12-21' WHERE goods_id = '001';
INSERT INTO `lalian`.`t_product`(goods_id, goods_status, createtime, modifytime) VALUES
('005', '待审核', '2019-12-21', '2019-12-21'),
('006', '待审核', '2019-12-21', '2019-12-21');

将数据导入到ods层与dw层

# 从原始数据层导入到ods 层insertinto lalian.ods_t_product
select*,'20191221'from lalian.t_product ;# 从ods同步到dw层insertinto lalian.dw_t_product
select*from lalian.ods_t_product where cdat='20191221';

查看dw层的运行结果

select*from lalian.dw_t_product where cdat='20191221';

goods_idgoods_statuscreatetimemodifytimecdat1待售2019/12/182019/12/21201912212待售2019/12/192019/12/20201912213在售2019/12/202019/12/20201912214已删除2019/12/152019/12/20201912215待审核2019/12/212019/12/21201912216待审核2019/12/212019/12/2120191221
增量导入12月22日数据

原始数据层导入12月22日数据（6条数据）

UPDATE`lalian`.`t_product`SET goods_status ='已删除', modifytime ='2019-12-22'WHERE goods_id ='003';UPDATE`lalian`.`t_product`SET goods_status ='已删除', modifytime ='2019-12-22'WHERE goods_id ='006';INSERTINTO`lalian`.`t_product`(goods_id, goods_status, createtime, modifytime)VALUES('007','待审核','2019-12-22','2019-12-22'),('008','待审核','2019-12-22','2019-12-22');

将数据导入到ods层与dw层

# 从原始数据层导入到ods 层
insert into lalian.ods_t_product
select *,'20191222' from lalian.t_product ;
# 从ods同步到dw层
insert into lalian.dw_t_productpeizhiwenjian
select * from lalian.ods_t_product where cdat='20191222';

查看dw层的运行结果

select*from lalian.dw_t_product where cdat='20191222';

goods_idgoods_statuscreatetimemodifytimecdat1待售2019/12/182019/12/21201912222待售2019/12/192019/12/20201912223已删除2019/12/202019/12/22201912224已删除2019/12/152019/12/20201912225待审核2019/12/212019/12/21201912226已删除2019/12/212019/12/22201912227待审核2019/12/222019/12/22201912228待审核2019/12/222019/12/2220191222
查看dw层的运行结果

select*from lalian.dw_t_product;

goods_idgoods_statuscreatetimemodifytimecdat1待审核2019/12/182019/12/20201912202待售2019/12/192019/12/20201912203在售2019/12/202019/12/20201912204已删除2019/12/152019/12/20201912201待售2019/12/182019/12/21201912212待售2019/12/192019/12/20201912213在售2019/12/202019/12/20201912214已删除2019/12/152019/12/20201912215待审核2019/12/212019/12/21201912216待审核2019/12/212019/12/21201912211待售2019/12/182019/12/21201912222待售2019/12/192019/12/20201912223已删除2019/12/202019/12/22201912224已删除2019/12/152019/12/20201912225待审核2019/12/212019/12/21201912226已删除2019/12/212019/12/22201912227待审核2019/12/222019/12/22201912228待审核2019/12/222019/12/2220191222
从上述案例，可以看到：表每天保留一份全量，每次全量中会保存很多不变的信息，如果数据量很大的话，对存储是极大的浪费，可以将表设计为拉链表，既能满足反应数据的历史状态，又可以最大限度地节省存储空间。

方案二: 使用拉链表保存历史快照

拉链表不存储冗余的数据，只有某

行的数据发生变化，才需要保存下来

，相比每次全量同步会节省存储空间

能够查询到历史快照

额外的增加了两列（

dw_start_date

、

dw_end_date

），为数据行的生命周期。

12月20日商品拉链表的数据

goods_idgoods_statuscreatetimemodifytimedw_start_date****dw_end_date001待审核2019-12-182019-12-202019-12-209999-12-31002待售2019-12-192019-12-202019-12-209999-12-31003在售2019-12-202019-12-202019-12-209999-12-31004已删除2019-12-152019-12-202019-12-209999-12-31
12月20日的数据是全新的数据导入到dw表

  • dw_start_date表示某一条数据的生命周期起始时间，即数据从该时间开始有效（即生效日期）
  • dw_end_date表示某一条数据的生命周期结束时间，即数据到这一天（不包含）（即失效日期）
  • dw_end_date为 9999-12-31，表示当前这条数据是最新的数据，数据到9999-12-31才过期
12月21日商品拉链表的数据

goods_idgoods_statuscreatetimemodifytimedw_start_date****dw_end_date001待审核2019-12-182019-12-202019-12-202019-12-21002待售2019-12-192019-12-202019-12-209999-12-31003在售2019-12-202019-12-202019-12-209999-12-31004已删除2019-12-152019-12-202019-12-209999-12-31001（变）待售2019-12-182019-12-212019-12-219999-12-31005（新）待审核2019-12-212019-12-212019-12-219999-12-31
拉链表中没有存储冗余的数据，即只要数据没有变化，无需同步

  • 001编号的商品数据的状态发生了变化（从待审核 → 待售），需要将原有的dw_end_date从9999-12-31变为2019-12-21，表示待审核状态，在2019/12/20（包含） - 2019/12/21（不包含）有效；
  • 001编号新的状态重新保存了一条记录，dw_start_date为2019/12/21，dw_end_date为9999/12/31；
  • 新数据005、006、dw_start_date为2019/12/21，dw_end_date为9999/12/31。
12月22日商品拉链表的数据

goods_idgoods_statuscreatetimemodifytimedw_start_date****dw_end_date001待审核2019-12-182019-12-202019-12-202019-12-21002待售2019-12-192019-12-202019-12-209999-12-31003在售2019-12-202019-12-202019-12-202019-12-22004已删除2019-12-152019-12-202019-12-209999-12-31001待售2019-12-182019-12-212019-12-219999-12-31005待审核2019-12-212019-12-212019-12-219999-12-31006待审核2019-12-212019-12-212019-12-219999-12-31003（变）已删除2019-12-202019-12-222019-12-229999-12-31007（新）待审核2019-12-222019-12-222019-12-229999-12-31008（新）待审核2019-12-222019-12-222019-12-229999-12-31
拉链表中没有存储冗余的数据，即只要数据没有变化，无需同步

  • 003编号的商品数据的状态发生了变化（从在售→已删除），需要将原有的 dw_end_date从9999-12-31变为2019-12-22，表示在售状态，在2019/12/20（包含） - 2019/12/22（不包含） 有效
  • 003编号新的状态重新保存了一条记录，dw_start_date为2019-12-22，dw_end_date为9999-12-31
  • 新数据007、008、dw_start_date为2019-12-22，dw_end_date为9999-12-31
MySQL数仓拉链表快照实现

操作流程：

  1. 在原有dw层表上，添加额外的两列
  2. 只同步当天修改的数据到ods层
  3. 拉链表算法实现
  4. 拉链表的数据为：当天最新的数据 UNION ALL 历史数据

代码实现

在MySQL中lalian库和商品表用于到原始数据层

-- 创建数据库createdatabaseifnotexists lalian;-- 创建商品表createtableifnotexists`lalian`.`t_product2`(
    goods_id varchar(50),-- 商品编号
    goods_status varchar(50),-- 商品状态
    createtime varchar(50),-- 商品创建时间
    modifytime varchar(50)-- 商品修改时间)defaultcharacterset='utf8';

在MySQL中创建ods和dw层 模拟数仓

-- ods创建商品表createtableifnotexists`lalian`.`ods_t_product2`(
    goods_id varchar(50),-- 商品编号
    goods_status varchar(50),-- 商品状态
    createtime varchar(50),-- 商品创建时间
    modifytime varchar(50),-- 商品修改时间
    cdat varchar(10)-- 模拟hive分区)defaultcharacterset='utf8';-- dw创建商品表createtableifnotexists`lalian`.`dw_t_product2`(
    goods_id varchar(50),-- 商品编号
    goods_status varchar(50),-- 商品状态
    createtime varchar(50),-- 商品创建时间
    modifytime varchar(50),-- 商品修改时间
    dw_start_date varchar(12),-- 生效日期
    dw_end_date varchar(12),-- 失效时间
    cdat varchar(10)-- 模拟hive分区)defaultcharacterset='utf8';

全量导入2019年12月20日数据

原始数据层导入12月20日数据（4条数据）

insertinto`lalian`.`t_product_2`(goods_id, goods_status, createtime, modifytime)values('001','待审核','2019-12-18','2019-12-20'),('002','待售','2019-12-19','2019-12-20'),('003','在售','2019-12-20','2019-12-20'),('004','已删除','2019-12-15','2019-12-20');

将数据导入到数仓中的ods层

insertinto lalian.ods_t_product2
select*,'20191220'from lalian.t_product2 where modifytime >='2019-12-20';

将数据从ods层导入到dw层

insertinto lalian.dw_t_product2
select goods_id, goods_status, createtime, modifytime, modifytime,'9999-12-31', cdat from lalian.ods_t_product2 where cdat='20191220';

增量导入2019年12月21日数据

原始数据层导入12月21日数据（6条数据）

UPDATE`lalian`.`t_product2`SET goods_status ='待售', modifytime ='2019-12-21'WHERE goods_id ='001';INSERTINTO`lalian`.`t_product2`(goods_id, goods_status, createtime, modifytime)VALUES('005','待审核','2019-12-21','2019-12-21'),('006','待审核','2019-12-21','2019-12-21');

原始数据层同步到ods层

insertinto lalian.ods_t_product2
select*,'20191221'from lalian.t_product2 where modifytime >='2019-12-21';

编写ods层到dw层重新计算 dw_end_date

select t1.goods_id, t1.goods_status, t1.createtime, t1.modifytime,
       t1.dw_start_date,casewhen(t2.goods_id isnotnulland t1.dw_end_date>'2019-12-21')then'2019-12-21'else t1.dw_end_date endas dw_end_date ,
       t1.cdat
from lalian.dw_t_product2 t1
leftjoin(select*from lalian.ods_t_product2 where cdat='20191221')t2 on t1.goods_id=t2.goods_id
unionselect goods_id, goods_status, createtime, modifytime, modifytime,'9999-12-31', cdat from lalian.ods_t_product2 where cdat='20191221';

执行结果如下：
goods_idgoods_statuscreatetimemodifytimedw_start_datedw_end_datecdat1待审核2019-12-182019-12-202019-12-202019-12-21201912202待售2019-12-192019-12-202019-12-209999-12-31201912203在售2019-12-202019-12-202019-12-209999-12-31201912204已删除2019-12-152019-12-202019-12-209999-12-31201912201待售2019-12-182019-12-212019-12-219999-12-31201912215待审核2019-12-212019-12-212019-12-219999-12-31201912216待审核2019-12-212019-12-212019-12-219999-12-3120191221
拉链历史表，既能满足反应数据的历史状态，又可以最大程度的节省存储。我们做拉链表的时候要确定拉链表的粒度，比如说拉链表每天只取一个状态，也就是说如果一天有3个状态变更，我们只取最后一个状态，这种天粒度的表其实已经能解决大部分的问题了。

标签： 拉链表 数据库 数据仓库
本文转载自: https://blog.csdn.net/weixin_35353187/article/details/117202713
版权归原作者 俊杰梓 所有， 如有侵权，请联系我们删除。
登录后发布评论

“拉链表详解”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

Docker完整卸载的两种方法详细教程例子解析

spark-libFM 项目常见问题解决方案

基于大数据 Python NBA球员数据分析系统(源码+LW+部署讲解+数据库+ppt)

软件测试笔记｜web自动化测试｜Web 自动化测试中，XPath 定位的方法有哪些？如何编写 XPath？

Spring Boot 日志打印配置详解

MySQL中日期和时间戳的转换：字符到DATE和TIMESTAMP的相互转换

VLAN高级+以太网安全

文章导航