ETL介紹與ETL工具比較

轉： https://blog.csdn.net/u013412535/article/details/43462537

ETL，是英文 Extract-Transform-Load 的縮寫，用來描述將資料從來源端經過萃取（extract）、轉置（transform）、載入（load）至目的端的過程。ETL一詞較常用在資料倉儲，但其物件並不限於資料倉儲。

ETL負責將分佈的、異構資料來源中的資料如關係資料、

平面資料檔案等抽取到臨時中間層後進行清洗、轉換、整合，最後載入到資料倉儲或資料集市中，成為聯機分析處理、資料探勘的基礎。

ETL是資料倉儲中的非常重要的一環。它是承前啟後的必要的一步。相對於關聯式資料庫，資料倉儲技術沒有嚴格的數學理論基礎，它更面向實際工程應用。所以從工程應用的角度來考慮，按著物理資料模型的要求載入資料並對資料進行一些系列處理，處理過程與經驗直接相關，同時這部分的工作直接關係資料倉儲中資料的質量，從而影響到聯機分析處理和資料探勘的結果的質量。

資料倉儲是一個獨立的資料環境，需要透過抽取過程將資料從聯機事務處理環境、外部資料來源和離線的資料儲存介質匯入到資料倉儲中；在技術上，ETL主要涉及到關聯、轉換、增量、排程和監控等幾個方面；資料倉儲系統中資料不要求與聯機事務處理系統中資料實時同步，所以ETL可以定時進行。但多個ETL的操作時間、順序和成敗對資料倉儲中資訊的有效性至關重要。

ETL中的關鍵技術

ETL過程中的主要環節就是資料抽取、資料轉換和加工、資料裝載。為了實現這些功能，各個ETL工具一般會進行一些功能上的擴充，例如工作流、排程引擎、規則引擎、指令碼支援、統計資訊等。

資料抽取

資料抽取是從資料來源中抽取資料的過程。實際應用中，資料來源較多采用的是關聯式資料庫。從資料庫中抽取資料一般有以下幾種方式。

(1)全量抽取

全量抽取類似於資料遷移或資料複製，它將資料來源中的表或檢視的資料原封不動的從資料庫中抽取出來，並轉換成自己的ETL工具可以識別的格式。全量抽取比較簡單。

(2)增量抽取

增量抽取只抽取自上次抽取以來資料庫中要抽取的表中新增或修改的資料。在ETL使用過程中。增量抽取較全量抽取應用更廣。如何捕獲變化的資料是增量抽取的關鍵。對捕獲方法一般有兩點要求：準確性，能夠將業務系統中的變化資料按一定的頻率準確地捕獲到；效能，不能對業務系統造成太大的壓力，影響現有業務。目前增量資料抽取中常用的捕獲變化資料的方法有：

a.觸發器：在要抽取的表上建立需要的觸發器，一般要建立插入、修改、刪除三個觸發器，每當源表中的資料發生變化，就被相應的觸發器將變化的資料寫入一個臨時表，抽取執行緒從臨時表中抽取資料，臨時表中抽取過的資料被標記或刪除。觸發器方式的優點是資料抽取的效能較高，缺點是要求業務表建立觸發器，對業務系統有一定的影響。

b.時間戳：它是一種基於快照比較的變化資料捕獲方式，在源表上增加一個時間戳欄位，系統中更新修改表資料的時候，同時修改時間戳欄位的值。當進行資料抽取時，透過比較系統時間與時間戳欄位的值來決定抽取哪些資料。有的資料庫的時間戳支援自動更新，即表的其它欄位的資料發生改變時，自動更新時間戳欄位的值。有的資料庫不支援時間戳的自動更新，這就要求業務系統在更新業務資料時，手工更新時間戳欄位。同觸發器方式一樣，時間戳方式的效能也比較好，資料抽取相對清楚簡單，但對業務系統也有很大的傾入性（加入額外的時間戳欄位），特別是對不支援時間戳的自動更新的資料庫，還要求業務系統進行額外的更新時間戳操作。另外，無法捕獲對時間戳以前資料的delete和update操作,在資料準確性上受到了一定的限制。

c.全表比對：典型的全表比對的方式是採用MD5校驗碼。ETL工具事先為要抽取的表建立一個結構類似的MD5臨時表，該臨時表記錄源表主鍵以及根據所有欄位的資料計算出來的MD5校驗碼。每次進行資料抽取時，對源表和MD5臨時表進行MD5校驗碼的比對，從而決定源表中的資料是新增、修改還是刪除，同時更新MD5校驗碼。MD5方式的優點是對源系統的傾入性較小（僅需要建立一個MD5臨時表），但缺點也是顯而易見的，與觸發器和時間戳方式中的主動通知不同，MD5方式是被動的進行全表資料的比對，效能較差。當表中沒有主鍵或唯一列且含有重複記錄時，MD5方式的準確性較差。

