ETL流程、數(shù)據(jù)流圖及ETL過程解決方案.ppt

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

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

3、義 模式及比較 ETL過程 問題分析 現(xiàn)狀分析,目錄,ETL過程,ETL過程： 數(shù)據(jù)抽取 數(shù)據(jù)清洗 數(shù)據(jù)轉(zhuǎn)換 數(shù)據(jù)加載 ETL的問題,ETL過程-0層DFD,1層-數(shù)據(jù)抽取,1層-數(shù)據(jù)清洗,1層-數(shù)據(jù)轉(zhuǎn)換,1層-數(shù)據(jù)加載,ETL過程-數(shù)據(jù)抽取,數(shù)據(jù)來源 文件系統(tǒng)，業(yè)務(wù)系統(tǒng) 抽取方式 根據(jù)具體業(yè)務(wù)進(jìn)行全量或增量抽取 抽取效率 將數(shù)據(jù)按一定的規(guī)則拆分成幾部分進(jìn)行并行處理 抽取策略 根據(jù)具體業(yè)務(wù)制定抽取的時(shí)間、頻度，以及抽取的流程,ETL過程-數(shù)據(jù)清洗,清洗規(guī)則： 數(shù)據(jù)補(bǔ)缺 對空數(shù)據(jù)、缺失數(shù)據(jù)進(jìn)行數(shù)據(jù)補(bǔ)缺操作，無法處理的作標(biāo)記 數(shù)據(jù)替換 對無效數(shù)據(jù)進(jìn)行數(shù)據(jù)的替換 格式規(guī)范化 將源數(shù)據(jù)抽取的數(shù)據(jù)格式轉(zhuǎn)

4、換成為便于進(jìn)入倉庫處理的目標(biāo)數(shù)據(jù)格式 主外鍵約束 通過建立主外鍵約束，對非法數(shù)據(jù)進(jìn)行替換或?qū)С龅藉e(cuò)誤文件重新處理,轉(zhuǎn)換規(guī)則 數(shù)據(jù)合并 多用表關(guān)聯(lián)實(shí)現(xiàn)，大小表關(guān)聯(lián)用lookup，大大表相交用join（每個(gè)字段加索引，保證關(guān)聯(lián)查詢的效率） 數(shù)據(jù)拆分 按一定規(guī)則進(jìn)行數(shù)據(jù)拆分 行列互換 排序/修改序號 去除重復(fù)記錄 數(shù)據(jù)驗(yàn)證：lookup，sum，count 實(shí)現(xiàn)方式 在ETL引擎中進(jìn)行（SQL無法實(shí)現(xiàn)的） 在數(shù)據(jù)庫中進(jìn)行（SQL可以實(shí)現(xiàn),ETL過程-數(shù)據(jù)加載,ETL定義 模式及比較 ETL過程 問題分析 現(xiàn)狀分析,目錄,ETL執(zhí)行時(shí)的異常處理,數(shù)據(jù)異常 將錯(cuò)誤信息單獨(dú)輸出，繼續(xù)執(zhí)行ETL，錯(cuò)誤數(shù)據(jù)修

5、改后再單獨(dú)加載 中斷ETL，修改后重新執(zhí)行ETL 原則：最大限度接收數(shù)據(jù) 環(huán)境異常 對于網(wǎng)絡(luò)中斷等外部原因造成的異常，設(shè)定嘗試次數(shù)或嘗試時(shí)間，超數(shù)或超時(shí)后，由外部人員手工干預(yù) 其他異常 例如源數(shù)據(jù)結(jié)構(gòu)改變、接口改變等異常狀況，應(yīng)進(jìn)行同步后，再裝載數(shù)據(jù),ETL設(shè)計(jì)規(guī)范,DI開發(fā)規(guī)范 ETL開發(fā)首要確定的是流程的執(zhí)行順序及條件；其次是具體表映射關(guān)系的定義，在數(shù)據(jù)庫性能允許的情況下，應(yīng)該盡可能使用sql語句進(jìn)行處理 對于具體映射和流程的命名，應(yīng)該以維護(hù)方便為前提： 映射：以目標(biāo)表名命名 流程：以流程要實(shí)現(xiàn)的功能命名 不允許使用臨時(shí)的SQL語句操縱數(shù)據(jù)庫，必須編寫好的SQL腳本或存儲過程 限定手工干預(yù)

