1.ETL過程概述

ETL系統(tǒng)設(shè)計過程至關(guān)重要妙啃。收集所有的相關(guān)信息,包括處理從操作型數(shù)據(jù)源中獲取·的代價測試某些關(guān)鍵的替代品区端。將轉(zhuǎn)換過程駐留到源系統(tǒng)敛劝、目標系統(tǒng)或其本身的平臺上是否有意義?在每種情況下,可以使用哪些工具?這些工具的有效性如何?

2.ETL開發(fā)規(guī)范

設(shè)計高層規(guī)劃

高層數(shù)據(jù)分階段

選擇ETL工具

1. 使用圖形工具可自動構(gòu)建文檔霎匈。硬代碼系統(tǒng)通常是造成臨時表澎迎、SQL腳本稼虎、存儲過程衅檀、操作系統(tǒng)腳本混亂的主要原因。
2. ETL過程的所有步驟的元數(shù)據(jù)基礎(chǔ)霎俩。
3. 多人開發(fā)環(huán)境需要使用的版本控制,版本控制還可實現(xiàn)備份和恢復(fù)一致性的版本哀军。高級轉(zhuǎn)換邏輯,例如模糊匹配算法、對名稱和地址集成訪問的重復(fù)數(shù)據(jù)刪除(deduplication)實用程序,以及數(shù)據(jù)挖掘算法等打却。
4. 以最基本的經(jīng)驗改進系統(tǒng)性能杉适。真正能夠成為使用關(guān)系數(shù)據(jù)庫處理大數(shù)據(jù)且能具備良好經(jīng)驗的專家型SQL開發(fā)人員相對較少。
5. 復(fù)雜的處理能力,包括自動實現(xiàn)任務(wù)并行化,以及當處理源不可用時具有自動容錯能力等柳击。
6. 將圖形化數(shù)據(jù)轉(zhuǎn)換模塊一步轉(zhuǎn)化為其物理等價物猿推。

開發(fā)默認策略

從主要的源系統(tǒng)獲取數(shù)據(jù)
歸檔獲取的數(shù)據(jù)或分層的數(shù)據(jù)
監(jiān)管維護和特定的事實數(shù)據(jù)質(zhì)量
維度屬性變化的管理
確保數(shù)據(jù)倉庫和ETL系統(tǒng)滿足系統(tǒng)可用性需求
設(shè)計數(shù)據(jù)審計子系統(tǒng)
組織ETL過渡期

按照目標表鉆取數(shù)據(jù)

在開發(fā)完所有的公共ETL任務(wù)后,應(yīng)當開始深入研究詳細的轉(zhuǎn)換工作,以填充數(shù)據(jù)倉庫中的目標表。在完成源到目標的映射后,您將完成更多的數(shù)據(jù)概要描述工作,以完整理解每個表和列所需要的數(shù)據(jù)轉(zhuǎn)換腻暮。

1. 確保層次的清楚性
   最好的源系統(tǒng)規(guī)范化層次,將不同級別放入多個表中,兩個級別間以外鍵關(guān)聯(lián)彤守。在此情況下,可以相信層次級別是清楚的。如果源系統(tǒng)沒有被規(guī)范化,特別是如果包含層次的源是業(yè)務(wù)用戶的臺式電腦的Excel電子報表時,則您必須要么對其進行清洗,要么承認沒有層次存在哭靖。

2. 開發(fā)詳盡的表達意圖

   
   
   范例

開發(fā)ETL規(guī)范化文檔

相關(guān)決策

1. 從每個主要的源系統(tǒng)獲取的默認策略具垫。
2. 歸檔策略。
3. 數(shù)據(jù)質(zhì)量跟蹤和元數(shù)據(jù)试幽。
4. 管理維度屬性變化的默認策略筝蚕。
5. 系統(tǒng)可用性需求與策略。
6. 數(shù)據(jù)審計子系統(tǒng)的設(shè)計铺坞。
7. 過渡區(qū)的定位起宽。

規(guī)范化文檔內(nèi)容

