千家信息网

什么是拉链表

发表于:2024-11-14 作者:千家信息网编辑
千家信息网最后更新 2024年11月14日,这篇文章主要介绍"什么是拉链表",在日常操作中,相信很多人在什么是拉链表问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答"什么是拉链表"的疑惑有所帮助!接下来,请跟着小编一
千家信息网最后更新 2024年11月14日什么是拉链表

这篇文章主要介绍"什么是拉链表",在日常操作中,相信很多人在什么是拉链表问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答"什么是拉链表"的疑惑有所帮助!接下来,请跟着小编一起来学习吧!

一、拉链表介绍

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

二、拉链表场景

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

表中的部分字段会被update,例如:用户的地址,产品的描述信息,品牌信息等等;

需要查看某一个时间点或者时间段的历史快照信息,例如:查看某一个产品在历史某一时间点的状态 查看某一个用户在过去某一段时间内,更新过几次等等

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

三、商品数据案例

需求:商品表:

列名类型说明
goods_idvarchar(50)商品编号
goods_statusvarchar(50)商品状态(待审核、待售、在售、已删除)
createtimevarchar(50)商品创建日期
modifytimevarchar(50)商品修改日期

2019年12月20日的数据如下所示:

goods_idgoods_statuscreatetimemodifytime
001待审核2019-12-202019-12-20
002待售2019-12-202019-12-20
003在售2019-12-202019-12-20
004已删除2019-12-202019-12-20

商品的状态,会随着时间推移而变化,我们需要将商品的所有变化的历史信息都保存下来。如何实现呢?

方案一: 快照每一天的数据到数仓(图解)

该方案为:

  • 每一天都保存一份全量,将所有数据同步到数仓中(我这里就使用MySQL操作的)

  • 很多记录都是重复保存,没有任何变化

12月20日(4条数据)

goods_idgoods_statuscreatetimemodifytime
001待审核2019-12-182019-12-20
002待售2019-12-192019-12-20
003在售2019-12-202019-12-20
004已删除2019-12-152019-12-20

12月21日(10条数据)

goods_idgoods_statuscreatetimemodifytime
以下为12月20日快照数据
001待审核2019-12-182019-12-20
002待售2019-12-192019-12-20
003在售2019-12-202019-12-20
004已删除2019-12-152019-12-20
以下为12月21日快照数据
001待售(从待审核到待售)2019-12-182019-12-21
002待售2019-12-192019-12-20
003在售2019-12-202019-12-20
004已删除2019-12-152019-12-20
005(新商品)待审核2019-12-212019-12-21
006(新商品)待审核2019-12-212019-12-21

12月22日(18条数据)

goods_idgoods_statuscreatetimemodifytime
以下为12月20日快照数据
001待审核2019-12-182019-12-20
002待售2019-12-192019-12-20
003在售2019-12-202019-12-20
004已删除2019-12-152019-12-20
以下为12月21日快照数据
001待售(从待审核到待售)2019-12-182019-12-21
002待售2019-12-192019-12-20
003在售2019-12-202019-12-20
004已删除2019-12-152019-12-20
005待审核2019-12-212019-12-21
006待审核2019-12-212019-12-21
以下为12月22日快照数据
001待售2019-12-182019-12-21
002待售2019-12-192019-12-20
003已删除(从在售到已删除)2019-12-202019-12-22
004待审核2019-12-212019-12-21
005待审核2019-12-212019-12-21
006已删除(从待审核到已删除)2019-12-212019-12-22
007待审核2019-12-222019-12-22
008待审核2019-12-222019-12-22

方案一: MySQL到,MySQL数仓代码实现

MySQL初始化

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

-- 创建数据库 create database if not exists zw; -- 创建商品表 create table if not exists `zw`.`t_product`( goods_id varchar(50), -- 商品编号  goods_status varchar(50), -- 商品状态  createtime varchar(50), -- 商品创建时间  modifytime varchar(50) -- 商品修改时间 );

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

-- ods创建商品表 create table if not exists `zw`.`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 `zw`.`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号数据

1.原始数据导入12月20号数据(4条)

