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

来源: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
  • WPF 已知問題 監聽 WMI 事件導致觸摸失效
    示例項目結構 在 Visual Studio 中創建一個 WinForms 應用程式後，項目結構如下所示： MyWinFormsApp/ │ ├───Properties/ │ └───Settings.settings │ ├───bin/ │ ├───Debug/ │ └───Release/ ...
  • [STAThread]特性
    [STAThread] 特性用於需要與 COM 組件交互的應用程式，尤其是依賴單線程模型（如 Windows Forms 應用程式）的組件。在 STA 模式下，線程擁有自己的消息迴圈，這對於處理用戶界面和某些 COM 組件是必要的。 [STAThread] static void Main(stri ...
  • UWP WinUI3 傳入 AddHandler 的 RoutedEventHandler 類型與事件所需不匹配將拋出參數異常
    在WinForm中使用全局異常捕獲處理 在WinForm應用程式中，全局異常捕獲是確保程式穩定性的關鍵。通過在Program類的Main方法中設置全局異常處理，可以有效地捕獲並處理未預見的異常，從而避免程式崩潰。 註冊全局異常事件 [STAThread] static void Main() { / ...
  • .NET 多版本 WinForm 開源控制項庫 SunnyUI
    前言 給大家推薦一款開源的 Winform 控制項庫，可以幫助我們開發更加美觀、漂亮的 WinForm 界面。 項目介紹 SunnyUI.NET 是一個基於 .NET Framework 4.0+、.NET 6、.NET 7 和 .NET 8 的 WinForm 開源控制項庫，同時也提供了工具類庫、擴展 ...
  • .net core8 使用Swagger（附當前源碼）
    說明 該文章是屬於OverallAuth2.0系列文章，每周更新一篇該系列文章（從0到1完成系統開發）。 該系統文章，我會儘量說的非常詳細，做到不管新手、老手都能看懂。 說明：OverallAuth2.0 是一個簡單、易懂、功能強大的許可權+可視化流程管理系統。 有興趣的朋友，請關註我吧(*^▽^*) ...
  • WPF 什麼時候 VisualTreeHelper.GetDescendantBounds 將返回無窮大
    一、下載安裝 1.下載git 必須先下載並安裝git，再TortoiseGit下載安裝 git安裝參考教程：https://blog.csdn.net/mukes/article/details/115693833 2.TortoiseGit下載與安裝 TortoiseGit，Git客戶端，32/6 ...
  • C# 開源教程帶你輕鬆掌握數據結構與演算法
    前言 在項目開發過程中，理解數據結構和演算法如同掌握蓋房子的秘訣。演算法不僅能幫助我們編寫高效、優質的代碼，還能解決項目中遇到的各種難題。 給大家推薦一個支持C#的開源免費、新手友好的數據結構與演算法入門教程：Hello演算法。 項目介紹 《Hello Algo》是一本開源免費、新手友好的數據結構與演算法入門 ...
  • 第22篇 生成proto文件bat腳本
    1.生成單個Proto.bat內容 @rem Copyright 2016, Google Inc. @rem All rights reserved. @rem @rem Redistribution and use in source and binary forms, with or with ...
  • 記一次 .NET某上位機視覺程式 卡死分析
    一：背景 1. 講故事 前段時間有位朋友找到我，說他的窗體程式在客戶這邊出現了卡死，讓我幫忙看下怎麼回事？dump也生成了，既然有dump了那就上 windbg 分析吧。 二：WinDbg 分析 1. 為什麼會卡死 窗體程式的卡死，入口門檻很低，後續往下分析就不一定了，不管怎麼說先用 !clrsta ...
  • 醫學DICOM文件與PACS系統進行數據傳輸
    前言 人工智慧時代，人臉識別技術已成為安全驗證、身份識別和用戶交互的關鍵工具。 給大家推薦一款.NET 開源提供了強大的人臉識別 API，工具不僅易於集成，還具備高效處理能力。 本文將介紹一款如何利用這些API，為我們的項目添加智能識別的亮點。 項目介紹 GitHub 上擁有 1.2k 星標的 C# ...
所有分類