這篇文章主要介紹了ETL加載策略的示例分析，具有一定借鑒價(jià)值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

在埇橋區(qū)等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強(qiáng)發(fā)展的系統(tǒng)性、市場前瞻性、產(chǎn)品創(chuàng)新能力，以專注、極致的服務(wù)理念，為客戶提供成都網(wǎng)站設(shè)計(jì)、做網(wǎng)站 網(wǎng)站設(shè)計(jì)制作按需定制,公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),品牌網(wǎng)站制作,網(wǎng)絡(luò)營銷推廣,成都外貿(mào)網(wǎng)站制作,埇橋區(qū)網(wǎng)站建設(shè)費(fèi)用合理。

ETL 數(shù)據(jù)加載機(jī)制概述

ETL 是數(shù)據(jù)抽取（Extract）、轉(zhuǎn)換（Transform）、加載（Load）的簡寫，它的功能是從數(shù)據(jù)源抽取出所需的數(shù)據(jù)，經(jīng)過數(shù)據(jù)清洗和轉(zhuǎn)換，最終按照預(yù)先定義好的數(shù)據(jù)倉庫模型，將數(shù)據(jù)加載到數(shù)據(jù)倉庫中去，是構(gòu)建數(shù)據(jù)倉庫最重要的一步。

在數(shù)據(jù)加載到數(shù)據(jù)庫的過程中，分為全量加載（更新）和增量加載（更新）。

• 全量加載：全表刪除后再進(jìn)行數(shù)據(jù)加載的方式。

• 增量加載：目標(biāo)表僅更新源表變化的數(shù)據(jù)。

全量加載從技術(shù)角度上說，比增量加載要簡單很多。一般只要在數(shù)據(jù)加載之前，清空目標(biāo)表，再全量導(dǎo)入源表數(shù)據(jù)即可。但是由于數(shù)據(jù)量，系統(tǒng)資源和數(shù)據(jù)的實(shí)時(shí)性的要求，很多情況下我們都需要使用增量加載機(jī)制。

增量加載難度在于必須設(shè)計(jì)正確有效的方法從數(shù)據(jù)源中抽取變化的數(shù)據(jù)以及雖然沒有變化，但受到變化數(shù)據(jù)影響的源數(shù)據(jù)，同時(shí)將這些變化的和未變化但受影響的數(shù)據(jù)在完成相應(yīng)的邏輯轉(zhuǎn)換后更新到數(shù)據(jù)倉庫中。優(yōu)秀的增量抽取機(jī)制不但要求 ETL 能夠?qū)I(yè)務(wù)系統(tǒng)中的變化數(shù)據(jù)按一定的頻率準(zhǔn)確地捕獲到，同時(shí)不能對業(yè)務(wù)系統(tǒng)造成太大的壓力，影響現(xiàn)有業(yè)務(wù)，而且要滿足數(shù)據(jù)轉(zhuǎn)換過程中的邏輯要求和加載后目標(biāo)表的數(shù)據(jù)正確性，同時(shí)數(shù)據(jù)加載的性能和作業(yè)失敗后的可恢復(fù)重啟的易維護(hù)性也是非常重要的考量方面。

增量抽取機(jī)制比較適用于以下特點(diǎn)的數(shù)據(jù)表：

• 數(shù)據(jù)量巨大的目標(biāo)表。

• 源表變化數(shù)據(jù)比較規(guī)律，例如按時(shí)間序列增長或減少。

• 源表變化數(shù)據(jù)相對數(shù)據(jù)總量較小。

• 目標(biāo)表需要記錄過期信息或者冗余信息

• 業(yè)務(wù)系統(tǒng)能直接提供增量（delta）數(shù)據(jù)

如果每次抽取都有超過 1/4 的業(yè)務(wù)源數(shù)據(jù)需要更新，就應(yīng)該考慮更改 ETL 的加載方法，由增量抽取改為全量抽取，另外全量抽取對于數(shù)據(jù)量較小，更新頻率較低的系統(tǒng)也比較適用。

ETL 數(shù)據(jù)增量加載機(jī)制：

ETL 增量加載在方式上主要包括：

• 系統(tǒng)日志分析方式

• 觸發(fā)器方式

• 時(shí)間戳方式

