技術分享｜基於SQL Server Change Tracking實現寬表的增量更新

但是，我們仔細想想，如果兩次排程的間隔非常短，業務系統在這兩次排程之間產生的業務資料也會非常少，甚至沒有任何資料產生，我們是否可以根據“變化的資料”來實現增量更新呢？

其實，ETL的目的就是為了生成一張大寬表，我們可以基於寬表模型設計一個增量更新的方案：

該流程可以每分鐘執行一次，將原先每分鐘的“全量計算”優化為了“增量計算”，這樣就大幅度的降低資料庫的壓力。

二、詳細方案

在開始之前，先看一下明源寬表的定義：所有的寬表都是“圍繞一個業務實體，將該業務實體相關的維度、指標和屬性關聯在一起，形成了一張資料庫表”。

“房間寬表”示例如下：

這裡有兩個特徵：

1、每個寬表必須有一個唯一的主鍵，這個主鍵來源於ERP的業務實體表。

2、其它的維度和指標可以來源於不同的業務表，但都能通過寬表的主鍵聯絡在一起。

這樣的模型非常有助於我們實現增量更新： 我們可以讓使用者將每組指標的計算邏輯拆分出來單獨定義，一旦某個業務發生之後，只需要對受影響的指標進行重新計算即可。

那麼，如何找到受影響的指標，以及如何找到受影響的行呢？接下來，對流程圖中幾個關鍵問題進行詳細討論。

（一）我們需要監聽哪些表的資料變化？

參與寬表邏輯計算的表才是我們需要監聽的表，其它表都不需要關注。

（二）如何對錶的變化資料進行跟蹤？

我們可以在表上建立觸發器或者新增時間戳欄位，用於獲取增量變化的資料。但是這種方案會對ERP資料庫產生侵入性，並且時間戳欄位解決不了delete的場景。針對這個痛點，微軟在SQL Server 2008及之後的版本中，提供了SQL Server Change Tracking和SQL Server Change Data Capture兩種解決方案，直接在資料庫級別記錄了各個表的資料變更日誌，開發人員按需獲取就行。我們需要在Change Tracking(簡稱CT)和Change Data Capture（簡稱CDC）之間做一個選擇，兩者的對比分析如下：

CT和CDC都有類似“版本號”和“序列號”的欄位用於記錄資料變化的順序，同時都有“operation”欄位用於標識資料變化的型別：

兩者的差異點在於：CT只返回了變化表的主鍵值，而CDC返回了整個資料行。從能力上講，CDC要大於CT，但是使用CDC需要開啟SQL Server Agent服務，而CT則沒有任何依賴。相對來說，CT相對於CDC更加輕量級，在產品上線時也沒有額外的負擔，這也是我們選擇CT的核心原因，我們很難確保所有客戶的SQL Server Agent服務都正確安裝和時刻執行。

我們對相關表開啟CT後，就可以通過CHANGETABLE函式來獲取增量資料了

SELECT * FROM CHANGETABLE(CHANGES @table_name, @last_synchronization_version) AS CT

@table_name代表獲取哪個表的變更資料。

@last_synchronization_version指的是獲取哪個版本號之後的變更資料，在每次獲取到變更資料之後，我們還需要將返回值中的最大版本號記錄下來，以便於以下次獲取增量資料。如果是第一次查詢，@last_synchronization_version則為0。

（三）如何根據“表的變化”推算出“寬表中受影響的行”？

在通過CT跟蹤到每張表的變更資料之後，CT函式只返回了變更表的主鍵，並不是“寬表的主鍵”。我們需要讓使用者針對每個表編寫一個SQL語句，用來告訴程式“該表變化後如何推算出寬表中受影響的主鍵”，這個步驟主要是靠使用者來定義。

還是以“房間寬表”為例，當101房間發生了簽約業務之後，我們就需要更新101房間的“簽約資訊”，由於CT中只能獲取到“合同Id”，這裡就需要使用者編寫一個SQL語句，便於程式將101房間計算出來。

針對"合同表"的轉換SQL可以編寫成如下:

SELECT 房間Id FROM 合同表 WHERE 合同Id=@合同Id

"@合同Id"代表CT中監聽到的“合同表主鍵”，我們將它帶入到SQL語句的引數中就得到了我們重新計算的“房間Id”。

（四）如何根據“表的變化”推算出“寬表中受影響的列”？

為了實現最小粒度的資料更新，我們需要讓使用者將清洗邏輯拆分成多個小段，每個小段負責一個或多個指標的邏輯計算，拆分的越小，增量的效果越明顯。為了保證程式能夠將所有的“小段邏輯”關聯起來，每個“小段邏輯”必須輸出寬表的主鍵。

有了上面的定義之後，我們再看“本次變化的表”出現在哪些“小段邏輯”中，這些“小段邏輯”對應的指標就是需要重新計算的列。

（五）如何重新計算受影響的資料？

將“第三步得到的結果”做為過濾條件，拼接在“第四步的小段邏輯中”，即可計算出受影響的資料，示例如下：

SELECT * FROM
  (
--ETL語句
SELECT 寬表主鍵，指標1
FROM 表1
LEFT JOIN 表2
LEFT JOIN 子查詢
GROUP BY ...
  ) t
WHERE 寬表主鍵 IN (@寬表中受影響的主鍵)

最後再將該指標的結果更新到寬表中。

三、如何實現

我們首先需要一個B/S架構的寬表管理系統，客戶端用來負責寬表的元資料管理，服務端用於資料的增量清洗。

寬表的元資料包含以下幾個物件：寬表的名稱、欄位定義、每個指標的清洗邏輯、每個表如何轉換出“寬表主鍵”的SQL。

服務端在第一次啟動寬表時，需要執行所有的“小段邏輯”，先將全量的資料生成到寬表中，然後再迴圈執行增量更新的流程。

詳細版的增量更新邏輯如下：

四、應用總結

明源寬表不僅解決了報表取數的效能問題，同時具備很好的時效性，為“傳統的ETL”技術賦予了“實時”的屬性。 在明源 ERP出庫時，已經將“ 寬表服務 ”作為了標準配套服務，目前累計服務超過了1400家客戶。

----- END ------

作者簡介

袁同學： SM，目前負責資料平臺相關工作。