ETL介紹ETL

ETL

TL，Extraction-Transformation-Loading的縮寫，中文名稱為資料擷取、轉換和下載。 ETL工具有：OWB(Oracle Warehouse Builder)、ODI(Oracle Data Integrator)、Informatic PowerCenter、AICloudETL、DataStage、Repository Explorer、Beeload、Kettle、DataSpider

　　ETL負責將分布的、異構資料來源中的資料如關係資料、平面資料檔案等抽取到臨時中介層後進行清洗、轉換、整合，最後載入到資料倉儲或資料集市中，成為線上分析處理、資料採礦的基礎。

　　ETL一詞較常出現在資料倉儲，但其對象並不局限於資料倉儲。

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

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

　　ETL(Extract-Transform-Load的縮寫，即資料幫浦、轉換、裝載的過 程)作為BI/DW（Business Intelligence）的核心和靈魂，能夠按照統一的規則集成並提高資料的價值，是負責完成資料從資料來源向目標資料倉儲轉化的過程，是實施資料倉儲的 重要步驟。如果說資料倉儲的模型設計是一座大廈的設計藍圖，資料是磚瓦的話，那麼ETL就是建設大廈的過程。在整個項目中最難部分是使用者需求分析和模型設 計，而ETL規則設計和實施則是工作量最大的，約佔整個項目的60%～80%，這是國內外從眾多實踐中得到的普遍共識。

　　ETL是資料幫浦（Extract）、清洗（Cleaning）、轉換（Transform）、裝載（Load）的過程。是構建資料倉儲的重要一環，使用者從資料來源抽取出所需的資料，經過資料清洗,最終按照預先定義好的資料倉儲模型，將資料載入到資料倉儲中去。

　　資訊是現代企業的重要資源，是企業運用科學管理、決策分析的基礎。目前，大多數企業花費大量的 資金和時間來構建聯機交易處理OLTP的業務系統和辦公自動化系統，用來記錄交易處理的各種相關資料。據統計，資料量每2～3年時間就會成倍增長，這些數 據蘊含著巨大的商業價值，而企業所關注的通常只佔在總資料量的2%～4%左右。因此，企業仍然沒有最大化地利用已存在的資料資源，以致於浪費了更多的時間 和資金，也失去制定關鍵商業決策的最佳契機。於是，企業如何通過各種技術手段，並把資料轉換為資訊、知識，已經成了提高其核心競爭力的主要瓶頸。而ETL 則是主要的一個技術手段。如何正確選擇ETL工具？如何正確應用ETL？

　　目前，ETL工具的典型代表有:Informatica、Datastage、OWB、微軟DTS、Beeload、Kettle……

　　開源的工具有eclips的etl外掛程式。cloveretl.

　　Data Integration：快速實現ETL

　　ETL的品質問題具體表現為正確性、完整性、一致性、完備性、有效性、時效性和可擷取性等幾個 特性。而影響品質問題的原因有很多，由系統整合和曆史資料造成的原因主要包括:業務系統不同時期系統之間資料模型不一致；業務系統不同時期業務過程有變 化；舊系統模組在運營、人事、財務、辦公系統等相關資訊的不一致；遺留系統和新業務、管理系統Data Integration不完備帶來的不一致性。

　　實現ETL，首先要實現ETL轉換的過程。它可以集中地體現為以下幾個方面：

　　空值處理 可捕獲欄位空值，進行載入或替換為其他含義資料，並可根據欄位空值實現分流載入到不同目標庫。

　　正常化資料格式 可實現欄位格式約束定義，對於資料來源中時間、數值、字元等資料，可自訂載入格式。

　　拆分資料 依據業務需求對欄位可進行分解。例，主叫號 861082585313-8148，可進列區域碼和電話號碼分解。

　　驗證資料正確性 可利用Lookup及拆分功能進行資料驗證。例如，主叫號861082585313-8148，進列區域碼和電話號碼分解後，可利用Lookup返回主叫網關或交換器記載的主叫地區，進行資料驗證。

　　資料替換 對於因業務因素，可實現無效資料、缺失資料的替換。

　　Lookup 查獲遺失資料 Lookup實現子查詢，並返回用其他手段擷取的缺失欄位，保證欄位完整性。

　　建立ETL過程的主外鍵約束 對無依賴性的非法資料，可替換或匯出到錯誤資料檔案中，保證主鍵唯一記錄的載入。

　　為了能更好地實現ETL，筆者建議使用者在實施ETL過程中應注意以下幾點：

　　第一，如果條件允許，可利用資料中轉區對運營資料進行預先處理，保證整合與載入的高效性；

　　第二，如果ETL的過程是主動“拉取”，而不是從內部“推送”，其可控性將大為增強；