d.日誌對比：透過分析資料庫自身的日誌來判斷變化的資料。Oracle的改變資料捕獲（CDC，Changed Data Capture）技術是這方面的代表。CDC 特性是在Oracle9i資料庫中引入的。CDC能夠幫助你識別從上次抽取之後發生變化的資料。利用CDC，在對源表進行insert、update或 delete等操作的同時就可以提取資料，並且變化的資料被儲存在資料庫的變化表中。這樣就可以捕獲發生變化的資料，然後利用資料庫檢視以一種可控的方式提供給目標系統。CDC體系結構基於釋出者/訂閱者模型。釋出者捕捉變化資料並提供給訂閱者。訂閱者使用從釋出者那裡獲得的變化資料。通常，CDC系統擁有一個釋出者和多個訂閱者。釋出者首先需要識別捕獲變化資料所需的源表。然後，它捕捉變化的資料並將其儲存在特別建立的變化表中。它還使訂閱者能夠控制對變化資料的訪問。訂閱者需要清楚自己感興趣的是哪些變化資料。一個訂閱者可能不會對釋出者釋出的所有資料都感興趣。訂閱者需要建立一個訂閱者檢視來訪問經發布者授權可以訪問的變化資料。CDC分為同步模式和非同步模式，同步模式實時的捕獲變化資料並儲存到變化表中，釋出者與訂閱都位於同一資料庫中。非同步模式則是基於Oracle的流複製技術。

ETL處理的資料來源除了關聯式資料庫外，還可能是檔案，例如txt檔案、excel檔案、xml檔案等。對檔案資料的抽取一般是進行全量抽取，一次抽取前可儲存檔案的時間戳或計算檔案的MD5校驗碼，下次抽取時進行比對，如果相同則可忽略本次抽取。

資料轉換和加工

從資料來源中抽取的資料不一定完全滿足目的庫的要求，例如資料格式的不一致、資料輸入錯誤、資料不完整等等，因此有必要對抽取出的資料進行資料轉換和加工。

資料的轉換和加工可以在ETL引擎中進行，也可以在資料抽取過程中利用關聯式資料庫的特性同時進行。

(1)ETL引擎中的資料轉換和加工

ETL引擎中一般以元件化的方式實現資料轉換。常用的資料轉換元件有欄位對映、資料過濾、資料清洗、資料替換、資料計算、資料驗證、資料加解密、資料合併、資料拆分等。這些元件如同一條流水線上的一道道工序，它們是可插拔的，且可以任意組裝，各元件之間透過資料匯流排共享資料。

有些ETL工具還提供了指令碼支援，使得使用者可以以一種程式設計的方式定製資料的轉換和加工行為。

(2)在資料庫中進行資料加工

關聯式資料庫本身已經提供了強大的SQL、函式來支援資料的加工，如在SQL查詢語句中新增where條件進行過濾，查詢中重新命名欄位名與目的表進行對映，substr函式，case條件判斷等等。下面是一個SQL查詢的例子。

select ID as USERID, substr(TITLE, 1, 20) as TITLE, case when REMARK is null then ' ' else REMARK end as CONTENT from TB_REMARK where ID > 100;

相比在ETL引擎中進行資料轉換和加工，直接在SQL語句中進行轉換和加工更加簡單清晰，效能更高。對於SQL語句無法處理的可以交由ETL引擎處理。

資料裝載

將轉換和加工後的資料裝載到目的庫中通常是ETL過程的最後步驟。裝載資料的最佳方法取決於所執行操作的型別以及需要裝入多少資料。當目的庫是關聯式資料庫時，一般來說有兩種裝載方式：

(1)直接SQL語句進行insert、update、delete操作。

(2)採用批次裝載方法，如bcp、bulk、關聯式資料庫特有的批次裝載工具或api。

大多數情況下會使用第一種方法，因為它們進行了日誌記錄並且是可恢復的。但是，批次裝載操作易於使用，並且在裝入大量資料時效率較高。使用哪種資料裝載方法取決於業務系統的需要。

ETL工具的典型代表有:Informatica、Datastage、ODI ,OWB、微軟DTS、Beeload、Kettle、久其ETL……

ETL工具

旗鼓相當：Datastage與Powercenter：

