DW層更新：HIVE腳本三步實作“緩慢變化維2更新”--保留歷史資料-有解無憂

今天，就分享一下我過去一周的兩點作業識訓：
1、DW層更新：“緩慢變化維2更新”，HIVE腳本三步實作
2、ODS層更新：源資料去重的兩種方式

“緩慢變化維1”是全量覆寫，一步到位，而"緩慢變化維2"，要保留歷史資料，實作需要三步走，
已經好幾個月沒有接觸HIVE了，之前也提到我們的人力專案的HIVE數倉被替換成了oracle數倉，在專案結束之際，“經營駕駛倉”的源浩大佬善意提醒：
“你之前的‘緩慢變換維’，hive腳本,是不是少了一步？”
我之前給大家的科普的思路只有兩步：
1、獲取“新”的有效資料

     UNION ALL

2、獲取“新”的失效資料

源表：ODS. workplace_info

CREATE TABLE IF NOT EXISTS ODS.WORKPLACE_INFO
(
      ID STRING COMMENT '企業員工唯一編碼'
    , NAME STRING COMMENT '姓名'
    , PROVINCE STRING COMMENT '作業地點--省'
    , CITY STRING COMMENT '作業地點--市'
    , LAST_UPDATE_DT STRING COMMENT '更新時間'
)
COMMENT '作業地點資訊表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\036'
STORED AS parquet;

維表：DW.dim_workplace_info

CREATE TABLE IF NOT EXISTS DW.DIM_WORKPLACE_INFO
(
      SK INT COMMENT '作業地點維度表代理鍵'
    , ID STRING COMMENT '企業員工唯一編碼'
    , NAME STRING COMMENT '姓名'
    , PROVINCE STRING COMMENT '作業地點--省'
    , CITY STRING COMMENT '作業地點--市'
    , VALID_START_DT STRING COMMENT '當前行生效日期'
    , VALID_END_DT STRING COMMENT '當前行失效日期'
    , ROW_STATUS STRING COMMENT '當前行是否有效'
)
COMMENT '作業地點維度'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\036'
STORED AS parquet;

更新思路：

1、獲取“新”的有效資料：從【源表】獲取相對于【維度表】新增的資料：
ODS. workplace_info ods

【left join】

DW.dim_workplace_info dw

WHERE dw.sk is null
OR ods.province <>dw.province
OR ods.city<>ods.city

    UNION ALL

2、獲取“新”的失效資料：從【維度表】獲取"相對于【源表】有變化的資料"：
DW.dim_workplace_info dw

【left join】

ODS. workplace_info ods

只要 dw.province <>ods.province 或 dw.city<>ods.city，該維度表需要做出以下操作：

行的“有效截至時間”vaild_end_dt的資料值需要更改成當前/當前的前一天
“行狀態”row_status標記為“失效”

代碼實體：

INSERT OVERWRITE TABLE dw.dim_workplace_info
SELECT
--維度表的代理鍵，由維度表中【最大值的代理鍵】 + 【新增資料排序結果】 得到的
      ROW_NUMBER() OVER ( ORDER BY t1.name, t1.province, t1.city) + max_sk AS sk      
    , t1.id
    , t1.name
    , t1.province
    , t1.city
    , CASE 
        WHEN t1.last_update_dt IS NOT NULL 
            THEN t1.last_update_dt
        ELSE DATE_FORMAT( DATA_SUB(CURRENT_DATE,1),'yyyyMMdd') --當前的前一天
      END AS valid_start_dt
    , '99991231' AS valid_end_dt
    , 'Current' AS row_status
--（一）、獲取“新”的有效資料：步驟1 [left join] 步驟2
FROM
---1、獲取源資料
    ods.workplace_info t1
