異動資料擷取

什麼是「異動資料擷取」

在 DW 的設定過程中，有些來源資料表會隨著時間不停的在變更，例如訂單資料表。 若要將這些變更定期載入到 DW 中，不可能每次都 Copy 整個來源快照集，因為這樣會耗費較多時間和資源。 所以「遞增載入」（Incremental Load）的需求就此產生，也就是每次執行 ETL 時只會轉移新增或異動的資料。

在處理這類問題時，你可以會遇到情況：

• 你可以區分出何謂新的資料，所以，你只要轉移新增或異動的資料即可。
• 你無法區分新舊的界線，但是你可以區分特定區間的資料，例如每次轉移特定月或特定天的資料。
• 沒有任何資訊可以讓你區分新舊資料的界線。

本章節將介紹幾種方法來解決「異動資料擷取」的問題。

使用動態 SQL 讀取資料

如果你的來源資料可以使用特定的欄位來區分新增的資料，例如 OrderDate 欄位，那麼你就可以在 SSIS 中定義一個 @IncrementalDate 變數， 透過動態 SQL 的方式，來轉移那些比 @IncrementalDate 還要之後的資料。

在 SSIS 中，如何使用動態 SQL ，會因使用何種來源連接器類型而有所不同。

在 OLEDB Source 中建立動態 SQL

若使用 OLE DB Source Adapter ，有二種方式產生動態的 SQL 。

1) 在 SQL Command 中套用參數

這個方法是在 SQL Command 中，加入「?」，再將變數值當做參數套用進去。

先定義一個變數。

設定 SQL Command Text 中，加入「?」。

將變數對應到參數。

2) 使用變數的 SQL Command

這個方法是整個 SQL Command 都由變數取得，所以你必須先定義一個類似 @SQLString 的字串變數。

點選上點中的「運算式」按鈕，開啟「運算式產生器」。

設定 OLE DB Source 的存取模式為「來自變數的 SQL 命令」。

在 ODBC Source 中建立動態

若是使用 ODBC 或 ADO.NET 資料來源，其動態 SQL 的設定方法必須在 Control Flow 中 Data Flow Task 的 expression 屬性上。

如果你開啟 ODBC Source 的編輯，你會發覺它不像 OLEDB Source 有個「參數」或者「來自變數的SQL命令」，可供設定動態 SQL 的方法。

1. 你可以在 Control Flow 中點一下 Data Flow Task ，然後開啟屬性視窗中的 expression 屬性。

2. 在 expression editor 中，選擇 [your odbc source].SqlCommand 屬性，並設定運算式。

3. 在 expression builder 中，輸入以下運算式。

"SELECT SalesOrderID, OrderDate, CustomerID, TaxAmt, SubTotal 
FROM Sales.SalesOrderHeader
WHERE OrderDate >=  '"  +  (DT_WSTR, 50)(DT_DBTIMESTAMP)  @[User::IncOrderDate] + "'"

4. 執行

SSIS 會將這個 expression 當做 ODBC 資料來源的 SQL Command 執行。

變更一下 expression 變數中的值，再次執行就可以得到不同的結果。

使用 SSIS 的 CDC 功能

上面說明例子中，剛好有個 OrderDate 欄位可用來識別變更的資料，所以可以做到累加式載入。 如果來源資料缺少這樣子的欄位，執行 ETL 時就必須整個資料集載入，或者自行設計「異動資料擷取（Change Data Capture）」。

「異動資料擷取」，簡單講就是當資料發生新增、修改、刪除操作時，將資料記錄下來。 通常可以透過 Trigger 來設計規劃。 但是自行設計的「異動資料擷取」功能，通常不僅繁鎖且耗系統資源。 所幸自 SQL 2008 開始，微軟已在 SQL Server 中加入了這個新功能，可以輕鬆做到「異動資料擷取」。

Enabling CDC on the Database

使用「異動資料擷取」功能，必須對資料庫設定啟用 CDC ，還必需針對每個你要擷取的 Table 設定啟用。 同時必須啟用 SQL Server Agent ，擷取處理序才會將交易記錄寫入變更資料表。

啟用 CDC 功能

啟用範例：

--using MyDW

CREATE ROLE cdc_role;

--設定資料庫啟用 CDC
EXEC sys.sp_cdc_enable_db;

--設定 stg.CDCSalesOrderHeader 啟用 CDC
EXEC sys.sp_cdc_enable_table
@source_schema = N'stg',
@source_name = N'CDCSalesOrderHeader',
@role_name = N'cdc_role',
@supports_net_changes = 1;

--PS.

--資料庫停用 CDC
EXEC sys.sp_cdc_disable_db
GO

--資料表停用 CDC
EXEC sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name   = N'MyTable',
@capture_instance = N'dbo_MyTable'
GO

CDC 系統資料表

在啟用 CDC 後，系統會自動建立以下幾個系統資料表：

