ETL是數(shù)據(jù)倉庫里最重要的數(shù)據(jù)處理過程，也是最體現(xiàn)工作量的環(huán)節(jié)，一般會(huì)占到整個(gè)數(shù)據(jù)倉庫項(xiàng)目工作量的一半以上。

● 抽取：從數(shù)據(jù)源獲取數(shù)據(jù)。

● 轉(zhuǎn)換：轉(zhuǎn)換數(shù)據(jù)，使之轉(zhuǎn)變?yōu)檫m用于查詢和分析的形式和結(jié)構(gòu)。

● 裝載：將轉(zhuǎn)換后的數(shù)據(jù)導(dǎo)入到最終的目標(biāo)數(shù)據(jù)倉庫。

數(shù)據(jù)倉庫的本質(zhì)就是要把來自于多個(gè)異構(gòu)的源系統(tǒng)的數(shù)據(jù)集成在一起，放置于一個(gè)集中的位置用于數(shù)據(jù)分析。如果沒有ETL，就無法對異構(gòu)的數(shù)據(jù)進(jìn)行結(jié)構(gòu)化的分析。

1、為什么要用ETL呢

??當(dāng)數(shù)據(jù)來自不同的平臺(tái)或系統(tǒng)時(shí)，這時(shí)候如使用SQL語句去處理的話，就顯得比較吃力且開銷也更大。

? 數(shù)據(jù)來源可以是各種不同的數(shù)據(jù)庫或者文件，比如有的是音頻，有的是視頻、有的是文字、有的是圖片、有的是網(wǎng)頁等等亂七八糟的數(shù)據(jù)（尤其是智慧城市、智慧建筑中的數(shù)據(jù)源），這時(shí)候需要先把他們整理成統(tǒng)一的格式后才可以進(jìn)行數(shù)據(jù)的處理，這一過程用代碼實(shí)現(xiàn)顯然有些麻煩。

? 在數(shù)據(jù)庫中我們當(dāng)然可以使用存儲(chǔ)過程去處理數(shù)據(jù)，但是處理海量數(shù)據(jù)的時(shí)候存儲(chǔ)過程顯然比較吃力，而且會(huì)占用較多數(shù)據(jù)庫的資源，這可能會(huì)導(dǎo)致數(shù)據(jù)資源不足，進(jìn)而影響數(shù)據(jù)庫的性能。

在我們的產(chǎn)品中，智慧園區(qū)是每時(shí)每刻都在抓取建筑運(yùn)行的海量數(shù)據(jù)，尤其是安防類數(shù)據(jù)，不僅數(shù)據(jù)量大、數(shù)據(jù)類型復(fù)雜、實(shí)時(shí)性強(qiáng)，對整個(gè)園區(qū)的運(yùn)營也起著重要的作用。針對智慧園區(qū)數(shù)據(jù)運(yùn)營平臺(tái)，為了滿足項(xiàng)目運(yùn)營方、物業(yè)管理方、入園企業(yè)方等不同利益相關(guān)方，需要滿足不同角色、不同管理場景、不同數(shù)據(jù)價(jià)值下的應(yīng)用數(shù)據(jù)。為了實(shí)現(xiàn)這一功能，數(shù)據(jù)清洗這一環(huán)節(jié)是重中之中。

接下來我們就看看ETL數(shù)據(jù)清洗的工作原理及常用的ETL工具--Kettle

2、數(shù)據(jù)抽取

從各個(gè)數(shù)據(jù)源獲取不同數(shù)據(jù)給后面的數(shù)據(jù)倉庫使用，這是ETL數(shù)據(jù)處理的第一步，也是最重要的一步。這些來源不一、類型不一的數(shù)據(jù)被成功抽取后，才可以進(jìn)行轉(zhuǎn)換并加載到數(shù)據(jù)倉庫中。能否正確地獲取所需數(shù)據(jù)直接關(guān)系到后面多元化場景數(shù)據(jù)分析的效果的。數(shù)據(jù)倉庫典型的源系統(tǒng)是事務(wù)處理應(yīng)用，例如，一個(gè)銷售數(shù)據(jù)倉庫的數(shù)據(jù)源，可能是一個(gè)微信小程序、原OA、PSM、企業(yè)微信里的客戶聊天記錄、發(fā)送的往來文件等，也包含當(dāng)前每一個(gè)銷售人員客戶線索跟進(jìn)過程中相關(guān)操作的全部記錄。

