ETL流程數(shù)據(jù)流圖及ETL過程解決方案課件

1、關(guān)于ETL相關(guān)問題的解決辦法第1頁，共43頁。ETL定義模式及比較ETL過程問題分析 現(xiàn)狀分析目錄第2頁，共43頁。ETL定義ETL定義涉及以下內(nèi)容：ETL定義ETL前提ETL原則第3頁，共43頁。ETL定義定義：數(shù)據(jù)的抽取(Extract)、轉(zhuǎn)換(Transform)、裝載(Load)的過程目標：數(shù)據(jù)優(yōu)化。以最小代價(包括對日常操作的影響和對技能的要求) 將針對日常業(yè)務操作的數(shù)據(jù)轉(zhuǎn)化為針對數(shù)據(jù)倉庫而存儲的決策支持型數(shù)據(jù) 第4頁，共43頁。ETL的前提確定ETL范圍通過對目標表信息的收集，確定ETL的范圍選擇ETL工具考慮資金運行的平臺、對源和目標的支持程度、可編程的靈活性、對源數(shù)據(jù)變化的監(jiān)測

2、、數(shù)據(jù)處理時間的控制、管理和調(diào)度功能、對異常情況的處理確定解決方案抽取分析、變化數(shù)據(jù)的捕獲、目標表的刷新策略、數(shù)據(jù)的轉(zhuǎn)換及數(shù)據(jù)驗證第5頁，共43頁。ETL過程中應盡量遵循以下原則：應盡量利用數(shù)據(jù)中轉(zhuǎn)區(qū)對運營數(shù)據(jù)進行預處理。保證數(shù)據(jù)的安全性、集成與加載的高效性。 ETL的過程應是主動“拉取”，而不是從內(nèi)部“推送”，其可控性將大為增強。 流程化的配置管理和標準協(xié)議 數(shù)據(jù)質(zhì)量的保證正確性、一致性、完整性、有效性、可獲取性第6頁，共43頁。ETL定義模式及比較ETL過程問題分析 現(xiàn)狀分析目錄第7頁，共43頁。ETL模式及比較兩種模式異構(gòu)同構(gòu)模式比較的維度：特點環(huán)境第8頁，共43頁。ETL模式-同構(gòu)第9

3、頁，共43頁。ETL模式-異構(gòu)第10頁，共43頁。兩種模式的比較-特點異構(gòu)(Asynchronous)同構(gòu)(Synchronous )比同構(gòu)模式提供了更好的數(shù)據(jù)處理性能，需要更少的處理時間，因為通過網(wǎng)絡傳輸文件的速度比直接通過數(shù)據(jù)庫存取數(shù)據(jù)要快很多。要避免性能瓶頸問題，解決辦法是縮小每次抽取的時間粒度，例如將抽取周期定為每日抽取，這樣可以保證每次抽取的增量數(shù)據(jù)數(shù)目是很少量的。在數(shù)據(jù)抽取過程中，應盡量避免本次抽取定義的時間區(qū)間內(nèi)的源數(shù)據(jù)在抽取過程中同時產(chǎn)生變動的情況。即抽取的理想狀況是抽取的同時源數(shù)據(jù)系統(tǒng)的數(shù)據(jù)是靜止的，沒有增、刪、改的情況伴隨發(fā)生。對于ODS系統(tǒng)來說，數(shù)據(jù)不會頻繁地發(fā)生變動；而

4、對于OLTP系統(tǒng)來說，應該選擇源數(shù)據(jù)變化較少的時段完成抽取工作。與異構(gòu)方式類似，應避免抽取時間區(qū)間和源數(shù)據(jù)系統(tǒng)的生產(chǎn)時段相重合。如果源數(shù)據(jù)系統(tǒng)時刻都有新數(shù)據(jù)插入，一種解決辦法是設(shè)置一個時間區(qū)間，定義每次抽取的開始和結(jié)束時間值：本次抽取的開始時間為上次抽取的結(jié)束時間，本次抽取的結(jié)束時間為機器系統(tǒng)時間（Sysdate）或者是目前數(shù)據(jù)庫系統(tǒng)中已有記錄的最大時間戳值。實際上就是定義某個時間區(qū)間內(nèi)的源數(shù)據(jù)的快照(Snapshot)，這樣就可以避免重復裝載的情況發(fā)生。除此之外，還應該充分考慮源和目標兩套數(shù)據(jù)庫系統(tǒng)的Down機的時間因素。需要兩套ETL包，一個用來抽取，一個用來裝載，兩個包都需要由專門的系統(tǒng)

