1、ETL數據抽取方案簡介ETL簡介ETL抽取方案1.數據抽取.2.數據轉換和加工.3.數據裝載.4.時間戳方式.5.全量刪除插入方式6.全量比對方式.錯誤錯誤錯誤未定義書簽。未定義書簽。未定義書簽。ETL簡介數據集成是把不同來源、格式和特點的數據在邏輯上或物理上有機地集中，從而提供全面的數據共享，是企業商務智能、數據倉庫系統的重要組成部分。ETLETL 是企業數據集成的主要解決方案。ETLETL 中三個字母分別代表的是 ExtractExtract、TransformTransform、LoadLoad，即抽取、轉換、加載。(1(1)數據抽取：從源數據源系統抽取目的數據源系統需要的數據;(2(2

2、 )數據轉換：將從源數據源獲取的數據按照業務需求，轉換成目的數據源要求的形式，并對錯誤、不一致的數據進行清洗和加工;(3(3)數據加載：將轉換后的數據裝載到目的數據源。ETLETL 作為構建數據倉庫的一個環節，負責將分布的、異構數據源中的數據如關系數據、平面數據文件等抽取到臨時中間層后進行清洗、轉換、集成，最后加載到數據倉庫或數據集市中，成為聯機分析處理、數ETL抽取方案ETLETL 過程中的主要環節就是數據抽取、數據轉換和加工、數據裝載。為了實現這些功能，ETLETL 工具會進行一些功能上的擴充，例如工作流、 調度引擎、規則引擎、腳本支持、統計信息等。1.數據抽取數據抽取是從數據源中抽取數據

3、的過程。實際應用中，數據源較多 采用的是關系數據庫。從數據庫中抽取數據一般有以下幾種方式:1）全量抽取全量抽取類似于數據遷移或數據復制，它將數據源中的表或視圖的數據原封不動的從數據庫中抽取出來，并轉換成自己的 ETLETL 工具可以識別的格式。全量 抽取比較簡單。2）增量抽取增量抽取只抽取自上次抽取以來數據庫中要抽取的表中新增或修改 的數據。在ETLETL 使用過程中，增量抽取較全量抽取應用更廣。如何捕獲 變化的數據是增量抽取的關鍵。對捕獲方法一般有兩點要求：準確性, 能夠將業務系統中的變化數據按一定的頻率準確地捕獲到；性能，不能 對業務系統造成太大的壓力，影響現有業務。目前增量數據抽取中常用

4、 的捕獲變化數據的方法有:a）觸發器方式（又稱快照式）在要抽取的表上建立需要的觸發器，一般要建立插入、修改、刪除 三個觸發器，每當源表中的數據發生變化，就被相應的觸發器將變化的 數據寫入一個臨時表，抽取線程從臨時表中抽取數據，臨時表中抽取過 的數據被標記或刪除。優點：數據抽取的性能高，ETLETL 加載規則簡單，速度快，不需要修 改業務系統表結構，可以實現數據的遞增加載。缺點：要求業務表建立觸發器，對業務系統有一定的影響，容易對 源數據庫構成威脅。b）時間戳方式它是一種基于快照比較的變化數據捕獲方式，在源表上增加一個時 間戳字段，系統中更新修改表數據的時候，同時修改時間戳字段的值。當進行數據抽

5、取時，通過比較上次抽取時間與時間戳字段的值來決定抽 取哪些數據。有的數據庫的時間戳支持自動更新，即表的其它字段的數 據發生改變時，自動更新時間戳字段的值。有的數據庫不支持時間戳的這就要求業務系統在更新業務數據時，手工更新時間戳字段。同觸發器方式一樣，時間戳方式的性能也比較好，ETLETL 系統時間戳維護需要由業務系統完成，對業務系統也有很大的傾 入性（加入額外的時間戳字段），特別是對不支持時間戳的自動更新的數 據庫，還要求業務系統進行額外的更新時間戳操作；另外，無法捕獲對 時間戳以前數據的 deletedelete 和upup datedate 操作，在數據準確性上受到了一定 的限制。C）全表

6、刪除插入方式每次 ETLETL 操作均刪除目標表數據，由 ETLETL 全新加載數據。優點：ETLETL 加載規則簡單，速度快。缺點：對于維表加外鍵不適應，當業務系統產生刪除數據操作時, 綜合數據庫將不會記錄到所刪除的歷史數據，不可以實現數據的遞增加 載；同時對于目標表所建立的關聯關系，需要重新進行創建。d）全表比對方式全表比對的方式是 ETLETL 工具事先為要抽取的表建立一個結構類似 的臨時表，該臨時表記錄源表主鍵以及根據所有字段的數據計算出來, 每次進行數據抽取時，對源表和臨時表進行的比對，如有不同，進行UpUp datedate 操作，如目標表沒有存在該主鍵值，表示該記錄還沒有，即進

7、行 InsertInsert 操作。優點：對已有系統表結構不產生影響，不需要修改業務操作程序, 所有抽取規則由ETLETL 完成，管理維護統一，可以實現數據的遞增加載,沒有風險。缺點：ETLETL 比對較復雜，設計較為復雜，速度較慢。與觸發器和時 間戳方式中的主動通知不同，全表比對方式是被動的進行全表數據的比 對，性能較差。當表中沒有主鍵或唯一列且含有重復記錄時，全表比對 方式的準確性較差。e)日志表方式在業務系統中添加系統日志表，當業務數據發生變化時，更新維護 日志表內容，自動更新,優點:設計清晰,源數據抽取相對清楚簡單，可以實現數據的遞增加載。缺點:當作 ETLETL 加載時，通過讀日志表

8、數據決定加載那些數據及 如何加載。優點：不需要修改業務系統表結構，源數據抽取清楚，速度較快。可以實現數據的遞增加載。缺點：日志表維護需要由業務系統完成，需要對業務系統業務操作 程序作修改，記錄日志信息。日志表維護較為麻煩，對原有系統有較大 影響。工作量較大，改動較大，有一定風險。f)Oracle變化數據捕捉(CDC方式)通過分析數據庫自身的日志來判斷變化的數據。OracleOracle 的改變數據 捕獲(CDCCDC，ChangedChanged DataData CaptureCapture )技術是這方面的代表。 CDCCDC 特 性是在 Oracle9iOracle9i 數據庫中引入的。

9、CDCCDC 能夠幫助你識別從上次抽取之 后發生變化的數據。利用 CDCCDC，在對源表進行 inin sertsert、upup datedate 或 deletedelete 等操作的同時就可以提取數據，并且變化的數據被保存在數據庫的變化 表中。這樣就可以捕獲發生變化的數據，然后利用數據庫視圖以一種可 控的方式提供給目標系統。CDCCDC 體系結構基于發布/ /訂閱模型。發布者捕捉變化數據并提供給訂閱者。訂閱者使用從發布者那里獲得的變化數 據。通常，CDCDC C系統擁有一個發布者和多個訂閱者。發布者首先需要 識別捕獲變化數據所需的源表。然后，它捕捉變化的數據并將其保存在 特別創建的變化表

10、中。它還使訂閱者能夠控制對變化數據的訪問。訂閱 者需要清楚自己感興趣的是哪些變化數據。一個訂閱者可能不會對發布 者發布的所有數據都感興趣。訂閱者需要創建一個訂閱者視圖來訪問經 發布者授權可以訪問的變化數據。 CDCCDC 分為同步模式和異步模式，同 步模式實時的捕獲變化數據并存儲到變化表中，發布者與訂閱都位于同 一數據庫中；異步模式則是基于OracleOracle 的流復制技術。優點：提供了易于使用的 APIAPI 來設置 CDCCDC 環境，縮短 ETLETL 的時 間。不需要修改業務系統表結構，可以實現數據的遞增加載。缺點：業務系統數據庫版本與產品不統一，難以統一實現，實現過 程相對復雜，

11、并且需深入研究方能實現。或者通過第三方工具實現，價 格昂貴。2.數據轉換和加工從數據源中抽取的數據不一定完全滿足目的庫的要求，例如數據格 式的不一致、數據輸入錯誤、數據不完整等等，因此有必要對抽取出的 數據進行數據轉換和加工。數據的轉換和加工可以在 ETLETL 引擎中進行，也可以在數據抽取過程 中利用關系數據庫的特性同時進行。a) ETL引擎中的數據轉換和加工ETLETL 引擎中一般以組件化的方式實現數據轉換。常用的數據轉換組件有字段映射、數據過濾、數據清洗、數據替換、數據計算、數據驗證、 數據加解密、數據合并、數據拆分等。這些組件如同一條流水線上的一 道道工序，它們是可插拔的，且可以任意組

12、裝，各組件之間通過數據總 線共享數據。同時 ETLETL 工具還提供了腳本支持，使得用戶可以以一種編 程的方式定制數據的轉換和加工行為。相比在數據庫中加工，性能較高，但不容易進行修改和清晰辨認。b)在數據庫中進行數據加工關系數據庫本身已經提供了強大的 SQLSQL、函數來支持數據的加工, 如在 SQLSQL 查詢語句中添加 wherewhere 條件進行過濾，查詢中重命名字段名 與目的表進行映射，substrsubstr 函數，casecase 條件判斷等等。相比在 ETLETL 引擎中進行數據轉換和加工，直接在 SQLSQL 語句中進行 轉換和加工更加簡單清晰；但依賴 SQLSQL 語句，有些數據加工通過 SQLSQL語句可能無法實現，對于 SQLSQL 語句無法處理的可以交由 ETLETL 引擎處理。將轉換和加工后的數據裝載到目的庫中通常是ETLETL 過程的最后步裝載數據的最佳方法取決于所執行操作的類型以及需要裝入多少數(1(1) 直接 SQLSQL 語