本篇文章給大家帶來了關于mysql的相關知識，其中主要介紹了關于數據庫拉鏈表的相關問題，拉鏈表是一種數據模型，主要是針對數據倉庫設計中表存儲數據的方式而定義的，顧名思義，所謂拉鏈，就是記錄歷史，下面一起來看一下，希望對大家有幫助。
推薦學習：mysql
拉鏈表產生背景
在數據倉庫的數據模型設計過程中，經常會遇到這樣的需求：
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’，這條語句會查詢到以下記錄：
| 訂單創建日期 | 訂單編號 | 訂單狀態 | 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
