ETL增量抽取方式

1、ETL增量抽取方式ETL增量抽取：要實(shí)現(xiàn)增量抽取，關(guān)鍵是如何準(zhǔn)確快速的捕獲變化的數(shù)據(jù)。優(yōu)秀的增量抽取機(jī)制要求ETL能夠?qū)I(yè)務(wù)系統(tǒng)中的變化數(shù)據(jù)按一定的頻率準(zhǔn)確地捕獲到，同時(shí)不能對(duì)業(yè)務(wù)系統(tǒng)造成太大的壓力，影響現(xiàn)有業(yè)務(wù)。相對(duì)全量抽取而言，增量抽取的設(shè)計(jì)更復(fù)雜1、觸發(fā)器方式觸發(fā)器方式是普遍采取的一種增量抽取機(jī)制。該方式是根據(jù)抽取要求，在要被抽取的源表上建立插入、修改、刪除3個(gè)觸發(fā)器，每當(dāng)源表中的數(shù)據(jù)發(fā)生變化，就被相應(yīng)的觸發(fā)器將變化的數(shù)據(jù)寫入一個(gè)增量日志表，ETL的增量抽取則是從增量日志表中而不是直接在源表中抽取數(shù)據(jù)，同時(shí)增量日志表中抽取過的數(shù)據(jù)要及時(shí)被標(biāo)記或刪除。為了簡(jiǎn)單起見，增量日志表一般不存儲(chǔ)增量

2、數(shù)據(jù)的所有字段信息，而只是存儲(chǔ)源表名稱、更新的關(guān)鍵字值和更新操作類型(knsen、update或delete),ETL增量抽取進(jìn)程首先根據(jù)源表名稱和更新的關(guān)鍵字值，從源表中提取對(duì)應(yīng)的完整記錄，再根據(jù)更新操作類型，對(duì)目標(biāo)表進(jìn)行相應(yīng)的處理。例如，對(duì)于源表為Oracle類型的數(shù)據(jù)庫(kù)，采用觸發(fā)器方式進(jìn)行增量數(shù)據(jù)捕獲的過程如下：這樣，對(duì)表T的所有DML操作就記錄在增量日志表DML_LOG中，注意增量日志表中并沒有完全記錄增量數(shù)據(jù)本身，只是記錄了增量數(shù)據(jù)的來源。進(jìn)行增量ETL時(shí)，只需要根據(jù)增量日志表中的記錄情況，反查源表得到真正的增量數(shù)據(jù)。Sql代碼1. (1)創(chuàng)建增量日志表DML_LOG:2.3. cr

3、eatetableDML_LOG(4.5. IDNUMBERprimarykey,/自增主鍵6.7. TABLENAMEVARCHAR2(200)/源表名稱8.9. RECORDIDNUMBER/源表增量記錄的主鍵值10.11. DMLTYPECHH(1)o/增量類型，I表示新增：U表示更新；D表示刪除12.13. EXECUTEDATEDATE/發(fā)生時(shí)間14.15. )；16.17. (2)為DML_LOG創(chuàng)建一個(gè)序列SEQ_DML_LOGk,以便觸發(fā)器寫增量日志表時(shí)生成ID值。18.19. (3)針對(duì)要監(jiān)聽的每一張表，創(chuàng)建一個(gè)觸發(fā)器，例如對(duì)表Test創(chuàng)建觸發(fā)器如下：20.21. creat

4、eOrreplacetriggerTBEFOREINSERTORUPDATE22.23. ORDELETEONTforeachrow24.25. declare1dmltypevarchar2(1);26.27. begin28.29. ifINSERTINGthenl_dmltype：=I;30.31. elsifUPDATINGthenI_dml_type:=。tY;32.33. elsifDELETINGthenl_dml_type：=D;34.35. endif;36.37. ifDELETINGthen38.39. insertintoDML_LOG(ID,TABLE_NAMEREC

5、ORD-40.41. ID,EXECUTE_DATEDMLjYPE)42.43. values(seq_dml_log.nextval,Test,:old.ID,sysdate,44.45. l_dml_type);46.47. else48.49. insertintoDML_LOG(ID,TABLE_NAMERECORD_50.51. ID,EXECUTE_DAEDMLjYPE)52.53. values(seq_dml_log.nextval,。Test,:new.ID,sysdate,L54.55. tirol_type);56.57. endif;58.59. end;2、時(shí)間戳方式