就Datastage和Powercenter而言，這兩者目前佔據了國內市場絕大部分的份額，在成本上看水平相當，雖然市面上還有諸如Business Objects公司的Data Integrator、Cognos公司的DecisionStream，但尚屬星星之火，未成燎原之勢。

談Datastage和Powercenter，如果有人說這個就是比那個好，那聽者就要小心一點了。在這種情況下有兩種可能：他或者是其中一個廠商的員工，或者就是在某個產品上有很多經驗而在另一產品上經驗缺乏的開發者。為什麼得出這一結論？一個很簡單的事實是，從網路上大家對它們的討論和爭執來看，基本上是各有千秋，都有著相當數量的成功案例和實施高手。確實，工具是死的，人才是活的。在兩大ETL工具技術的比對上，可以從對ETL流程的支援、對後設資料的支援、對資料質量的支援、維護的方便性、定製開發功能的支援等方面考慮。

一個專案中，從資料來源到最終目標表，多則上百個ETL過程，少則也有十幾個。這些過程之間的依賴關係、出錯控制以及恢復的流程處理，都是工具需要重點考慮。在這一方面，Datastage的早期版本對流程就缺乏考慮，而在6版本則加入Job Sequence的特性，可以將Job、shell指令碼用流程圖的方式表示出來，依賴關係、序列或是並行都可以一目瞭然，就直觀多了。Powercenter有Workflow的概念，也同樣可以將Session串聯起來，這和Datastage Sequence大同小異。

ETL的後設資料包括資料來源、目標資料的結構、轉換規則以及過程的依賴關係等。在這方面，Datastage和Powercenter從功能上看可謂不分伯仲，只是後者的後設資料更加開放，存放在關聯式資料庫中，可以很容易被訪問（Informatic把Metadata全部放在資料庫中而Datastage是自己管理Metadata，不依賴任何資料庫.）。此外，這兩個廠家又同時提供專門的後設資料管理工具，Ascential有Metastage，而Informatica擁有Superglue。你看，就不給你全部功能，變著法子從你口袋裡面多掏點錢。

資料質量方面，兩種產品都採用同樣的策略——獨立出ETL產品之外，另外有專門的資料質量管理產品。例如和Datastage配套用的有ProfileStage和QualityStage，而Informatica最近也索性收購了原先OEM的資料質量管理產品FirstLogic。而在它們的ETL產品中，只是在Job或是Session前後留下介面，所謂前過程、後過程，雖然不是專為資料質量預留的介面，不過至少可以利用它外掛一些資料質量控制的模組。

在具體實現上看，Datastage透過Job實現一個ETL過程，執行時可以透過指定不同引數執行多個例項。Powercenter透過Mapping表示一個ETL過程，執行時為Session，繫結了具體的物理資料檔案或表。在修改維護上，這兩個工具都是提供圖形化介面。這樣的好處是直觀、傻瓜式的；不好的地方就是改動還是比較費事（特別是批次化的修改）。

定製開發方面，兩者都提供抽取、轉換外掛的定製，但筆者認為，Datastage的定製開發性要比Powercenter要強那麼一點點。因為Datastage至少還內嵌一種類BASIC語言，可以寫一段批處理程式來增加靈活性，而Powercenter似乎還缺乏這類機制。另外從引數控制上，雖然兩者的引數傳遞都是比較混亂的，但Datastage至少可以對每個job設定引數，並且可以job內部引用這個引數名；而Powercenter顯得就有些偷懶，引數放在一個引數檔案中，理論上的確可以靈活控制引數，但這個靈活性需要你自己更新檔案中的引數值（例如日期更新）。另外，Powercenter還不能在mapping或session中引用引數名，這一點就讓人惱火。

總起來看，Datastage和Powercenter可謂旗鼓相當，在國內也都有足夠的支援能力，Datastage在2005年被IBM收購之後，可以說後勁十足。而Informatica則朝著BI全解決方案提供商方向發展，Powercenter顯然還將是它的核心產品。

ODI

ODI提出了知識模組的概念，把這些場景的詳細的實現步驟作為一個一個的知識模組並使用Jython指令碼語言結合資料庫的SQL語句錄製成一步一步的步驟忠實地記錄下來，這樣就形成了ODI裡的100多個知識模組，基本上包含了所有普通應用所涉及到的所有場景。更方便的是，使用者既可以直接使用ODI的知識模組完成資料的獲取工作，也可以直接在知識模組上面做各種定製，比如某一個業務場景可能並不需要知識模組裡的某一個特定的步驟，那就可以直接把該步驟刪除掉從而提供更好的效能。當然使用者也可以完全自己來開發這些知識模組。