6、只能運(yùn)行某個(gè)流程，不允許運(yùn)行單個(gè)過程 每一項(xiàng)手工操作必須留下記錄,設(shè)計(jì)規(guī)范 SQL語句應(yīng)書寫規(guī)范，關(guān)鍵字全部大寫，同時(shí)應(yīng)增加注釋 例如： /*表名：BSL_EMP_STATUS 作者：CASEY 日期：2007-12-17 描述：人員狀態(tài)中間表*/ 對于自定義列，列名應(yīng)采用標(biāo)準(zhǔn)后綴 數(shù)字類型，*_NO 字符類型，*_CODE,常見問題的分析,字符集問題 緩慢變化維處理 增量、實(shí)時(shí)同步的處理 錯(cuò)誤數(shù)據(jù)的檢測 變化數(shù)據(jù)的捕獲 抽取異常中止的處理,一、字符集問題,字符集定義 字符集是字符(包含字母，數(shù)字，符號和非打印字符等)以及所指定的內(nèi)碼所組成的特定的集合。是基于某種操作系統(tǒng)平臺和某種語言集支持的

7、。語言集的集合被稱為語言組，它可能包含一種或多種語言,C/S字符集轉(zhuǎn)換 直接轉(zhuǎn)換 對于同一語言組的不同字符集之間，可以直接進(jìn)行字符的轉(zhuǎn)換，不會產(chǎn)生亂碼 通過Unicode轉(zhuǎn)換 Unicode支持超過650種語言的國際字符集 Unicode系統(tǒng)缺省字符集utf-8,不同語言組的字符集進(jìn)行直接轉(zhuǎn)換的時(shí)候會出現(xiàn)亂碼,異構(gòu)庫字符集轉(zhuǎn)換 假設(shè)案例： 源數(shù)據(jù)庫為Oracle 10g，字符集zhs 16gbk 目標(biāo)數(shù)據(jù)庫Sybase IQ，字符集為x， locales.dat文件對應(yīng)操作系統(tǒng)下字符集設(shè)置為y x=cp936，y=cp936 結(jié)果：直接轉(zhuǎn)換出現(xiàn)亂碼。因?yàn)閟ybase目前支持的中文字符集有cp9

8、36、eucgb、gb18030、UTF-8 方案：利用unicode進(jìn)行轉(zhuǎn)換 ODBC 利用字符集設(shè)置為UTF-8的中間庫,二、緩慢變化維處理,緩慢變化維定義 在現(xiàn)實(shí)世界中，維度的屬性并不是靜態(tài)的，會隨著時(shí)間的流失發(fā)生緩慢的變化。這種隨時(shí)間發(fā)生變化的維度我們一般稱之為緩慢變化維。 處理方式 不保留歷史數(shù)據(jù) 保留歷史數(shù)據(jù) 起始-結(jié)束日期字段標(biāo)識 真/假狀態(tài)字段標(biāo)識 版本號字段標(biāo)識 代理鍵字段標(biāo)識 自增序列 構(gòu)造算法 保留且分析歷史信息 添加新的維度列（數(shù)據(jù)增多，維度列增多,三、增量、實(shí)時(shí)同步的處理,整表匹配 同一個(gè)庫中進(jìn)行 寫觸發(fā)器 客戶是否允許創(chuàng)建觸發(fā)器 是否影響數(shù)據(jù)庫性能 讀數(shù)據(jù)庫日志 O

9、racle：設(shè)定物化視圖日志,四、斷點(diǎn)續(xù)傳,利用源表的索引機(jī)制，抽取時(shí)按”數(shù)據(jù)塊”順序抽取 采取DBLink的機(jī)制，結(jié)合oracle自身機(jī)制優(yōu)化效率 生成本地文件塊，F(xiàn)TP傳輸減少對帶寬影響。若中斷，流程控制自動(dòng)回滾加載當(dāng)前數(shù)據(jù)塊,ETL工具大都支持異常中止后讀取斷點(diǎn)重新加載的處理 支持對變化數(shù)據(jù)的捕獲 與目標(biāo)數(shù)據(jù)庫松耦合,存儲過程與ETL,存儲過程：數(shù)據(jù)庫內(nèi)部利用索引的查找、排序、優(yōu)化，不涉及輸出、轉(zhuǎn)換等操作 ETL：異構(gòu)數(shù)據(jù)源或減少數(shù)據(jù)庫負(fù)荷、sql嵌套的情況 如果倉庫中同時(shí)需要明細(xì)和匯總數(shù)據(jù)，在倉庫匯總；如果倉庫不需要明細(xì)數(shù)據(jù)，ETL服務(wù)器上先排序然后匯總，減少業(yè)務(wù)系統(tǒng)負(fù)荷。 數(shù)據(jù)庫不適合做大數(shù)據(jù)量匯總的話，排序后按排序鍵匯總，并行處理加動(dòng)態(tài)分區(qū)（ETL廠商高性能匯總的機(jī)理,ETL定義 模式及比較 ETL過程 問題分析 現(xiàn)狀分析,目錄,ETL工具廠商,目前ETL