數據倉庫中歷史拉鏈表的更新方法

来源:http://www.cnblogs.com/Mrwan/archive/2017/08/16/7374138.html
-Advertisement-
Play Games

在之前介紹過數據倉庫中的歷史拉鏈表《極限存儲–歷史拉鏈表》， 使用這種方式即可以記錄歷史，而且最大程度的節省存儲。這裡簡單介紹一下這種歷史拉鏈表的更新方法。 本文中假設： CREATE TABLE orders ( orderid INT, createtime STRING, modifiedti ...


在之前介紹過數據倉庫中的歷史拉鏈表《極限存儲–歷史拉鏈表》，

使用這種方式即可以記錄歷史，而且最大程度的節省存儲。這裡簡單介紹一下這種歷史拉鏈表的更新方法。

本文中假設：

  1. 數據倉庫中訂單歷史表的刷新頻率為一天，當天更新前一天的增量數據；
  2. 如果一個訂單在一天內有多次狀態變化，則只會記錄最後一個狀態的歷史；
  3. 訂單狀態包括三個：創建、支付、完成；
  4. 創建時間和修改時間只取到天，如果源訂單表中沒有狀態修改時間，那麼抽取增量就比較麻煩，需要有個機制來確保能抽取到每天的增量數據；
  5. 本文中的表和SQL都使用Hive的HQL語法；
  6. 源系統中訂單表結構為：

CREATE TABLE orders (
　　orderid INT,
　　createtime STRING,
　　modifiedtime STRING,
　　status STRING
) stored AS textfile;

7.在數據倉庫的ODS層，有一張訂單的增量數據表，按天分區，存放每天的增量數據：

CREATE TABLE t_ods_orders_inc (
　　orderid INT,
　　createtime STRING,
　　modifiedtime STRING,
　　status STRING
) PARTITIONED BY (day STRING)
stored AS textfile;

8. 在數據倉庫的DW層，有一張訂單的歷史數據拉鏈表，存放訂單的歷史狀態數據：

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;

9. 暫未考慮Hive上表的查詢性能問題，只實現功能；

 

10. 2015-08-21至2015-08-23，每天原系統訂單表的數據如下，紅色標出的為當天發生變化的訂單，即增量數據：

 

 

 

全量初始化

在數據從源業務系統每天正常抽取和刷新到DW訂單歷史表之前，需要做一次全量的初始化，就是從源訂單表中昨天以前的數據全部抽取到ODW，並刷新到DW。

以上面的數據為例，比如在2015-08-21這天做全量初始化，那麼我需要將包括2015-08-20之前的所有的數據都抽取並刷新到DW：

第一步，抽取全量數據到ODS：
INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = ‘2015-08-20′)
SELECT orderid,createtime,modifiedtime,status
FROM orders
WHERE createtime <= ‘2015-08-20′;

第二步，從ODS刷新到DW：
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-20′;

完成後，DW訂單歷史表中數據如下：

  1. spark-sql> select * from t_dw_orders_his;
  2. 1 2015-08-18 2015-08-18 創建 2015-08-18 9999-12-31
  3. 2 2015-08-18 2015-08-18 創建 2015-08-18 9999-12-31
  4. 3 2015-08-19 2015-08-21 支付 2015-08-19 9999-12-31
  5. 4 2015-08-19 2015-08-21 完成 2015-08-19 9999-12-31
  6. 5 2015-08-19 2015-08-20 支付 2015-08-19 9999-12-31
  7. 6 2015-08-20 2015-08-20 創建 2015-08-20 9999-12-31
  8. 7 2015-08-20 2015-08-21 支付 2015-08-20 9999-12-31
  9. Time taken: 2.296 seconds, Fetched 7 row(s)

 

增量抽取

每天，從源系統訂單表中，將前一天的增量數據抽取到ODS層的增量數據表。
這裡的增量需要通過訂單表中的創建時間和修改時間來確定：
INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = ‘${day}‘)
SELECT orderid,createtime,modifiedtime,status
FROM orders
WHERE createtime = ‘${day}’ OR modifiedtime = ‘${day}';

註意：在ODS層按天分區的增量表，最好保留一段時間的數據，比如半年，為了防止某一天的數據有問題而回滾重做數據。

增量刷新曆史數據