ODI的知識模組主要分為幾個大類（RKM,CKM,LKM,IKM,SKM），其中最重要的是LKM（load KM）和IKM(Integration KM)RKM。

RKM:完成從源系統和目標系統的資料結構的反向工程來形成資料模型的功能。

CKM:CKM完成資料質量檢查。

LKM：LKM完成從源資料庫資料載入到臨時表。

IKM：IKM完成從臨時表的資料載入到目標表。

SKM：SKM完成ODI和WEB服務介面的功能。

ODI的效能不是很好，Powercenter > Datastage > ODI

獨樹一幟：Teradata的ETL Automation

繼續要說的第三種產品是Teradata的ETL Automation。之所以拿它單獨來說是因為它和前面兩種產品的體系架構都不太一樣。與其說它是ETL工具，不如說是提供了一套ETL框架。它沒有將注意力放在如何處理“轉換”這個環節上，而是利用Teradata資料庫本身的並行處理能力，用SQL語句來做資料轉換的工作，其重點是提供對ETL流程的支援，包括前後依賴、執行和監控等。

這樣的設計和Datastage、Powercenter風格迥異，後兩者給人的印象是具有靈活的圖形化介面，開發者可以傻瓜式處理ETL工作，它們一般都擁有非常多的“轉換”元件，例如聚集彙總、緩慢變化維的轉換。而對於Teradata的ETL Automation，有人說它其實應該叫做ELT，即裝載是在轉換之前的。的確，如果依賴資料庫的能力去處理轉換，恐怕只能是ELT，因為轉換隻能在資料庫內部進行。從這個角度看，Automation對資料庫的依賴不小，似乎是一種不靈活的設計。也正是這個原因，考慮它的成本就不單單是ETL產品的成本了。

其實，在購買現成的工具之外，還有自己從頭開發ETL程式的。

ETL工作看起來並不複雜，特別是在資料量小、沒有什麼轉換邏輯的時候，自己開發似乎非常節省成本。的確，主流的ETL工具價格不菲，動輒幾十萬；而從頭開發無非就是費點人力而已，可以控制。至於效能，人大多是相信自己的，認為自己開發出來的東西知根知底，至少這些程式可以完全由自己控制。

就目前自主開發的ETL程式而言，有人用c語言編寫，有人用儲存過程，還有人用各種語言混雜開發，程式之間各自獨立。這很危險，雖然能夠讓開發者過足編碼的癮，卻根本不存在架構。

有位銀行的朋友，他們幾年前上的資料倉儲系統，就是整合商自己用c語言專門為他們的專案開發的。單從效能上看似乎還不賴，然而一兩年下來，專案組成員風雨飄零，早已物是人非，只有那套程式還在那裡；而且，按照國內目前的軟體工程慣例，程式註釋和文件是不全或者是不一致的，這樣的程式已經對日常業務造成很大阻礙。最近，他們已經開始考慮使用ETL工具重新改造了。

國產ETL軟體—udis睿智ETL：

    再來看國產的, 採用SOA架構體系，具有更好的方便性和靈活性.缺點是配置複雜，缺少對後設資料的管理。

總體比較列表：

ETL工具選型參照表

工具

優點

缺點

主流工具

Datastage

內嵌一種類BASIC語言，可透過批處理程式增加靈活性，可對每個job設定引數並在job內部引用

早期版本對流程支援缺乏考慮；圖形化介面改動費事

Powercenter

後設資料管理更為開放，存放在關聯式資料庫中，可以很容易被訪問

沒有內嵌類BASIC語言，引數值需人為更新，且不能引用引數名；圖形化介面改動費事

Automation

提供一套ETL框架，利用Teradata資料倉儲本身的並行處理能力

對資料庫依賴性強，選型時需要考慮綜合成本（包括資料庫等）

udis睿智ETL

適合國內需求，價效比高

配置複雜，缺少對後設資料的管理

自主開發

相對於購買主流ETL工具，成本較低

各種語言混雜開發，無架構可言，後期維護難度大。

ETL工具的選擇

在資料整合中該如何選擇ETL工具呢？一般來說需要考慮以下幾個方面：

(1)對平臺的支援程度。

(2)對資料來源的支援程度。

(3)抽取和裝載的效能是不是較高，且對業務系統的效能影響大不大，傾入性高不高。

(4)資料轉換和加工的功能強不強。

(5)是否具有管理和排程功能。

(6)是否具有良好的整合性和開放性

--------------------- 

作者：大資料最好 

來源：CSDN 

原文：https://blog.csdn.net/u013412535/article/details/43462537 

版權宣告：本文為博主原創文章，轉載請附上博文連結！