ETL數(shù)據(jù)抽取方案簡介

1、ETL數(shù)據(jù)抽取方案簡介ETL簡介ETL抽取方案1.數(shù)據(jù)抽取2.數(shù)據(jù)轉(zhuǎn)換和加工3.數(shù)據(jù)裝載4.時間戳方式5.全量刪除插入方式6.全量比對方式錯誤!未定義書簽。錯誤!未定義書簽。錯誤!未定義書簽。ETL簡介數(shù)據(jù)集成是把不同來源、格式和特點的數(shù)據(jù)在邏輯上或物理上有機(jī) 地集中，從而提供全面的數(shù)據(jù)共享，是企業(yè)商務(wù)智能、數(shù)據(jù)倉庫系統(tǒng)的 重要組成部分。ETL是企業(yè)數(shù)據(jù)集成的主要解決方案。ETL中三個字母分別代表的是 Extract、Transform、Load ,即抽取、 轉(zhuǎn)換、加載。(1)數(shù)據(jù)抽?。簭脑磾?shù)據(jù)源系統(tǒng)抽取目的數(shù)據(jù)源系統(tǒng)需要的數(shù)據(jù)；(2)數(shù)據(jù)轉(zhuǎn)換：將從源數(shù)據(jù)源獲取的數(shù)據(jù)按照業(yè)務(wù)需求，轉(zhuǎn)換成目的數(shù)

2、據(jù)源要求的形式，并對錯誤、不一致的數(shù)據(jù)進(jìn)行清洗和加工；(3)數(shù)據(jù)加載：將轉(zhuǎn)換后的數(shù)據(jù)裝載到目的數(shù)據(jù)源。ETL作為構(gòu)建數(shù)據(jù)倉庫的一個環(huán)節(jié)，負(fù)責(zé)將分布的、異構(gòu)數(shù)據(jù)源中 的數(shù)據(jù)如關(guān)系數(shù)據(jù)、平面數(shù)據(jù)文件等抽取到臨時中間層后進(jìn)行清洗、轉(zhuǎn)據(jù)挖掘的基礎(chǔ)。移、交換和RJ步現(xiàn)在越來越多地將 ETL應(yīng)用于一般信息系統(tǒng)數(shù)據(jù)的遷。一個簡單 ETL流程如圖1所示。掩轉(zhuǎn)/力口 11s臨時集成，最后加載到數(shù)據(jù)倉庫或數(shù)據(jù)集市中，成為聯(lián)機(jī)分析處理圖1ETL抽取方案ETL過程中的主要環(huán)節(jié)就是數(shù)據(jù)抽取、數(shù)據(jù)轉(zhuǎn)換和加工、數(shù)據(jù)裝載。 為了實現(xiàn)這些功能，ETL工具會進(jìn)行一些功能上的擴(kuò)充，例如工作流、 調(diào)度引擎、規(guī)則引擎、腳本支持、統(tǒng)計信

3、息等。1.數(shù)據(jù)抽取數(shù)據(jù)抽取是從數(shù)據(jù)源中抽取數(shù)據(jù)的過程。實際應(yīng)用中，數(shù)據(jù)源較多采用的是關(guān)系數(shù)據(jù)庫。從數(shù)據(jù)庫中抽取數(shù)據(jù)一般有以下幾種方式：1）全量抽取全量抽取類似于數(shù)據(jù)遷移或數(shù)據(jù)復(fù)制，它將數(shù)據(jù)源中的表或視圖的數(shù)據(jù)原封不動的從數(shù)據(jù)庫中抽取出來，并轉(zhuǎn)換成自己的ETL工具可以識別的格式。全量 抽取比較簡單。2）增量抽取增量抽取只抽取自上次抽取以來數(shù)據(jù)庫中要抽取的表中新增或修改的數(shù)據(jù)。在ETL使用過程中，增量抽取較全量抽取應(yīng)用更廣。如何捕獲 變化的數(shù)據(jù)是增量抽取的關(guān)鍵。對捕獲方法一般有兩點要求：準(zhǔn)確性， 能夠?qū)I(yè)務(wù)系統(tǒng)中的變化數(shù)據(jù)按一定的頻率準(zhǔn)確地捕獲到；性能，不能 對業(yè)務(wù)系統(tǒng)造成太大的壓力，影響現(xiàn)有業(yè)務(wù)