　　第三，ETL之前應制定流程化的組態管理和標準協議；

　　第四，關鍵資料標準至關重要。目前，ETL面臨的最大挑戰是當接收資料時其各來源資料的異構性和 低品質。以電信為例，A系統按照統計代碼管理資料，B系統按照賬目數字管理，C系統按照語音ID管理。當ETL需要對這三個系統進行整合以獲得對客戶的全 面視角時，這一過程需要複雜的匹配規則、名稱/地址正常化與標準化。而ETL在處理過程中會定義一個關鍵資料標準，並在此基礎上，制定相應的資料介面標 准。

　　ETL過程在很大程度上受企業對來源資料的理解程度的影響，也就是說從業務的角度看Data Integration非常重要。一個優秀的ETL設計應該具有如下功能：

　　管理簡單；採用中繼資料方 法，集中進行管理；介面、資料格式、傳輸有嚴格的規範；盡量不在外部資料源安裝軟體；資料幫浦系統流程自動化，並有自動調度功能；抽取的資料及時、準確、 完整；可以提供同各種資料系統的介面，系統適應性強；提供軟體架構系統，系統功能改變時，應用程式很少改變便可適應變化；可擴充性強。

　　資料模型：標準定義資料

　　合理的業務模型設計對ETL至關重要。資料倉儲是企業唯一、真實、可靠的綜合資料平台。資料倉 庫的設計建模一般都依照三範式、星型模型、雪花模型，無論哪種設計思想，都應該最大化地涵蓋關鍵業務資料，把運營環境中雜亂無序的資料結構統一成為合理 的、關聯的、分析型的新結構，而ETL則會依照模型的定義去提取資料來源，進行轉換、清洗，並最終載入到目標資料倉儲中。

　　模型的重要之處在於對資料做標準化定義，實現統一的編碼、統一的分類和組織。標準化定義的內容 包括：標準代碼統一、業務術語統一。ETL依照模型進行初始載入、增量載入、緩慢增長維、慢速變化維、事實表載入等Data Integration，並根據業務需求制定相應的加 載策略、重新整理策略、匯總策略、維護策略。

　　中繼資料：拓展新型應用

　　對業務資料本身及其運行環境的描述與定義的資料，稱之為中繼資料（metadata）。中繼資料是 描述資料的資料。從某種意義上說，業務資料主要用於支援業務系統應用的資料，而中繼資料則是公司資訊門戶、客戶關係管理、資料倉儲、決策支援和B2B等新型 應用所不可或缺的內容。

　　中繼資料的典型表現為對象的描述，即對資料庫、表、列、列屬性（類型、格式、約束等）以及主鍵/ 外部鍵關聯等等的描述。特別是現行應用的異構性與分布性越來越普遍的情況下，統一的中繼資料就愈發重要了。“資訊孤島”曾經是很多企業對其應用現狀的一種抱 怨和概括，而合理的中繼資料則會有效地描繪出資訊的關聯性。

　　而中繼資料對於ETL的集中表現為：定義資料來源的位置及資料來源的屬性、確定從來源資料到目標資料的 對應規則、確定相關的商務邏輯、在資料實際載入前的其他必要的準備工作，等等，它一般貫穿整個資料倉儲項目，而ETL的所有過程必須最大化地參照中繼資料， 這樣才能快速實現ETL。

　　ETL體繫結構

　　為ETL體繫結構，它體現了主流ETL產品架構的主要組成部分。ETL是指從源系統中提取資料，轉換資料為一個標準的格式，並載入資料到目標資料存放區區，通常是資料倉儲。

ETL體繫結構圖

　　Design manager 提供一個圖形化的映射環境，讓開發人員定義從源到目標的映射關係、轉換、處理流程。設計過程的各對象的邏輯定義儲存在一個中繼資料資料庫中。

　　Meta data management 提供一個關於ETL設計和運行處理等相關定義、管理資訊的中繼資料資料庫。ETL引擎在運行時和其它應用都可參考此資料庫中的中繼資料。

　　Extract 通過介面提取來源資料，例如?ODBC、專用資料庫介面和一般檔案提取器，並參照中繼資料來決定資料的提取及其提取方式。

　　Transform 開發人員將提取的資料，按照業務需要轉換為目標資料結構，並實現匯總。

　　Load 載入經轉換和匯總的資料到目標資料倉儲中，可實現SQL或批量載入。

　　Transport services 利用網路通訊協定或檔案協議，在源和目標系統之間移動資料，利用記憶體在ETL處理的各組件中移動資料。

　　Administration and operation 可讓管理員基於事件和時間進行調度、運行、監測ETL作業、管理錯誤資訊、從失敗中恢複和調節從源系統的輸出。