設(shè)計(jì)和建立數(shù)據(jù)抽取過程，在ETL處理乃至整個(gè)數(shù)據(jù)倉庫處理過程中，一般是較為耗時(shí)的任務(wù)。因?yàn)?b>數(shù)據(jù)源很可能非常復(fù)雜并且缺少相應(yīng)的文檔或信息，因此只是決定需要抽取哪些數(shù)據(jù)可能就已經(jīng)非常困難了。而且數(shù)據(jù)都不是只抽取一次，而是需要以一定的時(shí)間間隔反復(fù)抽取，通過這樣的方式把數(shù)據(jù)的所有變化提供給數(shù)據(jù)倉庫，并保持?jǐn)?shù)據(jù)的及時(shí)性。除此之外，由于數(shù)據(jù)源不允許外部系統(tǒng)對它進(jìn)行修改，也不允許外部系統(tǒng)對它的性能和可用性產(chǎn)生影響，所以數(shù)據(jù)倉庫的抽取過程一定要能適應(yīng)這樣的需求。如果已經(jīng)明確了需要抽取的數(shù)據(jù)，下一步就該考慮從數(shù)據(jù)源抽取數(shù)據(jù)的方法了。

抽取方法的選擇高度依賴于數(shù)據(jù)源和目標(biāo)數(shù)據(jù)倉庫的業(yè)務(wù)需要。一般情況下，不能因?yàn)樾枰嵘龜?shù)據(jù)抽取的性能，而在數(shù)據(jù)源中添加額外的邏輯，也不能增加這些數(shù)據(jù)源的工作負(fù)載。下面介紹幾種數(shù)據(jù)抽取的方法：

全量抽取

即數(shù)據(jù)源中的數(shù)據(jù)全部被抽取。因?yàn)檫@種抽取類型涉及數(shù)據(jù)源中的所有的有效數(shù)據(jù)，所以不需要跟蹤自上次成功抽取以來的數(shù)據(jù)變化。數(shù)據(jù)源只需要原樣提供現(xiàn)有的數(shù)據(jù)而不需要附加的邏輯信息（比如抽取時(shí)間等）。全表導(dǎo)出的數(shù)據(jù)文件或者查詢源表所有數(shù)據(jù)的SQL語句，就是進(jìn)行全量抽取最好的技術(shù)實(shí)現(xiàn)方式。

增量抽取

即只抽取某個(gè)事件發(fā)生的特定時(shí)間點(diǎn)之后的數(shù)據(jù)。該事件發(fā)生的時(shí)間順序能夠反映數(shù)據(jù)的歷史變化，它可能是最后一次成功抽取，也可能是一個(gè)復(fù)雜的業(yè)務(wù)事件，如最后一次回款結(jié)算等。必須能夠標(biāo)識(shí)出特定時(shí)間點(diǎn)之后所有的數(shù)據(jù)變化。這些發(fā)生變化的數(shù)據(jù)可以由數(shù)據(jù)源自身來提供，例如能夠反映數(shù)據(jù)最后發(fā)生變化的時(shí)間標(biāo)記，或者是一個(gè)原始事務(wù)處理之外的，只用于跟蹤數(shù)據(jù)變化的變更日志表。大多數(shù)情況下，使用后者意味著需要在數(shù)據(jù)源上增加抽取邏輯。

在許多數(shù)據(jù)倉庫中，抽取過程不含任何變化數(shù)據(jù)捕獲技術(shù)。取而代之的是把數(shù)據(jù)源中的整個(gè)表抽取到數(shù)據(jù)倉庫過渡區(qū)，然后用這個(gè)表的數(shù)據(jù)和上次從數(shù)據(jù)源中抽取得到的表數(shù)據(jù)作比對，從而找出發(fā)生變化的數(shù)據(jù)。雖然這種方法不會(huì)對源系統(tǒng)造成很大的影響，但顯然需要考慮給數(shù)據(jù)倉庫處理增加的負(fù)擔(dān)，尤其是當(dāng)數(shù)據(jù)量很大的時(shí)候。

對變化數(shù)據(jù)的捕捉

在對源數(shù)據(jù)進(jìn)行抽取處理的過程中需要重點(diǎn)考慮增量抽取，也被稱為變化數(shù)據(jù)捕獲。假設(shè)一個(gè)數(shù)據(jù)倉庫，在每天夜里的12點(diǎn)開始從源系統(tǒng)抽取數(shù)據(jù)，那么增量抽取只需要抽出來過去24小時(shí)內(nèi)發(fā)生變化的數(shù)據(jù)即可。

變化數(shù)據(jù)捕獲也是建立準(zhǔn)實(shí)時(shí)數(shù)據(jù)倉庫的關(guān)鍵技術(shù)。當(dāng)你能夠識(shí)別并獲得最近發(fā)生變化的數(shù)據(jù)時(shí)，抽取及其后面的轉(zhuǎn)換、裝載操作顯然都會(huì)變得更高效，因?yàn)橐幚淼臄?shù)據(jù)量會(huì)小很多。遺憾的是，很多源系統(tǒng)很難識(shí)別出最近變化的數(shù)據(jù)，變化數(shù)據(jù)捕獲是數(shù)據(jù)抽取中典型的技術(shù)挑戰(zhàn)。常用的變化數(shù)據(jù)捕獲方法就是記錄時(shí)間戳。