4、。目前增量數(shù)據(jù)抽取中常用 的捕獲變化數(shù)據(jù)的方法有：a）觸發(fā)器方式（又稱快照式）在要抽取的表上建立需要的觸發(fā)器，一般要建立插入、修改、刪除 三個觸發(fā)器，每當(dāng)源表中的數(shù)據(jù)發(fā)生變化，就被相應(yīng)的觸發(fā)器將變化的 數(shù)據(jù)寫入一個臨時表，抽取線程從臨時表中抽取數(shù)據(jù)，臨時表中抽取過 的數(shù)據(jù)被標(biāo)記或刪除。優(yōu)點：數(shù)據(jù)抽取的性能高，ETL加載規(guī)則簡單，速度快，不需要修 改業(yè)務(wù)系統(tǒng)表結(jié)構(gòu)，可以實現(xiàn)數(shù)據(jù)的遞增加載。缺點：要求業(yè)務(wù)表建立觸發(fā)器，對業(yè)務(wù)系統(tǒng)有一定的影響，容易對 源數(shù)據(jù)庫構(gòu)成威脅。b）時間戳方式它是一種基于快照比較的變化數(shù)據(jù)捕獲方式，在源表上增加一個時 間戳字段，系統(tǒng)中更新修改表數(shù)據(jù)的時候，同時修改時間戳字段的

5、值。 當(dāng)進(jìn)行數(shù)據(jù)抽取時，通過比較上次抽取時間與時間戳字段的值來決定抽 取哪些數(shù)據(jù)。有的數(shù)據(jù)庫的時間戳支持自動更新，即表的其它字段的數(shù) 據(jù)發(fā)生改變時，自動更新時間戳字段的值。有的數(shù)據(jù)庫不支持時間戳的自動更新，這就要求業(yè)務(wù)系統(tǒng)在更新業(yè)務(wù)數(shù)據(jù)時，手工更新時間戳字段。優(yōu)點：同觸發(fā)器方式一樣，時間戳方式的性能也比較好，ETL系統(tǒng)設(shè)計清晰，源數(shù)據(jù)抽取相對清楚簡單，可以實現(xiàn)數(shù)據(jù)的遞增加載。I缺點：時間戳維護(hù)需要由業(yè)務(wù)系統(tǒng)完成，對業(yè)務(wù)系統(tǒng)也有很大的傾入性（加入額外的時間戳字段），特別是對不支持時間戳的自動更新的數(shù)據(jù)庫，還要求業(yè)務(wù)系統(tǒng)進(jìn)行額外的更新時間戳操作；另外，無法捕獲對時間戳以前數(shù)據(jù)的delete和upd

6、ate操作，在數(shù)據(jù)準(zhǔn)確性上受到了一定的限制。c）全表刪除插入方式每次ETL操作均刪除目標(biāo)表數(shù)據(jù)，由ETL全新加載數(shù)據(jù)。優(yōu)點：ETL加載規(guī)則簡單，速度快。缺點：對于維表加外鍵不適應(yīng)，當(dāng)業(yè)務(wù)系統(tǒng)產(chǎn)生刪除數(shù)據(jù)操作時，綜合數(shù)據(jù)庫將不會記錄到所刪除的歷史數(shù)據(jù)，不可以實現(xiàn)數(shù)據(jù)的遞增加載；同時對于目標(biāo)表所建立的關(guān)聯(lián)關(guān)系，需要重新進(jìn)行創(chuàng)建。d）全表比對方式全表比對的方式是 ETL工具事先為要抽取的表建立一個結(jié)構(gòu)類似的臨時表，該臨時表記錄源表主鍵以及根據(jù)所有字段的數(shù)據(jù)計算出來，每次進(jìn)行數(shù)據(jù)抽取時，對源表和臨時表進(jìn)行的比對，如有不同，進(jìn)行Update操作，如目標(biāo)表沒有存在該主鍵值，表示該記錄還沒有，即進(jìn)行Inse

