ETL的主要步驟

1、ETL的主要步驟ETL(Extract Transform Loading,數(shù)據(jù)抽取轉(zhuǎn)化裝載規(guī)則) 是負責(zé)完成是數(shù)據(jù)源數(shù)據(jù)向數(shù)據(jù)倉庫數(shù)據(jù)的轉(zhuǎn)化的過程。是實施數(shù)據(jù)倉庫中最重要的步驟?？梢孕蜗蟮恼f，ETL 的角色相當(dāng)于磚石修葺成房子的過程。在數(shù)據(jù)倉庫系統(tǒng)設(shè)計中最難的部分是用戶需求分析和模型設(shè)計，那么工作量最大的就是ETL 規(guī)則的設(shè)計和實施了，它要占到整個數(shù)據(jù)倉庫設(shè)計工作量的60%-70%，甚至更多。下面是本人對 ETL 的幾個重要步驟理解，和大家分享！一、ODS區(qū)的數(shù)據(jù)采集：最主要作用為了盡量減少對業(yè)務(wù)系統(tǒng)的影響。表結(jié)構(gòu)可以不必和DW 一致。根據(jù)具體業(yè)務(wù)需求和數(shù)據(jù)量情況， 將數(shù)據(jù)源的數(shù)據(jù)放入 OD

2、S 有各種不同的方法， 比如 Oracle 的數(shù)據(jù)庫鏈路，表復(fù)制，SQL*LOADER，Teradata的 Fastload，Sysbase的 BCP 等等。需要解決的問題包括：a、數(shù)據(jù)的時間差異性問題在抽取舊有數(shù)據(jù)時， 要將不同時期的數(shù)據(jù)定義統(tǒng)一， 較早的數(shù)據(jù)不夠完整或不符合新系統(tǒng)的數(shù)據(jù)規(guī)范，一般可以根據(jù)規(guī)則，在存入中轉(zhuǎn)區(qū)的過程中予以更新或補充。b、數(shù)據(jù)的平臺多樣性問題在抽取舊有數(shù)據(jù)時，大部分數(shù)據(jù)都可采用表復(fù)制方式直接導(dǎo)入數(shù)據(jù)中轉(zhuǎn)區(qū)集中，再做處理，但有部分數(shù)據(jù)可能需要轉(zhuǎn)換成文本文件或使用第三方工具如Informatica等裝載入數(shù)據(jù)中轉(zhuǎn)區(qū)。這部分 數(shù)據(jù)主要是與數(shù)據(jù)中轉(zhuǎn)區(qū)數(shù)據(jù)庫平臺不一致的數(shù)據(jù)

3、庫數(shù)據(jù)，或非存儲于數(shù)據(jù)庫內(nèi)的文本、excel等數(shù)據(jù)。c 、數(shù)據(jù)的不穩(wěn)定性問題對于重要信息的完整歷史變更記錄，在抽取時可以根據(jù)各時期的歷史信息，在抽取需要信息等基本屬性的舊有數(shù)據(jù)時，要與相應(yīng)時段的信息關(guān)聯(lián)得到真實的歷史屬性。d 、數(shù)據(jù)的依賴性問題舊有業(yè)務(wù)系統(tǒng)的數(shù)據(jù)關(guān)聯(lián)一般已有約束保證，代碼表和參照表等數(shù)據(jù)也比較準確，但仍有少量數(shù)據(jù)不完整，對這部分數(shù)據(jù)，需根據(jù)地稅的需求采取清洗策略，保證數(shù)據(jù)倉庫各事實表和維表之間的關(guān)聯(lián)完整有效。數(shù)據(jù)倉庫各事實表和維表的初始裝載順序有先后關(guān)系，要有一個集中的數(shù)據(jù)裝載任務(wù)順序方案， 確保初始數(shù)據(jù)裝載的準確。這可以通過操作系統(tǒng)或第三方工具的任務(wù)調(diào)度機制來保證。二、數(shù)據(jù)轉(zhuǎn)

4、換、清洗：將 ODS 中的數(shù)據(jù)，按照數(shù)據(jù)倉庫中數(shù)據(jù)存儲結(jié)構(gòu)進行合理的轉(zhuǎn)換，轉(zhuǎn)換步驟一般還要包含數(shù)據(jù)清洗的過程。數(shù)據(jù)清洗主要是針對源數(shù)據(jù)庫中出現(xiàn)二義性、重復(fù)、不完整、違反業(yè)務(wù)或邏輯規(guī)則等問 題的數(shù)據(jù)數(shù)據(jù)進行統(tǒng)一的處理，一般包括如：NULL 值處理，日期格式轉(zhuǎn)換，數(shù)據(jù)類型轉(zhuǎn)換等等。在清洗之前需要進行數(shù)據(jù)質(zhì)量分析，以找出存在問題的數(shù)據(jù)，否則數(shù)據(jù)清洗將無從談起。數(shù)據(jù)裝載是通 過裝載工具或自行編寫的SQL 程序?qū)⒊槿?、轉(zhuǎn)換后的結(jié)果數(shù)據(jù)加載到目標(biāo)數(shù)據(jù)庫中。數(shù)據(jù)質(zhì)量問題具體表現(xiàn)在以下幾個方面：a、正確性（ Accuracy）：數(shù)據(jù)是否正確的表示了現(xiàn)實或可證實的來源?b、完整性（ Integrity）：數(shù)據(jù)之