1. 表設(shè)計(列名、數(shù)據(jù)類型济榨、鍵和約束)坯沪。
2. 歷史數(shù)據(jù)加載參數(shù)(月數(shù))和容量(行計數(shù))。
3. 增量數(shù)據(jù)容量,對每個加載周期涉及的新的和更新的行擒滑。
4. 處理事實表和維度表的遲到數(shù)據(jù)腐晾。
5. 加載頻率。
6. 處理每個維度屬性的緩慢變化維度(SCD)變化丐一。
7. 表分區(qū),例如按月藻糖。
8. 數(shù)據(jù)來源概述,包括討論所有不常見的源特征,例如不常見的簡短存取窗口。
9. 詳細的源到目標的映射库车【奁猓·
10. 源數(shù)據(jù)概要,包括每個數(shù)字列的最小值和最大值,每個列中出現(xiàn)的不同值的計數(shù),包括空值的發(fā)生率。
11. 源數(shù)據(jù)獲取策略(例如,源系統(tǒng)的API、直接從數(shù)據(jù)庫查詢或轉(zhuǎn)儲到平面文件)洋满。
    依賴,包括某個表在處理前必須加載哪些其他表晶乔。
12. 文檔化轉(zhuǎn)換邏輯。該部分最好用偽代碼或圖表來撰寫,而不是試圖手工編制完整的句子芦岂。
13. 避免產(chǎn)生錯誤的前提條件瘪弓。例如,在繼續(xù)開展工作前, ETL系統(tǒng)必須檢查文件或數(shù)據(jù)庫空間。
14. 清洗步驟,例如刪除工作文件禽最。
15. 估計該部分ETL系統(tǒng)實現(xiàn)是容易腺怯、中等程度或難于實現(xiàn)。

3.開發(fā)一次性的歷史加載器

用歷史數(shù)據(jù)填充維度表

1. 填充類型一維度表
   最簡單的表填充類型是那些所有屬性都包含類型1重寫的維度表川无。在只包含類型1的維度中,直接從源系統(tǒng)獲取每個維度屬性的當前值呛占。

2. 維度轉(zhuǎn)化
   簡單數(shù)據(jù)轉(zhuǎn)換
   不同源的數(shù)據(jù)合并
   產(chǎn)品碼解碼
   驗證多對多,一對一的關(guān)系
   維度代理鍵的分配

3. 維度表加載

4. 加載類型2維度表歷史

5. 對歷史和其他維度表的補充

   
   
   產(chǎn)品維度中的雪花型層次關(guān)系

完成事實表的加載

1. 歷史事實表獲取
2. 審計系統(tǒng)
   在ETL系統(tǒng)的規(guī)劃階段,您會確定各種針對數(shù)據(jù)質(zhì)量的度量。這些度量通常是可計算的,例如計數(shù)和匯總,可以比較數(shù)據(jù)倉庫和源系統(tǒng)的數(shù)據(jù)以交叉檢查數(shù)據(jù)的完整性懦趋。這些數(shù)值可聯(lián)系操作型報表及數(shù)據(jù)倉庫加載過程的結(jié)果晾虑。能夠回朔到操作型系統(tǒng)是非常重要的,因為它是建立可信任數(shù)據(jù)倉庫的基礎(chǔ)。
   在某些場景中,想要從數(shù)據(jù)倉庫建立與源系統(tǒng)的反向聯(lián)系是不大容易實現(xiàn)的仅叫。多數(shù)情況下,數(shù)據(jù)倉庫獲取包括未被應(yīng)用到源系統(tǒng)的業(yè)務(wù)規(guī)則帜篇。更令人苦惱的是源系統(tǒng)中的錯誤。另外,時間上的差異也使得交叉檢查變得異常困難诫咱。如果無法實現(xiàn)數(shù)據(jù)的反向聯(lián)系,則需要解釋其中的差異笙隙。
