拉鏈表的創建、查詢和回滾

概述

使用這種方式即可以記錄歷史，而且最大程度的節省存儲。這裡簡單介紹一下這種歷史拉鏈表的更新方法。此文參考鏈接
本文中假設：
  1. 數據倉庫中訂單歷史表的刷新頻率為一天，當天更新前一天的增量數據；
  2. 如果一個訂單在一天內有多次狀態變化，則只會記錄最後一個狀態的歷史；
  3. 訂單狀態包括三個：創建、支付、完成；
  4. 創建時間和修改時間只取到天，如果源訂單表中沒有狀態修改時間，那麼抽取增量就比較麻煩，需要有個機制來確保能抽取到每天的增量數據；
  5. 本文中的表和SQL都使用Hive的HQL語法；
 

初始化

假設我們有三天數據，【2015-08-20，2015-08-21，2015-08-22】。
數據流向：（原系統表）orders ==> （增量表）t_ods_orders_inc ==> （拉鏈表）t_dw_orders_his 
建表腳本
-- 源系統中訂單表
CREATE TABLE orders (
    orderid INT,
    createtime STRING,
    modifiedtime STRING,
    status STRING
) stored AS textfile;

-- 訂單的增量數據表，按天分區，存放每天的增量數據，保留半年左右
CREATE TABLE t_ods_orders_inc (
    orderid INT,
    createtime STRING,
    modifiedtime STRING,
    status STRING
) PARTITIONED BY (day STRING)
stored AS textfile;

-- 訂單的歷史數據拉鏈表
CREATE TABLE t_dw_orders_his (
    orderid INT,
    createtime STRING,
    modifiedtime STRING,
    status STRING,
    dw_start_date STRING,
    dw_end_date STRING
) stored AS textfile;

 

 
數據初始化
-- 1. 源表orders，假設此表為21日狀態
insert into orders 
values 
(1,'2015-08-18','2015-08-18','創建'),
(2,'2015-08-18','2015-08-18','創建'),
(3,'2015-08-19','2015-08-21','支付'),
(4,'2015-08-19','2015-08-21','完成'),
(5,'2015-08-19','2015-08-20','支付'),
(6,'2015-08-20','2015-08-20','創建'),
(7,'2015-08-20','2015-08-21','支付'),
(8,'2015-08-21','2015-08-21','創建');

-- 2. 初始化ODS增量表-21日數據(全量初始化，將21號前的累加到此分區)
INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = '2015-08-21')
SELECT 
    orderid,
    createtime,
    modifiedtime,
    status
FROM orders
WHERE createtime <= '2015-08-21';

-- 3. 初始化ODS增量表-22日數據
insert overwrite table t_ods_orders_inc partition(day='2015-08-22')
values
(1,'2015-08-18','2015-08-22','支付'),
(2,'2015-08-18','2015-08-22','完成'),
(6,'2015-08-20','2015-08-22','支付'),
(8,'2015-08-21','2015-08-22','支付'),
(9,'2015-08-22','2015-08-22','創建'),
(10,'2015-08-22','2015-08-22','支付');

-- 4. 初始化ODS增量表-23日數據
insert overwrite table t_ods_orders_inc partition(day='2015-08-23')
values
(1,'2015-08-18','2015-08-23','完成'),
(3,'2015-08-19','2015-08-23','完成'),
(5,'2015-08-19','2015-08-23','完成'),
(8,'2015-08-21','2015-08-23','完成'),
(11,'2015-08-23','2015-08-23','創建'),
(12,'2015-08-23','2015-08-23','創建'),
(13,'2015-08-23','2015-08-23','支付');

 

拉鏈表創建

假設
  • 跑數時間 T= ‘${dt1}’
  • 拉鏈表有開始日期（生效日期）和結束日期（失效日期，最新記錄此列 = ‘9999-12-31’）
 

初始化

當21號跑數時，需要全量初始化拉鏈表，此時，拉鏈表就是21日的切片數據
-- 初始化拉鏈表,假設21號的就是原始數據
INSERT overwrite TABLE t_dw_orders_his
SELECT 
    orderid,
    createtime,
    modifiedtime,
    status,
    createtime   AS dw_start_date,
    '9999-12-31' AS dw_end_date
FROM t_ods_orders_inc
WHERE day = '2015-08-21';

 

增量更新

當22號跑數時，需要把要處理的所有數據分成兩部分，處理思路為：
  • 新增，22號增量數據，結束日期= ‘9999-12-31’
  • 更新，歷史拉鏈表與增量表進行比對
    • 當增量表中存在記錄，開始日期=歷史拉鏈表開始日期，結束日期= date_add(‘${dt1}’,-1)
    • 當增量表中不存在此記錄，代表不需要更新
 

具體操作

創建一張臨時表保存比對的結果數據。
-- 22號增量數據進來後，與21號的狀態數據(t_dw_orders_his)比對，更新拉鏈表
-- ${dt1} = '2015-08-22'
DROP TABLE IF EXISTS t_dw_orders_his_tmp;
CREATE TABLE t_dw_orders_his_tmp AS 
SELECT  orderid,
        createtime,
        modifiedtime,
        status,
        dw_start_date,
        dw_end_date 
