ETL加載策略的示例分析

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

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

ETL 是數(shù)據(jù)抽?。‥xtract）、轉(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ù)在完成相應(yīng)的邏輯轉(zhuǎn)換后更新到數(shù)據(jù)倉庫中。優(yōu)秀的增量抽取機制不但要求 ETL 能夠?qū)I(yè)務(wù)系統(tǒng)中的變化數(shù)據(jù)按一定的頻率準(zhǔn)確地捕獲到，同時不能對業(yè)務(wù)系統(tǒng)造成太大的壓力，影響現(xiàn)有業(yè)務(wù)，而且要滿足數(shù)據(jù)轉(zhuǎn)換過程中的邏輯要求和加載后目標(biāo)表的數(shù)據(jù)正確性，同時數(shù)據(jù)加載的性能和作業(yè)失敗后的可恢復(fù)重啟的易維護(hù)性也是非常重要的考量方面。

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

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

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

• 源表變化數(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ù)增量加載機制：

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

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

• 觸發(fā)器方式

• 時間戳方式

• 全表比對方式

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

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

優(yōu)點：實現(xiàn)方式簡單。隔離性好，如果發(fā)生加載失敗，不會影響源表及其事務(wù)的級聯(liá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)建一個與源表結(jié)構(gòu)類似的臨時表，然后創(chuàng)建一個三種類型的觸發(fā)器，分別對應(yīng) insert , update , delete 操作。每當(dāng)源表有數(shù)據(jù)變動的時候，利用觸發(fā)器將變化的數(shù)據(jù)填入此臨時表表中。最后通過維護(hù)這個臨時表，在進(jìn)行 ETL 過程的時候，將目標(biāo)表中相應(yīng)的數(shù)據(jù)進(jìn)行修改。ETL 過程結(jié)束后，清空此臨時表。

利用增量日志表進(jìn)行增量加載則是不直接抽取源表數(shù)據(jù)，僅僅是將操作內(nèi)容寫入一張增量日志表里（同時增量日志表中抽取過的數(shù)據(jù)要及時被標(biāo)記或刪除）。增量日志表一般不存儲增量數(shù)據(jù)的所有字段信息，而只是存儲源表名稱、更新的關(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 時，只需要根據(jù)增量日志表中的記錄情況，反查源表得到真正的增量數(shù)據(jù)。

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

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

缺點：要求業(yè)務(wù)表建立觸發(fā)器，對業(yè)務(wù)系統(tǒng)有影響，需要對用戶數(shù)據(jù)庫進(jìn)行修改，不能對多表和視圖進(jìn)行操作，如果目標(biāo)表發(fā)生錯誤會造成級聯(lián)事務(wù)失敗，這在生產(chǎn)系統(tǒng)無法忍受，另外一個缺點是如果觸發(fā)器運行過程中產(chǎn)生問題，有時需要重新加載整個表來恢復(fù)加載作業(yè)的運行。 這類方法適用于一對一且業(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ā)器方式就不太適用，這時采用時間戳的方法比較合適。

時間戳方式

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

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

時間戳方式優(yōu)缺點

優(yōu)點：實現(xiàn)邏輯簡單，可以大批量更新數(shù)據(jù)。不僅可以對一張源表進(jìn)行數(shù)據(jù)捕獲，也可以對多張源表的增量數(shù)據(jù)進(jìn)行捕獲。

缺點：

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

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

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

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

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

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

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

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

全表比對方式

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

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

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

a. 涉及多張源表的抽取與轉(zhuǎn)換，業(yè)務(wù)邏輯復(fù)雜的增量更新。

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

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

總結(jié)與分析

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

在選擇合適的增量加載機制時，需要注意的方面包括：

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

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

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

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

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

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

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

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

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