資料倉儲的累加式更新

 

作者：楊先民  
精誠資訊/恆逸教育訓練中心資深講師

※網路引用請註明完整出處

最近在課堂上被問到的一個問題，而這個問題是在講資料倉儲「累加式更新」時所被問到的，當下覺得這是一個還蠻有挑戰性的問題，所以本期就以累加式更新為例，舉例如果無法在 OLTP 資料庫中做資料結構異動時，我們該如何進行累加式更新。

累加式更新

在講本期之前，先簡單的介紹一下累加式更新這個東西，會需要累加式更新，主要在於 OLTP 資料庫不適合進行彙總運算，即便你設定了大量的索引，對於需要大量彙總運算的決策支援系統而言，一點幫助也沒有，所以，如果想要你的現有系統在決策支援中很快速的提供使用者報表、圖表，勢必要把資料庫的結構稍微做一些修改，使用原本第三正規化的 OLTP 資料庫因為資料表關聯過多，不但關聯花時間，大量的彙總運算更是效能低落，它唯一的好處是，它會有非常即時的資料。

所以，資料倉儲的課程，一開始並不介紹如何建立 Cube、 維度這些主題，而是要想辦法先將原本的 OLTP 正規化資料庫，先轉成反正規化的資料倉儲資料來源，如下圖：

其中， OLTP 與 OLAP 資料來源中有一個 staging 資料庫，用來暫存資料，因為這整個流程不是只做一次， OLTP 會天天有新資料進來，如何篩選哪些資料新增，哪些資料修改與刪除，就必須是我們這種 ETL 開發人員做的事情，而新增、修改、刪除的暫存資料都將會先放在 staging 資料庫之中，以便待會再匯入到資料倉儲的資料來源。

現在的問題就出現在這裡，在課程中會教有哪些方式可以達到「累加式更新」的境界，畢竟不太可能每次都打掉重來，如果有 100 萬筆資料，不可能每次都重新再匯一次吧？書上有介紹幾種方式，分別如下：

1. 儲存 Primary key 與 checksum：但書上沒有舉例。

2. 使用「high water mark」，也就是高水位線法，利用一個 log 資料表記錄上次 ETL 的時間，以及資料表在設計時會多一個 LastModifiedate 的欄位，當資料有異動時，利用 trigger 順便異動 LastModifiedate 的欄位，這些就可以利用 where 子句只將異動的資料匯入到 staging 資料庫即可，此種方法只能知道資料的新增與修改，無法知道資料的刪除。

3. 使用 Change Data Capture：這個是 SQL Server 企業版的功能，方法還是使用高水位線法，但是可以知道某段時間資料的新增、修改以及刪除情況。

4. 使用 Change Tracking，這個也是 SQL Server 的功能，利用版本控制的方式，每當資料有修改的時候，版本控制就會多 1 號版本，也是使用高水位線的方式取出第 N 個版本到第 M 個版本之間的資訊差異，不過同樣也只能得知資料的新增與修改，無法知道資料的刪除。

其實無法知道資料是否被刪除並不是個很重要的是，因為資料刪除一般在實務上的資料庫未必會真的將資料刪掉，例如員工離職也不會把員工資料刪除，而是把「員工狀態」改成離職，而一般採取的方法是邏輯刪除，也就是設計一個 deleted 的欄位，預設值為 0，如果該資料要刪除的話，就把 deleted 的欄位值由 0 改成 1 代表資料已經被刪除，換句話說，資料刪除其實就是資料修改。

好的，大概了解這個累加式刪除後，有個學員就問了，他的資料庫不是 SQL Server，也不能更動資料表原本的結構，在這樣的情況之下，要如何知道該資料有被異動？

這個條件老實說太嚴格了，說這個資料庫是外包balabala一堆理由，所以結構無法修改，如果是我是不會管這麼多的，我只知道，如果要解決問題，必須要用最簡單的方式，因為 OLTP 效能調校主要的關鍵在於下好的 SQL 指令，而 OLAP 資料倉儲效能調校的關鍵在於如何快速的找到異動的資料，並加以匯入到 staging 資料庫，並且在資料倉儲資料來源中快速的處理成 Cube 以供使用者使用。

如果不用現成的方式來做，硬說資料庫中的資料表連一個欄位都無法增加的話，勢必會造成資料量一大，效能就會變慢的問題，這點是我們在想解決方案之前不得不注意的問題。

方法到底有沒有呢？我們可以從新增以及修改兩個角度來看，刪除我們就視為是邏輯刪除，不直接把資料刪掉。

資料的新增

如何知道資料被新增，如果你有按照我們正常設計資料庫的方式，將 Primary key 設定成 identity 的話，則每列資料都是不重複的數字，這樣你就可以建立一個資料表，然後記錄上次上傳到哪個數字，下次則是把新增加進來的數字上傳即可，如下圖，就是建立一個資料表：

以上圖為例，表示資料表 Product，上次匯進的資料到606號，而目前 Product 資料表裡的資料如下：

對的，目前資料為606，所以並沒有新的資料被產生，而如果產品有新增時，productkey將會變成607，和原本 Log 資料表比對，就知道這個資料是新增進來的。

所以，以新增的角度來說，是比較容易處理的。

資料的修改

那麼，資料的修改該怎麼辦呢？

其實這個也不難，但是整體的效能我就無法保證，因為它將會是每一筆記錄都會去比較是否有異動，資料量越大，越有可能產生效能的問題，我可以預想到，接下來就是會問我「該如何解決效能問題」了…

SQL Server 有個函數叫做 checksum，或許可以利用這個函數來判斷資料行是否有修改，你可以使用下面的句子來判斷：

使用 checksum(*)來判斷其值，若任一欄位值有被修改，則 checksum(*)的結果都會被修改，例如上圖，productkey 為 210，其 checksum 值為 -620887203，如果我們隨意修改任何一個欄位的話，它的值就會被修改，如下圖：

比對一下，值變成了 -620887491，是不是有改變呢？

只要比較一下，有相同的 primary key，但是 checksum 的值不同，就表示這筆資料是有異動了，但到底異動了哪了欄位，或是你要對這筆異動的資料列做什麼處理，就是後續要做的事情了，此舉並沒有更改到資料表的任何結構。

當然，有些人對新增可能有點意見，認為新增一個 log 資料表有更改到資料表結構，其實那個資料表你不一定要加在原來的資料庫中，你可以另外建立一個資料庫，專門記錄這個 log資料表的資料，無法更改資料表的結構，那新增一個資料庫總可以了吧？如果連新增資料庫另外記錄上次匯入的 key 值都不行的話，我只能回答：roll your egg（滾你的蛋）！

此外，由於是使用 checksum 這個函數得到資料是否有無被修改，所以變成每筆資料列都要被檢查，資料量大的效能我可不敢測試…這就是標準的，為了要迎合情境，必須徹底犧牲效能的最佳例子。