TL，Extraction-Transformation-Loading的縮寫，中文名稱為資料擷取、轉換和下載。 ETL工具有：OWB(Oracle Warehouse Builder)、ODI(Oracle Data Integrator)、Informatic PowerCenter、AICloudETL、DataStage、Repository Explorer、Beeload、Kettle、DataSpider

　　ETL負責將分布的、異構資料來源中的資料如關係資料、平面資料檔案等抽取到臨時中介層後進行清洗、轉換、整合，最後載入到資料倉儲或資料集市中，成為線上分析處理、資料採礦的基礎。

　　ETL一詞較常出現在資料倉儲，但其對象並不局限於資料倉儲。

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

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

　　ETL(Extract-Transform-Load的縮寫，即資料幫浦、轉換、裝載的過 程)作為BI/DW（Business Intelligence）的核心和靈魂，能夠按照統一的規則集成並提高資料的價值，是負責完成資料從資料來源向目標資料倉儲轉化的過程，是實施資料倉儲的 重要步驟。如果說資料倉儲的模型設計是一座大廈的設計藍圖，資料是磚瓦的話，那麼ETL就是建設大廈的過程。在整個項目中最難部分是使用者需求分析和模型設 計，而ETL規則設計和實施則是工作量最大的，約佔整個項目的60%～80%，這是國內外從眾多實踐中得到的普遍共識。

　　ETL是資料幫浦（Extract）、清洗（Cleaning）、轉換（Transform）、裝載（Load）的過程。是構建資料倉儲的重要一環，使用者從資料來源抽取出所需的資料，經過資料清洗,最終按照預先定義好的資料倉儲模型，將資料載入到資料倉儲中去。

　　資訊是現代企業的重要資源，是企業運用科學管理、決策分析的基礎。目前，大多數企業花費大量的 資金和時間來構建聯機交易處理OLTP的業務系統和辦公自動化系統，用來記錄交易處理的各種相關資料。據統計，資料量每2～3年時間就會成倍增長，這些數 據蘊含著巨大的商業價值，而企業所關注的通常只佔在總資料量的2%～4%左右。因此，企業仍然沒有最大化地利用已存在的資料資源，以致於浪費了更多的時間 和資金，也失去制定關鍵商業決策的最佳契機。於是，企業如何通過各種技術手段，並把資料轉換為資訊、知識，已經成了提高其核心競爭力的主要瓶頸。而ETL 則是主要的一個技術手段。如何正確選擇ETL工具？如何正確應用ETL？

　　目前，ETL工具的典型代表有:Informatica、Datastage、OWB、微軟DTS、Beeload、Kettle……

　　開源的工具有eclips的etl外掛程式。cloveretl.

　　Data Integration：快速實現ETL

　　ETL的品質問題具體表現為正確性、完整性、一致性、完備性、有效性、時效性和可擷取性等幾個 特性。而影響品質問題的原因有很多，由系統整合和曆史資料造成的原因主要包括:業務系統不同時期系統之間資料模型不一致；業務系統不同時期業務過程有變 化；舊系統模組在運營、人事、財務、辦公系統等相關資訊的不一致；遺留系統和新業務、管理系統Data Integration不完備帶來的不一致性。

　　實現ETL，首先要實現ETL轉換的過程。它可以集中地體現為以下幾個方面：

　　空值處理 可捕獲欄位空值，進行載入或替換為其他含義資料，並可根據欄位空值實現分流載入到不同目標庫。

　　正常化資料格式 可實現欄位格式約束定義，對於資料來源中時間、數值、字元等資料，可自訂載入格式。

　　拆分資料 依據業務需求對欄位可進行分解。例，主叫號 861082585313-8148，可進列區域碼和電話號碼分解。

　　驗證資料正確性 可利用Lookup及拆分功能進行資料驗證。例如，主叫號861082585313-8148，進列區域碼和電話號碼分解後，可利用Lookup返回主叫網關或交換器記載的主叫地區，進行資料驗證。

　　資料替換 對於因業務因素，可實現無效資料、缺失資料的替換。

　　Lookup 查獲遺失資料 Lookup實現子查詢，並返回用其他手段擷取的缺失欄位，保證欄位完整性。

　　建立ETL過程的主外鍵約束 對無依賴性的非法資料，可替換或匯出到錯誤資料檔案中，保證主鍵唯一記錄的載入。

　　為了能更好地實現ETL，筆者建議使用者在實施ETL過程中應注意以下幾點：

　　第一，如果條件允許，可利用資料中轉區對運營資料進行預先處理，保證整合與載入的高效性；

　　第二，如果ETL的過程是主動“拉取”，而不是從內部“推送”，其可控性將大為增強；