3、數(shù)據(jù)轉(zhuǎn)換

從數(shù)據(jù)源獲取需要的數(shù)據(jù)后?，需要對其進(jìn)行統(tǒng)一數(shù)據(jù)類型、處理拼寫錯(cuò)誤、消除數(shù)據(jù)歧義、解析為標(biāo)準(zhǔn)格式等轉(zhuǎn)換，這就是數(shù)據(jù)轉(zhuǎn)換。數(shù)據(jù)轉(zhuǎn)換通常是最復(fù)雜的部分，也是ETL中用時(shí)最長的一步。數(shù)據(jù)轉(zhuǎn)換的范圍極廣，從單純的數(shù)據(jù)類型轉(zhuǎn)化到極為復(fù)雜的數(shù)據(jù)清洗技術(shù)。在數(shù)據(jù)轉(zhuǎn)換階段，為了能夠最終將數(shù)據(jù)裝載到數(shù)據(jù)倉庫中，需要在已經(jīng)抽取來的數(shù)據(jù)上應(yīng)用一系列的規(guī)則和函數(shù)。

數(shù)據(jù)轉(zhuǎn)換一個(gè)最重要的功能是清洗數(shù)據(jù)，目的是只有“合規(guī)”的數(shù)據(jù)才能進(jìn)入目標(biāo)數(shù)據(jù)倉庫。這步操作在不同系統(tǒng)間交互和通信時(shí)尤其必要，例如，一個(gè)系統(tǒng)的字符集在另一個(gè)系統(tǒng)中可能是無效的。另一方面，由于某些業(yè)務(wù)和技術(shù)的需要，也需要進(jìn)行多種數(shù)據(jù)轉(zhuǎn)換，例如下面的情況：

只加載有內(nèi)容的數(shù)據(jù)列。例如某列為空的的話，就不再加載。

統(tǒng)一數(shù)據(jù)編碼。例如，性別字段，有些系統(tǒng)使用的是1和0，有些是‘M’和‘F'，有些是‘男’和‘女’，統(tǒng)一成‘M’和‘F'。

自由值編碼。例如，將‘Male’改成‘M'。

預(yù)計(jì)算。例如，產(chǎn)品單價(jià) * 購買數(shù)量 = 金額。

基于某些規(guī)則重新排序以提高查詢性能。

合并多個(gè)數(shù)據(jù)源的數(shù)據(jù)并去重。

預(yù)聚合。例如，匯總銷售數(shù)據(jù)。

行列轉(zhuǎn)置。

將一列轉(zhuǎn)為多列。例如，某列存儲(chǔ)的數(shù)據(jù)是以逗號(hào)作為分隔符的字符串，將其分割成多列的單個(gè)值。

合并重復(fù)列。

預(yù)連接。例如，查詢多個(gè)關(guān)聯(lián)表的數(shù)據(jù)。

數(shù)據(jù)驗(yàn)證。針對驗(yàn)證的結(jié)果采取不同的處理，通過驗(yàn)證的數(shù)據(jù)交給裝載步驟，驗(yàn)證失敗的數(shù)據(jù)或直接丟棄，或記錄下來做進(jìn)一步檢查。

4、數(shù)據(jù)裝載

ETL的最后步驟是把轉(zhuǎn)換后的數(shù)據(jù)裝載進(jìn)目標(biāo)數(shù)據(jù)倉庫。這步操作需要重點(diǎn)考慮兩個(gè)問題，一是數(shù)據(jù)裝載的效率，二是一旦裝載過程中途失敗了，如何再次重復(fù)執(zhí)行裝載過程。

即使經(jīng)過了轉(zhuǎn)換、過濾和清洗，去掉了部分噪聲數(shù)據(jù)，但需要裝載的數(shù)據(jù)量還是很大的。執(zhí)行一次數(shù)據(jù)裝載可能需要幾個(gè)小時(shí)的時(shí)間，同時(shí)需要占用大量的系統(tǒng)資源。要提高裝載的效率，加快裝載速度，可以從以下幾方面入手：

首先保證足夠的系統(tǒng)資源。數(shù)據(jù)倉庫存儲(chǔ)的都是海量數(shù)據(jù)，所以要配置高性能的服務(wù)器，并且要獨(dú)占資源，不要與別的系統(tǒng)共用。

其次是在進(jìn)行裝載時(shí)，要禁用數(shù)據(jù)庫約束（唯一性、非空性，檢查約束等）和索引，當(dāng)裝載過程完全結(jié)束后，再啟用這些約束，重建索引，這種方法會(huì)很大的提高裝載速度。在數(shù)據(jù)倉庫環(huán)境中，一般不使用數(shù)據(jù)庫來保證數(shù)據(jù)的參考完整性，即不使用數(shù)據(jù)庫的外鍵約束，它應(yīng)該由ETL工具或程序來維護(hù)。

