拉鍊表的建立、查詢和回滾

X.Jan發表於2021-08-04

概述

使用這種方式即可以記錄歷史，而且最大程度的節省儲存。這裡簡單介紹一下這種歷史拉鍊表的更新方法。此文參考連結
本文中假設：
  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;

 

總結

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

　　

相關文章