3. 事實表轉(zhuǎn)換
   空值的處理
   所有數(shù)據(jù)庫引擎都支持空值。然而,在大多數(shù)源系統(tǒng)中,空值都被表示為合法事實的一個特殊的值坎缭。也許用特殊值-1表示空值竟痰。對大多數(shù)事實表度量來說,其場景中的"-1"應(yīng)該被真正的NULL取代√秃簦空值用于數(shù)字度量在多數(shù)事實表中是合理的坏快、常見的。在跨事,實行計算匯總和平均時,空值能夠執(zhí)行“正確的事情”憎夷。僅在維度表中您應(yīng)當盡力將空值替換為特殊的專門制定的默認值莽鸿。最后,不應(yīng)當允許以事實表列中的空值引用維度表鍵。這些外鍵列應(yīng)當始終被定義為非空(NOT NULL)拾给。
   改進事實表的內(nèi)容
   正如我們所強調(diào)的那樣,最終事實表行的所有事實必須以同一粒度表示富拗。這意味著在以天為粒度的事實表中不會存在表示年匯總情況的事實,也不會存在對某些地理情況的匯總比事實表粒度大的情況。如果獲取包括不同粒度的交錯的事實,則必須要消除這些聚集,或者將它們移入適當?shù)木奂碇小?br> 維度代理鍵查詢流水線(難點)
   事實表中的所有外鍵不應(yīng)存在空值,所有事實行對任何維度不會違反參照完整性鸣戴。
   分配審計維度鍵
   事實表的每個行通常都包含一個審計鍵。審計鍵指向描述加載特征的審計維度,審計維度包括相對靜態(tài)的環(huán)境以及數(shù)據(jù)質(zhì)量度量粘拾。審計維度可以很小窄锅。最初設(shè)計的審計維度僅包含兩個環(huán)境變量(主ETL版本號和利益分配的邏輯號)和一個質(zhì)量標志,該標志的值是Qualit Checks Passed(質(zhì)量檢查通過)和Quality Problems Encountered(發(fā)生質(zhì)量問題)。隨著時間的推移,這些變量和診斷指標可能會變得非常復(fù)雜和詳細。增加到事實表的維度審計鍵要么在代理鍵流水線之前立即增加,要么在之后立即增加入偷。
4. 事實表加載

4.開發(fā)增量式ETL過程(難點)

增量ETL過程的最大挑戰(zhàn)之一是區(qū)分新的追驴、發(fā)生變化的以及被刪除的行。在插入疏之、刪除殿雪、更新流處理之后, ETL系統(tǒng)可以按照幾乎相同的歷史數(shù)據(jù)加載業(yè)務(wù)規(guī)則執(zhí)行轉(zhuǎn)換工作。

維度表增量處理過程

1. 維度表獲取
   如果可能,構(gòu)建只獲取那些發(fā)生變化的行锋爪。如果源系統(tǒng)維護一個變化類型的指示器的話,這樣做特別方便且有價值丙曙。
2. 識別新的和變化的維度行
   如果維度包含類型2屬性,將行中有效日期列設(shè)置為維度成員出現(xiàn)在系統(tǒng)中的日期。如果是在晚間處理該工作,那么這個時間通常是昨天其骄。將行結(jié)束日期列設(shè)置為當前行的默認值亏镰。這個值通常是系統(tǒng)能夠支持的,指向遙遠未來的最大的日期。應(yīng)當避免在結(jié)束日期列使用空值,因為如果試圖將某一特定值與空值進行比較,則關(guān)系數(shù)據(jù)庫可能會產(chǎn)生錯誤或返回未知的特殊值拯爽。
   如果維度比較大,包含100萬行,采用簡單的列間比較的技術(shù)可能太慢,特別是如果維度表中的列還比較多的情況下索抓。比較好的替換策略是使用哈希或校驗功能加快比較處理的速度毯炮。可以在維度表中增加兩個新的管理列:哈希類型1和哈希類型2逼肯。應(yīng)當在哈希類型1列放置連接類型1屬性的哈希值,同樣道理應(yīng)用于哈希類型2,哈希算法將非常大的字符串轉(zhuǎn)換為相對小得多的且?guī)缀蹙哂形ㄒ恍缘闹怠?/strong>哈希值在維度表中計算及存儲。然后,用完全相同的方法對輸入行集合計算哈希值,并將它們與存儲的值比較桃煎。與單一的篮幢、相對較短的字符串列比較比成對比較大量不同列的方法更有效。另外,關(guān)系數(shù)據(jù)庫引擎可能包含類似EXCEPT語法能夠確保高性能地執(zhí)行發(fā)現(xiàn)改變行的查詢备禀。

3. 處理維度屬性的變化

   
   
   處理維度更新的邏輯流程

事實表增量處理過程