• 全表比對方式

• 源系統(tǒng)增量（delta）數(shù)據(jù)直接或者轉(zhuǎn)換后加載

源表與目標(biāo)表一對一增量更新

這類更新主要常見于生產(chǎn)庫與備份庫之間，或者是面對不同數(shù)據(jù)集市的數(shù)據(jù)分發(fā)業(yè)務(wù)。即源表和目標(biāo)表的數(shù)據(jù)對應(yīng)關(guān)系十分簡單，數(shù)據(jù)完全相同或者是源表僅作部分?jǐn)?shù)據(jù)過濾。

對于這種類型的增量更新，可以采用系統(tǒng)日志分析方式。

系統(tǒng)日志分析方式

該方式通過分析數(shù)據(jù)庫自身的日志來判斷變化的數(shù)據(jù)。關(guān)系型數(shù)據(jù)庫系統(tǒng)都會(huì)將所有的 DML 操作存儲(chǔ)在日志文件中，以實(shí)現(xiàn)數(shù)據(jù)庫的備份和還原功能。ETL 增量抽取進(jìn)程通過對數(shù)據(jù)庫的日志進(jìn)行分析，提取對相關(guān)源表在特定時(shí)間后發(fā)生的 DML 操作信息，就可以得知自上次抽取時(shí)刻以來該表的數(shù)據(jù)變化情況，從而指導(dǎo)增量抽取動(dòng)作。

系統(tǒng)日志分析方式優(yōu)缺點(diǎn)

優(yōu)點(diǎn)：實(shí)現(xiàn)方式簡單。隔離性好，如果發(fā)生加載失敗，不會(huì)影響源表及其事務(wù)的級聯(lián)失敗。 

缺點(diǎn)：日志表維護(hù)需要由OLTP系統(tǒng)完成，需要對OLTP系統(tǒng)業(yè)務(wù)操作程序作修改，記錄日志信息。日志表維護(hù)較為麻煩，對原有系統(tǒng)有較大影響。

觸發(fā)器方式

觸發(fā)器增量抽取主要有 2 種方式：

• 直接進(jìn)行數(shù)據(jù)加載

• 利用增量日志表進(jìn)行增量加載

直接進(jìn)行數(shù)據(jù)加載方式是創(chuàng)建一個(gè)與源表結(jié)構(gòu)類似的臨時(shí)表，然后創(chuàng)建一個(gè)三種類型的觸發(fā)器，分別對應(yīng) insert , update , delete 操作。每當(dāng)源表有數(shù)據(jù)變動(dòng)的時(shí)候，利用觸發(fā)器將變化的數(shù)據(jù)填入此臨時(shí)表表中。最后通過維護(hù)這個(gè)臨時(shí)表，在進(jìn)行 ETL 過程的時(shí)候，將目標(biāo)表中相應(yīng)的數(shù)據(jù)進(jìn)行修改。ETL 過程結(jié)束后，清空此臨時(shí)表。

利用增量日志表進(jìn)行增量加載則是不直接抽取源表數(shù)據(jù)，僅僅是將操作內(nèi)容寫入一張?jiān)隽咳罩颈砝铮ㄍ瑫r(shí)增量日志表中抽取過的數(shù)據(jù)要及時(shí)被標(biāo)記或刪除）。增量日志表一般不存儲(chǔ)增量數(shù)據(jù)的所有字段信息，而只是存儲(chǔ)源表名稱、更新的關(guān)鍵字值和更新操作類型 (insert、update 或 delete)，ETL 增量抽取進(jìn)程首先根據(jù)源表名稱和更新的關(guān)鍵字值，從源表中提取對應(yīng)的完整記錄，再根據(jù)更新操作類型，對目標(biāo)表進(jìn)行相應(yīng)的處理。

由于增量日志表中并沒有完全記錄增量數(shù)據(jù)本身，只是記錄了增量數(shù)據(jù)的來源。進(jìn)行增量 ETL 時(shí)，只需要根據(jù)增量日志表中的記錄情況，反查源表得到真正的增量數(shù)據(jù)。

觸發(fā)器方式優(yōu)缺點(diǎn)

優(yōu)點(diǎn)：數(shù)據(jù)抽取的性能較高。