---2、獲取當前行有效的維度資料    
LEFT OUTER JOIN
(
    SELECT
          sk
        , id
        , name
        , province
        , ctiy
    FROM
        dw.dim_workplace_info
    --獲取當前行有效的維度資料的【限制條件】
    WHERE valid_start_dt <= DATE_FORMAT( DATA_SUB(CURRENT_DATE,1),'yyyyMMdd')
        AND valid_end_dt> DATE_FORMAT( DATA_SUB(CURRENT_DATE,1),'yyyyMMdd')
)t2
ON t1.id= t2.id
--獲取維度表最大的外鍵，以便為新增資料生成外鍵
CROSS JOIN
(
    SELECT
        COALESCE(MAX(sk),0) AS max_sk
    FROM
        dw.dim_workplace_info
)dim_sk_tmp
WHERE t2.sko is null
    OR t1.province <> t2.province
    OR t1.city <> t2.city
UNION ALL
--（二）、獲取“新”的失效資料：步驟3 [left outer join] 步驟4
SELECT
      t3.sk
    , t3.id
    , t3.name
    , t3.province
    , t3.city
    , t3.gp_ent_short_des
    , t3.valid_start_dt
    , CASE
        WHEN t3.province <> t4.province
            OR t3.city <> t4.city
                THEN DATE_FORMAT( DATA_SUB(CURRENT_DATE,2),'yyyyMMdd')
        ELSE t3.valid_end_dt  --將維表SCD2欄位有變化的某行的有效截止時間valid_end_dt 修改成當前日期的前天
      END AS valid_end_dt
    , CASE
        WHEN t3.province <> t4.province
            OR t3.city <> t4.city
                THEN 'Expired' --將維表SCD2欄位有變化的某行的狀態row_status標記為“失效”--Expired
        ELSE t3.row_status
      END AS row_status
FROM
--3、獲取維度資料
(
    SELECT
          sk
        , id
        , name
        , province
        , ctiy
    FROM
        dw.dim_workplace_info
    --獲取當前行有效的維度資料的【限制條件】
    WHERE valid_start_dt <= DATE_FORMAT( DATA_SUB(CURRENT_DATE,1),'yyyyMMdd')
        AND valid_end_dt> DATE_FORMAT( DATA_SUB(CURRENT_DATE,1),'yyyyMMdd')
) t3
--4、獲取源資料
LEFT OUTER JOIN
    ods_gr_sci.sector_ent t4
ON t3.id = t4.id

以上兩個步驟寫出來的腳本，好像沒什么毛病，能跑起來，可以滿足需求，但你會發現更新前的“失效資料”不見了，為什么？你仔細看上面的兩個步驟，維度表取的都是“當前行有效”的資料，而“當前行失效”的資料不見了，所以以上腳本，每次更新，都會將上次得到的失效（歷史）資料給覆寫沒了，所以正確的“緩慢變化維2更新”腳本應該增加第三步：
1、獲取“新”的有效資料

     UNION ALL

2、獲取“新”的失效資料

    UNION ALL

3、獲取“舊”的失效資料

完整代碼實體

INSERT OVERWRITE TABLE dw.dim_workplace_info
SELECT
--維度表的代理鍵，由維度表中【最大值的代理鍵】 + 【新增資料排序結果】 得到的
      ROW_NUMBER() OVER ( ORDER BY t1.name, t1.province, t1.city) + max_sk AS sk      
    , t1.id
    , t1.name
    , t1.province
    , t1.city
    , CASE 
        WHEN t1.last_update_dt IS NOT NULL 
            THEN t1.last_update_dt
        ELSE DATE_FORMAT( DATA_SUB(CURRENT_DATE,1),'yyyyMMdd') --當前的前一天
      END AS valid_start_dt
    , '99991231' AS valid_end_dt
    , 'Current' AS row_status
--（一）、獲取“新”的有效資料：步驟1 [left join] 步驟2
FROM
---1、獲取源資料
    ods.workplace_info t1
