深入講解拉鍊表，還怕面試官問？

前言

         今天給大家分享一個面試中經常會被問到的拉鍊表，我在上篇文章中提出來一個需求如果不知道的請去→檢視，好，廢話不多說我們直接開始。提出的問題會在末尾講解。

一、拉鍊表介紹（百度百科）

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

二、拉鍊表場景

         資料倉儲的資料模型設計過程中，經常會遇到這樣的需求：

1. 表中的部分欄位會被update，例如：
   使用者的地址，產品的描述資訊，品牌資訊等等;
2. 需要檢視某一個時間點或者時間段的歷史快照資訊，例如：
   檢視某一個產品在歷史某一時間點的狀態
   檢視某一個使用者在過去某一段時間內，更新過幾次等等
3. 變化的比例和頻率不是很大，例如：
   總共有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. 在原有dw層表上，新增額外的兩列
2. 只同步當天修改的資料到ods層
3. 拉鍊表演算法實現
4. 拉鍊表的資料為：當天最新的資料 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'

4. 查詢結果
   

總結

         到這裡我們終於將拉鍊表實現完了，雖然實現拉鍊表這個功能有點複雜有點繞，但是它真的幫助我們節省很多的資源，以公司層面難道不選它嗎，也就為什麼面試數倉的時候基本上都會問拉鍊表的原因。很多小夥伴對dw_start_date與ds_end_date有疑惑我們可以在評論區一起討論。信自己，努力和汗水總會能得到回報的。我是大資料老哥，我們下期見~~~

獲取Flink面試題，Spark面試題，程式設計師必備軟體，hive面試題，Hadoop面試題，Docker面試題，簡歷模板等資源請去GitHub自行下載