如何用 DataCleaner 做 Data Profiling 資料分析

在如何用 SSIS 做 Data Profiling 資料分析 ↗我使用 MS SQL 內建工具處理 data profiling 資料分析，但玩了後總覺得不太方便。

一來得安裝肥大的 SQL Server，二來資料來源必須在 SQL Server 上。設定跟操作較為僵化，只能套用內建的幾種分析，而且 run 完沒辦法馬上看結果，得再換一個地方。

後來 google 到另一個 Open Source Tool - DataCleaner，一玩愛不釋手，趕快做個紀錄。

---

DataCleaner 簡介

名稱：DataCleaner
版權：Community 版是 Open Source，另有付費版本。基本分析用 Open Source 版即可
下載：http://datacleaner.org/get_datacleaner_ce ↗
內含 Desktop 與 Web Monitor 兩種，做基本分析單機跑的話抓 Desktop 足矣
安裝：把抓回來的 DataCleaner-windows.exe 解壓縮後執行裡面的 DataCleaner.exe 馬上可用，夠輕量

註：請先確定電腦裡已有 JRE 6 以上的版本

使用手冊：http://datacleaner.org/docs ↗
若是玩到一半對功能不清楚時，可以回到線上手冊來參考一下。
產品支援：forum ↗
在討論區發問真的有人回，還是作者 Kasper 本人。甚至在我回報玩出 error 的 24hr 內立馬包好一版新的。效率比一般商業軟體還高啊！為此深受感動覺得該好好寫一篇文章介紹。

產品更新速度：以我經驗至少每 3 個月就會 release 新版，包含 bug-fix 或 enhancement。提筆的此時我抓的是 3.6.1 版。這裡有 Release Note ↗ 可參考，從 2008 年至今也算成熟的產品了。

適用對象：完全不需要碰到程式，所以對沒資訊背景經驗者也很適用

---

個人覺得的特色

DataCleaner 對 data profiling 是友善操作的資料分析工具，它掌握了常見的 ad hoc 情境並把它用具象的方式呈現。

資料經常有加工的需求
為了分析目的我們經常需要先把資料加工整型，此時 Transform 可派上用場。

往返的分析過程
檢查資料是個往返瑣碎的過程，為了不迷路知道自己身在何處，Visualize 方便看出目前處理到哪一步。

可針對不同情況的資料在同一流程中分別處理
以往用 SQL 處理資料比較難針對不同情況在同一流程中處理，DataCleaner 可以針對資料下條件做 filter 過濾，再進行分別的處理。

分析出的結果可導為清理的開端
分析的同時也等於在清理，分析完畢的資料可以用 WriteData 保留下來。

整個流程可標準化重複執行
分析的流程可以保留，等下次有新資料加入時，再跑一次就可以看到結果。這點可以靠 Job 完成。

好，可以邊玩玩看了！

---

支援哪些資料源

光第一個功能就可以看出 DataCleaner 可吃的 Source 很多元，除了常見的 EXCEL、CSV 、SQL Server、Oracle、MySQL、Postgre SQL 外，連 Cloud Service 的 Salesforce.com、SugarCRM、MongoDB、CouchDB 等等都支援。我工作上最常碰的是 SQL Server，目前連 2008 R2、2012 都沒有問題。

---

牛刀小試 (1) | 哪個國家的訂單最多？

先用 DataCleaner 預設的 orderdb 來看看它好用在哪。按下 Analyze！

範例 1：訂單都來自哪些國家？
步驟 : 左邊是 orderdb 裡的 Table，在 CUSTOMERS > COUNTRY 的欄位點兩下，可看到 COUNTRY 已被吸到右邊的待分析欄位區。

接下來的畫面維持原設定，然後按右上角綠色的 Execute，不出 3 秒，馬上就長出漂亮的直條圖！可看出 USA 是大宗客戶，Germany 其次。

右邊除了可看到個別資料筆數外，還可 drill down 看資料細節。

分析結果可以匯出成 HTML ，這樣就能寄送給他人。或是存成 DataCleaner 自有格式、或是發佈到 DataCleaner 的 Web Monitor 上。