5、管理人員監(jiān)視是否裝載過程會發(fā)生錯誤。只需要一個ETL軟件包。系統(tǒng)管理人員也只需要監(jiān)視一套系統(tǒng)。源和目標之間沒有直接的聯(lián)系。只要中間過渡的文本文件結(jié)構(gòu)不發(fā)生變化，源和目標的結(jié)構(gòu)即使改變而不會對ETL流程產(chǎn)生很大的影響。源和目標的關(guān)系是被綁定在具體的映射中的。當源或者目標的結(jié)構(gòu)發(fā)生變化，相對應的映射也要做修改。第11頁，共43頁。異構(gòu)(Asynchronous)同構(gòu)(Synchronous )當ETL錯誤發(fā)生時，可以采用簡單的處理辦法修復數(shù)據(jù)：當抽取失敗時，修正問題并重新從源中抽?。划斞b載過程發(fā)生問題，回滾(Roll back)，返回上一次裝載的狀態(tài)并再次運行裝載的程序。必要時甚至可以將數(shù)據(jù)倉庫系

6、統(tǒng)恢復到某一個時點的狀態(tài)并批量地裝載文本文件。與異構(gòu)模式類似，當ETL錯誤發(fā)生也可以采用相應手段來修復數(shù)據(jù)。例如，倘若源數(shù)據(jù)庫中保留了所有歷史記錄，ETL程序可以根據(jù)設(shè)定的時間區(qū)間修復上一次裝載失敗的數(shù)據(jù)。前提是必須先刪除上一次裝載失敗從而在目標庫中產(chǎn)生的垃圾數(shù)據(jù)，回滾(Roll back)，返回到上一次加載數(shù)據(jù)前的狀態(tài)。可以根據(jù)目標表的主鍵來確定裝載過程中插入或更新記錄的策略，如果源記錄的主鍵是新的，那么就插入該記錄；如果源記錄的主鍵在目標數(shù)據(jù)庫中已經(jīng)存在，那么就用源記錄更新目標記錄。這樣就可以避免多次重復裝載的情況發(fā)生。同時，適當調(diào)大裝載的時間區(qū)段也不會發(fā)生數(shù)據(jù)重復加載的情況。需要有專門的

7、核查(Audit)程序來監(jiān)控數(shù)據(jù)傳輸或者裝載過程是否有失敗或者記錄缺失的情況發(fā)生。直接拷貝(direct copy), 可以避免傳送過程中的許多錯誤?？梢栽谠春湍繕藥熘羞\行Sum 和Count等聚合函數(shù)來對數(shù)據(jù)質(zhì)量進行校驗。能夠簡化查錯troubleshooting的過程，由于在中間步驟生成文本文件，實際上是所要抽取的源數(shù)據(jù)的快照(Snapshot)。問題發(fā)生時，可以很容易地確定是哪一個環(huán)節(jié)發(fā)生了問題，源，目標還是網(wǎng)絡傳輸？與異步模式相比，查錯過程比較復雜。源數(shù)據(jù)的不斷變化導致問題難以定位。很難判斷問題究竟是由于抽取程序還是由于源數(shù)據(jù)的變化引起的(上一次抽取的數(shù)據(jù)是什么?)。第12頁，共43頁

