增量抽取 增量抽取隻抽取自上次抽取以來資料庫中要抽取的表中新增或修改的資料。在ETL使用過程中。增量抽取較全量抽取應用更廣。如何捕獲變化的資料是增量抽取的關鍵。對捕獲方法一般有兩點要求：準确性，能夠将業務系統中的變化資料按一定的頻率準确地捕獲到；性能，不能對業務系統造成太大的壓力，影響現有業務。目前增量資料抽取中常用的捕獲變化資料的方法有：
a.觸發器：在要抽取的表上建立需要的觸發器，一般要建立插入、修改、删除三個觸發器，每當源表中的資料發生變化，就被相應的觸發器将變化的資料寫入一個臨時表，抽取線程從臨時表中抽取資料，臨時表中抽取過的資料被标記或删除。觸發器方式的優點是資料抽取的性能較高，缺點是要求業務表建立觸發器，對業務系統有一定的影響。
b.時間戳：它是一種基于快照比較的變化資料捕獲方式，在源表上增加一個時間戳字段，系統中更新修改表資料的時候，同時修改時間戳字段的值。當進行資料抽取時，通過比較系統時間與時間戳字段的值來決定抽取哪些資料。有的資料庫的時間戳支援自動更新，即表的其它字段的資料發生改變時，自動更新時間戳字段的值。有的資料庫不支援時間戳的自動更新，這就要求業務系統在更新業務資料時，手工更新時間戳字段。同觸發器方式一樣，時間戳方式的性能也比較好，資料抽取相對清楚簡單，但對業務系統也有很大的傾入性（加入額外的時間戳字段），特别是對不支援時間戳的自動更新的資料庫，還要求業務系統進行額外的更新時間戳操作。另外，無法捕獲對時間戳以前資料的delete操作,在資料準确性上受到了一定的限制。
c.全表比對：典型的全表比對的方式是采用MD5校驗碼。ETL工具事先為要抽取的表建立一個結構類似的MD5臨時表，該臨時表記錄源表主鍵以及根據所有字段的資料計算出來的MD5校驗碼。每次進行資料抽取時，對源表和MD5臨時表進行MD5校驗碼的比對，進而決定源表中的資料是新增、修改還是删除，同時更新MD5校驗碼。MD5方式的優點是對源系統的傾入性較小（僅需要建立一個MD5臨時表），但缺點也是顯而易見的，與觸發器和時間戳方式中的主動通知不同，MD5方式是被動的進行全表資料的比對，性能較差。當表中沒有主鍵或唯一列且含有重複記錄時，MD5方式的準确性較差。
d.日志對比：通過分析資料庫自身的日志來判斷變化的資料。Oracle的改變資料捕獲（CDC，ChangedDataCapture）技術是這方面的代表。CDC特性是在Oracle9i資料庫中引入的。CDC能夠幫助你識别從上次抽取之後發生變化的資料。利用CDC，在對源表進行insert、update或delete等操作的同時就可以提取資料，并且變化的資料被儲存在資料庫的變化表中。這樣就可以捕獲發生變化的資料，然後利用資料庫視圖以一種可控的方式提供給目标系統。CDC體系結構基于釋出者/訂閱者模型。釋出者捕捉變化資料并提供給訂閱者。訂閱者使用從釋出者那裡獲得的變化資料。通常，CDC系統擁有一個釋出者和多個訂閱者。釋出者首先需要識别捕獲變化資料所需的源表。然後，它捕捉變化的資料并将其儲存在特别建立的變化表中。它還使訂閱者能夠控制對變化資料的通路。訂閱者需要清楚自己感興趣的是哪些變化資料。一個訂閱者可能不會對釋出者釋出的所有資料都感興趣。訂閱者需要建立一個訂閱者視圖來通路經釋出者授權可以通路的變化資料。CDC分為同步模式和異步模式，同步模式實時的捕獲變化資料并存儲到變化表中，釋出者與訂閱都位于同一資料庫中。異步模式則是基于Oracle的流複制技術。
============================================================================================================
ETL之增量抽取方式
1、觸發器方式
觸發器方式是普遍采取的一種增量抽取機制。該方式是根據抽取要求，在要被抽取的源表上建立插入、修改、删除3個觸發器，每當源表中的資料發生變化，就被相應的觸發器将變化的資料寫入一個增量日志表，ETL的增量抽取則是從增量日志表中而不是直接在源表中抽取資料，同時增量日志表中抽取過的資料要及時被标記或删除。為了簡單起見，增量日志表一般不存儲增量資料的所有字段資訊，而隻是存儲源表名稱、更新的關鍵字值和更新操作類型(KNSEN、UPDATE或DELETE)，ETL增量抽取程序首先根據源表名稱和更新的關鍵字值，從源表中提取對應的完整記錄，再根據更新操作類型，對目标表進行相應的處理。
例如，對于源表為ORACLE類型的資料庫，采用觸發器方式進行增量資料捕獲的過程如下：
這樣，對表T的所有DML操作就記錄在增量日志表DML_LOG中，注意增量日志表中并沒有完全記錄增量資料本身，隻是記錄了增量資料的來源。進行增量ETL時，隻需要根據增量日志表中的記錄情況，反查源表得到真正的增量資料。
SQL代碼
(1)建立增量日志表DML_LOG：
CREATE TABLE DML_LOG(
ID NUMBER PRIMARY KEY, ／／自增主鍵
TABLE NAME VARCHAR2(200)． ／／源表名稱
RECORD ID NUMBER, ／／源表增量記錄的主鍵值
DML TYPE CH根(1)。∥增量類型，I表示新增：U表示更新；D表示删除
EXECUTE DATE DATE ／／發生時間
)；
(2)為DML_LOG建立一個序列SEQ_DML_LOG上，以便觸發器寫增量日志表時生成ID值。
(3)針對要監聽的每一張表，建立一個觸發器，例如對表TEST建立觸發器如下：
CREATE OR REPLACE TRIGGER T BEFORE INSERT OR UPDATE
OR DELETE ON T FOR EACH ROW
DECLARE 1 DML TYPE VARCHAR2(1)；
BEGIN
IF INSERTING THEN L_DML TYPE：= I’；
ELSIF UPDATING THEN I_DML_TYPE：=。TY；
ELSIF DELETING THEN L_DML_TYPE：= D’；
ENDIF；
IF DELETING THEN
INSERT INTO DML_LOG(ID，TABLE_NAME，RECORD—
ID，EXECUTE_DATE，DMLJYPE)
VALUES(SEQ_DML_LOG．NEXTVAL，’TEST ，：OLD．ID，SYSDATE，
L_DML_TYPE)；
ELSE
INSERT INTO DML_LOG(ID，TABLE_NAME，RECORD_
ID，EXECUTE_DATE，DMLJYPE)
VALUES(SEQ_DML_LOG．NEXTVAL，。TEST ，：NEW．ID，SYSDATE，L
TIROL_TYPE)；
ENDIF；
END；
2、時間戳方式
時間戳方式是指增量抽取時，抽取程序通過比較系統時間與抽取源表的時間戳字段的值來決定抽取哪些資料。這種方式需要在源表上增加一個時間戳字段，系統中更新修改表資料的時候，同時修改時間戳字段的值。有的資料庫(例如SQL SERVER)的時間戳支援自動更新，即表的其它字段的資料發生改變時，時間戳字段的值會被自動更新為記錄改變的時刻。在這種情下，進行ETL實施時就隻需要在源表加上時間戳字段就可以了。對于不支援時間戳自動更新的資料庫，這就要求業務系統在更新業務資料時，通過程式設計的方式手工更新時間戳字段。使用時間戳方式可以正常捕獲源表的插入和更新操作，但對于删除操作則無能為力，需要結合其它機制才能完成。
更新時間戳：
3、全表删除插入方式
全表删除插入方式是指每次抽取前先删除目标表資料，抽取時全新加載資料。該方式實際上将增量抽取等同于全量抽取。對于資料量不大，全量抽取的時間代價小于執行增量抽取的算法和條件代價時，可以采用該方式。
4、全表比對方式
全表比對即在增量抽取時，ETL程序逐條比較源表和目标表的記錄，将新增和修改的記錄讀取出來。優化之後的全部比對方式是采用MD5校驗碼，需要事先為要抽取的表建立一個結構類似的MD5臨時表，該臨時表記錄源表的主鍵值以及根據源表所有字段的資料計算出來的(BI)
MD5校驗碼，每次進行資料抽取時，對源表和MD5臨時表進行MD5校驗碼的比對，如有不同，進行UPDATE操作：如目标表沒有存在該主鍵值，表示該記錄還沒有，則進行INSERT操作。
然後，還需要對在源表中已不存在而目标表仍保留的主鍵值，執行DELETE操作。
5、日志表方式
對于建立了業務系統的生産資料庫，可以在資料庫中建立業務日志表，當特定需要監控的業務資料發生變化時，由相應的業務系統程式子產品來更新維護日志表内容。增量抽取時，
通過讀日志表資料決定加載哪些資料及如何加載。日志表的維護需要由業務系統程式用代碼來完成。
6、系統日志分析方式
該方式通過分析資料庫自身的日志來判斷變化的資料。關系犁資料庫系統都會将所有的DML操作存儲在日志檔案中，以實作資料庫的備份和還原功能。ETL增暈抽取程序通過對資料庫的日志進行分析，提取對相關源表在特定時間後發生的DML操作資訊，就可以得知自上次抽取時刻以來該表的資料變化情況，進而指導增量抽取動作。有些資料庫系統提供了通路日志的專用的程式包(例如ORACLE的LOGMINDER)，使資料庫日志的分析工作得到大大簡化。
、特定資料庫方式（ORACLE）
以下介紹常見的針對特有資料庫系統的增景抽取方式。
7.1 ORACLE改變資料捕獲(CHANGEDDATACAPTURE，CDC)方式：ORACLECDC特性是在ORAELE9I資料庫中引入的。CDC能夠幫助識别從上次抽取之後發生變化的資料。
利用CDC，在對源表進行INSERT、UPCLATE或DELETE等操作的同時就可以提取資料，并且變化的資料被儲存在資料庫的變化表中。這樣就可以捕獲發生變化的資料，然後利用資料庫視圖以一種可控的方式提供給ETL抽取程序，作為增量抽取的依據。CDC方式對源表資料變化情況的捕獲有兩種方式：同步CDC和異步CDC。同步CDC使用源資料庫觸發器來捕獲變更的資料。這種方式是實時的，沒有任何延遲。當DML操作送出後，變更表中就産生了變更資料。異步CDC使用資料庫重做日志(REDOLOG)檔案，在源資料庫發生變更以後，才進行資料捕獲。
7.2 ORACLE閃回查詢方式：ORACLE9I以上版本的資料庫系統提供了閃回查詢機制，允許使用者查詢過去某個時刻的資料庫狀态。這樣，抽取程序可以将源資料庫的(BI)
目前狀态和上次抽取時刻的狀态進行對比，快速得出源表資料記錄的變化情況。
8、比較和分析
可見，ETL在進行增量抽取操作時，有以上各種機制可以選擇。現從相容性、完備性、性能和侵入性3個方面對這些機制的優劣進行比較分析。資料抽取需要面對的源系統，并不一定都是關系型資料庫系統。某個ETL過程需要從若幹年前的遺留系統中抽取EXCEL或者CSV文本資料的情形是經常發牛的。這時，所有基于關系型資料庫産品的增量機制都無法工作，時間戳方式和全表比對方式可能有一定的利用價值，在最壞的情況下，隻有放棄增量抽取的思路，轉而采用全表删除插入方式。完備性方面，時間戳方式不能捕獲DELETE操作，需要結合其它方式一起使用。增量抽取的性能因素表現在兩個方面，一是抽取程序本身的性能，二是對源系統性能的負面影響。觸發器方式、日志表方式以及系統日志分析方式由于不需要在抽取過程中執行比對步驟，是以增量抽取的性能較佳。全表比對方式需要經過複雜的比對過程才能識别出更改的記錄，抽取性能最差。在對源系統的性能影響方面，觸發器方式由于是直接在源系統業務表上建立觸發器，同時寫臨時表，對于頻繁操作的業務系統可能會有一定的性能損失，尤其是當業務表上執行批量操作時，行級觸發器将會對性能産生嚴重的影響；同步CDC方式内部采用觸發器的方式實作，也同樣存在性能影響的問題；全表比對方式和日志表方式對資料源系統資料庫的性能沒有任何影響，隻是它們需要業務系統進行額外的運算和資料庫操作，會有少許的時間損耗；時間戳方式、系統日志分析方式以及基于系統日志分析的方式(異步CDC和閃回查詢)對資料庫性能的影響也是非常小的。對資料源系統的侵入性是指業務系統是否要為實作增抽取機制做功能修改和額外操作，在這一點上，時間戳方式值得特别關注該方式除了要修改資料源系統表結構外，對于不支援時間戳字段自動更新的關系型資料庫産品，還必須要修改業務系統的功能，讓它在源表T執行每次操作時都要顯式的更新表的時間戳字段，這在ETL實施過程中必須得到資料源系統高度的配合才能達到，并且在多數情況下這種要求在資料源系統看來是比較“過分”的，這也是時間戳方式無法得到廣泛運用的主要原因。另外，觸發器方式需要在源表上建立觸發器，這種在某些場合中也遭到拒絕。還有一些需要建立臨時表的方式，例如全表比對和日志表方式。可能因為開放給ETL程序的資料庫權限的限制而無法實施。同樣的情況也可能發生在基于系統日志分析的方式上，因為大多數的資料庫産品隻允許特定組的使用者甚至隻有DBA才能執行日志分析。閃回查詢在侵入性方面的影響是最小的.
在我從事的ETL工作中，大部分都是采用時間戳方式進行增量抽取，如銀行業務，VT新開戶，使用時間戳方式，可以在固定時間内，組織人員進行資料抽取，進行整合後，加載到目标系統。而觸發器方式，雖然可以自動進行抽取，但是執行頻率過多，影響效率！第三種方式對于大資料量來說是非常不可取的，尤其是對于一些銀行、電信行業，因為資料全量比較大，是以進行增量校對是比較耗時的，總起來說，個人趨向使用時間戳方式進行增量抽取，當然具體情況要看工作的使用環境！
==============================================================================================================
時間戳方式的增量：
這個實驗主要思想是在建立資料庫表的時候，
通過增加一個額外的字段，也就是時間戳字段，
例如在同步表 tt1 和表 tt2 的時候，
通過檢查那個表是最新更新的，那個表就作為新表，而另外的表最為舊表被新表中的資料進行更新。
實驗資料如下：
mysql database 5.1
test.tt1( id int primary key , name varchar(50) );
mysql.tt2( id int primary key, name varchar(50) );
快照表，可以将其存放在test資料庫中，
同樣可以為了簡便，可以将其建立為temporary 表類型。
資料如圖 kettle-1
kettle-1
============================================================
主流程如圖 kettle-2
kettle-2
在prepare中，向 tt1，tt2 表中增加 時間戳字段，
由于tt1，tt2所在的資料庫是不同的，是以分别建立兩個資料庫的連接配接。
prepare
kettle-3
在執行這個job之後，就會在資料庫查詢的時候看到下面的字段：
kettle-4
然後， 我們來對tt1表做一個 insert 操作 一個update操作吧~
kettle-5
在原表上無論是insert操作還是update操作，對應的updateTime都會發生變更。
如果tt1 表 和 tt2 表中 updateTime 字段為最新時間的話，則說明該表是新表 。
下面隻要是對應main_thread的截圖：
kettle-6
在這裡介紹一下Main的層次：
Main
START
Main.prepare
Main.main_thread
{
START
main_thread.create_tempTable
main_thread.insert_tempTable
main_thread.tt1_tt2_syn
SUCCESS
}
Main.finish
SUCCESS
在main_thread中的過程是這樣的：
作為一個局部的整體，使它每隔200s内進行一次循環，
這樣的話，如果在其中有指定的表 tt1 或是 tt2 對應被更新或是插入的話，
該表中的updateTime字段就會被捕捉到，并且進行同步。
如果沒有更新出現，則會走switch的 default 路線對應的是write to log.
繼續循環。
首先建立一個快照表，然後将tt1，tt2表中的最大(最新)時間戳的值插入到快照表中。
然後，通過一個transformation來判斷那個表的updateTime值最新，
來選擇對應是 tt1表來更新 tt2 還是 tt2 表來更新 tt1 表；
main_thread.create_tempTable.JOB:
main_thread.insert_tempTable.Job:
PS： 對于第二個SQL 應該改成(不修改會出錯的)
set @var1 = ( select MAX(updatetime) from tt2);
insert into test.temp values ( 2 , @var1 ) ;
因為conn對應的是連接配接mysql（資料庫執行個體名稱），
但是我們把快照表和tt1 表都存到了test（資料庫執行個體名稱）裡面。
在上面這個圖中對應的語句是想實作，在temp表中插入兩行記錄元組。
其中id為1 的元組對應的temp.lastTime 字段 是 從tt1 表中選出的 updateTime 值為最新的，
id 為2的元組對應的 temp.lastTime 字段 是 從 tt2 表中選出的 updateTime 值為最新的 字段。
當然 ， id 是用來給後續 switch 操作提供參考的，用于标示最新 updateTime 是來自 tt1 還是 tt2，
同樣也可以使用 tableName varchar(50) 這種字段 來存放 最新updateTime 對應的 資料庫.資料表的名稱也可以的。
main_thread.tt1_tt2_syn.Transformation:
首先，建立連接配接 test 資料庫的 temp 表的連接配接，
選擇 temp表中 對應 lastTime 值最新的所在的記錄
所對應的 id 号碼。
首先将temp中 lastTime 字段進行 降序排列，
然後選擇id ， 并且将選擇記錄僅限定成一行。
然後根據id的值進行 switch選擇。
在這裡LZ很想使用，SQL Executor，
但是它無法傳回對應的id值。
但是表輸入可以傳回對應的id值，
并被switch接收到。
下圖是對應 switch id = 1 的時候：即 tt1 更新 tt2
注意合并行比較 的新舊資料源 的選擇
和Insert/Update 中的Target table的選擇
下圖是對應 switch id = 2 的時候：即 tt2 更新 tt1
注意合并行比較 的新舊資料源 的選擇
和Insert/Update 中的Target table的選擇
但是考慮到增加一個 column 會浪費很多的空間，
是以咋最終結束同步之後使用 finish操作步驟來将該 updateTime這個字段進行删除操作即可。
這個與Main中的prepare的操作是相對應的。
Main.finish
這樣的話，實驗環境已經搭建好了，
接下來進行，實驗的資料測試了，寫到下一個部落格中。
當然，觸發器也是一種同步的好方法，寫到後續部落格中吧~
時間戳的方式相比于觸發器，較為簡單并且通用，
但是 資料庫表中的時間戳字段，很費空間，并且無法對應删除操作，
也就是說 表中删除一行記錄， 該表應該作為新表來更新其餘表，但是由于記錄删除 時間戳無所依附是以無法記錄到。
posted @ 2014-10-26 00:12 Bobby0322 閱讀( 476) 評論( 1) 編輯 收藏