5、間的參照完整性是否存在或一致? c、一致性（ Consistency）：數(shù)據(jù)是否被一致的定義或理解?d、完備性（ Completeness）：所有需要的數(shù)據(jù)都存在嗎 ?e、有效性（ Validity）：數(shù)據(jù)是否在企業(yè)定義的可接受的范圍之內(nèi)? f 、時效性（ Timeliness）：數(shù)據(jù)在需要的時侯是有效的嗎?g 、可獲取性（ Accessibility）：數(shù)據(jù)是否易于獲取、易于理解和易于使用?以下綜合說明數(shù)據(jù)倉庫中數(shù)據(jù)質(zhì)量要求，包括格式、完整性要求。a、業(yè)務(wù)描述統(tǒng)一，對數(shù)據(jù)模型的不同版本融合、映射為唯一版本。包括： 1 、在業(yè)務(wù)邏輯沒有變化的前提下，舊的業(yè)務(wù)數(shù)據(jù)映射在新模型上。2 、 遺留系統(tǒng)

6、的人事信息、考核相關(guān)信息與業(yè)務(wù)系統(tǒng)、行政其他模塊要一致。b 、信息描述規(guī)范、完整。1 、不存在格式違規(guī)數(shù)據(jù)類型不存在潛在錯誤。2 、參照完整性未被破壞數(shù)據(jù)不會找不到參照。3 、不存在交叉系統(tǒng)匹配違規(guī)，數(shù)據(jù)被很好集成相同的數(shù)據(jù)存在于多個系統(tǒng)中，數(shù)據(jù)之間要匹配。4 、數(shù)據(jù)在內(nèi)部一致主要問題數(shù)據(jù)完整性問題表現(xiàn)形式大量的空值字段的出現(xiàn)產(chǎn)生原因原 OLTP 系統(tǒng)中對很多字段沒有做非空限制1.2.超出字典表范圍清洗策略交由 OLTP 系統(tǒng)重新錄入 , 補齊 在數(shù)據(jù)倉庫對應(yīng)的維表中建立一個 新的字段 , 將這些空值字段的值統(tǒng)一的賦值交由 OLTP 系統(tǒng)重新錄入 , 補齊在數(shù)據(jù)倉庫對應(yīng)的維表中建立一個新數(shù)據(jù)一

7、致性問題一個特定的字段在不同的表中內(nèi)容不同應(yīng)該成為主鍵的值不唯一填寫這些值的時候是直接讓用戶填寫而非下拉框選擇錄入, 同步的問題1.2.的字段 , 將這些空值字段的值統(tǒng)一的賦值1. 選取最可靠的表中的字段為確定值原 OLTP 系統(tǒng)中未建立有效的主鍵關(guān)系1. 消除錯誤 , 重復(fù)的主鍵同樣的紀錄字段在同一個表中重復(fù)出現(xiàn)，不能有差別。以下是對主要數(shù)據(jù)質(zhì)量問題的清洗策略：三、數(shù)據(jù)加載：將轉(zhuǎn)換和清洗完的數(shù)據(jù)按照數(shù)據(jù)倉庫的結(jié)構(gòu)進行數(shù)據(jù)加載。需要考慮初始數(shù)據(jù)裝載、數(shù)據(jù)刷新、加載順序等等問題。a、針對數(shù)據(jù)現(xiàn)狀，初始導(dǎo)入有這樣一些問題需要考慮：1 、如何解決時間差異性？2、如何解決平臺差異性？3、如何適應(yīng)數(shù)據(jù)的

8、不穩(wěn)定性？4、如何解決數(shù)據(jù)依賴性？b 、數(shù)據(jù)刷新的策略要根據(jù)業(yè)務(wù)需求和應(yīng)用系統(tǒng)的承受能力和數(shù)據(jù)情況決定。主要有這樣一些問題需要考慮： 1、如何解決時間差異性？2 、如何適應(yīng)數(shù)據(jù)的不穩(wěn)定性？3 、如何解決平臺差異性？4 、如何解決數(shù)據(jù)依賴性？5 、如何減少對業(yè)務(wù)系統(tǒng)的影響？c、不同的刷新任務(wù)類型，對業(yè)務(wù)系統(tǒng)的影響不同，刷新任務(wù)有以下種歸類特性： 1、刷新頻率：實時刷新、每數(shù)小時、每日、每周、每月、不定期手動刷新。2、刷新方式：數(shù)據(jù)庫表復(fù)制、文本文件ftp 再裝載、物化視圖、數(shù)據(jù)庫trigger。3、數(shù)據(jù)加工方式：簡單插入更新、增加計算項字段、多表關(guān)聯(lián)更新、匯總、多表關(guān)聯(lián)匯總計算。并可針對各種異常