insert into `zw`.`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 zw.ods_t_product select *,'20191220' from zw.t_product ; # 从ods同步到dw层 insert into zw.dw_t_product select * from zw.ods_t_product where cdat='20191220';

增量导入12月21数据

1.原始数据层导入12月21日数据(6条数据)

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

2.将数据导入到ods层与dw层

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

3.查看dw层的运行结果

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

增量导入12月22日数据

1.原始数据层导入12月22日数据(6条数据)

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

2.将数据导入到ods层与dw层

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

3.查看dw层的运行结果

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

从上述案例,可以看到:

表每天保留一份全量,每次全量中会保存很多不变的信息,如果数据量很大的话,对存储是极大的浪费

可以讲表设计为拉链表,既能满足反应数据的历史状态,又可以最大限度地节省存储空间。

方案二: 使用拉链表保存历史快照(思路/图解)

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

  • 能够查询到历史快照

  • 额外的增加了两列(dw_start_date、dw_end_date),为数据行的生命周期

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

goods_idgoods_statuscreatetimemodifytimedw_start_datedw_end_date
001待审核2019-12-182019-12-202019-12-209999-12-31
002待售2019-12-192019-12-202019-12-209999-12-31
003在售2019-12-202019-12-202019-12-209999-12-31
004已删除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_datedw_end_date
001待审核2019-12-182019-12-202019-12-202019-12-21
002待售2019-12-192019-12-202019-12-209999-12-31
003在售2019-12-202019-12-202019-12-209999-12-31
004已删除2019-12-152019-12-202019-12-209999-12-31
001(变)待售2019-12-182019-12-212019-12-219999-12-31
005(新)待审核2019-12-212019-12-212019-12-219999-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日商品拉链表的数据

goods_idgoods_statuscreatetimemodifytimedw_start_datedw_end_date
001待审核2019-12-182019-12-202019-12-202019-12-21
002待售2019-12-192019-12-202019-12-209999-12-31
003在售2019-12-202019-12-202019-12-202019-12-22
004已删除2019-12-152019-12-202019-12-209999-12-31
001待售2019-12-182019-12-212019-12-219999-12-31
005待审核2019-12-212019-12-212019-12-219999-12-31
006待审核2019-12-212019-12-212019-12-219999-12-31
003(变)已删除2019-12-202019-12-222019-12-229999-12-31
007(新)待审核2019-12-222019-12-222019-12-229999-12-31
008(新)待审核2019-12-222019-12-222019-12-229999-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

方案二: 拉链表快照代码实现

操作流程:

  1. 鸿蒙官方战略合作共建--HarmonyOS技术社区

  2. 在原有dw层表上,添加额外的两列

  3. 只同步当天修改的数据到ods层

  4. 拉链表算法实现

  5. 拉链表的数据为:当天最新的数据 UNION ALL 历史数据

代码实现

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

-- 创建数据库 create database if not exists zw;  -- 创建商品表 create table if not exists `zw`.`t_product_2`( goods_id varchar(50), -- 商品编号 goods_status varchar(50), -- 商品状态  createtime varchar(50), -- 商品创建时间  modifytime varchar(50) -- 商品修改时间 )default character set = 'utf8';

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

-- ods创建商品表 create table if not exists `zw`.`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 `zw`.`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日数据

1.原始数据层导入12月20日数据(4条数据)

insert into `zw`.`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');

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

insert into zw.ods_t_product2 select *,'20191220' from zw.t_product_2 where modifytime >='2019-12-20'

3.将数据从ods层导入到dw层

insert into zw.dw_t_product2 select goods_id, goods_status, createtime, modifytime, modifytime,'9999-12-31', cdat from zw.ods_t_product2 where cdat='20191220'

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

1.原始数据层导入12月21日数据(6条数据)

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

2.原始数据层同步到ods层

insert into zw.ods_t_product2 select *,'20191221' from zw.t_product_2 where modifytime >='2019-12-21';

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

注意:我这里直接将结果的SQL语句放在这里语句 因为需要将覆盖写入到数据库中我这里就没有写了,但是不影响我们结果。12月22 号的操作流程跟21 一样我就里就不写了

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__date end as end ,        t1.cdat from zw.dw_t_product2 t1 left join (select * from zw.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 zw.ods_t_product2 where cdat='20191221'

查询结果

到此,关于"什么是拉链表"的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注网站,小编会继续努力为大家带来更多实用的文章!

数据 商品 拉链 时间 状态 原始 快照 变化 同步 历史 日数 日数据 信息 方案 日期 结果 存储 增量 学习 有效 数据库的安全要保护哪些东西 数据库安全各自的含义是什么 生产安全数据库录入 数据库的安全性及管理 数据库安全策略包含哪些 海淀数据库安全审计系统 建立农村房屋安全信息数据库 易用的数据库客户端支持安全管理 连接数据库失败ssl安全错误 数据库的锁怎样保障安全 大华存储服务器配置 电信网络技术支持岗位职责 硬科技领域权威数据库 简述一个软件开发流程 施乐服务器管理员密码 安徽华为服务器虚拟化价格 应急网络安全演练图片 生意软件开发 软件开发乙级资质名录 软件开发主管的工作内容 油料网络技术 上海黑马互联网科技有限公司 创建数据库的总结语 金蝶数据库日志收缩工具 湖北随州网络安全宣传 软件开发 所有权的问题 物联网概论传感器网络技术 数据库第四版课后答案所有 同一个数据库怎么连接 阿里云服务器降配置 退款 博士论文的原始数据库 网络安全研究都有哪些方面 计算机软件开发工程师资格证 电商小程序软件开发怎么收费 四川大学网络安全学院考研分数线 世界网络安全产业的发展趋势 2019ftp服务器权限设置 温州华盟商务广场互联网科技馆 知网查重的数据库只有知网嘛 深圳市网络安全产业
0