大資料與機器學習：實踐方法與行業案例.2.3 ETL

<b>2.3　etl</b>

<b></b>

etl是extract-transform-load的縮寫，是資料的抽取、轉換、加載過程，當需要将資料從一個環境轉移到另一個環境時（例如從生産環境到分析環境），或者需要對資料進行進一步加工處理時（例如在分析環境中，在資料倉庫基礎上産出每日交易量名額），即需要借助etl過程。

etl是建構資料閉環自循環過程的重要途徑，幾乎每個環節均可以通過etl來完成。通過專門的etl工具，定制滿足業務要求的etl作業，并結合自動排程工具，即可以實作資料的自動循環。

2.3.1　etl工具

目前國内商用etl工具以ibm的datastage為代表，該etl工具在金融行業有廣泛的應用，但datastage價格昂貴，許多公司從成本上考慮，采用了pentaho的開源etl工具kettle。

在本書成書時，盡管kettle本身仍然存在一些bug，但由于部署簡單、使用友善并且完全免費的特點，使其成為越來越多使用者的首選。

1. 開源etl工具：kettle

kettle是國外的一款開源etl工具，中文名稱為水壺。在寫作本書的時候，官網上最新的釋出版本為data integration 5.4.0，下載下傳後會得到一個pdi-ce-5.4.x.x-xxx.zip的壓縮包，使用解壓縮工具解壓該壓縮包，便可以開始使用kettle了。

kettle可以運作在windows環境或者linux環境，如果運作在windows環境，則進入解壓後的data-integration目錄，可以看到spoon.bat（linux環境為spoon.sh），輕按兩下spoon.bat打開kettle的圖形界面，如圖2-8所示。

圖2-8　kettle的圖形界面

圖2-8展示了kettle的兩個基本元件：轉換（transformation）和作業（job）。轉換用來定義資料處理的一個或多個步驟（step），如讀取檔案、過濾輸出行、資料清洗、資料加載至目的資料庫等。作業用來将多個定制完成的轉換串接起來，使轉換能夠按照一定的順序和規則執行。

定義完成的轉換和作業可以使用程式或者腳本進行調用，首先将定義的轉換或者作業存儲在kettle的資源庫（repository）中，然後通過kettle提供的pan和kitchen元件分别進行調用（pan用來調用transformation，kitchen用來調用job），我們将在随後的内容介紹調用方法。

通常，首次接觸“資源庫”這個詞彙會讓人感覺難以了解，但撇開這個名詞本身，它本質上就是建立關系資料庫中的一些配置表，這些配置表用來存儲轉換或者作業的相關資訊（如轉換的名稱、資料庫連接配接的字元串等），pan和kitchen元件可以根據資源庫裡的這些資訊來調用對應的轉換或者作業。

在使用資源庫之前，需要先建立一個資源庫，在圖形界面中進入工具→資源庫→連接配接資源庫，即可以出現相應的建立向導，通過向導可以輕松完成資源庫的建立。

一旦成功建立并連接配接了資源庫，随後進行的檔案讀取和儲存操作就可以對應到該資源庫。例如，作者在自己的測試環境中連接配接資源庫後，單擊菜單檔案→打開，kettle會自動讀取資源庫中儲存的轉換和作業資訊，并将所有的轉換和作業展示出來以供選擇，如圖2-9所示。

圖2-9　kettle資源庫中的轉換和作業

這些存放在資源庫中的轉換和作業，可以通過pan和kitchen元件進行調用，pan是kettle提供的用于批量調用轉換的工具，kitchen是用于批量調用作業的工具。在data-integration目錄中可以找到pan.bat和kitchen.bat（windows環境對應pan.sh，linux環境對應kitchen.sh）。

在linux環境中可以使用如代碼清單2-37所示的腳本調用轉換。

代碼清單　2-37

pan.sh -rep=kettle_rep_test -trans="batch-into-table" -dir=/ -user=admin

-pass=admin

    -level=basic

調用作業可以使用如代碼清單2-38所示的指令。

代碼清單　2-38

kitchen.sh -rep=kettle_rep_test -job="hive-oracle-test" -dir=/ -user=admin