9、情況做處理：回滾，重新裝載，斷點重新裝載等等，還可在任務(wù)完成后（或失敗后）將日志以 Email方式發(fā)給數(shù)據(jù)倉庫管理人員。四、匯總層、 CUBE加載：ODS 加載進入數(shù)據(jù)倉庫的數(shù)據(jù)只是底層詳細層數(shù)據(jù)，還需按定義的匯總規(guī)則進行匯總，生成數(shù)據(jù)集市用的匯總表或 CUBE 。ETL 流程是指完成每個維表數(shù)據(jù)及事實表數(shù)據(jù)導(dǎo)入的順序,其包括兩個部分,初始導(dǎo)入數(shù)據(jù)時的ETL 流程, 及增量導(dǎo)入時的ETL 流程。初始導(dǎo)入數(shù)據(jù)時的ETL 流程第一步:自動生成維的數(shù)據(jù)裝載自動生成維一般來說就是日期，年度月份，年度等時間類維度（年度月份，年度其實都是日期維的一個層次，但某些事實表中沒有日期信息，只有月份信息，所以需額

10、外建立此二維度），幾乎數(shù)據(jù)倉庫中每個數(shù)據(jù)模型都需使用時間類維度，在加載其它維度和事實之前， 需要先將時間維度生成出來。第二步:手工維護維度裝載實際數(shù)據(jù)倉庫開發(fā)中， 很可能會有些維度的數(shù)據(jù)在業(yè)務(wù)系統(tǒng)中無發(fā)得到，典型的是一些外部信息指表的類型代碼，是由數(shù)據(jù)倉庫開發(fā)人員設(shè)計的。所以需要手工方式建立這些信息，然后導(dǎo)入數(shù)據(jù)倉庫。第三步:緩慢變化維表數(shù)據(jù)裝載這些維度可以從業(yè)務(wù)系統(tǒng)中找到來源，但變化比較緩慢。對于初始裝載時，需要考慮對緩慢變化維的處理方式要和增量刷新方式一致。在裝載事實表數(shù)據(jù)之前，需要先裝載這些維表。需要注意的是，有些維本身就是事實表，其所依賴的維必須先裝載完成。第四步:事實表數(shù)據(jù)裝載然后是

11、初始裝載所有的事實表數(shù)據(jù)。事實表之間也有依賴關(guān)系， 某些事實表需在其他事實表裝載之前裝載。（如果ETL 程序已保障了數(shù)據(jù)的完整性，也可以在將關(guān)聯(lián)約束禁用的情況下不考慮先后順序，但一般不建議）。第五步：聚合表初始生成許多數(shù)據(jù)倉庫的前端應(yīng)用，并非直接使用主題星型模型中的事實表數(shù)據(jù)，而是聚合表中匯總，運 算好的數(shù)據(jù)。（ Oracle OLAP Service所建立的 ROLAP和數(shù)據(jù)集市實際上也是使用一系列的經(jīng)過大量預(yù)先計算得到的聚合表）增量導(dǎo)入第一步:緩慢變化維表數(shù)據(jù)裝載每天將所有變化過的維度信息刷新到數(shù)據(jù)倉庫中，維表數(shù)據(jù)的刷新必須現(xiàn)于事實表。第二步:事實表數(shù)據(jù)裝載階段每天新增事實數(shù)據(jù)的導(dǎo)入，如同

12、初始化導(dǎo)入一樣，需要考慮任務(wù)之間的先后順序。第三步:數(shù)據(jù)匯總和聚合根據(jù)設(shè)定的聚合規(guī)則和時間段對數(shù)據(jù)進行聚合。第四步:作業(yè)調(diào)度和異常情況處理五、任務(wù)調(diào)度策略驅(qū)動策略前導(dǎo) Job 驅(qū)動：只有滿足另外一個 JOB 成功后，自己才運行。文件驅(qū)動：當(dāng)下傳的文件到達，并經(jīng)過檢驗準確后 JOB 才運行。時間驅(qū)動：當(dāng)?shù)竭_某個時點時，Job 便開始運行。事件驅(qū)動：如人工參與，導(dǎo)致 JOB 執(zhí)行。通知設(shè)計：重要信息（成功 / 失?。┑耐ㄖ? 、成功退出分段提交方式， 當(dāng)分段提交的當(dāng)次任務(wù)都正確完成，即 Job 運行狀態(tài)臨時表中登記的作業(yè)狀態(tài)全部為完成時，退出ETL 調(diào)度。自動提交方式， 當(dāng)當(dāng)期所有的任務(wù)都正確完成， 即 Job 運行狀態(tài)表中登記的作業(yè)狀態(tài)全部為完成時，退出 ETL 調(diào)度。2 、失敗退出關(guān)鍵作業(yè)異常，關(guān)鍵作