7、rt操作。優(yōu)點：對已有系統(tǒng)表結(jié)構(gòu)不產(chǎn)生影響，不需要修改業(yè)務(wù)操作程序，所有抽取規(guī)則由ETL完成，管理維護(hù)統(tǒng)一，可以實現(xiàn)數(shù)據(jù)的遞增加載,沒有風(fēng)險缺點：ETL比對較復(fù)雜，設(shè)計較為復(fù)雜，速度較慢。與觸發(fā)器和時問戳方式中的主動通知不同，全表比對方式是被動的進(jìn)行全表數(shù)據(jù)的比I對，性能較差。當(dāng)表中沒有主鍵或唯一列且含有重復(fù)記錄時，全表比對 方式的準(zhǔn)確性較差。e) 日志表方式在業(yè)務(wù)系統(tǒng)中添加系統(tǒng)日志表，當(dāng)業(yè)務(wù)數(shù)據(jù)發(fā)生變化時，更新維護(hù)日志表內(nèi)容，當(dāng)作ETL加載時，通過讀日志表數(shù)據(jù)決定加載那些數(shù)據(jù)及 如何加載。優(yōu)點：不需要修改業(yè)務(wù)系統(tǒng)表結(jié)構(gòu)，源數(shù)據(jù)抽取清楚，速度較快?？梢詫崿F(xiàn)數(shù)據(jù)的遞增加載。缺點：日志表維護(hù)需要由

8、業(yè)務(wù)系統(tǒng)完成，需要對業(yè)務(wù)系統(tǒng)業(yè)務(wù)操作 程序作修改，記錄日志信息。日志表維護(hù)較為麻煩，對原有系統(tǒng)有較大 影響。工作量較大，改動較大，有一定風(fēng)險。f) Oracle變化數(shù)據(jù)捕捉(CDC方式)通過分析數(shù)據(jù)庫自身的日志來判斷變化的數(shù)據(jù)。Oracle的改變數(shù)據(jù)捕獲(CDC , Changed Data Capture )技術(shù)是這方面的代表。 CDC特性是在Oracle9i數(shù)據(jù)庫中引入的。CDC能夠幫助你識別從上次抽取之后發(fā)生變化的數(shù)據(jù)。利用CDC ,在對源表進(jìn)行insert、update或delete 等操作的同時就可以提取數(shù)據(jù)，并且變化的數(shù)據(jù)被保存在數(shù)據(jù)庫的變化 表中。這樣就可以捕獲發(fā)生變化的數(shù)據(jù)，然

9、后利用數(shù)據(jù)庫視圖以一種可 控的方式提供給目標(biāo)系統(tǒng)。CDC體系結(jié)構(gòu)基于發(fā)布/訂閱模型。發(fā)布者捕捉變化數(shù)據(jù)并提供給訂閱者。訂閱者使用從發(fā)布者那里獲得的變化數(shù)據(jù)。通常，CDC系統(tǒng)擁有一個發(fā)布者和多個訂閱者。發(fā)布者首先需要識別捕獲變化數(shù)據(jù)所需的源表。然后，它捕捉變化的數(shù)據(jù)并將其保存在I特別創(chuàng)建的變化表中。它還使訂閱者能夠控制對變化數(shù)據(jù)的訪問。訂閱 者需要清楚自己感興趣的是哪些變化數(shù)據(jù)。一個訂閱者可能不會對發(fā)布 者發(fā)布的所有數(shù)據(jù)都感興趣。訂閱者需要創(chuàng)建一個訂閱者視圖來訪問經(jīng) 發(fā)布者授權(quán)可以訪問的變化數(shù)據(jù)。 CDC分為同步模式和異步模式，同 步模式實時的捕獲變化數(shù)據(jù)并存儲到變化表中，發(fā)布者與訂閱都位于同