-level=basic

如果有很多轉換和作業需要運作，那麼可以将這些指令寫在一個shell腳本中，然後通過linux系統自帶的crontab進行排程，或者通過專門的排程工具進行排程（請參閱2.4節）。

kettle方面的推薦書籍：《pentaho kettle解決方案：使用pdi建構開源etl解決方案》（作者：matt casters、roland bouman、jos van dongen著，初建軍、曹雪梅譯，電子工業出版社）。

2. 商用etl工具：datastage

datastage是ibm infosphere開發的一款商用etl工具，是ibm infosphere information server套件的簡稱。該套件包含三個元件：infosphere datastage and qualitystage designer、infosphere datastage and qualitystage director、infosphere datastage and qualitystage administrator。

infosphere datastage and qualitystage designer用于建立datastage作業。

infosphere datastage and qualitystage director用于驗證、排程、運作和監視datastage作業。

infosphere datastage and qualitystage administrator用于系統管理（例如設定 ibm infosphere information server 使用者，記錄、建立和移動項目，設定清除記錄的條件等）。

顯然，相對于kettle的輕量級部署，datastage本身的架構已經非常複雜，相應的部署要求也比較高。datastage作為ibm公司的一款産品，其目标客戶群為大型企業，它甚至支援在大型機上運作etl作業（能夠生成可在大型機上運作的cobol代碼）。由于目前國内的銀行仍然沿用ibm公司的大機系統，是以datastage在國内的客戶多存在于金融行業。

datastage價格昂貴，一般需要支付年服務費、購買license等。同樣，由于收取年服務費，是以能夠提供很好的教育訓練和技術支援。是以，需要根據企業自身特點選擇商用etl工具或者開源etl工具，對于小型公司而言，開源工具仍是首選。

2.3.2　etl作業

etl作業是按照一定順序組織的資料處理過程，它将資料處理的各個環節關聯起來，并定義各個環節的觸發規則，進而完成整個資料處理流程。

以kettle為例，etl作業由多個步驟（或稱為作業項）組成，如圖2-10所示。該作業除了開始的“start”與最後的“成功”步驟外，還包含以下三個實體作業項：

1）檢測昨日交易明細檔案是否存在。

2）sql server批量加載。

3）統計昨日交易。

作業項1）負責檢測昨日的交易明細檔案是否存在，如果該步驟傳回“true”，則進行下一個作業項，否則退出作業。

作業項2）将昨日的交易明細檔案批量加載至sql server資料庫中，該作業項需要指定檔案名稱、格式檔案等相關資訊，使用的指令即是“sql server ：bcp in”章節中講述的方式。如果該作業項執行成功，那麼昨日交易明細資料将增量更新至sql server的資料庫表中；如果該作業項執行失敗，則退出作業。

作業項3）對作業項2）中加載的交易資料進行統計，該作業項執行一段sql腳本，并将計算結果存儲在對應的結果表中。

圖2-10　etl作業示例

圖2-10中的etl作業按照預定的順序将多個作業項串聯起來，完成一個完整的資料加載和統計過程，該過程的每個步驟作為一個作業項獨立存在，僅當上遊的作業項執行成功後，才開始下一個作業項的執行。

需要注意的是，etl工具僅用于作業的建立和簡單排程，如果需要周期性地執行etl作業，則需要使用專門的排程工具。

為了使etl作業便于排程和監控，為etl作業制定規範是一項非常重要的工作，良好的etl作業命名規範和日志規範可以極大地友善作業監控和錯誤排查。接下來深入介紹這兩個實用性的操作規範：etl作業命名規範和etl作業日志規範。

1. etl作業命名規範

etl作業命名規範主要是為了通過作業名稱來辨別作業的歸屬、重要程度、主要用途等，以便于作業的自動排程和監控，它不是etl工具的強制要求。

通常需要根據企業具體的管理要求為etl作業制定命名規範，該規範要盡可能地反應作業的歸屬用途等，并且長度不能太長，下面給出一個範例：

[員工編号].[作業類型].[作業描述]

