什么是拉鏈表

這篇文章主要介紹“什么是拉鏈表”，在日常操作中，相信很多人在什么是拉鏈表問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”什么是拉鏈表”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

 一、拉鏈表介紹

拉鏈表：維護歷史狀態，以及最新狀態數據的一種表，拉鏈表根據拉鏈粒度的不同，實際上相當于快照，只不過做了優化，去除了一部分不變的記錄，通過拉鏈表可以很方便的還原出拉鏈時點的客戶記錄

二、拉鏈表場景

數據倉庫的數據模型設計過程中，經常會遇到這樣的需求：

表中的部分字段會被update，例如：用戶的地址，產品的描述信息，品牌信息等等;

需要查看某一個時間點或者時間段的歷史快照信息，例如：查看某一個產品在歷史某一時間點的狀態 查看某一個用戶在過去某一段時間內，更新過幾次等等

變化的比例和頻率不是很大，例如：總共有1000萬的會員，每天新增和發生變化的有10萬左右

三、商品數據案例

需求：商品表：

2019年12月20日的數據如下所示：

商品的狀態，會隨著時間推移而變化，我們需要將商品的所有變化的歷史信息都保存下來。如何實現呢?

方案一: 快照每一天的數據到數倉(圖解)

該方案為：

• 每一天都保存一份全量，將所有數據同步到數倉中(我這里就使用MySQL操作的)

• 很多記錄都是重復保存，沒有任何變化

12月20日(4條數據)

12月21日(10條數據)

12月22日(18條數據)

方案一: 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日商品拉鏈表的數據：

12月20日的數據是全新的數據導入到dw表

• dw_start_date表示某一條數據的生命周期起始時間，即數據從該時間開始有效(即生效日期)

• dw_end_date表示某一條數據的生命周期結束時間，即數據到這一天(不包含)(即失效日期)

• dw_end_date為9999-12-31，表示當前這條數據是最新的數據，數據到9999-12-31才過期

12月21日商品拉鏈表的數據

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日商品拉鏈表的數據

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'

查詢結果

到此，關于“什么是拉鏈表”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！