详述业务中拉链表的应用方式（二）

问题导读：
1、如何将数据导入到ods层与dw层？
2、如何使用拉链表保存历史快照？
3、如何使用MySQL数仓拉链表快照实现？
4、原始数据层如何同步到ods层？

上一篇：
详述业务中拉链表的应用方式（一）
https://www.aboutyun.com/forum.php?mod=viewthread&tid=31085

增量导入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_product
select *,'20191221' from lalian.t_product ;
复制代码

# 从ods同步到dw层

insert into lalian.dw_t_product
select * from lalian.ods_t_product where cdat='20191221';
复制代码

查看dw层的运行结果

select * from lalian.dw_t_product where cdat='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';
复制代码

查看dw层的运行结果

select * from lalian.dw_t_product;
复制代码

从上述案例，可以看到：表每天保留一份全量，每次全量中会保存很多不变的信息，如果数据量很大的话，对存储是极大的浪费，可以将表设计为拉链表，既能满足反应数据的历史状态，又可以最大限度地节省存储空间。

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

拉链表不存储冗余的数据，只有某行的数据发生变化，才需要保存下来，相比每次全量同步会节省存储空间

能够查询到历史快照

额外的增加了两列（dw_start_date、dw_end_date），为数据行的生命周期。

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



12月20日的数据是全新的数据导入到dw表

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

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



拉链表中没有存储冗余的数据，即只要数据没有变化，无需同步

•     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日商品拉链表的数据




拉链表中没有存储冗余的数据，即只要数据没有变化，无需同步

•     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_product2
select *,'20191220' from lalian.t_product2 where modifytime >='2019-12-20';
复制代码

将数据从ods层导入到dw层

insert into 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';
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_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,
       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.cdat
from lalian.dw_t_product2 t1
left join (select * from lalian.ods_t_product2 where cdat='20191221')t2 on t1.goods_id=t2.goods_id
union
select goods_id, goods_status, createtime, modifytime, modifytime,'9999-12-31', cdat from lalian.ods_t_product2 where cdat='20191221';
复制代码

执行结果如下：




拉链历史表，既能满足反应数据的历史状态，又可以最大程度的节省存储。我们做拉链表的时候要确定拉链表的粒度，比如说拉链表每天只取一个状态，也就是说如果一天有3个状态变更，我们只取最后一个状态，这种天粒度的表其实已经能解决大部分的问题了。

作者：俊杰梓
来源：https://blog.csdn.net/weixin_35353187/article/details/117202713

最新经典文章，欢迎关注公众号

感谢分享