缺點(diǎn)：要求業(yè)務(wù)表建立觸發(fā)器，對業(yè)務(wù)系統(tǒng)有影響，需要對用戶數(shù)據(jù)庫進(jìn)行修改，不能對多表和視圖進(jìn)行操作，如果目標(biāo)表發(fā)生錯(cuò)誤會(huì)造成級聯(lián)事務(wù)失敗，這在生產(chǎn)系統(tǒng)無法忍受，另外一個(gè)缺點(diǎn)是如果觸發(fā)器運(yùn)行過程中產(chǎn)生問題，有時(shí)需要重新加載整個(gè)表來恢復(fù)加載作業(yè)的運(yùn)行。 這類方法適用于一對一且業(yè)務(wù)邏輯不復(fù)雜的表的增量更新。

源表與目標(biāo)表一對一增量更新，但需要進(jìn)行一些數(shù)據(jù)轉(zhuǎn)換操作

有些源表與目標(biāo)表之間不是簡單的數(shù)據(jù)對應(yīng)關(guān)系，可能兩者之間有不同的數(shù)據(jù)結(jié)構(gòu)，需要進(jìn)行一些數(shù)據(jù)轉(zhuǎn)換，例如匯總，行轉(zhuǎn)列等操作后才能進(jìn)行更新。上文提到的日志分析方式 和觸發(fā)器方式就不太適用，這時(shí)采用時(shí)間戳的方法比較合適。

時(shí)間戳方式

實(shí)現(xiàn)原理是指增量抽取時(shí)，抽取進(jìn)程通過比較系統(tǒng)時(shí)間或者源表上次抽取時(shí)的最大時(shí)間戳與抽取源表的時(shí)間戳字段的值來決定抽取哪些數(shù)據(jù)。這種方式需要在源表上增加一個(gè)時(shí)間戳字段，系統(tǒng)中更新修改表數(shù)據(jù)的時(shí)候，同時(shí)修改時(shí)間戳字段的值。

采用時(shí)間戳進(jìn)行增量更新時(shí)需要源表有相應(yīng)的時(shí)間戳字段，所以對于沒有時(shí)間戳的源表需要進(jìn)行相應(yīng)業(yè)務(wù)需要改造，增加必要的時(shí)間戳字段。

時(shí)間戳方式優(yōu)缺點(diǎn)

優(yōu)點(diǎn)：實(shí)現(xiàn)邏輯簡單，可以大批量更新數(shù)據(jù)。不僅可以對一張?jiān)幢磉M(jìn)行數(shù)據(jù)捕獲，也可以對多張?jiān)幢淼脑隽繑?shù)據(jù)進(jìn)行捕獲。

缺點(diǎn)：

a. 使用時(shí)間戳方式可以正常捕獲源表的插入和更新操作，但對于刪除操作則無能為力，需要結(jié)合其它機(jī)制才能完成。這時(shí)可以設(shè)計(jì)一張和源表相同的數(shù)據(jù)表記錄源表中刪除的數(shù)據(jù)，同時(shí)記錄刪除時(shí)的時(shí)間戳，在對目標(biāo)表更新時(shí)同時(shí)讀取這張表的記錄，進(jìn)行刪除操作。

或者在目標(biāo)表通過打標(biāo)記的方式（update active_flag=1）進(jìn)行邏輯刪除。

b. 如果系統(tǒng)自動(dòng)更新或手工更新時(shí)間戳字段時(shí)可能會(huì)出現(xiàn)數(shù)據(jù)延遲現(xiàn)象產(chǎn)生。

c. 如果采用系統(tǒng)自動(dòng)更新時(shí)間戳的方式，需要特別注意在 Load 整個(gè)表的操作時(shí)，要保持此字段數(shù)值不變，否則時(shí)間戳將會(huì)自動(dòng)加載為 Load 的最新時(shí)間，影響整個(gè)表的增量更新邏輯。

