etl 1
ETL是數(shù)據(jù)抽取(Extract)、轉(zhuǎn)換(Transform)、清洗(Cleansing)、裝載(Load)的過程。是構(gòu)建數(shù)據(jù)倉庫的重要一環(huán),用戶從數(shù)據(jù)源抽取出所需的數(shù)據(jù),經(jīng)過數(shù)據(jù)清洗,最終按照預(yù)先定義好的數(shù)據(jù)倉庫模型,將數(shù)據(jù)加載到數(shù)據(jù)倉庫中去。
信息是現(xiàn)代企業(yè)的重要資源,是企業(yè)運用科學(xué)管理、決策分析的基礎(chǔ)。目前,大多數(shù)企業(yè)花費大量的資金和時間來構(gòu)建聯(lián)機事務(wù)處理OLTP的業(yè)務(wù)系統(tǒng)和辦公自動化系統(tǒng),用來記錄事務(wù)處理的各種相關(guān)數(shù)據(jù)。據(jù)統(tǒng)計,數(shù)據(jù)量每2~3年時間就會成倍增長,這些數(shù)據(jù)蘊含著巨大的商業(yè)價值,而企業(yè)所關(guān)注的通常只占在總數(shù)據(jù)量的2%~4%左右。因此,企業(yè)仍然沒有最大化地利用已存在的數(shù)據(jù)資源,以致于浪費了更多的時間和資金,也失去制定關(guān)鍵商業(yè)決策的最佳契機。于是,企業(yè)如何通過各種技術(shù)手段,并把數(shù)據(jù)轉(zhuǎn)換為信息、知識,已經(jīng)成了提高其核心競爭力的主要瓶頸。而ETL則是主要的一個技術(shù)手段。如何正確選擇ETL工具?如何正確應(yīng)用ETL?
目前,ETL工具的典型代表有:Informatica、Datastage、OWB、微軟DTS……
數(shù)據(jù)集成:快速實現(xiàn)ETL
ETL的質(zhì)量問題具體表現(xiàn)為正確性、完整性、一致性、完備性、有效性、時效性和可獲取性等幾個特性。而影響質(zhì)量問題的原因有很多,由系統(tǒng)集成和歷史數(shù)據(jù)造成的原因主要包括:業(yè)務(wù)系統(tǒng)不同時期系統(tǒng)之間數(shù)據(jù)模型不一致;業(yè)務(wù)系統(tǒng)不同時期業(yè)務(wù)過程有變化;舊系統(tǒng)模塊在運營、人事、財務(wù)、辦公系統(tǒng)等相關(guān)信息的不一致;遺留系統(tǒng)和新業(yè)務(wù)、管理系統(tǒng)數(shù)據(jù)集成不完備帶來的不一致性。
實現(xiàn)ETL,首先要實現(xiàn)ETL轉(zhuǎn)換的過程。它可以集中地體現(xiàn)為以下幾個方面:
空值處理 可捕獲字段空值,進(jìn)行加載或替換為其他含義數(shù)據(jù),并可根據(jù)字段空值實現(xiàn)分流加載到不同目標(biāo)庫。
規(guī)范化數(shù)據(jù)格式 可實現(xiàn)字段格式約束定義,對于數(shù)據(jù)源中時間、數(shù)值、字符等數(shù)據(jù),可自定義加載格式。
拆分?jǐn)?shù)據(jù) 依據(jù)業(yè)務(wù)需求對字段可進(jìn)行分解。例,主叫號 861084613409,可進(jìn)行區(qū)域碼和電話號碼分解。
驗證數(shù)據(jù)正確性 可利用Lookup及拆分功能進(jìn)行數(shù)據(jù)驗證。例如,主叫號861084613409,進(jìn)行區(qū)域碼和電話號碼分解后,可利用Lookup返回主叫網(wǎng)關(guān)或交換機記載的主叫地區(qū),進(jìn)行數(shù)據(jù)驗證。
數(shù)據(jù)替換 對于因業(yè)務(wù)因素,可實現(xiàn)無效數(shù)據(jù)、缺失數(shù)據(jù)的替換。
Lookup 查獲丟失數(shù)據(jù) Lookup實現(xiàn)子查詢,并返回用其他手段獲取的缺失字段,保證字段完整性。
建立ETL過程的主外鍵約束 對無依賴性的非法數(shù)據(jù),可替換或?qū)С龅藉e誤數(shù)據(jù)文件中,保證主鍵惟一記錄的加載。
為了能更好地實現(xiàn)ETL,筆者建議用戶在實施ETL過程中應(yīng)注意以下幾點:
第一,如果條件允許,可利用數(shù)據(jù)中轉(zhuǎn)區(qū)對運營數(shù)據(jù)進(jìn)行預(yù)處理,保證集成與加載的高效性;
第二,如果ETL的過程是主動“拉取”,而不是從內(nèi)部“推送”,其可控性將大為增強;
第三,ETL之前應(yīng)制定流程化的配置管理和標(biāo)準(zhǔn)協(xié)議;
第四,關(guān)鍵數(shù)據(jù)標(biāo)準(zhǔn)至關(guān)重要。目前,ETL面臨的最大挑戰(zhàn)是當(dāng)接收數(shù)據(jù)時其各源數(shù)據(jù)的異構(gòu)性和低質(zhì)量。以電信為例,A系統(tǒng)按照統(tǒng)計代碼管理數(shù)據(jù),B系統(tǒng)按照賬目數(shù)字管理,C系統(tǒng)按照語音ID管理。當(dāng)ETL需要對這三個系統(tǒng)進(jìn)行集成以獲得對客戶的全面視角時,這一過程需要復(fù)雜的匹配規(guī)則、名稱/地址正常化與標(biāo)準(zhǔn)化。而ETL在處理過程中會定義一個關(guān)鍵數(shù)據(jù)標(biāo)準(zhǔn),并在此基礎(chǔ)上,制定相應(yīng)的數(shù)據(jù)接口標(biāo)準(zhǔn)。
ETL過程在很大程度上受企業(yè)對源數(shù)據(jù)的理解程度的影響,也就是說從業(yè)務(wù)的角度看數(shù)據(jù)集成非常重要。一個優(yōu)秀的ETL設(shè)計應(yīng)該具有如下功能:
管理簡單;采用元數(shù)據(jù)方法,集中進(jìn)行管理;接口、數(shù)據(jù)格式、傳輸有嚴(yán)格的規(guī)范;盡量不在外部數(shù)據(jù)源安裝軟件;數(shù)據(jù)抽取系統(tǒng)流程自動化,并有自動調(diào)度功能;抽取的數(shù)據(jù)及時、準(zhǔn)確、完整;可以提供同各種數(shù)據(jù)系統(tǒng)的接口,系統(tǒng)適應(yīng)性強;提供軟件框架系統(tǒng),系統(tǒng)功能改變時,應(yīng)用程序很少改變便可適應(yīng)變化;可擴展性強。
數(shù)據(jù)模型:標(biāo)準(zhǔn)定義數(shù)據(jù)
合理的業(yè)務(wù)模型設(shè)計對ETL至關(guān)重要。數(shù)據(jù)倉庫是企業(yè)惟一、真實、可靠的綜合數(shù)據(jù)平臺。數(shù)據(jù)倉庫的設(shè)計建模一般都依照三范式、星型模型、雪花模型,無論哪種設(shè)計思想,都應(yīng)該最大化地涵蓋關(guān)鍵業(yè)務(wù)數(shù)據(jù),把運營環(huán)境中雜亂無序的數(shù)據(jù)結(jié)構(gòu)統(tǒng)一成為合理的、關(guān)聯(lián)的、分析型的新結(jié)構(gòu),而ETL則會依照模型的定義去提取數(shù)據(jù)源,進(jìn)行轉(zhuǎn)換、清洗,并最終加載到目標(biāo)數(shù)據(jù)倉庫中。
模型的重要之處在于對數(shù)據(jù)做標(biāo)準(zhǔn)化定義,實現(xiàn)統(tǒng)一的編碼、統(tǒng)一的分類和組織。標(biāo)準(zhǔn)化定義的內(nèi)容包括:標(biāo)準(zhǔn)代碼統(tǒng)一、業(yè)務(wù)術(shù)語統(tǒng)一。ETL依照模型進(jìn)行初始加載、增量加載、緩慢增長維、慢速變化維、事實表加載等數(shù)據(jù)集成,并根據(jù)業(yè)務(wù)需求制定相應(yīng)的加載策略、刷新策略、匯總策略、維護(hù)策略。
元數(shù)據(jù):拓展新型應(yīng)用
對業(yè)務(wù)數(shù)據(jù)本身及其運行環(huán)境的描述與定義的數(shù)據(jù),稱之為元數(shù)據(jù)(metadata)。元數(shù)據(jù)是描述數(shù)據(jù)的數(shù)據(jù)。從某種意義上說,業(yè)務(wù)數(shù)據(jù)主要用于支持業(yè)務(wù)系統(tǒng)應(yīng)用的數(shù)據(jù),而元數(shù)據(jù)則是企業(yè)信息門戶、客戶關(guān)系管理、數(shù)據(jù)倉庫、決策支持和B2B等新型應(yīng)用所不可或缺的內(nèi)容。
元數(shù)據(jù)的典型表現(xiàn)為對象的描述,即對數(shù)據(jù)庫、表、列、列屬性(類型、格式、約束等)以及主鍵/外部鍵關(guān)聯(lián)等等的描述。特別是現(xiàn)行應(yīng)用的異構(gòu)性與分布性越來越普遍的情況下,統(tǒng)一的元數(shù)據(jù)就愈發(fā)重要了。“信息孤島”曾經(jīng)是很多企業(yè)對其應(yīng)用現(xiàn)狀的一種抱怨和概括,而合理的元數(shù)據(jù)則會有效地描繪出信息的關(guān)聯(lián)性。
而元數(shù)據(jù)對于ETL的集中表現(xiàn)為:定義數(shù)據(jù)源的位置及數(shù)據(jù)源的屬性、確定從源數(shù)據(jù)到目標(biāo)數(shù)據(jù)的對應(yīng)規(guī)則、確定相關(guān)的業(yè)務(wù)邏輯、在數(shù)據(jù)實際加載前的其他必要的準(zhǔn)備工作,等等,它一般貫穿整個數(shù)據(jù)倉庫項目,而ETL的所有過程必須最大化地參照元數(shù)據(jù),這樣才能快速實現(xiàn)ETL。
下圖為ETL體系結(jié)構(gòu),它體現(xiàn)了主流ETL產(chǎn)品框架的主要組成部分。ETL是指從源系統(tǒng)中提取數(shù)據(jù),轉(zhuǎn)換數(shù)據(jù)為一個標(biāo)準(zhǔn)的格式,并加載數(shù)據(jù)到目標(biāo)數(shù)據(jù)存儲區(qū),通常是數(shù)據(jù)倉庫。
ETL體系結(jié)構(gòu)圖
Design manager 提供一個圖形化的映射環(huán)境,讓開發(fā)者定義從源到目標(biāo)的映射關(guān)系、轉(zhuǎn)換、處理流程。設(shè)計過程的各對象的邏輯定義存儲在一個元數(shù)據(jù)資料庫中。
Meta data management 提供一個關(guān)于ETL設(shè)計和運行處理等相關(guān)定義、管理信息的元數(shù)據(jù)資料庫。ETL引擎在運行時和其它應(yīng)用都可參考此資料庫中的元數(shù)據(jù)。
Extract 通過接口提取源數(shù)據(jù),例如ODBC、專用數(shù)據(jù)庫接口和平面文件提取器,并參照元數(shù)據(jù)來決定數(shù)據(jù)的提取及其提取方式。
Transform 開發(fā)者將提取的數(shù)據(jù),按照業(yè)務(wù)需要轉(zhuǎn)換為目標(biāo)數(shù)據(jù)結(jié)構(gòu),并實現(xiàn)匯總。
Load 加載經(jīng)轉(zhuǎn)換和匯總的數(shù)據(jù)到目標(biāo)數(shù)據(jù)倉庫中,可實現(xiàn)SQL或批量加載。
Transport services 利用網(wǎng)絡(luò)協(xié)議或文件協(xié)議,在源和目標(biāo)系統(tǒng)之間移動數(shù)據(jù),利用內(nèi)存在ETL處理的各組件中移動數(shù)據(jù)。
Administration and operation 可讓管理員基于事件和時間進(jìn)行調(diào)度、運行、監(jiān)測ETL作業(yè)、管理錯誤信息、從失敗中恢復(fù)和調(diào)節(jié)從源系統(tǒng)的輸出。