一起来聊聊数据库拉链表
时间：2022-06-21 [网络编程]作者：fabuyuan 浏览：1 次
拉链表产生背景
在数据仓库的数据模型设计过程中，经常会遇到这样的需求：
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-20 | 001 | 创建订单 |
| 2012-06-20 | 002 | 创建订单 |
| 2012-06-20 | 003 | 支付完成 |
到6月21日，表中有5条记录：
| 订单创建日期 | 订单编号 | 订单状态 |
|---|---|---|
| 2012-06-20 | 001 | 创建订单 |
| 2012-06-20 | 002 | 创建订单 |
| 2012-06-20 | 003 | 支付完成 |
| 2012-06-21 | 004 | 创建订单 |
| 2012-06-21 | 005 | 创建订单 |
到6月22日，表中有6条记录：
| 订单创建日期 | 订单编号 | 订单状态 |
|---|---|---|
| 2012-06-20 | 001 | 创建订单 |
| 2012-06-20 | 002 | 创建订单 |
| 2012-06-20 | 003 | 支付完成 |
| 2012-06-21 | 004 | 创建订单 |
| 2012-06-21 | 005 | 创建订单 |
| 2012-06-22 | 006 | 创建订单 |
数据仓库中对该表的保留方法：
1、只保留一份全量，则数据和6月22日的记录一样，如果需要查看6月21日订单001的状态，则无法满足；
2、每天都保留一份全量，则数据仓库中的该表共有14条记录，但好多记录都是重复保存，没有任务变化，如订单002,004，数据量大了，会造成很大的存储浪费；
如果在数据仓库中设计成历史拉链表保存该表，则会有下面这样一张表：
| 订单创建日期 | 订单编号 | 订单状态 | dw_bigin_date | dw_end_date |
|---|---|---|---|---|
| 2012-06-20 | 001 | 创建订单 | 2012-06-20 | 2012-06-20 |
| 2012-06-20 | 001 | 支付完成 | 2012-06-21 | 9999-12-31 |
| 2012-06-20 | 002 | 创建订单 | 2012-06-20 | 9999-12-31 |
| 2012-06-20 | 003 | 支付完成 | 2012-06-20 | 2012-06-21 |
| 2012-06-20 | 003 | 已发货 | 2012-06-22 | 9999-12-31 |
| 2012-06-21 | 004 | 创建订单 | 2012-06-21 | 9999-12-31 |
| 2012-06-21 | 005 | 创建订单 | 2012-06-21 | 2012-06-21 |
| 2012-06-21 | 005 | 支付完成 | 2012-06-22 | 9999-12-31 |
| 2012-06-22 | 006 | 创建订单 | 2012-06-22 | 9999-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_date | dw_end_date |
|---|---|---|---|---|
| 2012-06-20 | 001 | 支付完成 | 2012-06-21 | 9999-12-31 |
| 2012-06-20 | 002 | 创建订单 | 2012-06-20 | 9999-12-31 |
| 2012-06-20 | 003 | 支付完成 | 2012-06-20 | 2012-06-21 |
| 2012-06-21 | 004 | 创建订单 | 2012-06-21 | 9999-12-31 |
| 2012-06-21 | 005 | 创建订单 | 2012-06-21 | 2012-06-21 |
和源表在6月21日的记录完全一致：
| 订单创建日期 | 订单编号 | 订单状态 |
|---|---|---|
| 2012-06-20 | 001 | 创建订单 |
| 2012-06-20 | 002 | 创建订单 |
| 2012-06-20 | 003 | 支付完成 |
| 2012-06-21 | 004 | 创建订单 |
| 2012-06-21 | 005 | 创建订单 |
可以看出，这样的历史拉链表，既能满足对历史数据的需求，又能很大程度的节省存储资源；
拉链表示例2：
在历史表中对人的一生的记录可能就这样几条记录，避免了按每一天记录客户状态造成的海量存储的问题：
| 人名 | 开始日期 | 结束日期 | 状态 |
|---|---|---|---|
| client | 19000101 | 19070901 | H在家 |
| client | 19070901 | 19130901 | A小学 |
| client | 19130901 | 19160901 | B初中 |
| client | 19160901 | 19190901 | C高中 |
| client | 19190901 | 19230901 | D大学 |
| client | 19230901 | 19601231 | E公司 |
| client | 19601231 | 29991231 | H退休在家 |
上面的每一条记录都是不算末尾的，比如到19070901，client已经在A，而不是H了。所以除最后一条记录因为状态到目前都未改变的，其余的记录实际上在结束日期那天，都不在是该条记录结束日期那天的状态。这种现象可以理解为算头不算尾。
拉链表实现方式
1、定义两个临时表，一个为当日全量数据，另一个为需要新增或更新的数据；
CREATE VOLATILE TABLE VT_xxxx_NEW AS xxxx WITH NO DATA ON COMMIT PRESERVE ROWS;CREATE VOLATILE SET TABLE VT_xxxx_CHG,NO LOG AS xxxx WITH NO DATA ON COMMIT PRESERVE ROWS;
2、获取当日全量数据
INSERT INTO VT_xxxx_NEW(xx) SELECT (xx,cur_date, max_date) FROM xxxx_sorce;
3、抽取新增或有变化的数据，从xxxx_NEW临时表到xxxx_CHG临时表；
INSERT INTO VT_xxxx_CHG(xx)SELECT xx FROM VT_xxxx_NEWWHERE (xx) NOT IN (select xx from xxxx_HIS where end_date='max_date');
4、更新历史表的失效记录的end_date为max值
UPDATE A1 FROM xxxx_HIS A1, VT_xxxx_CHG A2SET End_Date='current_date'WHERE A1.xx=A2.xx AND A1.End_Date='max_date';
5、将新增或者有变化的数据插入目标表
INSERT INTO xxxx_HIS SELECT * FROM VT_xxxx_CHG;
以商品数据为例
存在商品表 t_product，表结构如下：
| 列名 | 类型 | 说明 |
|---|---|---|
| goods_id | varchar(50) | 商品编号 |
| goods_status | varchar(50) | 商品状态（待审核、待售、在售、已删除） |
| createtime | varchar(50) | 商品创建日期 |
| modifytime | varchar(50) | 商品修改日期 |
2019年12月20日的数据如下所示：
| goods_id | goods_status | createtime | modifytime |
|---|---|---|---|
| 001 | 待审核 | 2019-12-20 | 2019-12-20 |
| 002 | 待售 | 2019-12-20 | 2019-12-20 |
| 003 | 在售 | 2019-12-20 | 2019-12-20 |
| 004 | 已删除 | 2019-12-20 | 2019-12-20 |
商品的状态，会随着时间推移而变化，我们需要将商品的所有变化的历史信息都保存下来。
方案一: 快照每一天的数据到数仓
该方案为：每一天都保存一份全量，将所有数据同步到数仓中，很多记录都是重复保存，没有任何变化。
12月20日（4条数据）
| goods_id | goods_status | createtime | modifytime |
|---|---|---|---|
| 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 |
12月21日（10条数据）
| goods_id | goods_status | createtime | modifytime |
|---|---|---|---|
| 以下为12月20日快照数据 | |||
| 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 |
| 以下为12月21日快照数据 | |||
| 001 | 待售（从待审核到待售） | 2019-12-18 | 2019-12-21 |
| 002 | 待售 | 2019-12-19 | 2019-12-20 |
| 003 | 在售 | 2019-12-20 | 2019-12-20 |
| 004 | 已删除 | 2019-12-15 | 2019-12-20 |
| 005（新商品） | 待审核 | 2019-12-21 | 2019-12-21 |
| 006（新商品） | 待审核 | 2019-12-21 | 2019-12-21 |
12月22日（18条数据）
| goods_id | goods_status | createtime | modifytime |
|---|---|---|---|
| 以下为12月20日快照数据 | |||
| 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 |
| 以下为12月21日快照数据 | |||
| 001 | 待售（从待审核到待售） | 2019-12-18 | 2019-12-21 |
| 002 | 待售 | 2019-12-19 | 2019-12-20 |
| 003 | 在售 | 2019-12-20 | 2019-12-20 |
| 004 | 已删除 | 2019-12-15 | 2019-12-20 |
| 005 | 待审核 | 2019-12-21 | 2019-12-21 |
| 006 | 待审核 | 2019-12-21 | 2019-12-21 |
| 以下为12月22日快照数据 | |||
| 001 | 待售 | 2019-12-18 | 2019-12-21 |
| 002 | 待售 | 2019-12-19 | 2019-12-20 |
| 003 | 已删除（从在售到已删除） | 2019-12-20 | 2019-12-22 |
| 004 | 待审核 | 2019-12-21 | 2019-12-21 |
| 005 | 待审核 | 2019-12-21 | 2019-12-21 |
| 006 | 已删除（从待审核到已删除） | 2019-12-21 | 2019-12-22 |
| 007 | 待审核 | 2019-12-22 | 2019-12-22 |
| 008 | 待审核 | 2019-12-22 | 2019-12-22 |
MySQL数仓代码实现
MySQL初始化
在MySQL中 lalian 库和商品表用于到原始数据层
-- 创建数据库create database if not exists lalian;-- 创建商品表create table if not exists `lalian`.`t_product`(
goods_id varchar(50), -- 商品编号
goods_status varchar(50), -- 商品状态
createtime varchar(50), -- 商品创建时间
modifytime varchar(50) -- 商品修改时间);在MySQL中创建ods和dw层来模拟数仓
-- ods创建商品表create table if not exists `lalian`.`ods_t_product`( goods_id varchar(50), -- 商品编号 goods_status varchar(50), -- 商品状态 createtime varchar(50), -- 商品创建时间 modifytime varchar(50), -- 商品修改时间 cdat varchar(10) -- 模拟hive分区)default character set = 'utf8';-- dw创建商品表create table if not exists `lalian`.`dw_t_product`( goods_id varchar(50), -- 商品编号 goods_status varchar(50), -- 商品状态 createtime varchar(50), -- 商品创建时间 modifytime varchar(50), -- 商品修改时间 cdat varchar(10) -- 模拟hive分区)default character set = 'utf8';
增量导入12月20号数据
原始数据导入12月20号数据（4条）
insert into `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 层insert into lalian.ods_t_productselect *,'20191220' from lalian.t_product ;# 从ods同步到dw层insert into lalian.dw_t_productselect * from lalian.ods_t_product where cdat='20191220';
查看dw层的运行结果
select * from lalian.dw_t_product where cdat='20191220';
| goods_id | goods_status | createtime | modifytime | cdat |
|---|---|---|---|---|
| 1 | 待审核 | 2019/12/18 | 2019/12/20 | 20191220 |
| 2 | 待售 | 2019/12/19 | 2019/12/20 | 20191220 |
| 3 | 在售 | 2019/12/20 | 2019/12/20 | 20191220 |
| 4 | 已删除 | 2019/12/15 | 2019/12/20 | 20191220 |
增量导入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 层insert into lalian.ods_t_productselect *,'20191221' from lalian.t_product ;# 从ods同步到dw层insert into lalian.dw_t_productselect * from lalian.ods_t_product where cdat='20191221';
查看dw层的运行结果
select * from lalian.dw_t_product where cdat='20191221';
| goods_id | goods_status | createtime | modifytime | cdat |
|---|---|---|---|---|
| 1 | 待售 | 2019/12/18 | 2019/12/21 | 20191221 |
| 2 | 待售 | 2019/12/19 | 2019/12/20 | 20191221 |
| 3 | 在售 | 2019/12/20 | 2019/12/20 | 20191221 |
| 4 | 已删除 | 2019/12/15 | 2019/12/20 | 20191221 |
| 5 | 待审核 | 2019/12/21 | 2019/12/21 | 20191221 |
| 6 | 待审核 | 2019/12/21 | 2019/12/21 | 20191221 |
增量导入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';INSERT INTO `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_id | goods_status | createtime | modifytime | cdat |
|---|---|---|---|---|
| 1 | 待售 | 2019/12/18 | 2019/12/21 | 20191222 |
| 2 | 待售 | 2019/12/19 | 2019/12/20 | 20191222 |
| 3 | 已删除 | 2019/12/20 | 2019/12/22 | 20191222 |
| 4 | 已删除 | 2019/12/15 | 2019/12/20 | 20191222 |
| 5 | 待审核 | 2019/12/21 | 2019/12/21 | 20191222 |
| 6 | 已删除 | 2019/12/21 | 2019/12/22 | 20191222 |
| 7 | 待审核 | 2019/12/22 | 2019/12/22 | 20191222 |
| 8 | 待审核 | 2019/12/22 | 2019/12/22 | 20191222 |
查看dw层的运行结果
select * from lalian.dw_t_product;
| goods_id | goods_status | createtime | modifytime | cdat |
|---|---|---|---|---|
| 1 | 待审核 | 2019/12/18 | 2019/12/20 | 20191220 |
| 2 | 待售 | 2019/12/19 | 2019/12/20 | 20191220 |
| 3 | 在售 | 2019/12/20 | 2019/12/20 | 20191220 |
| 4 | 已删除 | 2019/12/15 | 2019/12/20 | 20191220 |
| 1 | 待售 | 2019/12/18 | 2019/12/21 | 20191221 |
| 2 | 待售 | 2019/12/19 | 2019/12/20 | 20191221 |
| 3 | 在售 | 2019/12/20 | 2019/12/20 | 20191221 |
| 4 | 已删除 | 2019/12/15 | 2019/12/20 | 20191221 |
| 5 | 待审核 | 2019/12/21 | 2019/12/21 | 20191221 |
| 6 | 待审核 | 2019/12/21 | 2019/12/21 | 20191221 |
| 1 | 待售 | 2019/12/18 | 2019/12/21 | 20191222 |
| 2 | 待售 | 2019/12/19 | 2019/12/20 | 20191222 |
| 3 | 已删除 | 2019/12/20 | 2019/12/22 | 20191222 |
| 4 | 已删除 | 2019/12/15 | 2019/12/20 | 20191222 |
| 5 | 待审核 | 2019/12/21 | 2019/12/21 | 20191222 |
| 6 | 已删除 | 2019/12/21 | 2019/12/22 | 20191222 |
| 7 | 待审核 | 2019/12/22 | 2019/12/22 | 20191222 |
| 8 | 待审核 | 2019/12/22 | 2019/12/22 | 20191222 |
从上述案例，可以看到：表每天保留一份全量，每次全量中会保存很多不变的信息，如果数据量很大的话，对存储是极大的浪费，可以将表设计为拉链表，既能满足反应数据的历史状态，又可以最大限度地节省存储空间。
方案二: 使用拉链表保存历史快照
拉链表不存储冗余的数据，只有某行的数据发生变化，才需要保存下来，相比每次全量同步会节省存储空间
能够查询到历史快照
额外的增加了两列（dw_start_date、dw_end_date），为数据行的生命周期。
12月20日商品拉链表的数据
| goods_id | goods_status | createtime | modifytime | dw_start_date | dw_end_date |
|---|---|---|---|---|---|
| 001 | 待审核 | 2019-12-18 | 2019-12-20 | 2019-12-20 | 9999-12-31 |
| 002 | 待售 | 2019-12-19 | 2019-12-20 | 2019-12-20 | 9999-12-31 |
| 003 | 在售 | 2019-12-20 | 2019-12-20 | 2019-12-20 | 9999-12-31 |
| 004 | 已删除 | 2019-12-15 | 2019-12-20 | 2019-12-20 | 9999-12-31 |
12月20日的数据是全新的数据导入到dw表
- dw_start_date表示某一条数据的生命周期起始时间，即数据从该时间开始有效（即生效日期）
- dw_end_date表示某一条数据的生命周期结束时间，即数据到这一天（不包含）（即失效日期）
- dw_end_date为 9999-12-31，表示当前这条数据是最新的数据，数据到9999-12-31才过期
12月21日商品拉链表的数据
| goods_id | goods_status | createtime | modifytime | dw_start_date | dw_end_date |
|---|---|---|---|---|---|
| 001 | 待审核 | 2019-12-18 | 2019-12-20 | 2019-12-20 | 2019-12-21 |
| 002 | 待售 | 2019-12-19 | 2019-12-20 | 2019-12-20 | 9999-12-31 |
| 003 | 在售 | 2019-12-20 | 2019-12-20 | 2019-12-20 | 9999-12-31 |
| 004 | 已删除 | 2019-12-15 | 2019-12-20 | 2019-12-20 | 9999-12-31 |
| 001（变） | 待售 | 2019-12-18 | 2019-12-21 | 2019-12-21 | 9999-12-31 |
| 005（新） | 待审核 | 2019-12-21 | 2019-12-21 | 2019-12-21 | 9999-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_id | goods_status | createtime | modifytime | dw_start_date | dw_end_date |
|---|---|---|---|---|---|
| 001 | 待审核 | 2019-12-18 | 2019-12-20 | 2019-12-20 | 2019-12-21 |
| 002 | 待售 | 2019-12-19 | 2019-12-20 | 2019-12-20 | 9999-12-31 |
| 003 | 在售 | 2019-12-20 | 2019-12-20 | 2019-12-20 | 2019-12-22 |
| 004 | 已删除 | 2019-12-15 | 2019-12-20 | 2019-12-20 | 9999-12-31 |
| 001 | 待售 | 2019-12-18 | 2019-12-21 | 2019-12-21 | 9999-12-31 |
| 005 | 待审核 | 2019-12-21 | 2019-12-21 | 2019-12-21 | 9999-12-31 |
| 006 | 待审核 | 2019-12-21 | 2019-12-21 | 2019-12-21 | 9999-12-31 |
| 003（变） | 已删除 | 2019-12-20 | 2019-12-22 | 2019-12-22 | 9999-12-31 |
| 007（新） | 待审核 | 2019-12-22 | 2019-12-22 | 2019-12-22 | 9999-12-31 |
| 008（新） | 待审核 | 2019-12-22 | 2019-12-22 | 2019-12-22 | 9999-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数仓拉链表快照实现
操作流程：
- 在原有dw层表上，添加额外的两列
- 只同步当天修改的数据到ods层
- 拉链表算法实现
- 拉链表的数据为：当天最新的数据 UNION ALL 历史数据
代码实现
在MySQL中lalian库和商品表用于到原始数据层
-- 创建数据库create database if not exists lalian;-- 创建商品表create table if not exists `lalian`.`t_product2`(
goods_id varchar(50), -- 商品编号
goods_status varchar(50), -- 商品状态
createtime varchar(50), -- 商品创建时间
modifytime varchar(50) -- 商品修改时间)default character set = 'utf8';在MySQL中创建ods和dw层 模拟数仓
-- ods创建商品表create table if not exists `lalian`.`ods_t_product2`( goods_id varchar(50), -- 商品编号 goods_status varchar(50), -- 商品状态 createtime varchar(50), -- 商品创建时间 modifytime varchar(50), -- 商品修改时间 cdat varchar(10) -- 模拟hive分区)default character set = 'utf8';-- dw创建商品表create table if not exists `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分区)default character set = 'utf8';
全量导入2019年12月20日数据
原始数据层导入12月20日数据（4条数据）
insert into `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层
insert into lalian.ods_t_product2select *,'20191220' from lalian.t_product2 where modifytime >='2019-12-20';
将数据从ods层导入到dw层
insert into lalian.dw_t_product2select 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';INSERT INTO `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层
insert into lalian.ods_t_product2select *,'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,
case when (t2.goods_id is not null and t1.dw_end_date>'2019-12-21') then '2019-12-21'else t1.dw_end_date end as dw_end_date ,
t1.cdatfrom lalian.dw_t_product2 t1left join (select * from lalian.ods_t_product2 where cdat='20191221')t2 on t1.goods_id=t2.goods_idunionselect goods_id, goods_status, createtime, modifytime, modifytime,'9999-12-31', cdat from lalian.ods_t_product2 where cdat='20191221';执行结果如下：
| goods_id | goods_status | createtime | modifytime | dw_start_date | dw_end_date | cdat |
|---|---|---|---|---|---|---|
| 1 | 待审核 | 2019-12-18 | 2019-12-20 | 2019-12-20 | 2019-12-21 | 20191220 |
| 2 | 待售 | 2019-12-19 | 2019-12-20 | 2019-12-20 | 9999-12-31 | 20191220 |
| 3 | 在售 | 2019-12-20 | 2019-12-20 | 2019-12-20 | 9999-12-31 | 20191220 |
| 4 | 已删除 | 2019-12-15 | 2019-12-20 | 2019-12-20 | 9999-12-31 | 20191220 |
| 1 | 待售 | 2019-12-18 | 2019-12-21 | 2019-12-21 | 9999-12-31 | 20191221 |
| 5 | 待审核 | 2019-12-21 | 2019-12-21 | 2019-12-21 | 9999-12-31 | 20191221 |
| 6 | 待审核 | 2019-12-21 | 2019-12-21 | 2019-12-21 | 9999-12-31 | 20191221 |
拉链历史表，既能满足反应数据的历史状态，又可以最大程度的节省存储。我们做拉链表的时候要确定拉链表的粒度，比如说拉链表每天只取一个状态，也就是说如果一天有3个状态变更，我们只取最后一个状态，这种天粒度的表其实已经能解决大部分的问题了。
推荐学习：mysql视频教程
以上就是一起来聊聊数据库拉链表的详细内容，更多请关注站长家园其它相关文章！
本文标签： mysql
转载请注明来源：一起来聊聊数据库拉链表
本文永久链接地址：https://www.adminjie.com/post/13739.html
免责声明：
本站所发布的一切资源仅限用于学习和研究目的；不得将上述内容用于商业或者非法用途，否则，一切后果请用户自负。本站信息来自网络，版权争议与本站无关。您必须在下载后的24个小时之内，从您的电脑中彻底删除上述内容。如果您喜欢该程序，请支持正版软件，购买注册，得到更好的正版服务。
附：
二○○二年一月一日《计算机软件保护条例》第十七条规定：为了学习和研究软件内含的设计思想和原理，通过安装、显示、传输或者存储软件等方式使用软件的，可以不经软件著作权人许可，不向其支付报酬！鉴于此，也希望大家按此说明研究软件！
版权声明：
一、本站致力于为软件爱好者提供国内外软件开发技术和软件共享，着力为用户提供优资资源。
二、本站提供的部分源码下载文件为网络共享资源，请于下载后的24小时内删除。如需体验更多乐趣，还请支持正版。
三、我站提供用户下载的所有内容均转自互联网。如有内容侵犯您的版权或其他利益的，若有侵犯你的权益请：提交版权证明文件到邮箱 2225329873#qq.com(#换为@) 站长会进行审查之后，情况属实的会在三个工作日内为您删除。
更多精彩内容
- VUE中V-IF条件判断改变元素的样式操作
- Discuz如何解决安装时报错run_sql_error
- 低版本VS项目在VS2019无法正常编译的问题
- PHP+Redis链表解决高并发下商品超卖问题(实现原理及步骤)
- Oracle数据库的实例/表空间/用户/表之间关系简单讲解
- RSA2是啥？PHP-RSA2签名验证怎么实现？
- app是什么应用程序的简称
- 华为dubal20是什么型号
- 小程序大小超限除了分包还能怎么做？如何避免和解决大小限制？
- 电脑显示信号线无连接是什么意思
- html5中onclick是什么意思
- ana an00华为是什么型号
- 超清视效是什么意思
- html中value是什么意思
- vivov1818a是什么手机型号
- 最新文章
-
-
一起来聊聊数据库拉链表
本篇文章给大家带来了关于mysql的相关知识，其中主要介绍了关于数据库拉链表的相关问题，拉链表是一种数据模型，主要是针对数据仓库设计中表存储数据的方式而定义的，...
-
PHP实现排序功能总结
本篇文章给大家带来了关于PHP的相关知识，其中主要介绍了关于排序功能的相关问题，通过实例完成php+mysqli排序功能的实现，下面一起来看一下，希望对大家有帮...
-
mysql不是内部命令要怎么解决
解决方法：1、右击“计算机”图标，在打开的菜单中选择“属性”；2、在系统界面中，点击“高级系统设置”；3、在“系统属性”弹窗中，点击“环境变量”；4、在“环境变...
-
mysql分页查询怎么优化
分页查询的优化方式：1、子查询优化，可通过把分页的SQL语句改写成子查询的方法获得性能上的提升。2、id限定优化，可以根据查询的页数和查询的记录数计算出查询的i...
-
sql与mysql有什么区别
区别：1、SQL是一种结构化查询语言，而MySQL是一种数据库软件，使用SQL语言来查询数据库；2、SQL不提供连接工具，而MySQL提供工具，可用一个名为“M...
-
- 热门文章
-
-
VUE中V-IF条件判断改变元素的样式操作
这篇文章主要介绍了VUE中V-IF条件判断改变元素的样式操作，具有很好的参考价值，希望对大家有所帮助。一起跟随想过来看看吧...
-
Discuz如何解决安装时报错run_sql_error
问题环境VMware虚拟机Centos7.3PHP7.0MySQL8.0NGINX1.14Discuz3.4问题还原本地环境为PHP5.6+MySQL5.6在安...
-
低版本VS项目在VS2019无法正常编译的问题
低版本VS项目在VS2019无法正常编译的问题这里指的编译并不准确，只是为了方便说明。后有（未安装），201？...
-
PHP+Redis链表解决高并发下商品超卖问题(实现原理及步骤)
实现原理使用redis链表来做，因为pop操作是原子的，即使有很多用户同时到达，也是依次执行，推荐使用。实现步骤第一步，先将商品库存入队列/**.trigge...
-
Oracle数据库的实例/表空间/用户/表之间关系简单讲解
完整的Oracle数据库通常由两部分组成：Oracle数据库和数据库实例。Oracle是一种数据库管理系统，是一种关系型的数据库管理系统。我们用这些高级权限账号...
-