d. 應(yīng)用起來有部分局限性。即源表都需要有時(shí)間戳字段。如果部分源表（或參考表）無時(shí)間戳字段，且源表有部分字段更新時(shí)（常見于維度表的定義更新，如地區(qū)維度，產(chǎn)品維度等），則面臨歷史數(shù)據(jù)的更新問題。此時(shí)采用時(shí)間戳方法只能更新新增數(shù)據(jù)的維度定義，而無法更新歷史數(shù)據(jù)。這時(shí)一般需要采用 SQL 語句或者下文介紹的全表對比方式進(jìn)行歷史數(shù)據(jù)更新?；蛘哒{(diào)整時(shí)間戳范圍，做全表數(shù)據(jù)的刷新。這種情況需要對目標(biāo)表的實(shí)時(shí)性要求不高，可以在系統(tǒng)空閑時(shí)進(jìn)行處理。

e. 由于時(shí)間戳增量更新經(jīng)常應(yīng)用于業(yè)務(wù)邏輯復(fù)雜的 ETL 過程，在面對源表里有多條記錄匯總至目標(biāo)表一條記錄情況時(shí)，例如源表里記錄 R1，R2，R3（主鍵均相同），根據(jù)主鍵匯總至目標(biāo)表生成記錄 T1，則需要注意如果時(shí)間窗口內(nèi)有源表一條數(shù)據(jù)發(fā)生了數(shù)據(jù)變動(dòng)（R2），則需要利用 R2 的主鍵將源表中的 R1，R3 也同時(shí)捕獲出來（雖然 R1，R3 在時(shí)間窗口內(nèi)沒有數(shù)據(jù)變動(dòng)），重新進(jìn)行匯總，更新目標(biāo)表中的 T1 記錄，以避免數(shù)據(jù)丟失或者數(shù)據(jù)邏輯不正確問題。

源表與目標(biāo)表多對一增量更新

日常的 ETL 更新中，還會(huì)遇到目標(biāo)表的數(shù)據(jù)來源來自于多張?jiān)幢?，通過關(guān)鍵字段的拼接進(jìn)行更新操作。如果多張?jiān)幢矶加袝r(shí)間戳字段，可以利用時(shí)間戳進(jìn)行增量更新，另外還可以采用全表比對的方式進(jìn)行增量更新。

全表比對方式

全表比對即在增量抽取時(shí)，ETL 進(jìn)程逐條比較源表和目標(biāo)表的記錄，將新增和修改的記錄讀取出來。優(yōu)化之后的全部比對方式是采用 MD5 校驗(yàn)碼，需要事先為要抽取的表建立一個(gè)結(jié)構(gòu)類似的臨時(shí)表，該臨時(shí)表記錄源表的主鍵值以及根據(jù)源表所有字段的數(shù)據(jù)計(jì)算出來的 MD5 校驗(yàn)碼，每次進(jìn)行數(shù)據(jù)抽取時(shí)，對源表和 MD5 臨時(shí)表進(jìn)行 MD5 校驗(yàn)碼的比對，如有不同，進(jìn)行 UPDATE 操作：如目標(biāo)表沒有存在該主鍵值，表示該記錄還沒有，則進(jìn)行 INSERT 操作。然后，還需要對在源表中已不存在而目標(biāo)表仍保留的主鍵值，執(zhí)行 DELETE 操作。

全表比對方式優(yōu)缺點(diǎn)

優(yōu)點(diǎn)是適用于：

a. 涉及多張?jiān)幢淼某槿∨c轉(zhuǎn)換，業(yè)務(wù)邏輯復(fù)雜的增量更新。

b. 源表無時(shí)間戳字段，無法采用時(shí)間戳方式進(jìn)行增量更新。

缺點(diǎn)是采用全表比對，對于大數(shù)據(jù)量數(shù)據(jù)表，效率不高。

總結(jié)與分析

表 1. 各類增量抽取方式比較表

在選擇合適的增量加載機(jī)制時(shí)，需要注意的方面包括：

1. 增量抽取的容災(zāi)能力。主要是指增量加載更新方式如果運(yùn)行失敗或者數(shù)據(jù)庫宕機(jī)重啟后，是否可以重新加載增量數(shù)據(jù)或者是否需要手工補(bǔ)錄數(shù)據(jù)的能力，一定程度上也影響著增量加載的可維護(hù)性。