• cdc.change_tables ：記錄哪些 tables 啟用 CDC 。
• cdc.captured_columns ：記錄哪些 columns 啟用 CDC 。
• cdc.ddl_history ：History of all of the data definition (DDL) changes since CDC enablement.
• cdc.index_columns ：Indexes associated with CDC tables.
• cdc.lsn_time_mapping ：Used to map between log sequence number (LSN) commit　values and the time the transaction was committed.
• cdc.＜CDC_Table＞_CT ：這個資料表是針對啟用CDC的來源資料表，所建立的系統資料表，用來記錄插入和刪除作業的資料。如果是更新作業則記錄兩個資料列(刪除+插入)。

啟用 SQL Server Agent 作業

在啟用 CDC 時，系統也會在 SQL Server Agent 中建立二個作業，所以要使用 CDC ， SQL Server Agent 也必須要啟動執行。

取得淨變更項目

完成以上設定後，你可以針對你設定的 CDC 資料表進行變更或新增資料，觀查一下 CDC 功能是如何在 cdc.＜CDC_Table＞_CT 資料表記錄變化。

雖然由 cdc.＜CDC_Table＞_CT 資料表可以取得變更的記錄，但是你也可以透過以下 CDC 函式取得變更。

• cdc.fn_cdc_get_all_changes_＜capture_instance＞：傳回在定義間隔內發生的所有變更。
• cdc.fn_cdc_get_net_changes_＜capture_instance＞：傳回在定義間隔內發生的淨變更。

DECLARE @begin_time datetime, @end_time datetime;
DECLARE @from_lsn binary(10), @to_lsn binary(10);

SET @begin_time = GETDATE() -1;
UPDATE SalesOrderHeader SET TerritoryID = 6 WHERE SalesOrderID = 43659;
UPDATE SalesOrderHeader SET TerritoryID = 7 WHERE SalesOrderID = 43659;
INSERT dbo.SalesOrderHeader values ('75124','2005-7-2','SO75124','29825','279','7',0,0,0)
SET @end_time = GETDATE();

SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time);
SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);

所有變更

select * from cdc.fn_cdc_get_all_changes_dbo_SalesOrderHeader (@from_lsn, @to_lsn, 'all')

淨變更

select * from cdc.fn_cdc_get_net_changes_dbo_SalesOrderHeader (@from_lsn, @to_lsn, 'all')

SSIS CDC Components

為了讓 SSIS 在設計 CDC 時可以更加容易，SQL Server 2012 加入了幾個新元件：

• CDC Control Task
• CDC Source Adapter
  
• CDC Splitter
  

CDC Control Task

「CDC Control Task」主要用來控制 CDC 封裝的生命週期。 它會管理 CDC 封裝在執行時候的記錄序號 (Log Sequence Number) ，也會維護 CDC 封裝中的狀態變數，並將它保存在資料庫資料表中。

CDC 控制作業

• 標示初始載入開頭（Mark Initail Load Start）： 初始載入封裝開始讀取資料之前。
• 標示初始載入結尾（Mark Initail Load End）：初始載入封裝結束讀取資料之後。
• 標記 CDC 開始（Mark CDC start）：
• 取得處理範圍（Get processing range）：遞增載入封裝開始讀取資料之前
• 標示已處理的範圍（Mark processed range）：遞增載入封裝結束讀取資料之後
• 重設 CDC 狀態（Reset CDC state）：

CDC Source Adapter

CDC Source會從 SQL Server 2012 變更資料表中讀取變更資料的範圍，並將這些變更向下游傳遞至其他 SSIS 元件。

CDC 處理模式

使用 CDC Source Adapter 時，它提供 5 種取得變更資料的方式：

• 全部（All）：傳回目前 CDC 範圍中的變更，不含舊值（更新之前的值）。
• 全部（含舊值）（All with old values）：傳回目前 CDC 處理範圍中的變更，包括舊值（更新之前的值）。每個更新作業都有兩個資料列：一個包含更新之前的值，另一個則包含更新之後的值。
• 淨值（Net）：針對目前 CDC 範圍中的變更，每個資料列只傳回一筆結合變更後的資料列。
• 淨值（含更新遮罩）（Net with update mask）：這種模式與一般的淨值模式很相似，但是它還加入了名稱模式為 __$＜column-name＞__Changed 的布林資料行，表示目前變更資料列中的變更資料行。
• 淨值（含合併）（Net with merge）：這種模式與一般的淨模式很相似，但是插入和更新作業會合併成單一合併作業 (UPSERT)。

CDC Splitter

CDC Splitter 是用來將來自於 CDC Source 中的資料，依插入、刪除、更新等作業類型，分割成三個獨立的輸出。

ETL Strategy for Incrementally Loading Fact Tables

The following lists some general guidelines for loading fact tables:

• Partition your fact tables.
• Incremental data should be on one partition, so that you can easily remove this data without a delete operation by using partition switching.
• Use this loading strategy:
  • Load incremental data to a table that has the same structure as the destination fact table, without compression or indexes.
  • Apply the necessary indexes and compression.
  • Switch the loaded table with the partition in the destination fact table.
• Use fully cached lookups to get appropriate surrogate keys.

• 異動資料擷取的概觀
• 異動資料擷取 (SSIS)