---

牛刀小試 (2) | CUSTOMER 的資料完整度

範例 2 : 看 CUSTOMER 的資料完整度
應用 : Quick analysis (罐頭分析)
步驟 : 點選 orderdb > CUSTOMER 後按右鍵 > Quick analysis，可看到對每個欄位的分析。

罐頭分析會依 data type 的不同，自動幫你套幾個適用的 Analysis

string 類型 - 除了一般的計數，還能用圖型顯示與 drill down。

Number 類型
→ 看到 Second moment 就傻了…都還給老師了

Date/Time 類型 (可挑 ORDERS table 試看看)

由 Quick analysis 的可發現面對臨時需要知道資料長相時，這個功能超級方便。不花吹灰之力便可從圖表知道資料的完整度、最大最小值、空值的數量等等，即使不是資訊人員也可自行完成。

---

牛刀小試 (3) | 從下單日 ~ 出貨日要多少天

範例 3 : 看從下單日 ~ 出貨日要多少天
應用 : Transform、Value Distribution
步驟

1. 點選 orderdb > ORDERS ，分別在 ORDERDATE 和 SHIPPEDDATE 上按兩下，確認兩個欄位被吸到右邊的待分析欄。

2. 假設不考慮 SHIPPEDDATE 為 NULL 的情況，直覺就是要多長出一欄計算 ORDERDATE 和 SHIPPEDDATE 的間隔，然後看這個數字的狀況。

在 DataCleaner 的進到 Transform > Date and time > Date difference / period length

接下來的畫面分三塊

Input columns ：選 FROM = ORDERDATE、TO = SHIPPEDDATE
Required properties：選 Days 代表想以『天』看間隔的單位
Output columns：為計算出的間隔天數欄位取個名字，我取 DIFF_DAYS，設好後按 Preview 看一下結果

以上的動作屬 Transform，它把常用的資料處理做成小功能，我們只要懂得套用即可。

有了 DIFF_DAYS 後，再點選 Analyze 進行分析，這邊我用 Value distribution 看間隔天數的分佈。Input columns 勾 DIFF_DAYS 即可。最底下的 Context visualization 則可看出一路設定的軌跡。當分析過程很複雜時，這張圖可保你不迷路。設好後按 Execute。

看得出大部份的出貨都在 5 天完成。但平均到底多久呢？此時可以再加挑一個 Analyze > Number analyzer 看看統計數據

這是 DataCleaner 很巧妙的地方，多一份 Analysis 在結果頁就會多長出個 tab，切換對照即可。
原來平均出貨日約 3.75 天。

---

牛刀小試 (4) | 看各國郵遞區號的 pattern

範例 4 : 看各國郵遞區號的 pattern ，再決定該如何清理資料
應用 : Filter、pattern finder
步驟

1. 點選 orderdb > CUSTOMERS ，分別在 COUNTRY 和 POSTALCODE 上按兩下，確認兩個欄位被吸到右邊的待分析欄。

2. 假設我們已知 CUSTOMERS 裡包含 USA 跟其它非 USA 的資料，我們想知道 USA / 非 USA 的 POSTALCODE 長的有什麼不一樣，藉此準備不同的清 POSTALCODE方法。

在 DataCleaner 的進到 Transform > Filter > Equals

我們要找 USA 的資料，所以選 COUNTRY = USA，並把符合條件的資料視為 VALID，以供後續使用。中間的選項請按下 VALID，並選 Set as default requirement。從最底的圖可看出透過 Filter ，資料被分成兩條岔路。

設好後按上方的 Analyze > Pattern finder，可以看到有個像小向日葵的圖案顯示現在是以 VALID ( COUNTRY = USA ) 的資料為分析主體。把 POSTALCODE 打勾，其餘保持不變，再按 Execute 。

USA 的 POSTALCODE 資料還蠻整齊的，都是數字。