---2、獲取當前行有效的維度資料    
LEFT OUTER JOIN
(
    SELECT
          sk
        , id
        , name
        , province
        , ctiy
    FROM
        dw.dim_workplace_info
    --獲取當前行有效的維度資料的【限制條件】
    WHERE valid_start_dt <= DATE_FORMAT( DATA_SUB(CURRENT_DATE,1),'yyyyMMdd')
        AND valid_end_dt> DATE_FORMAT( DATA_SUB(CURRENT_DATE,1),'yyyyMMdd')
)t2
ON t1.id= t2.id
--獲取維度表最大的外鍵，以便為新增資料生成外鍵
CROSS JOIN
(
    SELECT
        COALESCE(MAX(sk),0) AS max_sk
    FROM
        dw.dim_workplace_info
)dim_sk_tmp
WHERE t2.sko is null
    OR t1.province <> t2.province
    OR t1.city <> t2.city
UNION ALL
--（二）、獲取“新”的失效資料：步驟3 [left outer join] 步驟4
SELECT
      t3.sk
    , t3.id
    , t3.name
    , t3.province
    , t3.city
    , t3.gp_ent_short_des
    , t3.valid_start_dt
    , CASE
        WHEN t3.province <> t4.province
            OR t3.city <> t4.city
                THEN DATE_FORMAT( DATA_SUB(CURRENT_DATE,2),'yyyyMMdd')
        ELSE t3.valid_end_dt  --將維表SCD2欄位有變化的某行的有效截止時間valid_end_dt 修改成當前日期的前天
      END AS valid_end_dt
    , CASE
        WHEN t3.province <> t4.province
            OR t3.city <> t4.city
                THEN 'Expired' --將維表SCD2欄位有變化的某行的狀態row_status標記為“失效”--Expired
        ELSE t3.row_status
      END AS row_status
FROM
--3、獲取維度資料
(
    SELECT
          sk
        , id
        , name
        , province
        , ctiy
    FROM
        dw.dim_workplace_info
    --獲取當前行有效的維度資料的【限制條件】
    WHERE valid_start_dt <= DATE_FORMAT( DATA_SUB(CURRENT_DATE,1),'yyyyMMdd')
        AND valid_end_dt> DATE_FORMAT( DATA_SUB(CURRENT_DATE,1),'yyyyMMdd')
) t3
--4、獲取源資料
LEFT OUTER JOIN
    ods_gr_sci.sector_ent t4
ON t3.id = t4.id
--（三）獲取“舊”的失效資料
UNION ALL
SELECT
     sk
    , id
    , name
    , province
    , ctiy
    , valid_start_dt
    , valid_end_dt
    , row_status
FROM
    dw.dim_workplace_info
    --獲取當前行失效的維度資料的【限制條件】
WHERE  valid_end_dt<= DATE_FORMAT( DATA_SUB(CURRENT_DATE,1),'yyyyMMdd')

影響更新成敗的細節：
還有一個特別重要的細節，就是ODS過來的資料，要確保當前時間內，一個ID只有一條資料，否則更新到DW時會資料發散，所以這里就要涉及到“源資料”的去重了：

兩條資料完全相同的，全部欄位GROUP BY 就可以

代碼示例：

SELECT
      id
    , name
    , province
    , ctiy
    , MAX(last_update_dt) AS ast_update_dt
FROM
    ods.dim_workplace_info
GROUP BY           
    id
    , name
    , province
    , ctiy

兩條資料不完全相同，ROW_NUMNER(）

代碼示例：

SELECT
      id
    , name
    , province
    , ctiy
    , last_update_dt
FROM
(
SELECT
      id
    , name
    , province
    , ctiy
    , last_update_dt
    ,ROW_NUMBER()OVER(PARTITION BY id ORDER BY name,province,ctiy) AS NUM
FROM
    ods.workplace_info
) T1
WHERE NUM=1

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/230699.html

標籤：其他

上一篇：十代因特爾NUCi7ubuntu16.04解決無wifi、無聲音、無法播放視頻問題

下一篇：撰寫一個滿足下面要求的程式：創建一個由100個隨機選取的整數構成的陣列提示用戶輸入陣列的下標，然后顯示對應的元素值，如果指定的下標越界，則顯示訊息“Out of Bounds”