從2015-08-22開始，需要每天正常刷新前一天（2015-08-21）的增量數據到歷史表。

第一步，通過增量抽取，將2015-08-21的數據抽取到ODS：
INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = ‘2015-08-21′)
SELECT orderid,createtime,modifiedtime,status
FROM orders
WHERE createtime = ‘2015-08-21′ OR modifiedtime = ‘2015-08-21′;

ODS增量表中2015-08-21的數據如下：

  1. spark-sql> select * from t_ods_orders_inc where day = '2015-08-21';
  2. 3 2015-08-19 2015-08-21 支付 2015-08-21
  3. 4 2015-08-19 2015-08-21 完成 2015-08-21
  4. 7 2015-08-20 2015-08-21 支付 2015-08-21
  5. 8 2015-08-21 2015-08-21 創建 2015-08-21
  6. Time taken: 0.437 seconds, Fetched 4 row(s)

第二步，通過DW歷史數據(數據日期為2015-08-20)，和ODS增量數據(2015-08-21)，刷新曆史表：

先把數據放到一張臨時表中：

  1. DROP TABLE IF EXISTS t_dw_orders_his_tmp;
  2. CREATE TABLE t_dw_orders_his_tmp AS
  3. SELECT orderid,
  4. createtime,
  5. modifiedtime,
  6. status,
  7. dw_start_date,
  8. dw_end_date
  9. FROM (
  10. SELECT a.orderid,
  11. a.createtime,
  12. a.modifiedtime,
  13. a.status,
  14. a.dw_start_date,
  15. CASE WHEN b.orderid IS NOT NULL AND a.dw_end_date > '2015-08-21' THEN '2015-08-20' ELSE a.dw_end_date END AS dw_end_date
  16. FROM t_dw_orders_his a
  17. left outer join (SELECT * FROM t_ods_orders_inc WHERE day = '2015-08-21') b
  18. ON (a.orderid = b.orderid)
  19. UNION ALL
  20. SELECT orderid,
  21. createtime,
  22. modifiedtime,
  23. status,
  24. modifiedtime AS dw_start_date,
  25. '9999-12-31' AS dw_end_date
  26. FROM t_ods_orders_inc
  27. WHERE day = '2015-08-21'
  28. ) x
  29. ORDER BY orderid,dw_start_date;

其中：
UNION ALL的兩個結果集中，第一個是用歷史表left outer join 日期為 ${yyy-MM-dd} 的增量，能關聯上的，並且dw_end_date > ${yyy-MM-dd}，說明狀態有變化，則把原來的dw_end_date置為(${yyy-MM-dd} – 1), 關聯不上的，說明狀態無變化，dw_end_date無變化。
第二個結果集是直接將增量數據插入歷史表。

最後把臨時表中數據插入歷史表：
INSERT overwrite TABLE t_dw_orders_his
SELECT * FROM t_dw_orders_his_tmp;

 

刷新完後，歷史表中數據如下

  1. spark-sql> select * from t_dw_orders_his order by orderid,dw_start_date;
  2. 1 2015-08-18 2015-08-18 創建 2015-08-18 9999-12-31
  3. 2 2015-08-18 2015-08-18 創建 2015-08-18 9999-12-31
  4. 3 2015-08-19 2015-08-21 支付 2015-08-19 2015-08-20
  5. 3 2015-08-19 2015-08-21 支付 2015-08-21 9999-12-31
  6. 4 2015-08-19 2015-08-21 完成 2015-08-19 2015-08-20
  7. 4 2015-08-19 2015-08-21 完成 2015-08-21 9999-12-31
  8. 5 2015-08-19 2015-08-20 支付 2015-08-19 9999-12-31
  9. 6 2015-08-20 2015-08-20 創建 2015-08-20 9999-12-31
  10. 7 2015-08-20 2015-08-21 支付 2015-08-20 2015-08-20
  11. 7 2015-08-20 2015-08-21 支付 2015-08-21 9999-12-31
  12. 8 2015-08-21 2015-08-21 創建 2015-08-21 9999-12-31
  13. Time taken: 0.717 seconds, Fetched 11 row(s) 

由於在2015-08-21做了8月20日以前的數據全量初始化，而訂單3、4、7在2015-08-21的增量數據中也存在，因此都有兩條記錄，但不影響後面的查詢。