數(shù)據(jù)裝載過程中可能由于多種原因而失敗，比如裝載過程中某些源表和目標(biāo)表的結(jié)構(gòu)不一致而導(dǎo)致失敗，而這時(shí)已經(jīng)有部分表裝載成功了。在數(shù)據(jù)量很大的情況下，如何能在重新執(zhí)行裝載過程時(shí)只裝載失敗的部分是一個(gè)不小的挑戰(zhàn)。對于這種情況，實(shí)現(xiàn)可重復(fù)裝載的關(guān)鍵是要記錄下失敗點(diǎn)，并在裝載程序中處理相關(guān)的邏輯。還有一種情況，就是裝載成功后，數(shù)據(jù)又發(fā)生了改變（比如有些滯后的數(shù)據(jù)在ETL執(zhí)行完才進(jìn)入系統(tǒng)，就會(huì)帶來數(shù)據(jù)的更新或新增），這時(shí)需要重新再執(zhí)行一遍裝載過程，已經(jīng)正確裝載的數(shù)據(jù)可以被覆蓋，但相同數(shù)據(jù)不能重復(fù)新增。簡單的實(shí)現(xiàn)方式是先刪除再插入，或者用replace into、mergeinto等類似功能的操作。

裝載到數(shù)據(jù)倉庫里的數(shù)據(jù)，經(jīng)過匯總、聚合等處理后交付數(shù)據(jù)可視化、儀表盤等報(bào)表工具做進(jìn)一步的展示和數(shù)據(jù)分析了。

5、常見的ETL工具

1、kettle

底層是JAVA，無需安裝，數(shù)據(jù)抽取高效穩(wěn)定

擴(kuò)展性好，成本低、免費(fèi)開源、部署簡單

缺點(diǎn)是處理速度慢性、不支持實(shí)時(shí)同步，支持千萬級(jí)別數(shù)據(jù)，數(shù)量太大時(shí)會(huì)吃力；

2、datastage

ETL中處理速度最快，性能最強(qiáng)的工具

IBM開發(fā)的，收費(fèi)用的

數(shù)據(jù)處理速度快、有技術(shù)支持、支持并行處理

部署復(fù)雜，對硬件要求高

3、informatica

數(shù)據(jù)處理速度快，穩(wěn)定，有商業(yè)化的技術(shù)支持

部署難度適中

數(shù)據(jù)監(jiān)控功能不直觀、支持實(shí)時(shí)，效率低

國內(nèi)市場占用率最高的是kettle、informatica，在這里我們簡要說下kettle。

Kettle里主要有“轉(zhuǎn)換”和“作業(yè)”兩個(gè)功能模塊。轉(zhuǎn)換是ETL解決方案中最主要的部分，它處理ETL各階段各種對數(shù)據(jù)的操作。轉(zhuǎn)換有輸入、輸出、檢驗(yàn)、映射、加密、腳本等很多分類，每個(gè)分類中包括多個(gè)步驟，如輸入轉(zhuǎn)換中就有表輸入、CSV文件輸入、文本文件輸入等很多步驟。轉(zhuǎn)換里的步驟通過跳（hop）來連接，跳定義了一個(gè)單向通道，允許數(shù)據(jù)從一個(gè)步驟流向另外一個(gè)步驟。在Kettle里，數(shù)據(jù)的單位是行，數(shù)據(jù)流就是數(shù)據(jù)行從一個(gè)步驟到另一個(gè)步驟的移動(dòng)。

轉(zhuǎn)換是以并行方式執(zhí)行的，而作業(yè)則是以串行方式處理的，驗(yàn)證數(shù)據(jù)表是否存在這樣的操作就需要作業(yè)來完成。一個(gè)作業(yè)包括一個(gè)或多個(gè)作業(yè)項(xiàng)，作業(yè)項(xiàng)是以某種順序來執(zhí)行的，作業(yè)執(zhí)行順序由作業(yè)項(xiàng)之間的跳（hop）和每個(gè)作業(yè)項(xiàng)的執(zhí)行結(jié)果決定。和轉(zhuǎn)換一樣，作業(yè)也有很多分類，每個(gè)分類中包括多個(gè)作業(yè)項(xiàng)，如轉(zhuǎn)換就是一個(gè)通用分類里的作業(yè)項(xiàng)。作業(yè)項(xiàng)也可以是一個(gè)作業(yè)，此時(shí)稱該作業(yè)為子作業(yè)。

Kettle非常容易使用，其所有的功能都通過用戶界面完成，不需要任何編碼工作。你只需要告訴它做什么，而不用指示它怎么做，這大大提高了ETL過程的開發(fā)效率。