1. 事實表獲取和數(shù)據(jù)質(zhì)量檢查點
   一旦從源系統(tǒng)獲得了發(fā)生變化的和被更新的事實行,就必須在過渡區(qū)中建立一個未轉(zhuǎn)換數(shù)據(jù)的拷貝洲拇。同時,對有關(guān)原始獲取數(shù)據(jù)的數(shù)據(jù)質(zhì)量度量開展計算工作。數(shù)據(jù)過渡包含三種意圖:
   為實現(xiàn)審計歸檔曲尸。
   為后續(xù)的數(shù)據(jù)質(zhì)量驗證提供開始點赋续。
   為重啟過程提供開始點。
2. 事實表轉(zhuǎn)換和代理鍵流水線

處理違反完整性的方法

1. 終止加載另患。這不是一個常用的方法,但在大多數(shù)ETL工具中,該方法常常是默認的配置方法纽乱。
2. 拋棄錯誤行。某些情況下,丟失維度值是一種信號,表明數(shù)據(jù)與底層數(shù)據(jù)倉庫的業(yè)務(wù)需求不相關(guān)昆箕。
3. 將錯誤行寫入文件或表中以便后續(xù)分析解寝。設(shè)計一種機制將需要改正的行移入掛起文件中。對財務(wù)系統(tǒng)來說,該方法不是一個好的選擇,在這樣的系統(tǒng)中,所有的行都需要加載搓谆。
4. 通過建立虛擬維度行并返回其代理鍵到流水線中對錯誤行進行修改嘹害。在增量代理鍵流水線中最有吸引力的處理違反參照完整性錯誤的方法是在執(zhí)行過程中為未知的自然鍵建立虛擬維度行。自然鍵是有關(guān)維度成員的僅有的信息塊,所有其他屬性必須被設(shè)置為默認值纤泵。當有關(guān)維度成員的詳細信息可用時,該虛擬維度行將以類型1進行更新骆姐。
5. 通過映射到每個維度中單一的未知成員修改錯誤行。該方法不是我們推薦的方法。問題是,對所有事實表獲取中得到的未知自然鍵值,所有錯誤行被映射到同一個維度成員上玻褪。

延遲到來的事實和代理鍵流水線
如果所有維度都以類型1重寫模式被管理的話,延遲到達事實不會存在什么特別的挑戰(zhàn)肉渴。但是多數(shù)系統(tǒng)都同時包含類型1和類型2屬性。延遲到達的事實必須與事實發(fā)生時有效的維度成員版本關(guān)聯(lián)带射。要實現(xiàn)這一工作需要對維度表中的行開始和結(jié)束有效日期進行查詢同规。

增量事實表加載
考慮設(shè)計一個將日分區(qū)合并為按周或按月進行分區(qū)的處理方法
加載快照事實表
最大的事實表通常是事務(wù)型的。事務(wù)事實表通常僅通過插入進行加載窟社。周期快照事實表通常在月末被加載券勺。當前月的數(shù)據(jù)有時會在當前月至今的每一天被更新。在此情況下, .按月的事實表的分區(qū)使重新加載當前月的工作具有極高的性能桥爽。
加載加速周期

聚集表和OLAP加載(緩慢變化維是個難題)

如果聚集表包括對日期維度的聚集結(jié)果,也許以月為粒度,聚集維護過程將更加復(fù)雜朱灿。當前月數(shù)據(jù)必須被更新,或者刪除及重建,以反映當前天的數(shù)據(jù)。
如果聚集表是按照作為類型1重寫的維度屬性定義的,類似問題將會發(fā)生钠四。維度屬性的所有類型1變化將會影響所有的事實表聚集以及按照該屬性定義的OLAP多維數(shù)據(jù)庫盗扒。ETL過程必須將原有聚集層次的事實刪除并以新值替代。保持聚集與底層的事實數(shù)據(jù)同步是聚集管理系統(tǒng)中極其重要的工作缀去。
如果查詢直接面對底層細節(jié)事實或來自預(yù)先計算的聚集,則不要指望建立一個返回不同結(jié)果集的系統(tǒng)侣灶。

ETL系統(tǒng)操作與自動化

5.實時的影響

實時分類

低延遲發(fā)布的數(shù)據(jù)質(zhì)量權(quán)衡

實時結(jié)構(gòu)權(quán)衡

替換批處理文件
限制數(shù)據(jù)質(zhì)量檢查
連接事實和維度
消除數(shù)據(jù)過渡區(qū)

展現(xiàn)服務(wù)器上的實時分區(qū)