10、一數(shù)據(jù)庫中；異步模式則是基于 Oracle的流復(fù)制技術(shù)。優(yōu)點：提供了易于使用的 API來設(shè)置CDC環(huán)境，縮短ETL的時 問。不需要修改業(yè)務(wù)系統(tǒng)表結(jié)構(gòu)，可以實現(xiàn)數(shù)據(jù)的遞增加載。缺點：業(yè)務(wù)系統(tǒng)數(shù)據(jù)庫版本與產(chǎn)品不統(tǒng)一，難以統(tǒng)一實現(xiàn)，實現(xiàn)過 程相對復(fù)雜，并且需深入研究方能實現(xiàn)?；蛘咄ㄟ^第三方工具實現(xiàn)，價 格昂貴。2 .數(shù)據(jù)轉(zhuǎn)換和加工從數(shù)據(jù)源中抽取的數(shù)據(jù)不一定完全滿足目的庫的要求，例如數(shù)據(jù)格 式的不一致、數(shù)據(jù)輸入錯誤、數(shù)據(jù)不完整等等，因此有必要對抽取出的數(shù)據(jù)進(jìn)行數(shù)據(jù)轉(zhuǎn)換和加工。數(shù)據(jù)的轉(zhuǎn)換和加工可以在 ETL引擎中進(jìn)行，也可以在數(shù)據(jù)抽取過程 中利用關(guān)系數(shù)據(jù)庫的特性同時進(jìn)行。a) ETL引擎中的數(shù)據(jù)轉(zhuǎn)換和加

11、工ETL引擎中一般以組件化的方式實現(xiàn)數(shù)據(jù)轉(zhuǎn)換。常用的數(shù)據(jù)轉(zhuǎn)換組 件有字段映射、數(shù)據(jù)過濾、數(shù)據(jù)清洗、數(shù)據(jù)替換、數(shù)據(jù)計算、數(shù)據(jù)驗證、 數(shù)據(jù)加解密、數(shù)據(jù)合并、數(shù)據(jù)拆分等。這些組件如同一條流水線上的一I道道工序，它們是可插拔的，且可以任意組裝，各組件之間通過數(shù)據(jù)總 線共享數(shù)據(jù)。同時ETL工具還提供了腳本支持，使得用戶可以以一種編程的方式定制數(shù)據(jù)的轉(zhuǎn)換和加工行為。相比在數(shù)據(jù)庫中加工，性能較高，但不容易進(jìn)行修改和清晰辨認(rèn)。b)在數(shù)據(jù)庫中進(jìn)行數(shù)據(jù)加工關(guān)系數(shù)據(jù)庫本身已經(jīng)提供了強(qiáng)大的 SQL、函數(shù)來支持?jǐn)?shù)據(jù)的加工， 如在SQL查詢語句中添加where條件進(jìn)行過濾，查詢中重命名字段名 與目的表進(jìn)行映射，substr函數(shù)，case條件判斷等等。相比在ETL引擎中進(jìn)行數(shù)據(jù)轉(zhuǎn)換和加工，直接在SQL語句中進(jìn)行 轉(zhuǎn)換和加工更加簡單清晰；但依賴 SQL語句，有些數(shù)據(jù)加工通過 SQL 語句可能無法實現(xiàn)，對于SQL語句無法處理的可以交由ETL引擎處理。3 .數(shù)據(jù)裝載將轉(zhuǎn)換和加工后的數(shù)據(jù)裝載到目的庫中通常是 ETL過程的最后步 驟。裝載數(shù)據(jù)的最佳方法取決于所執(zhí)行