再看將2015-08-22的增量數據刷新到歷史表：

  1. INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = '2015-08-22')
  2. SELECT orderid,createtime,modifiedtime,status
  3. FROM orders
  4. WHERE createtime = '2015-08-22' OR modifiedtime = '2015-08-22';
  5.  
  6. DROP TABLE IF EXISTS t_dw_orders_his_tmp;
  7. CREATE TABLE t_dw_orders_his_tmp AS
  8. SELECT orderid,
  9. createtime,
  10. modifiedtime,
  11. status,
  12. dw_start_date,
  13. dw_end_date
  14. FROM (
  15. SELECT a.orderid,
  16. a.createtime,
  17. a.modifiedtime,
  18. a.status,
  19. a.dw_start_date,
  20. CASE WHEN b.orderid IS NOT NULL AND a.dw_end_date > '2015-08-22' THEN '2015-08-21' ELSE a.dw_end_date END AS dw_end_date
  21. FROM t_dw_orders_his a
  22. left outer join (SELECT * FROM t_ods_orders_inc WHERE day = '2015-08-22') b
  23. ON (a.orderid = b.orderid)
  24. UNION ALL
  25. SELECT orderid,
  26. createtime,
  27. modifiedtime,
  28. status,
  29. modifiedtime AS dw_start_date,
  30. '9999-12-31' AS dw_end_date
  31. FROM t_ods_orders_inc
  32. WHERE day = '2015-08-22'
  33. ) x
  34. ORDER BY orderid,dw_start_date;
  35.  
  36.  
  37. INSERT overwrite TABLE t_dw_orders_his
  38. SELECT * FROM t_dw_orders_his_tmp;
  39.  

刷新完後歷史表數據如下：

  1. spark-sql> select * from t_dw_orders_his order by orderid,dw_start_date;
  2. 1 2015-08-18 2015-08-18 創建 2015-08-18 2015-08-21
  3. 1 2015-08-18 2015-08-22 支付 2015-08-22 9999-12-31
  4. 2 2015-08-18 2015-08-18 創建 2015-08-18 2015-08-21
  5. 2 2015-08-18 2015-08-22 完成 2015-08-22 9999-12-31
  6. 3 2015-08-19 2015-08-21 支付 2015-08-19 2015-08-20
  7. 3 2015-08-19 2015-08-21 支付 2015-08-21 9999-12-31
  8. 4 2015-08-19 2015-08-21 完成 2015-08-19 2015-08-20
  9. 4 2015-08-19 2015-08-21 完成 2015-08-21 9999-12-31
  10. 5 2015-08-19 2015-08-20 支付 2015-08-19 9999-12-31
  11. 6 2015-08-20 2015-08-20 創建 2015-08-20 2015-08-21
  12. 6 2015-08-20 2015-08-22 支付 2015-08-22 9999-12-31
  13. 7 2015-08-20 2015-08-21 支付 2015-08-20 2015-08-20
  14. 7 2015-08-20 2015-08-21 支付 2015-08-21 9999-12-31
  15. 8 2015-08-21 2015-08-21 創建 2015-08-21 2015-08-21
  16. 8 2015-08-21 2015-08-22 支付 2015-08-22 9999-12-31
  17. 9 2015-08-22 2015-08-22 創建 2015-08-22 9999-12-31
  18. 10 2015-08-22 2015-08-22 支付 2015-08-22 9999-12-31
  19. Time taken: 0.66 seconds, Fetched 17 row(s)
查看2015-08-21的歷史快照數據：
  1. spark-sql> select * from t_dw_orders_his where dw_start_date <= '2015-08-21' and dw_end_date >= '2015-08-21';
  2. 1 2015-08-18 2015-08-18 創建 2015-08-18 2015-08-21
  3. 2 2015-08-18 2015-08-18 創建 2015-08-18 2015-08-21
  4. 3 2015-08-19 2015-08-21 支付 2015-08-21 9999-12-31
  5. 4 2015-08-19 2015-08-21 完成 2015-08-21 9999-12-31
  6. 5 2015-08-19 2015-08-20 支付 2015-08-19 9999-12-31
  7. 6 2015-08-20 2015-08-20 創建 2015-08-20 2015-08-21
  8. 7 2015-08-20 2015-08-21 支付 2015-08-21 9999-12-31
  9. 8 2015-08-21 2015-08-21 創建 2015-08-21 2015-08-21