FROM (
    -- 22號前需更新狀態的數據
    SELECT  a.orderid,
            a.createtime,
            a.modifiedtime,
            a.status,
            a.dw_start_date,
            CASE WHEN b.orderid IS NOT NULL AND a.dw_end_date = '9999-12-31' 
                THEN date_add('${dt1}',-1) -- 把22號前有效的數據失效日期置為dt1的上一日
            ELSE a.dw_end_date END AS dw_end_date 
    FROM t_dw_orders_his a 
    left join t_ods_orders_inc b 
      ON a.orderid = b.orderid
     and b.day = '${dt1}'
    UNION ALL 
    -- 22號最新狀態數據
    SELECT  orderid,
            createtime,
            modifiedtime,
            status,
            modifiedtime AS dw_start_date,
            '9999-12-31' AS dw_end_date 
    FROM t_ods_orders_inc 
    WHERE day = '${dt1}' 
) x 
ORDER BY orderid,dw_start_date;

-- 臨時數據正式入庫
INSERT overwrite TABLE t_dw_orders_his
SELECT * FROM t_dw_orders_his_tmp;

-- 23號數據入t_dw_orders_his表請重複上述22號的刷新步驟
-- ${dt1} = '2015-08-23'
-- ... ...

 

　

 
查詢和使用場景
1. 查詢拉鏈表最新狀態數據
select * from t_dw_orders_his where dw_end_date='9999-12-31';

 

 
2.查詢某日所有訂單快照
--假設查詢22號數據狀態 
--'${dt1}' = '2021-08-22'
select 
* 
from t_dw_orders_his 
where dw_start_date<='${dt1}'
and dw_end_date>='${dt1}';

 

回滾方法

 

方法

先把拉鏈表的數據分為三份，分別為 T-N、T、T+N 的數據，T是回滾日期。
那麼我們假設，在23號發現數據有問題，需要回滾22號的數據，此時拉鏈表數據有三塊。
 
 
其中：
  • 對於結束日期為21號及之前的數據，【保留】，下圖綠色
  • 對於22日有效的數據，【更新】，其數據又分兩種
    • 一種是結束日期是22日的，把結束日期 = ‘9999-12-31’即可，下圖黃色
    • 一種是22日前創建，22日後還有效的數據，把結束日期 = ‘9999-12-31’，下圖藍色
  • 對於22日後產生的數據，【刪除】，下圖紅色
 
所以，拉鏈表的回滾過程的增刪改就如下圖所示：
 
 

具體操作

創建三個臨時表，分別保存 T-N、T日的數據，最後合到一張結果表中。
-- 1. 綠色，保留
DROP TABLE t_dw_orders_his_tmp1;
CREATE TABLE t_dw_orders_his_tmp1
AS
SELECT 
　　orderid,
　　createtime,
　　modifiedtime,
　　status,
　　dw_start_date,
　　dw_end_date
FROM 
　　t_dw_orders_his
WHERE 
　　dw_end_date < '2015-08-22';

-- 2. 黃色，更新-當日生效的數據
DROP TABLE t_dw_orders_his_tmp2;
CREATE TABLE t_dw_orders_his_tmp2 
AS 
SELECT 　　
　　orderid,
　　createtime, 　　
　　modifiedtime, 　　
　　status, 　　
　　dw_start_date, 　　
　　'9999-12-31' AS dw_end_date 
FROM 
　　t_dw_orders_his
WHERE 
　　dw_end_date = '2015-08-22';
  
-- 2. 藍色，更新-22號前到22號後還生效的數據
DROP TABLE t_dw_orders_his_tmp3;
CREATE TABLE t_dw_orders_his_tmp3
AS
SELECT 
　　orderid,
　　createtime,
　　modifiedtime,
　　status,
　　dw_start_date,
　　'9999-12-31' dw_end_date
FROM 
　　t_dw_orders_his
WHERE 
　　dw_start_date <= '2015-08-22' AND dw_end_date > '2015-08-22';
  
-- 4. 數據插入到新表  
CREATE TABLE t_dw_orders_his_new
AS
select * 
from ( 
    SELECT a.* ,'綠色，保留,號前的數據' FROM t_dw_orders_his_tmp1 a
    UNION ALL
    SELECT b.*,'黃色，更新-當日生效的數據' FROM t_dw_orders_his_tmp2 b
    UNION ALL
    SELECT c.*,'藍色，更新-22號前到22號後還生效的數據' FROM t_dw_orders_his_tmp3 c
 ) a 
ORDER BY a.orderid,a.dw_start_date;

 

總結

拉鏈表的出現是為了壓縮存儲和記錄數，針對頻繁更新的數據會很有效。但是回滾操作不方便，查詢的時候也必須要指定時間才能正確取數，操作成本高，得權衡利弊後再確認是否合適自己使用。

　　

Tags:

VirMach 便宜 VPS

QNews

QNews