8、。異構(gòu)(Asynchronous)同構(gòu)(Synchronous )源和目標的數(shù)據(jù)接口分離，只需要定義好中間的文本文件數(shù)據(jù)接口，就可以同步完成獨立的源和目標的開發(fā)工作。當各自模塊完成后再將其裝配，提高開發(fā)效率。開發(fā)人員不僅需要熟悉源的結(jié)構(gòu)，對目標的結(jié)構(gòu)也要有所了解。要將數(shù)據(jù)導出成字節(jié)流并寫入導文本文件中。如果源包含圖形數(shù)據(jù)，要將其導出成文本，實現(xiàn)起來有一定的難度。從源到目標的直接映射，不需要從ASCII或者Unicode作為中間過渡。維護過程中要求不能誤刪文件或其中的某些記錄，以免破壞文本文件之間的關(guān)聯(lián)關(guān)系。主表和子表之間有主外鍵相關(guān)聯(lián)，數(shù)據(jù)庫中的各表記錄能夠保持相互間的匹配關(guān)系。數(shù)據(jù)轉(zhuǎn)換過程包

9、括兩個步驟：1、將數(shù)據(jù)庫中的表導出成中間過渡的文本；2、裝載文件。導出的處理過程比較靈活，可以從源表導出，也可以從相關(guān)的視圖導出，也可將源表內(nèi)容先導出到臨時表再導出到文本文件。處理導出過程的存儲過程可以在源上，也可以在目標上。數(shù)據(jù)轉(zhuǎn)換過程只有一個步驟，一次性地完成導出和裝載的工作。簡化了設(shè)計和測試的過程，但是另一方面也降低了靈活性。要求具備兩套安全控制機制，對于源數(shù)據(jù)庫有讀權(quán)限，對于目標數(shù)據(jù)庫有寫權(quán)限。同時還需要有能夠在源和目標服務器上有寫文件的權(quán)限（用于存放中間文本文件和上傳文件到目標服務器）。與異構(gòu)模式類似，也需要對于源數(shù)據(jù)庫有讀權(quán)限，對于目標數(shù)據(jù)庫有寫權(quán)限。但是抽取過程可以不需要源和目標

10、服務器上操作系統(tǒng)級的文件管理權(quán)限。第13頁，共43頁。兩種模式的比較-環(huán)境條件異構(gòu)(Asynchronous) 同構(gòu)(Synchronous)數(shù)據(jù)傳輸(Data transfer)大數(shù)據(jù)量小數(shù)據(jù)量網(wǎng)絡連接(Network connectivity)廣域網(wǎng)局域網(wǎng)或者同一數(shù)據(jù)中心源和目標在物理架構(gòu)上是否屬于不同的分布式環(huán)境是不是抽取數(shù)據(jù)的復雜度(Complexity of data)源中只包含了文本或數(shù)值類型的字段源數(shù)據(jù)庫中包含了圖形類字段第14頁，共43頁。ETL定義模式及比較ETL過程問題分析 現(xiàn)狀分析目錄第15頁，共43頁。ETL過程ETL過程：數(shù)據(jù)抽取數(shù)據(jù)清洗數(shù)據(jù)轉(zhuǎn)換數(shù)據(jù)加載ETL的問題第

11、16頁，共43頁。ETL過程-0層DFD第17頁，共43頁。1層-數(shù)據(jù)抽取第18頁，共43頁。1層-數(shù)據(jù)清洗第19頁，共43頁。1層-數(shù)據(jù)轉(zhuǎn)換第20頁，共43頁。1層-數(shù)據(jù)加載第21頁，共43頁。ETL過程-數(shù)據(jù)抽取數(shù)據(jù)來源文件系統(tǒng)，業(yè)務系統(tǒng)抽取方式根據(jù)具體業(yè)務進行全量或增量抽取抽取效率將數(shù)據(jù)按一定的規(guī)則拆分成幾部分進行并行處理抽取策略根據(jù)具體業(yè)務制定抽取的時間、頻度，以及抽取的流程第22頁，共43頁。ETL過程-數(shù)據(jù)清洗清洗規(guī)則：數(shù)據(jù)補缺對空數(shù)據(jù)、缺失數(shù)據(jù)進行數(shù)據(jù)補缺操作，無法處理的作標記數(shù)據(jù)替換對無效數(shù)據(jù)進行數(shù)據(jù)的替換格式規(guī)范化將源數(shù)據(jù)抽取的數(shù)據(jù)格式轉(zhuǎn)換成為便于進入倉庫處理的目標數(shù)據(jù)格式主