訂單1在2015-08-21的時候還處於創建的狀態，在2015-08-22的時候狀態變為支付。

再刷新2015-08-23的增量數據：

按照上面的方法刷新完後，歷史表數據如下：

  1. spark-sql> select * from t_dw_orders_his order by orderid,dw_start_date;
  2. 1 2015-08-18 2015-08-18 創建 2015-08-18 2015-08-21
  3. 1 2015-08-18 2015-08-22 支付 2015-08-22 2015-08-22
  4. 1 2015-08-18 2015-08-23 完成 2015-08-23 9999-12-31
  5. 2 2015-08-18 2015-08-18 創建 2015-08-18 2015-08-21
  6. 2 2015-08-18 2015-08-22 完成 2015-08-22 9999-12-31
  7. 3 2015-08-19 2015-08-21 支付 2015-08-19 2015-08-20
  8. 3 2015-08-19 2015-08-21 支付 2015-08-21 2015-08-22
  9. 3
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • error: 'retain' is unavailable: not available in automatic reference counting. 解決辦法
    報錯原因是 項目使用的是ARC，但是有非ARC代碼。 項目中要混合使用ARC和非ARC。 解決： 如果使用的非 ARC ，則為 ARC 的代碼加入 -fobjc-arc 如果使用的是 ARC ，則為非 ARC 代碼加入 -fno-objc-arc 判斷項目是否用的ARC： 如果使用的非 ARC ，則 ...
  • [教程] 【原創】媒體掃描耗電的徹底解決辦法（申精）
    http://bbs.gfan.com/android-6740350-1-1.html 原創處女貼，呵呵。。。 研究換4.1.2也有段時間了，4.1.2各方面功能均讓我挺滿意的，用著也蠻順手的。偶爾上論壇，看到有人說，4.1.2存在媒體掃描耗電的bug，當時我還不以為然，也慶幸自己的這個版本沒有問 ...
  • 【代碼筆記】iOS-播放從網路上下載的語音
    代碼： ViewController.m ...
  • Lucene搜索引擎+HDFS+MR完成垂直搜索
    介於上一篇的java實現網路爬蟲基礎之上，這一篇的思想是將網路收集的數據保存到HDFS和資料庫（Mysql）中；然後用MR對HDFS的數據進行索引處理，處理成倒排索引；搜索時先用HDFS建立好的索引來搜索對應的數據ID，根據ID從資料庫中提取數據，呈現到網頁上。 這是一個完整的集合網路爬蟲、資料庫、 ...
  • MySQL資料庫 —子查詢,聯合查詢
    一 使用IN關鍵字的子查詢 1.查詢游戲類型是'棋牌類' 的游戲的分數信息 游戲分數表中並未包含游戲類型信息 思路一：採用鏈接查詢 思路二: 分兩步進行，首先找到所以'棋牌類'游戲的編號，再以這一組編號為查詢依據完成查詢 select * from scores where gno in (sele ...
  • 淺析MySQL中的Index Condition Pushdown (ICP 索引條件下推)和Multi-Range Read（MRR 索引多範圍查找）查詢優化
    本文出處：http://www.cnblogs.com/wy123/p/7374078.html（保留出處並非什麼原創作品權利，本人拙作還遠遠達不到，僅僅是為了鏈接到原文，因為後續對可能存在的一些錯誤進行修正或補充，無他） ICP優化原理 Index Condition Pushdown (ICP) ...
  • DAX/PowerBI系列 - 參數表(Parameter Table) 度量值模板
    DAX/PowerBI系列 - 參數表(Parameter Table) 度量值模板 難度： ★★☆☆☆（2星） 適用範圍： ★★★☆☆（3星） 概況： 當你有多個度量值都需要計算YTD，MoM，而又不想重覆所有這些給每一個度量值分別設定計算度量值的話，有沒有一個簡便的方法，少點coding呢？ 同 ...
  • hadoop生態圈列式存儲系統--kudu介紹及安裝配置
    介紹 Kudu 是一個針對 Apache Hadoop 平臺而開發的列式存儲管理器。Kudu 共用 Hadoop 生態系統應用的常見技術特性: 它在 commodity hardware（商品硬體）上運行，horizontally scalable（水平可擴展），並支持 highly availab ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類