kettle自動化的那些事

一、kettle介紹

  Kettle 是一款國外開源的 ETL 工具，純 Java 編寫，綠色無需安裝，數據抽取高效穩定(數據遷移工具)。Kettle 中有兩種腳本文件，transformation 和 job，transformation 完成針對數據的基礎轉換，job 則完成整個工作流的控制。

二、ETL介紹

  ETL是將業務系統的數據經過抽取、清洗轉換之後加載到數據倉庫的過程，目的是將企業中的分散、零亂、標準不統一的數據整合到一起，爲企業的決策提供分析依據， ETL是BI（商業智能）項目重要的一個環節。

三、ETL實現細說

  其實實現ETL功能的工具很多，我熟悉並使用過的：Informatica PowerCenter、kettle、sql、PLSQL編程、python等等。

分別簡單聊聊這些實現ETL的手段：

(1)、Informatica PowerCenter

  此軟件是商用的，網上成熟的中文資料比較少，而且版本較舊。英文資料居多，給很多初學者帶來了困擾。國內最出名的大神就是楊曉東，國內的中文資料幾乎全是楊曉東分享的，資料對應的版本還停留在7.6和8.5。(我當年學的時候，自己買了書，在淘寶上淘的視頻，還在楊曉東的羣裏打醬油，經過6個月的努力，才得上手正常工作)

(2)、kettle

  此軟件是開源的，純java編寫，網上文檔和視頻資料特別多，有很多人在博客分享自己的案例。(從第一次接觸Kettle，到靈活使用，我一共花了2天時間。這主要受益於我的技術沉澱：java編程、sql、Informatica PowerCenter的使用)

(3)、sql

  一提到sql，各位感覺只要是個幹IT的人就會。你以爲就是select\insert\update\delete，你就實現etl了？醒醒吧鐵子，你如果這樣想，你永遠吃不上4個菜!!

此處提到的sql，要與你工作中的業務相結合，你得吃透業務，然後編寫出來的sql。你操作完sql後，你還要驗證一下，結果和你想的對不對？時效如何？如果性能不好，你還要sql調優。一說調優，有些人會說百度上找啊，文章特別多。你會發現很文章，好像是近親，天下學文一大抄啊！而且你不懂業務，你要調優純扯淡。

舉例說明：

小明飯量很大，他一頓飯吃了一隻烤鴨，小明說我剛剛吃飽了，吃飽了太幸福了。

你的飯量很小，你平時每頓就吃半個饅頭的。你聽了小明的說法，你也吃一隻烤鴨，你肚皮都撐爆了，一張嘴烤鴨都快從嘴中吐出來了。

上面的小例子說明，不同的業務規則\數據量\訪問量，對應的調優手段也不同。其實也是有一些通用的調優方法，就從sql的寫法上實現的。可以來參考我的博文：點擊 oracle調優筆記(揭開傳言的面紗)

(4)、PLSQL編程

  PLSQL是oracle的高級編程，如果想使用PLSQL編程實現ETL，基本要求和上面第3點sql的要求很相似。另外你要熟悉PLSQL語法，並能排除PLSQL運行時產生的Exception。(我做的是ORALCE DBA，對PLSQL編程非常熟悉，所以此處提到PLSQL。大部分數據都有自己編程，只要你熟悉都可以實現ETL)。如果你對plsql編程也感興趣，可以參考我的博文：點擊 史上最簡單的數據抽取

(5)、Python

  Python是一種編程語言，它裏面集成了很多數學函數、工具類，都可以幫我們更加方便的實現ETL操作。如果你要操作的數據源是excel和csv，你就可以使用pandas，太方便了。

四、kettle基本概念

  (1)ktr轉換：將一個或多個數據源組裝成一條數據流水線，根據業務要求，利用Kettle內部的組件，進行數據處理，最後輸出到某一個地方(文件或數據庫)。

  (2)kjb作業：可以調度設計好的一個或多個轉換，也可以執行一些文件處理(比較\刪除等)，還可以往ftp上傳和下載文件，發送郵箱，執行shell命令等等。

