使用SSIS進行數據清洗

文章出處

簡介

OLTP系統的后端關系數據庫用于存儲不同種類的數據，理論上來講，數據庫中每一列的值都有其所代表的特定含義，數據也應該在存入數據庫之前進行規范化處理，比如說“age”列，用于存儲人的年齡，設置的數據類型為INT類型。存入數據庫的值是2000雖然看起來沒有任何問題，但結合業務規則，這樣的”Noisy”數據在數據分析過程中就會造成數據分析的結果嚴重失真，比如極大的拉高平均年齡。在真實的OLTP系統中，這類不該存在的數據往往會由于各種各樣的原因大量存在，類似這種類型的數據都會在數據進入數據倉庫之前，在預處理階段進行清洗。

上面舉出的簡單例子僅僅是一個違反業務規則的情況，但實際的情況會根據具體業務的不同而不同，并不是一個簡單統一的過程就能夠解決，文本列舉了一些典型的需要數據清洗的場景和SSIS中的實現方式。

為什么不使用SQL語句進行處理？

使用SQL語句來查詢和處理這類問題是可行的，但使用SQL語句有其局限性，例如：

假如數據源不是關系數據庫呢？
假如業務邏輯非常復雜，需要非常復雜的SQL語句呢，處理過程中的調試、日志如何處理？

使用SSIS可以很好的解決上述問題，SSIS提供了基于圖的數據流，并在處理過程中提供了調試、日志、查看轉換中的數據的能力，并且SSIS包相較于SQL更適合于團隊作業。

下面是一些典型的數據清理作業，使用SSIS進行處理。

缺失默認值

缺失默認值是最常見的一種需要數據清洗的一種情況，缺失默認值指的是數據庫列中的值為“NULL”，而在數據分析中，該類為NULL的數據需要替換中符合業務邏輯的值，比如NULL出現在銷售地區列可以替換成“其他”，出現在銷售數據列可以替換成“未知”等。

在SSIS中我們可以通過“派生列”任務來為缺失默認值的列添加默認值，本例中我們對Adventureworks示例數據庫的Person.Address數據庫的AddressLine2列進行替換，該列中為NULL的值我們統一替換成“無附加地址”，如圖1所示。

圖1.使用派生列替換NULL

除了上述使用常量替換"NULL"值之外，我們也可以按照業務規則根據其他數據源的數據填補NULL值，圖2中的例子是ProductCostHistory表中部分EndDate列為NULL，通過查找Product表的ModifiedDate列將為NULL的EndDate列中的值補全。

圖2.使用有條件拆分判斷EndDate列值是否為NULL

語義轉換

有時候，某些列的值需要做語義上的轉換，比如說某個產品的"產地"列為5，分析時很難知道5代表什么，因此可以去對應的表或文件進行查找來替換這個5，比如5代表河北。在SSIS中，實現該類轉換可以類似圖2中直接去表中查找，也可以為該查找表（5代表河北這張表）建立緩存，然后通過緩存進行引用查找。

為了實現通過緩存做語義轉換，我們首先設置兩個步驟，第一步首先將查找表的數據導入緩存，第二步再對緩存進行查找從而實現語義轉換。

圖3是總體圖。

圖3.語義轉換

首先將查找表的數據導入緩存，如圖4所示。

圖4.導入緩存

然后通過緩存更新數據，如圖5所示。

圖5.使用緩存進行數據查找

數據類型轉換

當數據來自不同數據源時，不同類型的數據源數據類型不兼容可能導致報錯。在SSIS中，可以使用“派生列”來實現數據轉換，派生列允許寫自定義公式實現比較復雜的轉換，也可以通過數據轉換任務實現簡單的數據類型轉換，從而使得不同數據源的數據兼容。如圖6所示。

圖6.數據類型轉換

數據截斷問題

某些情況下，數據可能在傳輸過程中由于數據長度的限制導致數據截斷，在SSIS中的解決辦法是將截斷或者出錯的數據導入到一個中間表中待進一步處理，如圖7所示。

圖7.數據截斷錯誤輸出到另一個源

在OLEDB源的配置如圖8所示。

圖8.OLEDB數據源設置

查找匹配失敗

在某些情況下，將多個數據源中的數據進行集成時，同一個語義的數據可能以不同的方式存儲，比如一些數據源存的值為“北京”，而另一些數據源存的為“北京市”。解決該類問題的辦法就是“模糊查找”任務。如圖9所示。

圖9.對數據進行模糊查找

在圖9中，我們對數據進行了模糊查找，設置匹配度大于0.5的為匹配，小于0.5的為不匹配，分別輸出到不同的數據源中。從而幫助數據進行了統一。

違反業務規則

在設計數據倉庫時，很重要的一點是列中何種類型的值是合適的。比如值是否在業務范圍之內，或者列中數據與其相關的數據結合是否有效（比如說結束日志不能小于開始日志）。對于該類數據導入到目標表之前應該進行清理，在SSIS中清洗的方式有很多，比如使用“有條件拆分”任務將不符合業務規范的數據進行剔除或處理，或導入stage表。這取決于業務類型。