6、時(shí)間戳方式是指增量抽取時(shí)，抽取進(jìn)程通過比較系統(tǒng)時(shí)間與抽取源表的時(shí)間戳字段的值來決定抽取哪些數(shù)據(jù)。這種方式需要在源表上增加一個(gè)時(shí)間戳字段，系統(tǒng)中更新修改表數(shù)據(jù)的時(shí)候，同時(shí)修改時(shí)間戳字段的值。有的數(shù)據(jù)庫(kù)(例如SqlServer)的時(shí)間戳支持自動(dòng)更新，即表的其它字段的數(shù)據(jù)發(fā)生改變時(shí)，時(shí)間戳字段的值會(huì)被自動(dòng)更新為記錄改變的時(shí)刻。在這種情下，進(jìn)行ETL實(shí)施時(shí)就只需要在源表加上時(shí)間戳字段就可以了。對(duì)于不支持時(shí)間戳自動(dòng)更新的數(shù)據(jù)庫(kù)，這就要求業(yè)務(wù)系統(tǒng)在更新業(yè)務(wù)數(shù)據(jù)時(shí)，通過編程的方式手工更新時(shí)間戳字段。使用時(shí)間戳方式可以正常捕獲源表的插入和更新操作，但對(duì)于刪除操作則無能為力，需要結(jié)合其它機(jī)制才能完成。更新時(shí)間戳

7、：3、全表刪除插入方式全表刪除插入方式是指每次抽取前先刪除目標(biāo)表數(shù)據(jù)，抽取時(shí)全新加載數(shù)據(jù)。該方式實(shí)際上將增量抽取等同于全量抽取。對(duì)于數(shù)據(jù)量不大，全量抽取的時(shí)間代價(jià)小于執(zhí)行增量抽取的算法和條件代價(jià)時(shí)，可以采用該方式。4、全表比對(duì)方式全表比對(duì)即在增量抽取時(shí)，ETL進(jìn)程逐條比較源表和目標(biāo)表的記錄，將新增和修改的記錄讀取出來。優(yōu)化之后的全部比對(duì)方式是采用MD5校驗(yàn)碼，需要事先為要抽取的表建立一個(gè)結(jié)構(gòu)類似的MD5臨時(shí)表，該臨時(shí)表記錄源表的主鍵值以及根據(jù)源表所有字段的數(shù)據(jù)計(jì)算出來的（回）MD5校驗(yàn)碼，每次進(jìn)行數(shù)據(jù)抽取時(shí)，對(duì)源表和MD5臨時(shí)表進(jìn)行MD5校驗(yàn)碼的比對(duì)，如有不同，進(jìn)行update操作：如目標(biāo)表沒

8、有存在該主鍵值，表示該記錄還沒有，則進(jìn)行insert操作。然后，還需要對(duì)在源表中已不存在而目標(biāo)表仍保留的主鍵值，執(zhí)行delete操作。5、日志表方式對(duì)于建立了業(yè)務(wù)系統(tǒng)的生產(chǎn)數(shù)據(jù)庫(kù)，可以在數(shù)據(jù)庫(kù)中創(chuàng)建業(yè)務(wù)日志表，當(dāng)特定需要監(jiān)控的業(yè)務(wù)數(shù)據(jù)發(fā)生變化時(shí)，由相應(yīng)的業(yè)務(wù)系統(tǒng)程序模塊來更新維護(hù)日志表內(nèi)容。增量抽取時(shí)，通過讀日志表數(shù)據(jù)決定加載哪些數(shù)據(jù)及如何加載。日志表的維護(hù)需要由業(yè)務(wù)系統(tǒng)程序用代碼來完成。6、系統(tǒng)日志分析方式該方式通過分析數(shù)據(jù)庫(kù)自身的日志來判斷變化的數(shù)據(jù)。關(guān)系犁數(shù)據(jù)庫(kù)系統(tǒng)都會(huì)將所有的DML操作存儲(chǔ)在日志文件中，以實(shí)現(xiàn)數(shù)據(jù)庫(kù)的備份和還原功能。ETL增暈抽取進(jìn)程通過對(duì)數(shù)據(jù)庫(kù)的日志進(jìn)行分析，提取對(duì)相