舉例說明：

  ktr轉換：ktr是根據業務編寫的，它們分別實現不的操作，ktr會有多個。ktr就是建築工地上幹活的工人們，每個工人的技能不同(小工、瓦匠、鋼筋工、木工、塔吊司機)

  kjb作業：kjb管理多個ktr。kjb就是包工頭，包工頭管理多個工人。當有任務的時候，直接找包工頭說，包工頭就會根據任務的要求，找不同技術的工人，來完成工作。

五、業務說明

  我們公司的業務是多源的，有一條業務線是服務於國家醫保局，根據全國各地名提供的，醫院HIS\PACK\LIS\財務數據，及醫保局的結算數據，進行數據分析和篩查，配合業務專家進行飛行檢查，找出違規收費等問題。對醫院進行管控，實現就醫的合理收費，醫患和睦，使用最少的醫保費用，幹更多的事，爲老百姓謀幸福。

六、工作的流程

  根據業務專家的經驗，將醫保中出現的各種違規收費規則，寫成kettle的轉換，每一個轉換就是一個檢查規則，這樣的檢查規則有好幾百個，部分如下圖：

這麼多的轉換，不可能一個一個去運行，我把每10個轉換交給1個作業來管理調用，部分截圖如下：

這樣的話就方便多了，我只需要調用這幾十個作業，就可以對間接的調用這幾百個轉換了。

七、工作的困難

  最開始只有一個地區的10家醫院，我們根據地區創建數據庫，把本地區的所有醫院的數據保存在對應庫中hive的分區表裏，使用醫院的醫療機構編碼作爲分區Key。每家醫院有自己的篩查轉換文件，因爲轉換文件中的條件會使用到各醫院的醫療機構編碼不同，每家醫院篩查的結果保存的路徑也不同。每家醫院對應500個轉換和50個作業文件，10家醫院我共修改5000個作文件和500個作業文件，當然了是使用工具快速查找替換的。

  但是現在有多個地區，醫院數也由最初的10家變成了100多家。 我還是手工去替換修改的話，我當場就去世了。現在還要修改數據庫連接信息，因爲多個地區，對應多個數據庫。後期全國的數據都上來了，要修改的轉換和作業文件，太多了。我也不用活了，直接就腦梗死了。

八、解決困難

  前面提到了每個醫院都有自己的幾百個篩查轉換，所有醫院的數據結構是不變的。能不能只保留一套通用的篩查轉換文件，因爲篩查規則是一樣的，就是各醫院的醫療機構編碼、結果文件輸出路徑、數據庫連接信息是不同的。於是想到了可以使用java調用kettle，調用kettle時傳入不同的參數，在kettle中是可以使用命名參數來接收，這樣就實現了程序的複用性、靈活性。

  數據庫相關替換的參數有：數據庫名、數據庫IP、端口號、用戶名、密碼。

  任務相關替換的參數有：醫院的醫療機構編碼、作業存放的結對路徑、篩查結果文件保存路徑。

附上 作業和轉換的 命名參數 案例，如下截圖：

  (1)kjb作業 全局的命名參數

(2)kjb作業中調用的轉換 命名參數

(3)ktr轉換 全局的命名參數

(4)ktr轉換 DB連接的參數

(5)結果文件輸出路徑 參數

  好多年不幹java開發了，現在又要動代碼，想想就頭疼。但工作還要繼續，日子還要過。上百度查查吧，還真查到了SpringBoot調用kettle的文章。剩下的就是動手幹吧，再次化身程序員。

九、項目代碼結構

十、項目代碼運行

(1)、啓動springboot

  InsuranceETLApplication類中，右鍵------>Run As----->Java Application

(2)、在瀏覽器上輸入

http://localhost:9090/kettle/task

  (3)、控制檯輸出打印

從控制檯輸出的信息中，可以看到現在正在調用的load_violation_data_to_hive_01作業，並輸出了該作業中當前轉換執行的sql語句詳細信息。

就寫這麼多吧，如果你也在搞ETL自動化，咱們可以多交流。我在開發代碼時，遇到了太多的坑，一起學習一起進步！

學習累了讓自己笑一下吧，告訴自己未來的生活是美好的！！！