信息是現代企業的重要資源，是企業運用科學管理、決策分析的基礎。據統計，數據量每經過2-3年時間就會成倍增長，這些數據蘊含著巨大的商業價值，而企業所關注的通常只占總數據量的2%~4%左右。因此，企業仍然沒有最大化地利用已存在的數據資源，以至于浪費了更多的時間和資金，也失去制定關鍵商業決策的最佳契機。 于是，企業如何通過各種技術手段，并把數據轉換為信息、知識，已經成了提高其核心競爭力的關鍵，其中的數據處理在大數據的生態中始終處于不可缺少的地位，因為數據處理的時效性，準確性直接影響數據的分析與挖掘，分析的最終結果影響業務的營銷與收入。 今天我們就來說說一種重要的數據處理手段ETL（Extract-Transform-Load）。 01、ETL發展的歷史背景 隨著企業的發展，各業務線、產品線、部門都會承建各種信息化系統方便開展自己的業務。隨著信息化建設的不斷深入，由于業務系統之間各自為政、相互獨立造成的數據孤島”現象尤為普遍，業務不集成、流程不互通、數據不共享。這給企業進行數據的分析利用、報表開發、分析挖掘等帶來了巨大困難。 在此情況下，為了實現企業全局數據的系統化運作管理（信息孤島、數據統計、數據分析、數據挖掘） ，為DSS（決策支持系統）、BI（商務智能）、經營分析系統等深度開發應用奠定基礎，挖掘數據價值 ，企業會開始著手建立數據倉庫，數據中臺。將相互分離的業務系統的數據源整合在一起，建立一個統一的數據采集、處理、存儲、分發、共享中心，從而使公司的成員能夠從不同業務部門查看綜合數據，而這個過程中使用的數據處理方法之一就是ETL。 ETL是數據中心建設、BI分析項目中不可或缺的環節。各個業務系統中分布的、異構的數據源，經過ETL過程的數據抽取、轉換，最終存儲到目標數據庫或者數據倉庫，為上層BI數據分析，或其他業務功能做數據支撐。 02、什么是ETL？ ETL，Extract-Transform-Load的縮寫，是將業務系統的數據經過抽取、清洗轉換之后加載到數據倉庫的過程。ETL是數據集成的第一步，也是構建數據倉庫最重要的步驟，目的是將企業中的分散、零亂、標準不統一的數據整合到一起，為企業的決策提供分析依據。ETL一詞較常用在數據倉庫，但其對象并不限于數據倉庫。 舉個例子，某電商公司分析人員根據訂單數據進行用戶特征分析。這時需要基于訂單數據，計算一些相應的分析指標，如每個用戶的消費頻次，銷售額最大的單品，用戶復購時間間隔等，這些指標都要通過計算轉換得到。 03、ETL的流程 ETL如同它代表的三個英文單詞，涉及三個獨立的過程：抽取、轉換和加載。工作流程往往作為一個正在進行的過程來實現，各模塊可靈活進行組合，形成ETL處理流程。 1.數據抽取 數據抽取指的是從不同的網絡、不同的操作平臺、不同的數據庫和數據格式、不同的應用中抽取數據的過程。目標源可能包括ERP、CRM和其他企業系統，以及來自第三方源的數據。 不同的系統傾向于使用不同的數據格式，在這個過程中，首先需要結合業務需求確定抽取的字段，形成一張公共需求表頭，并且數據庫字段也應與這些需求字段形成一一映射關系。這樣通過數據抽取所得到的數據都具有統一、規整的字段內容，為后續的數據轉換和加載提供基礎，具體步驟如下： ①確定數據源，需要確定從哪些源系統進行數據抽取 ②定義數據接口，對每個源文件及系統的每個字段進行詳細說明 ③確定數據抽取的方法：是主動抽取還是由源系統推送？是增量抽取還是全量抽取？是按照每日抽取還是按照每月抽取？ 2.數據轉換 數據轉換實際上還包含了數據清洗的工作，需要根據業務規則對異常數據進行清洗，主要將不完整數據、錯誤數據、重復數據進行處理，保證后續分析結果的準確性。 數據轉換就是處理抽取上來的數據中存在的不一致的過程。數據轉換一般包括兩類：第一類：數據名稱及格式的統一，即數據粒度轉換、商務規則計算以及統一的命名、數據格式、計量單位等；第二類：數據倉庫中存在源數據庫中可能不存在的數據，因此需要進行字段的組合、分割或計算。主要涉及以下幾個方面： ①空值處理：可捕獲字段空值，進行加載或替換為其他含義數據，或數據分流問題庫 ②數據標準：統一元數據、統一標準字段、統一字段類型定義 ③數據拆分：依據業務需求做數據拆分，如身份證號，拆分區劃、出生日期、性別等 ④數據驗證：時間規則、業務規則、自定義規則 ⑤數據替換：對于因業務因素，可實現無效數據、缺失數據的替換 ⑥數據關聯：關聯其他數據或數學，保障數據完整性 3.數據加載 數據加載的主要任務是將經過清洗后的干凈的數據集按照物理數據模型定義的表結構裝入目標數據倉庫的數據表中，如果是全量方式則采用LOAD方式，如果是增量則根據業務規則MERGE進數據庫，并允許人工干預，以及提供強大的錯誤報告、系統日志、數據備份與恢復功能。整個操作過程往往要跨網絡、跨操作平臺。 在實際的工作中，數據加載需要結合使用的數據庫系統（Oracle、Mysql、Spark、Impala等），確定最優的數據加載方案，節約CPU、硬盤IO和網絡傳輸資源。 04、ETL與ELT有什么區別？ ETL架構按其字面含義理解就是按照E-T-L這個順序流程進行處理的架構：先抽取、然后轉換、完成后加載到目標數據庫中。 在ETL架構中，數據的流向是從源數據流到ETL工具，ETL工具是一個單獨的數據處理引擎，一般會在單獨的硬件服務器上，實現所有數據轉化的工作，然后將數據加載到目標數據倉庫中。如果要增加整個ETL過程的效率，則只能增強ETL工具服務器的配置，優化系統處理流程（一般可調的東西非常少）。 ELT架構則把“L”這一步工作提前到“T”之前來完成：先抽取、然后加載到目標數據庫中、在目標數據庫中完成轉換操作。在ELT架構中，ELT只負責提供圖形化的界面來設計業務規則，數據的整個加工過程都在目標和源的數據庫之間流動，ELT協調相關的數據庫系統來執行相關的應用，數據加工過程既可以在源數據庫端執行，也可以在目標數據倉庫端執行（主要取決于系統的架構設計和數據屬性）。當ETL過程需要提高效率，則可以通過對相關數據庫進行調優，或者改變執行加工的服務器就可以達到。 ELT架構的特殊優勢：①ELT主要通過數據庫引擎來實現系統的可擴展性；②ELT可以保持所有的數據始終在數據庫當中，避免數據的加載和導出，從而保證效率，提高系統的可監控性；③ELT可以根據數據的分布情況進行并行處理優化，并可以利用數據庫的固有功能優化磁盤I/O；④ELT的可擴展性取決于數據庫引擎和其硬件服務器的可擴展性；⑤通過對相關數據庫進行性能調優，ELT過程獲得3到4倍的效率提升一般不是特別困難。 （1）當您想要執行復雜的計算時，ETL工具比數據倉庫或數據池更有效 （2）如果要在加載到目標存儲之前進行大量數據清理。ETL是一種更好的解決方案，因為您不會將不需要的數據移動到目標。 （3）當您僅使用結構化數據或傳統結構化數據倉庫時。ETL工具通常最有效地將結構化數據從一個環境移動到另一個環境。 （4）當你想要擴展補充數據時。如果要在將數據移動到目標存儲時擴展補充數據，則需要使用ETL工具。例如，添加時間戳。 05、如何才能做好ETL? 1.數據抽取設計 數據的抽取需要在調研階段做大量工作，要搞清楚以下幾個問題：數據是從幾個業務系統中來？各個業務系統的數據庫服務器運行什么DBMS？是否存在手工數據，手工數據量有多大？是否存在非結構化的數據？等等類似問題，當收集完這些信息之后進行數據抽取的設計。常見的數據抽取設計方式有四種： （1）與存放DW的數據庫系統相同的數據源處理方法 這一類數源在設計比較容易，一般情況下，DBMS(包括SQLServer，Oracle)都會提供數據庫鏈接功能，在DW數據庫服務器和原業務系統之間建立直接的鏈接關系就可以寫Select 語句直接訪問。 （2）與DW數據庫系統不同的數據源的處理方法 這一類數據源一般情況下也可以通過ODBC的方式建立數據庫鏈接，如SQL Server和Oracle之間。如果不能建立數據庫鏈接，可以有兩種方式完成，一種是通過工具將源數據導出成.txt或者是.xls文件，然后再將這些源系統文件導入到ODS中。另外一種方法通過程序接口來完成。 （3）對于文件類型數據源（.txt，.xls） 可以培訓業務人員利用數據庫工具將這些數據導入到指定的數據庫，然后從指定的數據庫抽取。或者可以借助工具實現，如SQL SERVER 2005 的SSIS服務的平面數據源和平面目標等組件導入ODS中去。 （4）增量更新問題 對于數據量大的系統，必須考慮增量抽取。一般情況，業務系統會記錄業務發生的時間，可以用作增量的標志，每次抽取之前首先判斷ODS中記錄最大的時間，然后根據這個時間去業務系統取大于這個時間的所有記錄。利用業務系統的時間戳，一般情況下，業務系統沒有或者部分有時間戳。 2.數據清洗處理規范 不符合要求的數據主要有不完成數據（缺失值）、錯誤數據（異常值）、重復數據、不同類型需歸一化處理數據幾類。幾類數據的處理方法如下： 缺失值：不完整的數據，其特征是是一些應該有的信息缺失，如供應商的名稱，分公司的名稱，客戶的區域信息缺失、業務系統中主表與明細表不能匹配等。需要將這一類數據過濾出來，按缺失的內容分別采取定（范圍）刪（字段）補（數據）。 定范圍：哪些字段缺失，缺失范圍如何，缺失字段的重要性如何？刪字段：刪數據的判斷，a\對業務清晰的判斷，b\“有心殺賊，無力回天”缺失數據太多。這時候可以看看是否有其他數據可以彌補。補數據：就是補充缺失值。這里有三種補數據的方式：A、業務知識驚訝填充；B、使用均值、中位數、眾數填充；C、使用其他渠道補充，如身份證前6位是地區=手機號歸屬地。補全后才寫入數據倉庫。 異常值：產生原因是業務系統不夠健全，在接收輸入后沒有進行判斷直接寫入后臺數據庫造成的，比如數值數據輸成全角數字字符、字符串數據后面有一個回車、日期格式不正確、日期越界等。這一類數據也要分類，對于類似于全角字符、數據前后有不面見字符的問題只能寫SQL的方式找出來，然后要求客戶在業務系統修正之后抽取；日期格式不正確的或者是日期越界的這一類錯誤會導致ETL運行失敗，這一類錯誤需要去業務系統數據庫用SQL的方式挑出來，交給業務主管部門要求限期修正，修正之后再抽取。 重復數據：特別是維表中比較常見，將重復的數據的記錄所有字段導出來，讓客戶確認并整理。 數據歸一化：歸一化的問題，就是將絕對數變成相對數的問題。因為不同維度的絕對數是沒有可比性的，這時候需要將絕對數轉化成相對一個標準的相對數。那如何進行歸一化處理呢？三種方式，A、最值歸一化、均值方差歸一化、非線性歸一化。 數據清洗是一個反復的過程，不可能在幾天內完成，只有不斷的發現問題，解決問題。對于是否過濾、是否修正一般要求客戶確認；對于過濾掉的數據，寫入Excel文件或者將過濾數據寫入數據表，在ETL開發的初期可以每天向業務單位發送過濾數據的郵件，促使他們盡快的修正錯誤，同時也可以作為將來驗證數據的依據。數據清洗需要注意的是不要將有用的數據過濾掉了，對于每個過濾規則認真進行驗證，并要用戶確認才行。 3.數據轉換處理規范 數據轉換的任務主要是進行不一致的數據轉換、數據粒度的轉換和一些商務規則的計算。 （1）不一致數據轉換，這個過程是一個整合的過程，將不同業務系統的相同類型的數據統一，比如同一個供應商在結算系統的編碼是XX0001，而在CRM中編碼是YY0001，這樣在抽取過來之后統一轉換成一個編碼。 （2）數據粒度的轉換，業務系統一般存儲非常明細的數據，而數據倉庫中的數據是用來分析的，不需要非常明細的數據，會將業務系統數據按照數據倉庫粒度進行聚合。一般數據轉換有離散化和屬性構造兩種方式。離散化主要分為簡單離散、分桶離散、聚類離散、回歸平滑四類，屬性構造分為特征工程和隨意構造后篩選。 （3）商務規則的計算，不同的企業有不同的業務規則，不同的數據指標，這些指標有的時候不是簡單的加加減減就能完成，這個時候需要在ETL中將這些數據指標計算好了之后存儲在數據倉庫中，供分析使用。 4.ETL日志與警告發送 （1）ETL日志 記錄日志的目的是隨時可以知道ETL運行情況，如果出錯了，出錯在那里。 ETL日志分為三類。①執行過程日志，是在ETL執行過程中每執行一步的記錄，記錄每次運行每一步驟的起始時間，影響了多少行數據，流水賬形式。②是錯誤日志，當某個模塊出錯的時候需要寫錯誤日志，記錄每次出錯的時間，出錯的模塊以及出錯的信息等。③日志是總體日志，只記錄ETL開始時間，結束時間是否成功信息。 如果使用ETL工具，工具會自動產生一些日志，這一類日志也可以作為ETL日志的一部分。 （2）警告發送 ETL出錯了，不僅要寫ETL出錯日志而且要向系統管理員發送警告，發送警告的方式有多種，常用的就是給系統管理員發送郵件，并附上錯誤信息，便于管理員排查。 06、小結 在這里涉及到ETL中，我們只要有一個清晰的認識，它不是想象中的簡單一蹴而就，在實際的過程，你可以會遇到各種各樣的問題，甚至是部門之間溝通的問題。出現以上問題時，可以和團隊小伙伴或者業務側一起制定解決方案，不斷完善，只有這樣才能保證我們的業務分析結果是準確的，才能指導公司做出正確的決策。