ETL的可擴充套件性和可維護性

:[@more@]

ETL的過程我想很多人都談過了，最近我在比較SSIS，OWB和infomatica，結合以前的專案，加深了我對ETL過程的理解和認識。
其實這三種工具，除去應用平臺以為，大同小異，各有利弊。今天我想分享一下我的經驗，主要在擴充套件和維護方面。

1：資料落地和ELT
很多人談到資料落地的概念，說白了，就是ODS或者DWH中，有資料input或者output的，都先把要操作的資料放到臨時表裡面，而資料傳輸的兩端的表的結構都是一樣的，這樣的操作比較便捷，幾乎不用考慮轉換的問題。
同時我們還要提到的就是ELT，ELT和ETL其實各有好處，一個是用工具來轉化資料，一個用SP，工具一般都是一行行的處理，而SP一般都是一列列的處理。我個人認為ELT，用SP處理資料比較好，因為我們的測試或者將來的維護，都需要經常改變表間的mapping關係。而SP只是需要在資料庫裡面做一些修改和操作，而且還比較容易除錯，去發現問題的根源。

2：指令碼檔案
這裡的指令碼檔案可以分為FTP script，table Script，Stored proc Script還有Shell指令碼，不管是dos shell 還是unix shell，這些都是一些作業系統的控制檔案，我們可以暫時不談。為什麼要談談這些指令碼了，其實我們在設計和開發結構的時候很簡單，但是我們修改的時候，就比較麻煩了，特別是ETL工具在開啟一個package的時候，需要載入和驗證，有的時候很慢很慢，如果我們只是修改一些小引數的話，花長時間就不值得，如果我們將指令碼檔案都放在一起的話，那麼開啟txt或者bat檔案就很方便。還有就是我們對於一些臨時表，有的時候需要將資料全部刪掉，我們可以用truncate table，有的時候用drop和create也是不錯的選擇。
當然，如果用指令碼檔案的話，主機的安全性一定要好，而且對於賬號的表級操作許可權也要分配好。

3. 策略表
我們按照不同的頻率，定時執行package，有的時候可能遇到error，有的時候可能由於其他的問題不能按時執行。其實我們可以做一張策略表，儲存每個package的執行的時間規則，然後每天開始執行前，將所有的package初始化出當天的執行情況，記錄開始結束時間，還有成功標記。執行時，可以先讀取上一個執行日的執行情況，將未完成的package，延續的到當日。

4. 日誌表
準確的來說，ETL工作中，從package執行開始的那一刻，每一個小步都要記錄日誌，記錄發生了什麼，做了什麼，還有出錯的資訊和控制情況，必要時，我們還要按照資料庫的事物處理方式，將相關的包的執行資料全部釋放掉。這樣日誌表和策略表join，我們就可以清楚的看到我們執行的效能，效率和準確的詳細的執行資訊。根據package的優先順序，我們可以將日誌報表和緊急的錯誤，透過SMS或者email，發給相關的人員。

5. 重新執行
重新重新整理資料是經常遇到的，我們發現資料不準確，就有可能需要重新執行，重新執行有中斷重新執行和完全重新執行，甚至需要全部重新執行。
我們用batchid來控制每一天或者每一批的資料，但是有的時候由於這樣那樣的問題，倒是我們的package在執行中中斷，我們就需要去尋找日誌，解決問題，然後重新執行。這一步的手工操作比較多，完全重新執行就是重新執行某天的某個packge，需要將對應的資料表做一些操作，比如刪除，全部重新執行，就是將資料倉儲裡面的資料全部重新創新，相當於從0開始。
這幾種操作都是我們會遇到的，我們在設計包的時候，儘量的讓package自己去完成，我們人為的控制儘量的減少。可以用一些引數的方式去控制。

6. 團隊協作性
ETL的工作佔整個資料倉儲工作量的50%-70%，所以團隊協作性一定要好。而ETL包含E，T，L還有日誌的控制，資料模型，原資料驗證，資料質量等等方面。例如我們要整合一個企業亞太區的資料，但是每個國家都有自己的資料來源，有的是ERP，有的是Access，而且資料庫都不一樣，好要考慮網路的效能問題，如果直接用ODBC去連線兩地的資料來源，這樣的做法很顯然是不合理的，因為網路不好，經常連線，很容易資料庫連結不能釋放導致當機。如果我們在各地區的伺服器放置一個資料匯出為access或者flat file的程式，這樣檔案就比較方便的透過FTP的方式進行傳輸。我們就需要有幾項工作：
1）有人寫一個通用的資料匯出工具，可以用java，可以用VB，或者其他的工具，總而言之就是要通用，可以透過不同的指令碼檔案來控制，使各地區的不同資料庫匯出的檔案格式是一樣的。而且還可以實現並行操作。
2）有人寫FTP的程式，可以用bat，可以用ETL工具，可以用其他的方式，總之要準確，而且方便呼叫和控制。
3）有人設計資料模型，包括1）點中的匯出後的結構，還有ODS和DWH中的表結構
4）有人寫SP，包括ETL中需要用到的SP還有日常維護系統的SP，比如檢查資料質量之類的
5）有人分析原資料，包括表結構，資料質量，空值還有業務邏輯
6）有人負責開發流程，包括實現各種功能，還有日誌的記錄等等。
7）有人測試

真正好的ETL，都是團隊來完成的，一個人的力量是有限的

本日誌同步記錄在blog：http://bidwhome.itpub.net/