12、外鍵約束通過建立主外鍵約束，對非法數(shù)據(jù)進行替換或?qū)С龅藉e誤文件重新處理第23頁，共43頁。轉(zhuǎn)換規(guī)則數(shù)據(jù)合并多用表關(guān)聯(lián)實現(xiàn)，大小表關(guān)聯(lián)用lookup，大大表相交用join（每個字段加索引，保證關(guān)聯(lián)查詢的效率）數(shù)據(jù)拆分按一定規(guī)則進行數(shù)據(jù)拆分行列互換排序/修改序號去除重復記錄數(shù)據(jù)驗證：lookup，sum，count實現(xiàn)方式在ETL引擎中進行（SQL無法實現(xiàn)的）在數(shù)據(jù)庫中進行（SQL可以實現(xiàn)）第24頁，共43頁。ETL過程-數(shù)據(jù)加載實現(xiàn)方式優(yōu)點缺點時戳方式在業(yè)務表中統(tǒng)一添加字段作為時戳，當OLTP系統(tǒng)更新修改業(yè)務數(shù)據(jù)時，同時修改時戳字段值源數(shù)據(jù)抽取相對簡單清楚，速度快，適合數(shù)據(jù)的增量加載需要修改業(yè)務

13、表中的數(shù)據(jù)結(jié)構(gòu)，業(yè)務數(shù)據(jù)變動時工作量比較大，相對風險較大日志表方式在OLTP系統(tǒng)中添加日志表，業(yè)務數(shù)據(jù)發(fā)生變化時，更新維護日志表內(nèi)容不需要修改業(yè)務表中的數(shù)據(jù)結(jié)構(gòu)。源數(shù)據(jù)抽取簡單清楚，速度快，適合數(shù)據(jù)的增量加載業(yè)務系統(tǒng)中更新記錄日志操作麻煩全表對比方式抽取所有源數(shù)據(jù)，在更新目標表之前先根據(jù)主鍵和字段進行數(shù)據(jù)比對，有更新的進行update或insert對系統(tǒng)表結(jié)構(gòu)沒有任何影響，管理維護統(tǒng)一，可以實現(xiàn)數(shù)據(jù)的增量加載數(shù)據(jù)比對復雜，設(shè)計比較復雜，執(zhí)行速度慢全表刪除插入方式刪除目標表數(shù)據(jù)，將源數(shù)據(jù)全部插入ETL規(guī)則簡單，速度快對維表加代理健不適應，OLTP系統(tǒng)有刪除數(shù)據(jù)時，不能在數(shù)據(jù)倉庫體現(xiàn)被刪數(shù)據(jù)，不能

14、實現(xiàn)增量加載第25頁，共43頁。ETL定義模式及比較ETL過程問題分析 現(xiàn)狀分析目錄第26頁，共43頁。ETL執(zhí)行時的異常處理數(shù)據(jù)異常將錯誤信息單獨輸出，繼續(xù)執(zhí)行ETL，錯誤數(shù)據(jù)修改后再單獨加載中斷ETL，修改后重新執(zhí)行ETL原則：最大限度接收數(shù)據(jù)環(huán)境異常對于網(wǎng)絡中斷等外部原因造成的異常，設(shè)定嘗試次數(shù)或嘗試時間，超數(shù)或超時后，由外部人員手工干預其他異常例如源數(shù)據(jù)結(jié)構(gòu)改變、接口改變等異常狀況，應進行同步后，再裝載數(shù)據(jù)第27頁，共43頁。ETL設(shè)計規(guī)范DI開發(fā)規(guī)范ETL開發(fā)首要確定的是流程的執(zhí)行順序及條件；其次是具體表映射關(guān)系的定義，在數(shù)據(jù)庫性能允許的情況下，應該盡可能使用sql語句進行處理對于具