該命名規範包含三個部分，用“.”分割：

1）員工編号，用于描述作業的歸屬，一般使用作業建立人或者負責人的員工編号。

2）作業類型，用于描述作業的重要程度，比如将作業類型定義為analysis、report、product等，分别對應分析、報表、生産。不同的作業類型的作業出現錯誤時，可以根據重要程度進行不同等級的報警通知。

3）作業描述，用于描述作業的主要功能，比如圖2-10中的作業可以描述為trx_load_and_static，或者使用中文描述（如果etl工具支援中文名稱）。

命名規範同樣可以規定etl作業中出現的字母統一使用大寫或者小寫，本書采用小寫的方式。按照這個規範，圖2-10中的etl作業将命名為：z06837. analysis.trx_load_and_static，其中z06837是員工編号，analysis說明該作業屬于分析型的作業。

按照規範進行作業命名後，作業監控程序便可以自動發現運作失敗的作業，并且根據作業名稱中的員工編号找到該員工的郵箱位址和手機号碼（需預先在資料庫中儲存員工編号與郵箱位址和手機号碼的對應關系），并發送郵件通知和短信提醒，還可以根據作業類型在郵件中标記緊急程度，這部分内容将在2.5節進一步展開。

2. etl作業日志規範

etl作業一般包含多個步驟（作業項），作業運作中某些步驟可能運作失敗，記錄下失敗原因對于錯誤排查非常重要。

雖然etl工具都自帶日志記錄功能，但系統自動記錄的日志資訊一般可讀性很差且缺乏靈活性。etl作業日志規範就是要自定義一個統一且靈活的日志記錄方式，以便于作業的監控和錯誤排查。下面給出一個etl作業日志規範的範例：

1）etl作業中需包含記錄作業開始和作業完成的作業項。

2）每個作業項均需增加作業項運作失敗分支，并發送郵件通知。

3）日志記錄統一記錄在資料庫表etl_job_log中。

4）日志記錄中的狀态在作業狀态表etl_job_status中統一定義。

5）使用統一的存儲過程進行日志記錄。

根據規範1、2的要求，圖2-10中的etl作業将修改為圖2-11所示的樣子。

圖2-11　滿足規範1和規範2的etl作業

圖2-11中，作業開始後增加了一個作業項“記錄日志：作業開始”，這個作業項往etl_job_log表中插入一條新記錄，記錄今日該作業的開始時間等相關資訊。另外，在作業的最後加入了作業項“記錄日志：作業完成”，用于更新作業的最終狀态，圖2-12是表etl_job_log中記錄的部分etl作業日志。

圖2-12　作業日志表中的部分記錄

在其餘的三個作業項上面，分别增加了運作錯誤分支。這些錯誤分支分别記錄對應的作業項出錯資訊，同時記錄下作業項出錯時的系統時間，并在錯誤日志記錄完成後，發送失敗通知郵件。

修改後的etl作業會在運作過程中将作業狀态自動記錄到資料庫中，随後bi工具可以根據資料庫中的日志記錄展示監控報表或者進行錯誤報警。

日志記錄表etl_job_log建立表腳本如代碼清單2-39所示。

代碼清單　2-39

create table etl_job_log

(

id   bigint not null auto_increment comment '自增長id',

job_name varchar(100) not null comment '作業名稱',

run_date varchar(20) comment '運作日期',

start_time datetime comment '作業開始時間',

end_time datetime comment '作業結束時間',

upt_time datetime default current_timestamp comment '更新時間',

job_status int not null comment '作業狀态id',

remark varchar(1000) comment '作業狀态補充說明',

primary key (id)

);

其中，remark字段的記錄原則為：作業項名稱+錯誤說明，例如，“sql server批量加載：出錯。”可以友善追蹤到作業出錯的作業項。

job_status字段是表etl_job_status的外鍵，記錄的是狀态id，其對應的狀态描述可以通過關聯表etl_job_status得到。

作業狀态表etl_job_status建立表腳本如代碼清單2-40所示。

代碼清單　2-40

create table etl_job_status

status_desc varchar(1000) not null comment '狀态描述',