ETL加載策略的示例分析

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

ETL 數據加載機制概述

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

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

• 全量加載：全表刪除后再進行數據加載的方式。

• 增量加載：目標表僅更新源表變化的數據。

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

增量加載難度在于必須設計正確有效的方法從數據源中抽取變化的數據以及雖然沒有變化，但受到變化數據影響的源數據，同時將這些變化的和未變化但受影響的數據在完成相應的邏輯轉換后更新到數據倉庫中。優秀的增量抽取機制不但要求 ETL 能夠將業務系統中的變化數據按一定的頻率準確地捕獲到，同時不能對業務系統造成太大的壓力，影響現有業務，而且要滿足數據轉換過程中的邏輯要求和加載后目標表的數據正確性，同時數據加載的性能和作業失敗后的可恢復重啟的易維護性也是非常重要的考量方面。

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

• 數據量巨大的目標表。

• 源表變化數據比較規律，例如按時間序列增長或減少。

• 源表變化數據相對數據總量較小。

• 目標表需要記錄過期信息或者冗余信息

• 業務系統能直接提供增量（delta）數據

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

ETL 數據增量加載機制：

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

• 系統日志分析方式

• 觸發器方式

• 時間戳方式

• 全表比對方式

• 源系統增量（delta）數據直接或者轉換后加載

源表與目標表一對一增量更新

這類更新主要常見于生產庫與備份庫之間，或者是面對不同數據集市的數據分發業務。即源表和目標表的數據對應關系十分簡單，數據完全相同或者是源表僅作部分數據過濾。

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

系統日志分析方式

該方式通過分析數據庫自身的日志來判斷變化的數據。關系型數據庫系統都會將所有的 DML 操作存儲在日志文件中，以實現數據庫的備份和還原功能。ETL 增量抽取進程通過對數據庫的日志進行分析，提取對相關源表在特定時間后發生的 DML 操作信息，就可以得知自上次抽取時刻以來該表的數據變化情況，從而指導增量抽取動作。

系統日志分析方式優缺點

優點：實現方式簡單。隔離性好，如果發生加載失敗，不會影響源表及其事務的級聯失敗。 

缺點：日志表維護需要由OLTP系統完成，需要對OLTP系統業務操作程序作修改，記錄日志信息。日志表維護較為麻煩，對原有系統有較大影響。

觸發器方式

觸發器增量抽取主要有 2 種方式：

• 直接進行數據加載

• 利用增量日志表進行增量加載

直接進行數據加載方式是創建一個與源表結構類似的臨時表，然后創建一個三種類型的觸發器，分別對應 insert , update , delete 操作。每當源表有數據變動的時候，利用觸發器將變化的數據填入此臨時表表中。最后通過維護這個臨時表，在進行 ETL 過程的時候，將目標表中相應的數據進行修改。ETL 過程結束后，清空此臨時表。

利用增量日志表進行增量加載則是不直接抽取源表數據，僅僅是將操作內容寫入一張增量日志表里（同時增量日志表中抽取過的數據要及時被標記或刪除）。增量日志表一般不存儲增量數據的所有字段信息，而只是存儲源表名稱、更新的關鍵字值和更新操作類型 (insert、update 或 delete)，ETL 增量抽取進程首先根據源表名稱和更新的關鍵字值，從源表中提取對應的完整記錄，再根據更新操作類型，對目標表進行相應的處理。

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

觸發器方式優缺點

優點：數據抽取的性能較高。

缺點：要求業務表建立觸發器，對業務系統有影響，需要對用戶數據庫進行修改，不能對多表和視圖進行操作，如果目標表發生錯誤會造成級聯事務失敗，這在生產系統無法忍受，另外一個缺點是如果觸發器運行過程中產生問題，有時需要重新加載整個表來恢復加載作業的運行。 這類方法適用于一對一且業務邏輯不復雜的表的增量更新。

源表與目標表一對一增量更新，但需要進行一些數據轉換操作

有些源表與目標表之間不是簡單的數據對應關系，可能兩者之間有不同的數據結構，需要進行一些數據轉換，例如匯總，行轉列等操作后才能進行更新。上文提到的日志分析方式 和觸發器方式就不太適用，這時采用時間戳的方法比較合適。

時間戳方式

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

采用時間戳進行增量更新時需要源表有相應的時間戳字段，所以對于沒有時間戳的源表需要進行相應業務需要改造，增加必要的時間戳字段。

時間戳方式優缺點

優點：實現邏輯簡單，可以大批量更新數據。不僅可以對一張源表進行數據捕獲，也可以對多張源表的增量數據進行捕獲。

缺點：

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

或者在目標表通過打標記的方式（update active_flag=1）進行邏輯刪除。

b. 如果系統自動更新或手工更新時間戳字段時可能會出現數據延遲現象產生。

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

d. 應用起來有部分局限性。即源表都需要有時間戳字段。如果部分源表（或參考表）無時間戳字段，且源表有部分字段更新時（常見于維度表的定義更新，如地區維度，產品維度等），則面臨歷史數據的更新問題。此時采用時間戳方法只能更新新增數據的維度定義，而無法更新歷史數據。這時一般需要采用 SQL 語句或者下文介紹的全表對比方式進行歷史數據更新。或者調整時間戳范圍，做全表數據的刷新。這種情況需要對目標表的實時性要求不高，可以在系統空閑時進行處理。

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

源表與目標表多對一增量更新

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

全表比對方式

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

全表比對方式優缺點

優點是適用于：

a. 涉及多張源表的抽取與轉換，業務邏輯復雜的增量更新。

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

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

總結與分析

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

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

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

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

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

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

另外業界普遍常用的方式是源系統在它的閑時抽取增量 delta 數據文件，數據倉庫系統得到 delta 文件后再加載到倉庫系統。在數據倉庫架構設計中，生產系統層和 ODS 層的直接數據交互即為此類型。

3. 觸發器方式需要在源表上建立觸發器，這種在某些應用場合中遭到拒絕。還有一些需要建立臨時表的方式，例如全表比對和日志表方式。可能因為開放給 ETL 進程的數據庫權限的限制而無法實施。同樣的情況也可能發生在基于系統日志分析的方式上，因為大多數的數據庫產品只允許特定組的用戶甚至只有 DBA 才能執行日志分析。例如 DB2 的 Replication 功能只能由 DBA 維護與修改，普通用戶無法操作。

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

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

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