|
ETL
的考慮
做數據倉庫系統，
ETL
是關鍵的一環。說大了，
ETL
是數據整合解決方案，說小了，就是倒數據的工具。回憶一下工作這么些年來，處理數據遷移、轉換的工作倒還真的不少。但是那些工作基本上是一次性工作或者很小數據量，使用
access
、
DTS
或是自己編個小程序搞定。可是在數據倉庫系統中，
ETL
上升到了一定的理論高度，和原來小打小鬧的工具使用不同了。究竟什么不同，從名字上就可以看到，人家已經將倒數據的過程分成
3
個步驟，
E
、
T
、
L
分別代表抽取、轉換和裝載。
其實
ETL
過程就是數據流動的過程，從不同的數據源流向不同的目標數據。但在數據倉庫中，
ETL
有幾個特點，一是數據同步，它不是一次性倒完數據就拉到，它是經常性的活動，按照固定周期運行的，甚至現在還有人提出了實時
ETL
的概念。二是數據量，一般都是巨大的，值得你將數據流動的過程拆分成
E
、
T
和
L
。
現在有很多成熟的工具提供
ETL
功能，例如
datastage
、
powermart
等，且不說他們的好壞。從應用角度來說，
ETL
的過程其實不是非常復雜，這些工具給數據倉庫工程帶來和很大的便利性，特別是開發的便利和維護的便利。但另一方面，開發人員容易迷失在這些工具中。舉個例子，
VB
是一種非常簡單的語言并且也是非常易用的編程工具，上手特別快，但是真正
VB
的高手有多少？微軟設計的產品通常有個原則是“將使用者當作傻瓜”，在這個原則下，微軟的東西確實非常好用，但是對于開發者，如果你自己也將自己當作傻瓜，那就真的傻了。
ETL
工具也是一樣，這些工具為我們提供圖形化界面，讓我們將主要的精力放在規則上，以期提高開發效率。從使用效果來說，確實使用這些工具能夠非常快速地構建一個
job
來處理某個數據，不過從整體來看，并不見得他的整體效率會高多少。問題主要不是出在工具上，而是在設計、開發人員上。他們迷失在工具中，沒有去探求
ETL
的本質。
可以說這些工具應用了這么長時間，在這么多項目、環境中應用，它必然有它成功之處，它必定體現了
ETL
的本質。如果我們不透過表面這些工具的簡單使用去看它背后蘊涵的思想，最終我們作出來的東西也就是一個個獨立的
job
，將他們整合起來仍然有巨大的工作量。大家都知道“理論與實踐相結合”，如果在一個領域有所超越，必須要在理論水平上達到一定的高度
?
探求
ETL
本質之一
ETL
的過程就是數據流動的過程，從不同異構數據源流向統一的目標數據。其間，數據的抽取、清洗、轉換和裝載形成串行或并行的過程。
ETL
的核心還是在于
T
這個過程，也就是轉換，而抽取和裝載一般可以作為轉換的輸入和輸出，或者，它們作為一個單獨的部件，其復雜度沒有轉換部件高。和
OLTP
系統中不同，那里充滿這單條記錄的
insert
、
update
和
select
等操作，
ETL
過程一般都是批量操作，例如它的裝載多采用批量裝載工具，一般都是
DBMS
系統自身附帶的工具，例如
Oracle SQLLoader
和
DB2
的
autoloader
等。
?
ETL
本身有一些特點，在一些工具中都有體現，下面以
datastage
和
powermart
舉例來說。
?
1
、靜態的
ETL
單元和動態的
ETL
單元實例；一次轉換指明了某種格式的數據如何格式化成另一種格式的數據，對于數據源的物理形式在設計時可以不用指定，它可以在運行時，當這個
ETL
單元創建一個實例時才指定。對于靜態和動態的
ETL
單元，
Datastage
沒有嚴格區分，它的一個
Job
就是實現這個功能，在早期版本，一個
Job
同時不能運行兩次，所以一個
Job
相當于一個實例，在后期版本，它支持
multiple instances
，而且還不是默認選項。
Powermart
中將這兩個概念加以區分，靜態的叫做
Mapping
，動態運行時叫做
Session
。
?
2
、
ETL
元數據；元數據是描述數據的數據，他的含義非常廣泛，這里僅指
ETL
的元數據。主要包括每次轉換前后的數據結構和轉換的規則。
ETL
元數據還包括形式參數的管理，形式參數的
ETL
單元定義的參數，相對還有實參，它是運行時指定的參數，實參不在元數據管理范圍之內。
?
3
、數據流程的控制；要有可視化的流程編輯工具，提供流程定義和流程監控功能。流程調度的最小單位是
ETL
單元實例，
ETL
單元是不能在細分的
ETL
過程，當然這由開發者來控制，例如可以將抽取、轉換放在一個
ETL
單元中，那樣這個抽取和轉換只能同時運行，而如果將他們分作兩個單元，可以分別運行，這有利于錯誤恢復操作。當然，
ETL
單元究竟應該細分到什么程度應該依據具體應用來看，目前還沒有找到很好的細分策略。比如，我們可以規定將裝載一個表的功能作為一個
ETL
單元，但是不可否認，這樣的
ETL
單元之間會有很多共同的操作，例如兩個單元共用一個
Hash
表，要將這個
Hash
表裝入內存兩次。
?
4
、轉換規則的定義方法；提供函數集提供常用規則方法，提供規則定義語言描述規則。
?
5
、對數據的快速索引；一般都是利用
Hash
技術，將參照關系表提前裝入內存，在轉換時查找這個
hash
表。
Datastage
中有
Hash
文件技術，
Powermart
也有類似的
Lookup
功能。
?
探求
ETL
本質之二（分類）
昨在
IT-Director
上閱讀一篇報告，關于
ETL
產品分類的。一般來說，我們眼中的
ETL
工具都是價格昂貴，能夠處理海量數據的家伙，但是這是其中的一種。它可以分成
4
種，針對不同的需求，主要是從轉換規則的復雜度和數據量大小來看。它們包括
1
、交互式運行環境，你可以指定數據源、目標數據，指定規則，立馬
ETL
。這種交互式的操作無疑非常方便，但是只能適合小數據量和復雜度不高的
ETL
過程，因為一旦規則復雜了，可能需要語言級的描述，不能簡簡單單拖拖拽拽就可以的。還有數據量的問題，這種交互式必然建立在解釋型語言基礎上，另外他的靈活性必然要犧牲一定的性能為代價。所以如果要處理海量數據的話，每次讀取一條記錄，每次對規則進行解釋執行，每次在寫入一條記錄，這對性能影響是非常大的。
2
、專門編碼型的，它提供了一個基于某種語言的程序框架，你可以不必將編程精力放在一些周邊的功能上，例如讀文件功能、寫數據庫的功能，而將精力主要放在規則的實現上面。這種近似手工代碼的性能肯定是沒話說，除非你的編程技巧不過關（這也是不可忽視的因素之一）。對于處理大數據量，處理復雜轉換邏輯，這種方式的
ETL
實現是非常直觀的。
3
、代碼生成器型的，它就像是一個
ETL
代碼生成器，提供簡單的圖形化界面操作，讓你拖拖拽拽將轉換規則都設定好，其實他的后臺都是生成基于某種語言的程序，要運行這個
ETL
過程，必須要編譯才行。
Datastage
就是類似這樣的產品，設計好的
job
必須要編譯，這避免了每次轉換的解釋執行，但是不知道它生成的中間語言是什么。以前我設計的
ETL
工具大挪移其實也是歸屬于這一類，它提供了界面讓用戶編寫規則，最后生成
C++
語言，編譯后即可運行。這類工具的特點就是要在界面上下狠功夫，必須讓用戶輕松定義一個
ETL
過程，提供豐富的插件來完成讀、寫和轉換函數。大挪移在這方面就太弱了，規則必須手寫，而且要寫成標準
c++
語法，這未免還是有點難為最終用戶了，還不如做成一個專業編碼型的產品呢。另外一點，這類工具必須提供面向專家應用的功能，因為它不可能考慮到所有的轉換規則和所有的讀寫，一方面提供插件接口來讓第三方編寫特定的插件，另一方面還有提供特定語言來實現高級功能。例如
Datastage
提供一種類
Basic
的語言，不過他的
Job
的腳本化實現好像就做的不太好，只能手工繪制
job
，而不能編程實現
Job
。
4
、最后還有一種類型叫做數據集線器，顧名思義，他就是像
Hub
一樣地工作。將這種類型分出來和上面幾種分類在標準上有所差異，上面三種更多指
ETL
實現的方法，此類主要從數據處理角度。目前有一些產品屬于
EAI
（
Enterprise Application Integration
），它的數據集成主要是一種準實時性。所以這類產品就像
Hub
一樣，不斷接收各種異構數據源來的數據，經過處理，在實施發送到不同的目標數據中去。
雖然，這些類看似各又千秋，特別在
BI
項目中，面對海量數據的
ETL
時，中間兩種的選擇就開始了，在選擇過程中，必須要考慮到開發效率、維護方面、性能、學習曲線、人員技能等各方面因素，當然還有最重要也是最現實的因素就是客戶的意象。
?
探求
ETL
本質之三（轉換）
ETL
探求之一中提到，
ETL
過程最復雜的部分就是
T
，這個轉換過程，
T
過程究竟有哪些類型呢？
一、宏觀輸入輸出
從對數據源的整個宏觀處理分，看看一個
ETL
過程的輸入輸出，可以分成下面幾類：
1
、大小交，這種處理在數據清洗過程是常見了，例如從數據源到
ODS
階段，如果數據倉庫采用維度建模，而且維度基本采用代理鍵的話，必然存在代碼到此鍵值的轉換。如果用
SQL
實現，必然需要將一個大表和一堆小表都
Join
起來，當然如果使用
ETL
工具的話，一般都是先將小表讀入內存中再處理。這種情況，輸出數據的粒度和大表一樣。
2
、大大交，大表和大表之間關聯也是一個重要的課題，當然其中要有一個主表，在邏輯上，應當是主表
Left Join
輔表。大表之間的關聯存在最大的問題就是性能和穩定性，對于海量數據來說，必須有優化的方法來處理他們的關聯，另外，對于大數據的處理無疑會占用太多的系統資源，出錯的幾率非常大，如何做到有效錯誤恢復也是個問題。對于這種情況，我們建議還是盡量將大表拆分成適度的稍小一點的表，形成大小交的類型。這類情況的輸出數據粒度和主表一樣。
3
、站著進來，躺著出去。事務系統中為了提高系統靈活性和擴展性，很多信息放在代碼表中維護，所以它的“事實表”就是一種窄表，而在數據倉庫中，通常要進行寬化，從行變成列，所以稱這種處理情況叫做“站著進來，躺著出去”。大家對
Decode
肯定不陌生，這是進行寬表化常見的手段之一。窄表變寬表的過程主要體現在對窄表中那個代碼字段的操作。這種情況，窄表是輸入，寬表是輸出，寬表的粒度必定要比窄表粗一些，就粗在那個代碼字段上。
4
、聚集。數據倉庫中重要的任務就是沉淀數據，聚集是必不可少的操作，它是粗化數據粒度的過程。聚集本身其實很簡單，就是類似
SQL
中
Group by
的操作，選取特定字段（維度），對度量字段再使用某種聚集函數。但是對于大數據量情況下，聚集算法的優化仍是探究的一個課題。例如是直接使用
SQL
的
Group by
，還是先排序，在處理。
二、微觀規則
從數據的轉換的微觀細節分，可以分成下面的幾個基本類型，當然還有一些復雜的組合情況，例如先運算，在參照轉換的規則，這種基于基本類型組合的情況就不在此列了。
ETL
的規則是依賴目標數據的，目標數據有多少字段，就有多少條規則。
1
、直接映射，原來是什么就是什么，原封不動照搬過來，對這樣的規則，如果數據源字段和目標字段長度或精度不符，需要特別注意看是否真的可以直接映射還是需要做一些簡單運算。
2
、字段運算，數據源的一個或多個字段進行數學運算得到的目標字段，這種規則一般對數值型字段而言。
3
、參照轉換，在轉換中通常要用數據源的一個或多個字段作為
Key
，去一個關聯數組中去搜索特定值，而且應該只能得到唯一值。這個關聯數組使用
Hash
算法實現是比較合適也是最常見的，在整個
ETL
開始之前，它就裝入內存，對性能提高的幫助非常大。
4
、字符串處理，從數據源某個字符串字段中經常可以獲取特定信息，例如身份證號。而且，經常會有數值型值以字符串形式體現。對字符串的操作通常有類型轉換、字符串截取等。但是由于字符類型字段的隨意性也造成了臟數據的隱患，所以在處理這種規則的時候，一定要加上異常處理。
5
、空值判斷，對于空值的處理是數據倉庫中一個常見問題，是將它作為臟數據還是作為特定一種維成員？這恐怕還要看應用的情況，也是需要進一步探求的。但是無論怎樣，對于可能有
NULL
值的字段，不要采用“直接映射”的規則類型，必須對空值進行判斷，目前我們的建議是將它轉換成特定的值。
6
、日期轉換，在數據倉庫中日期值一般都會有特定的，不同于日期類型值的表示方法，例如使用
8
位整型
20040801
表示日期。而在數據源中，這種字段基本都是日期類型的，所以對于這樣的規則，需要一些共通函數來處理將日期轉換為
8
位日期值、
6
位月份值等。
7
、日期運算，基于日期，我們通常會計算日差、月差、時長等。一般數據庫提供的日期運算函數都是基于日期型的，而在數據倉庫中采用特定類型來表示日期的話，必須有一套自己的日期運算函數集。
8
、聚集運算，對于事實表中的度量字段，他們通常是通過數據源一個或多個字段運用聚集函數得來的，這些聚集函數為
SQL
標準中，包括
sum,count,avg,min,max
。
9
、既定取值，這種規則和以上各種類型規則的差別就在于它不依賴于數據源字段，對目標字段取一個固定的或是依賴系統的值。
?
探求
ETL
本質之四（數據質量）
“不要絕對的數據準確，但要知道為什么不準確。”
這是我們在構建
BI
系統是對數據準確性的要求。確實，對絕對的數據準確誰也沒有把握，不僅是系統集成商，包括客戶也是無法確定。準確的東西需要一個標準，但首先要保證這個標準是準確的，至少現在還沒有這樣一個標準。客戶會提出一個相對標準，例如將你的
OLAP
數據結果和報表結果對比。雖然這是一種不太公平的比較，你也只好認了吧。
?
首先在數據源那里，已經很難保證數據質量了，這一點也是事實。在這一層有哪些可能原因導致數據質量問題？可以分為下面幾類：
1
、數據格式錯誤，例如缺失數據、數據值超出范圍或是數據格式非法等。要知道對于同樣處理大數據量的數據源系統，他們通常會舍棄一些數據庫自身的檢查機制，例如字段約束等。他們盡可能將數據檢查在入庫前保證，但是這一點是很難確保的。這類情況諸如身份證號碼、手機號、非日期類型的日期字段等。
2
、數據一致性，同樣，數據源系統為了性能的考慮，會在一定程度上舍棄外鍵約束，這通常會導致數據不一致。例如在帳務表中會出現一個用戶表中沒有的用戶
ID
，在例如有些代碼在代碼表中找不到等。
3
、業務邏輯的合理性，這一點很難說對與錯。通常，數據源系統的設計并不是非常嚴謹，例如讓用戶開戶日期晚于用戶銷戶日期都是有可能發生的，一個用戶表中存在多個用戶
ID
也是有可能發生的。對這種情況，有什么辦法嗎？
?
構建一個
BI
系統，要做到完全理解數據源系統根本就是不可能的。特別是數據源系統在交付后，有更多維護人員的即興發揮，那更是要花大量的時間去尋找原因。以前曾經爭辯過設計人員對規則描述的問題，有人提出要在
ETL
開始之前務必將所有的規則弄得一清二楚。我并不同意這樣的意見，倒是認為在
ETL
過程要有處理這些質量有問題數據的保證。一定要正面這些臟數據，是丟棄還是處理，無法逃避。如果沒有質量保證，那么在這個過程中，錯誤會逐漸放大，拋開數據源質量問題，我們再來看看
ETL
過程中哪些因素對數據準確性產生重大影響。
1
、規則描述錯誤。上面提到對設計人員對數據源系統理解的不充分，導致規則理解錯誤，這是一方面。另一方面，是規則的描述，如果無二義性地描述規則也是要探求的一個課題。規則是依附于目標字段的，在探求之三中，提到規則的分類。但是規則總不能總是用文字描述，必須有嚴格的數學表達方式。我甚至想過，如果設計人員能夠使用某種規則語言來描述，那么我們的
ETL
單元就可以自動生成、同步，省去很多手工操作了。
2
、
ETL
開發錯誤。即時規則很明確，
ETL
開發的過程中也會發生一些錯誤，例如邏輯錯誤、書寫錯誤等。例如對于一個分段值，開區間閉區間是需要指定的，但是常常開發人員沒注意，一個大于等于號寫成大于號就導致數據錯誤。
3
、人為處理錯誤。在整體
ETL
流程沒有完成之前，為了圖省事，通常會手工運行
ETL
過程，這其中一個重大的問題就是你不會按照正常流程去運行了，而是按照自己的理解去運行，發生的錯誤可能是誤刪了數據、重復裝載數據等。
?
探求
ETL
本質之五（質量保證）
上回提到
ETL
數據質量問題，這是無法根治的，只能采取特定的手段去盡量避免，而且必須要定義出度量方法來衡量數據的質量是好還是壞。對于數據源的質量，客戶對此應該更加關心，如果在這個源頭不能保證比較干凈的數據，那么后面的分析功能的可信度也都成問題。數據源系統也在不斷進化過程中，客戶的操作也在逐漸規范中，
BI
系統也同樣如此。本文探討一下對數據源質量和
ETL
處理質量的應對方法。
如何應對數據源的質量問題？記得在
onteldatastage
列表中也討論過一個話題－
"-1
的處理
"
，在數據倉庫模型維表中，通常有一條
-1
記錄，表示“未知”，這個未知含義可廣了，任何可能出錯的數據，
NULL
數據甚至是規則沒有涵蓋到的數據，都轉成
-1
。這是一種處理臟數據的方法，但這也是一種掩蓋事實的方法。就好像寫一個函數
FileOpen(filename)
，返回一個錯誤碼，當然，你可以只返回一種錯誤碼，如
-1
，但這是一種不好的設計，對于調用者來說，他需要依據這個錯誤碼進行某些判斷，例如是文件不存在，還是讀取權限不夠，都有相應的處理邏輯。數據倉庫中也是一樣，所以，建議將不同的數據質量類型處理結果分別轉換成不同的值，譬如，在轉換后，
-1
表示參照不上，
-2
表示
NULL
數據等。不過這僅僅對付了上回提到的第一類錯誤，數據格式錯誤。對于數據一致性和業務邏輯合理性問題，這仍有待探求。但這里有一個原則就是“必須在數據倉庫中反應數據源的質量”。
對于
ETL
過程中產生的質量問題，必須有保障手段。從以往的經驗看，沒有保障手段給實施人員帶來麻煩重重。實施人員對于反復裝載數據一定不會陌生，甚至是最后數據留到最后的
Cube
，才發現了第一步
ETL
其實已經錯了。這個保障手段就是數據驗證機制，當然，它的目的是能夠在
ETL
過程中監控數據質量，產生報警。這個模塊要將實施人員當作是最終用戶，可以說他們是數據驗證機制的直接收益者。
首先，必須有一個對質量的度量方法，什么是高質什么是低質，不能靠感官感覺，但這卻是在沒有度量方法條件下通常的做法。那經營分析系統來說，聯通總部曾提出測試規范，這其實就是一種度量方法，例如指標的誤差范圍不能高于
5%
等，對系統本身來說其實必須要有這樣的度量方法，先不要說這個度量方法是否科學。對于
ETL
數據處理質量，他的度量方法應該比聯通總部測試規范定義的方法更要嚴格，因為他更多將
BI
系統看作一個黑盒子，從數據源到展現的數據誤差允許一定的誤差。而
ETL
數據處理質量度量是一種白盒的度量，要注重每一步過程。因此理論上，要求輸入輸出的指標應該完全一致。但是我們必須正面完全一致只是理想，對于有誤差的數據，必須找到原因。
在質量度量方法的前提下，就可以建立一個數據驗證框架。此框架依據總量、分量數據稽核方法，該方法在高的《數據倉庫中的數據稽核技術》一文中已經指出。作為補充，下面提出幾點功能上的建議：
|