9、關(guān)源表在特定時(shí)間后發(fā)生的DML操作信息，就可以得知自上次抽取時(shí)刻以來該表的數(shù)據(jù)變化情況，從而指導(dǎo)增量抽取動(dòng)作。有些數(shù)據(jù)庫(kù)系統(tǒng)提供了訪問日志的專用的程序包（例如Oracle的LogMinder）,使數(shù)據(jù)庫(kù)日志的分析工作得到大大簡(jiǎn)化。7、特定數(shù)據(jù)庫(kù)方式（Oracle）以下介紹常見的針對(duì)特有數(shù)據(jù)庫(kù)系統(tǒng)的增景抽取方式。7.1Oracle改變數(shù)據(jù)捕獲（changeddatacapture,CDC方式：OracleCDC特性是在Oraele9i數(shù)據(jù)庫(kù)中引入的。CDC能夠幫助識(shí)別從上次抽取之后發(fā)生變化的數(shù)據(jù)。利用CDQ在對(duì)源表進(jìn)行insert、upclate或delete等操作的同時(shí)就可以提取數(shù)據(jù)，并且變化

10、的數(shù)據(jù)被保存在數(shù)據(jù)庫(kù)的變化表中。這樣就可以捕獲發(fā)生變化的數(shù)據(jù)，然后利用數(shù)據(jù)庫(kù)視圖以一種可控的方式提供給ETL抽取進(jìn)程，作為增量抽取的依據(jù)。CDC方式對(duì)源表數(shù)據(jù)變化情況的捕獲有兩種方式：同步CDC和異步CDG同步CDC使用源數(shù)據(jù)庫(kù)觸發(fā)器來捕獲變更的數(shù)據(jù)。這種方式是實(shí)時(shí)的，沒有任何延遲。當(dāng)DML操作提交后，變更表中就產(chǎn)生了變更數(shù)據(jù)。異步CDC使用數(shù)據(jù)庫(kù)重做日志（redolog）文件，在源數(shù)據(jù)庫(kù)發(fā)生變更以后，才進(jìn)行數(shù)據(jù)捕獲。7.2Oracle閃回查詢方式：Oracle9i以上版本的數(shù)據(jù)庫(kù)系統(tǒng)提供了閃回查詢機(jī)制，允許用戶查詢過去某個(gè)時(shí)刻的數(shù)據(jù)庫(kù)狀態(tài)。這樣，抽取進(jìn)程可以將源數(shù)據(jù)庫(kù)的（BI）當(dāng)前狀態(tài)和上次

11、抽取時(shí)刻的狀態(tài)進(jìn)行對(duì)比，快速得出源表數(shù)據(jù)記錄的變化情況。8、比較和分析可見，ETL在進(jìn)行增量抽取操作時(shí)，有以上各種機(jī)制可以選擇?，F(xiàn)從兼容性、完備性、性能和侵入性3個(gè)方面對(duì)這些機(jī)制的優(yōu)劣進(jìn)行比較分析。數(shù)據(jù)抽取需要面對(duì)的源系統(tǒng)，并不一定都是關(guān)系型數(shù)據(jù)庫(kù)系統(tǒng)。某個(gè)ETL過程需要從若干年前的遺留系統(tǒng)中抽取Excel或者CSV文本數(shù)據(jù)的情形是經(jīng)常發(fā)牛的。這時(shí)，所有基于關(guān)系型數(shù)據(jù)庫(kù)產(chǎn)品的增量機(jī)制都無法工作，時(shí)間戳方式和全表比對(duì)方式可能有一定的利用價(jià)值，在最壞的情況下，只有放棄增量抽取的思路，轉(zhuǎn)而采用全表刪除插入方式。完備性方面，時(shí)間戳方式不能捕獲delete操作，需要結(jié)合其它方式一起使用。增量抽取的性能因