其中系統(tǒng)日志分析方式 在失敗或者未運(yùn)行狀況下，將無法捕獲源表的增量數(shù)據(jù)，無法恢復(fù)歷史增量數(shù)據(jù)的加載，所以容災(zāi)能力最差。觸發(fā)器方式和時(shí)間戳方式，如果有相應(yīng)的日志表存在，或者增量日志未被清除，則可以在再次啟動(dòng)后重新加載歷史增量數(shù)據(jù)，容災(zāi)性一般。而全表比對方式，根據(jù)它的實(shí)現(xiàn)原理則在重新啟動(dòng)時(shí)不受任何影響，可以全面捕獲增量數(shù)據(jù)，容災(zāi)性最好。

2. 增量抽取的性能因素。表現(xiàn)在兩個(gè)方面，一是抽取進(jìn)程本身的性能，二是對源系統(tǒng)性能的負(fù)面影響。觸發(fā)器方式、日志表方式以及系統(tǒng)日志分析方式由于不需要在抽取過程中執(zhí)行比對步驟，所以增量抽取的性能較佳。全表比對方式需要經(jīng)過復(fù)雜的比對過程才能識(shí)別出更改的記錄，抽取性能最差。

如果增量更新的業(yè)務(wù)邏輯比較復(fù)雜，對機(jī)器性能要求較高，在進(jìn)行更新時(shí)可能會(huì)有影響現(xiàn)有業(yè)務(wù)邏輯表的性能，則可以評估其增量更新的重要性，如果對現(xiàn)有業(yè)務(wù)影響不大，且源表比較穩(wěn)定，客戶可以容忍暫時(shí)性的一定程度的數(shù)據(jù)不一致。則可以考慮定時(shí)進(jìn)行增量更新。例如更新程序每日凌晨運(yùn)行一次，甚至每周空閑時(shí)間運(yùn)行一次。

另外業(yè)界普遍常用的方式是源系統(tǒng)在它的閑時(shí)抽取增量 delta 數(shù)據(jù)文件，數(shù)據(jù)倉庫系統(tǒng)得到 delta 文件后再加載到倉庫系統(tǒng)。在數(shù)據(jù)倉庫架構(gòu)設(shè)計(jì)中，生產(chǎn)系統(tǒng)層和 ODS 層的直接數(shù)據(jù)交互即為此類型。

3. 觸發(fā)器方式需要在源表上建立觸發(fā)器，這種在某些應(yīng)用場合中遭到拒絕。還有一些需要建立臨時(shí)表的方式，例如全表比對和日志表方式?？赡芤?yàn)殚_放給 ETL 進(jìn)程的數(shù)據(jù)庫權(quán)限的限制而無法實(shí)施。同樣的情況也可能發(fā)生在基于系統(tǒng)日志分析的方式上，因?yàn)榇蠖鄶?shù)的數(shù)據(jù)庫產(chǎn)品只允許特定組的用戶甚至只有 DBA 才能執(zhí)行日志分析。例如 DB2 的 Replication 功能只能由 DBA 維護(hù)與修改，普通用戶無法操作。

4. 為了保證增量更新的數(shù)據(jù)準(zhǔn)確性，建議建立一系列的核查腳本，以保證源表和數(shù)據(jù)表的數(shù)據(jù)一致性問題。核查腳本可以根據(jù)輕重緩急設(shè)定其運(yùn)行頻率。

5. 數(shù)據(jù)抽取需要面對的源系統(tǒng)，并不一定都是關(guān)系型數(shù)據(jù)庫系統(tǒng)。某個(gè) ETL 過程需要從若干年前的遺留系統(tǒng)中抽取 EXCEL 或者 CSV 文本數(shù)據(jù)的情形是經(jīng)常發(fā)生的。這時(shí)，所有基于關(guān)系型數(shù)據(jù)庫產(chǎn)品的增量機(jī)制都無法工作，時(shí)間戳方式和全表比對方式可能有一定的利用價(jià)值，在最壞的情況下，只有放棄增量抽取的思路，轉(zhuǎn)而采用全表刪除插入方式（或者要求源系統(tǒng)提供增量 delta 數(shù)據(jù)文件）。

感謝你能夠認(rèn)真閱讀完這篇文章，希望小編分享的“ETL加載策略的示例分析”這篇文章對大家有幫助，同時(shí)也希望大家多多支持創(chuàng)新互聯(lián)，關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，更多相關(guān)知識(shí)等著你來學(xué)習(xí)!