　　第三，ETL之前應制定流程化的組態管理和標準協議；

　　第四，關鍵資料標準至關重要。目前，ETL面臨的最大挑戰是當接收資料時其各來源資料的異構性和 低品質。以電信為例，A系統按照統計代碼管理資料，B系統按照賬目數字管理，C系統按照語音ID管理。當ETL需要對這三個系統進行整合以獲得對客戶的全 面視角時，這一過程需要複雜的匹配規則、名稱/地址正常化與標準化。而ETL在處理過程中會定義一個關鍵資料標準，並在此基礎上，制定相應的資料介面標 准。

　　ETL過程在很大程度上受企業對來源資料的理解程度的影響，也就是說從業務的角度看Data Integration非常重要。一個優秀的ETL設計應該具有如下功能：

　　管理簡單；採用中繼資料方 法，集中進行管理；介面、資料格式、傳輸有嚴格的規範；盡量不在外部資料源安裝軟體；資料幫浦系統流程自動化，並有自動調度功能；抽取的資料及時、準確、 完整；可以提供同各種資料系統的介面，系統適應性強；提供軟體架構系統，系統功能改變時，應用程式很少改變便可適應變化；可擴充性強。

　　資料模型：標準定義資料

　　合理的業務模型設計對ETL至關重要。資料倉儲是企業唯一、真實、可靠的綜合資料平台。資料倉 庫的設計建模一般都依照三範式、星型模型、雪花模型，無論哪種設計思想，都應該最大化地涵蓋關鍵業務資料，把運營環境中雜亂無序的資料結構統一成為合理 的、關聯的、分析型的新結構，而ETL則會依照模型的定義去提取資料來源，進行轉換、清洗，並最終載入到目標資料倉儲中。

　　模型的重要之處在於對資料做標準化定義，實現統一的編碼、統一的分類和組織。標準化定義的內容 包括：標準代碼統一、業務術語統一。ETL依照模型進行初始載入、增量載入、緩慢增長維、慢速變化維、事實表載入等Data Integration，並根據業務需求制定相應的加 載策略、重新整理策略、匯總策略、維護策略。

　　中繼資料：拓展新型應用

　　對業務資料本身及其運行環境的描述與定義的資料，稱之為中繼資料（metadata）。中繼資料是 描述資料的資料。從某種意義上說，業務資料主要用於支援業務系統應用的資料，而中繼資料則是公司資訊門戶、客戶關係管理、資料倉儲、決策支援和B2B等新型 應用所不可或缺的內容。

　　中繼資料的典型表現為對象的描述，即對資料庫、表、列、列屬性（類型、格式、約束等）以及主鍵/ 外部鍵關聯等等的描述。特別是現行應用的異構性與分布性越來越普遍的情況下，統一的中繼資料就愈發重要了。“資訊孤島”曾經是很多企業對其應用現狀的一種抱 怨和概括，而合理的中繼資料則會有效地描繪出資訊的關聯性。

　　而中繼資料對於ETL的集中表現為：定義資料來源的位置及資料來源的屬性、確定從來源資料到目標資料的 對應規則、確定相關的商務邏輯、在資料實際載入前的其他必要的準備工作，等等，它一般貫穿整個資料倉儲項目，而ETL的所有過程必須最大化地參照中繼資料， 這樣才能快速實現ETL。

　　ETL體繫結構

　　為ETL體繫結構，它體現了主流ETL產品架構的主要組成部分。ETL是指從源系統中提取資料，轉換資料為一個標準的格式，並載入資料到目標資料存放區區，通常是資料倉儲。

ETL體繫結構圖

　　Design manager 提供一個圖形化的映射環境，讓開發人員定義從源到目標的映射關係、轉換、處理流程。設計過程的各對象的邏輯定義儲存在一個中繼資料資料庫中。

　　Meta data management 提供一個關於ETL設計和運行處理等相關定義、管理資訊的中繼資料資料庫。ETL引擎在運行時和其它應用都可參考此資料庫中的中繼資料。

　　Extract 通過介面提取來源資料，例如?ODBC、專用資料庫介面和一般檔案提取器，並參照中繼資料來決定資料的提取及其提取方式。

　　Transform 開發人員將提取的資料，按照業務需要轉換為目標資料結構，並實現匯總。

　　Load 載入經轉換和匯總的資料到目標資料倉儲中，可實現SQL或批量載入。

　　Transport services 利用網路通訊協定或檔案協議，在源和目標系統之間移動資料，利用記憶體在ETL處理的各組件中移動資料。

　　Administration and operation 可讓管理員基於事件和時間進行調度、運行、監測ETL作業、管理錯誤資訊、從失敗中恢複和調節從源系統的輸出。