12、素表現(xiàn)在兩個(gè)方面，一是抽取進(jìn)程本身的性能，二是對(duì)源系統(tǒng)性能的負(fù)面影響。觸發(fā)器方式、日志表方式以及系統(tǒng)日志分析方式由于不需要在抽取過程中執(zhí)行比對(duì)步驟，所以增量抽取的性能較佳。全表比對(duì)方式需要經(jīng)過復(fù)雜的比對(duì)過程才能識(shí)別出更改的記錄，抽取性能最差。在對(duì)源系統(tǒng)的性能影響方面，觸發(fā)器方式由于是直接在源系統(tǒng)業(yè)務(wù)表上建立觸發(fā)器，同時(shí)寫臨時(shí)表，對(duì)于頻繁操作的業(yè)務(wù)系統(tǒng)可能會(huì)有一定的性能損失，尤其是當(dāng)業(yè)務(wù)表上執(zhí)行批量操作時(shí)，行級(jí)觸發(fā)器將會(huì)對(duì)性能產(chǎn)生嚴(yán)重的影響；同步CDC方式內(nèi)部采用觸發(fā)器的方式實(shí)現(xiàn)，也同樣存在性能影響的問題；全表比對(duì)方式和日志表方式對(duì)數(shù)據(jù)源系統(tǒng)數(shù)據(jù)庫(kù)的性能沒有任何影響，只是它們需要業(yè)務(wù)系統(tǒng)進(jìn)行額外

13、的運(yùn)算和數(shù)據(jù)庫(kù)操作，會(huì)有少許的時(shí)間損耗；時(shí)間戳方式、系統(tǒng)日志分析方式以及基于系統(tǒng)日志分析的方式（異步CDC和閃回查詢）對(duì)數(shù)據(jù)庫(kù)性能的影響也是非常小的。對(duì)數(shù)據(jù)源系統(tǒng)的侵入性是指業(yè)務(wù)系統(tǒng)是否要為實(shí)現(xiàn)增抽取機(jī)制做功能修改和額外操作，在這一點(diǎn)上，時(shí)間戳方式值得特別關(guān)注該方式除了要修改數(shù)據(jù)源系統(tǒng)表結(jié)構(gòu)外，對(duì)于不支持時(shí)間戳字段自動(dòng)更新的關(guān)系型數(shù)據(jù)庫(kù)產(chǎn)品，還必須要修改業(yè)務(wù)系統(tǒng)的功能，讓它在源表t執(zhí)行每次操作時(shí)都要顯式的更新表的時(shí)間戳字段，這在ETL實(shí)施過程中必須得到數(shù)據(jù)源系統(tǒng)高度的配合才能達(dá)到，并且在多數(shù)情況下這種要求在數(shù)據(jù)源系統(tǒng)看來是比較“過分”的，這也是時(shí)間戳方式無法得到廣泛運(yùn)用的主要原因。另外，觸發(fā)器方式需要在源表上建立觸發(fā)器，這種在某些場(chǎng)合中也遭到拒絕。還有一些需要建立臨時(shí)表的方式，例如全表比對(duì)和日志表方式?？赡芤?yàn)殚_放給ETL進(jìn)程的數(shù)據(jù)庫(kù)權(quán)限的限制而無法實(shí)施。同樣的情況也可能發(fā)生在基于系統(tǒng)日志分析的方式上，因?yàn)榇蠖鄶?shù)的數(shù)據(jù)庫(kù)產(chǎn)品只允許特定組的用戶甚至只有DBA才能執(zhí)行日志分析。閃回杏詢?cè)谇秩胄苑矫娴挠绊懯亲钚〉?。（旦）各種數(shù)據(jù)增量抽取機(jī)制的優(yōu)劣性綜合分析如表l所示。增量機(jī)制觸發(fā)器方式兼容性關(guān)系型數(shù)據(jù)庫(kù)完備性高抽取性能優(yōu)對(duì)源系統(tǒng)性能影響