15、體映射和流程的命名，應該以維護方便為前提：映射：以目標表名命名流程：以流程要實現(xiàn)的功能命名不允許使用臨時的SQL語句操縱數(shù)據(jù)庫，必須編寫好的SQL腳本或存儲過程限定手工干預只能運行某個流程，不允許運行單個過程每一項手工操作必須留下記錄第28頁，共43頁。設(shè)計規(guī)范SQL語句應書寫規(guī)范，關(guān)鍵字全部大寫，同時應增加注釋例如： /*表名：BSL_EMP_STATUS 作者：CASEY 日期：2007-12-17 描述：人員狀態(tài)中間表*/對于自定義列，列名應采用標準后綴數(shù)字類型，*_NO字符類型，*_CODE第29頁，共43頁。常見問題的分析字符集問題緩慢變化維處理增量、實時同步的處理錯誤數(shù)據(jù)的檢測變化

16、數(shù)據(jù)的捕獲抽取異常中止的處理第30頁，共43頁。一、字符集問題字符集定義字符集是字符(包含字母，數(shù)字，符號和非打印字符等)以及所指定的內(nèi)碼所組成的特定的集合。是基于某種操作系統(tǒng)平臺和某種語言集支持的。語言集的集合被稱為語言組，它可能包含一種或多種語言。第31頁，共43頁。C/S字符集轉(zhuǎn)換直接轉(zhuǎn)換對于同一語言組的不同字符集之間，可以直接進行字符的轉(zhuǎn)換，不會產(chǎn)生亂碼通過Unicode轉(zhuǎn)換Unicode支持超過650種語言的國際字符集 Unicode系統(tǒng)缺省字符集utf-8不同語言組的字符集進行直接轉(zhuǎn)換的時候會出現(xiàn)亂碼！第32頁，共43頁。異構(gòu)庫字符集轉(zhuǎn)換 假設(shè)案例：源數(shù)據(jù)庫為Oracle 10g，

17、字符集zhs 16gbk目標數(shù)據(jù)庫Sybase IQ，字符集為x， locales.dat文件對應操作系統(tǒng)下字符集設(shè)置為y x=cp936，y=cp936 結(jié)果：直接轉(zhuǎn)換出現(xiàn)亂碼。因為sybase目前支持的中文字符集有cp936、eucgb、gb18030、UTF-8方案：利用unicode進行轉(zhuǎn)換ODBC利用字符集設(shè)置為UTF-8的中間庫第33頁，共43頁。二、緩慢變化維處理緩慢變化維定義在現(xiàn)實世界中，維度的屬性并不是靜態(tài)的，會隨著時間的流失發(fā)生緩慢的變化。這種隨時間發(fā)生變化的維度我們一般稱之為緩慢變化維。處理方式不保留歷史數(shù)據(jù) 保留歷史數(shù)據(jù)起始-結(jié)束日期字段標識真/假狀態(tài)字段標識版本號字段

18、標識代理鍵字段標識自增序列構(gòu)造算法保留且分析歷史信息添加新的維度列（數(shù)據(jù)增多，維度列增多）第34頁，共43頁。三、增量、實時同步的處理整表匹配同一個庫中進行寫觸發(fā)器客戶是否允許創(chuàng)建觸發(fā)器是否影響數(shù)據(jù)庫性能讀數(shù)據(jù)庫日志Oracle：設(shè)定物化視圖日志第35頁，共43頁。四、斷點續(xù)傳利用源表的索引機制，抽取時按”數(shù)據(jù)塊”順序抽取 采取DBLink的機制，結(jié)合oracle自身機制優(yōu)化效率 生成本地文件塊，F(xiàn)TP傳輸減少對帶寬影響。若中斷，流程控制自動回滾加載當前數(shù)據(jù)塊 第36頁，共43頁。ETL工具大都支持異常中止后讀取斷點重新加載的處理支持對變化數(shù)據(jù)的捕獲與目標數(shù)據(jù)庫松耦合第37頁，共43頁。存儲過程與ETL存儲過程：數(shù)據(jù)庫內(nèi)部利用索引的查找、排序、優(yōu)化，不涉及輸出、轉(zhuǎn)換等操作ETL：異構(gòu)數(shù)據(jù)源或減少數(shù)據(jù)庫負荷、sql嵌套的情況如果倉庫中同時需要明細和匯總數(shù)據(jù)，在倉庫匯總；如果倉庫不需要明細數(shù)據(jù)，ETL服務器上先排序然后匯總，減少業(yè)務系統(tǒng)負荷。數(shù)據(jù)庫不