那非 USA 的資料呢？我們回到設定 pattern 的畫面，把小向日葵的設定改成 Equals : INVALID。跑出來的結果可多了，有都是數字的，也有文數字夾雜還包含空白、dash 的。這個線索有助於瞭解哪幾種狀況需要考慮，未來做 data cleansing 時才不至於遺漏。

---

牛刀小試 (5) | 看資料裡有哪幾種國家的文字

範例 5 : 看資料裡有哪幾種國家的文字
應用 : Character Set
步驟 : (我使用自己的資料)，用 Analyze > Character set 即可跑出結果。

特別挑這個分析是因為手上的客戶名單來自全球，得看看資料特性。同事也對 DataCleaner 如何找出資料的 Character Set 感到好奇，Kasper 不吝分享他們使用了這個 library http://site.icu-project.org/，就留待需要的朋友參考。

而跑分析不難，難的是搞懂跑出來的究竟是什麼東東。Character set、Unicode、UTF-8 這些詞經常看到，我總是似懂非懂，趁這機會來查一下，標於本文最後。

---

結論 | 運用你的巧思開始觀察資料吧

我想，DataCleaner 讓我愛不釋手的原因在於它看穿了 Data Profiling、Data Quality 擁有 ad hoc 的本質，是個得視資料情形而定，需要不斷往返修改的事情。

從 Tool 的設計可以感受到以資料為主，其它操縱像一個個作用在資料之上的小開關，你可以任意組合這些開關，像堆積木一般達到目的。

而 MS SQL 的 Data Profiling 像制式的模板，它已經設好那些開關，沒法多也沒法少，你把 SQL DB Only 的資料餵進去後就只能等它跑出結果，中間沒辦法動手腳。

DataCleaner 讓你當麵包師傅還附贈小徒弟(一堆好用的開關)，MS SQL 的 Data Profiling 讓你用麵包機 (還是不能加料的那種)

---

註 : Character Set, Unicode, Database Collation 的差異

What’s the difference between Character Set, Unicode ?
What is Character Set :

• a fixed collection of symbol. (符號)
• For example, the English alphabet “A” to “Z” and “a” to “z” can be a character set, with a total of 52 symbols. (像英文字母這種東西)

What is Character Encoding :

• a standardized way to transform each char in a char set into a number. (轉換的方法 - 把字母轉成數字)
• Any file has to go thru encoding/decoding in order to be properly stored as file or displayed on screen. (檔案需要經由編碼/解碼以便能存到電腦或秀在螢幕畫面上)
• Need a way to translate the character set of your language’s writing system into a sequence of 1s and 0s. (需要有一種方法將人類書寫的文字轉換成電腦的 0101)

Popular Encoding System

• ASCII. For English. Most widely used before year 2000.
• Unicode. Includes ALL human language’s written symbols. It includes the tens of thousands Chinese characters, math symbols, as well as characters of dead languages, such as Egyptian hieroglyphs.
• UTF-8 of Unicode (used in Linux by default, and much of the Internet)
• UTF-16 of Unicode (used by Microsoft Windows and Mac OS X’s file systems, Java, …)
• Unicode 6.3 Character Code Charts
• IEC 8859 series (used for most European langs)
• GB 18030 (Used in China, contains all Unicode chars).
• EUC (Extended Unix Code). Used in Japan.

What is Database Collation

• a set of rules that determine how data is sorted and compared. (規範 - 資料怎麼存、比對 )
• Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

比較瞭解後再回來看結果，可知底下這些都是 Character Set。

Name
Arabic
Cyrillic	通行於斯拉夫語族大多數民族中的字母書寫系統 Cyrillic
Han	漢 CJK Strokes
Hangul	韓 Hangul Jamo
Hebrew
Hiragana	平假名 Hiragana
Katakana	片假名 Katakana
Latin, ASCII	標準鍵盤上打出來的英數字符號，如這些 Basic Latin (ASCII)
Latin, non-ASCII	也是拉丁文，但不在上面此列，如 Latin-1 Supplement Latin Extended-A Latin Extended-B Latin Extended-C Latin Extended-D Latin Extended Additional Latin Ligatures Fullwidth Latin Letters