ETL工具一一kettle的使用

ETL工具之kettle的使用

• • • • 1、ETL及其常用工具
      • 2、kettle的下載下傳安裝
      • • 2.1 kettle簡介
        • 2.2 kettle下載下傳安裝
      • 3、kettle的使用
      • • 3.1 kettle之轉換的基本概念
        • 3.2 輸入控件的使用
        • 3.3 輸出控件的使用
        • 3.4 腳本控件的使用
        • 3.5 案例1

1、ETL及其常用工具

ETL：Extract-Transform-Load的縮寫，用來描述将資料從來源端經過抽取（extract）、轉換（transform）、加載（load）至目的端的過程。ETL是将業務系統的資料經過抽取、清洗轉換後加載到資料倉庫的過程。

ETL詳細介紹：https://www.cnblogs.com/yjd_hycf_space/p/7772722.html

ETL常用工具：

1、informatica、Datastage

information專業的ETL工具，收費，價格比Datastage便宜一點，适合大規模的ETL項目，Datastage是IBM公司的ETL工具，也要收費，适合大規模的ETL項目，這兩個都是分用戶端和服務端的，因為收費速度比較快，比較穩定

2、Talend

java編寫的，分為開源版和企業版，按照以後也可以界面化操作，不過隻能job，沒有transform，說實話沒用過，隻是看了下，感覺适合大型項目，操作有點複雜

3、Kettle

​ kettle是基于Java的ETL工具，隻需要jvm環境就可以使用，并且是免費開源的，可跨平台，擴充性比較好，提供界面化操作，操作還比較簡單

2、kettle的下載下傳安裝

2.1 kettle簡介

kettle家族目前包括4個産品：Spoon Pan Chef Kitchen

Spoon：kettle的界面化操作，可以通過圖形界面來操作ETL的轉換過程

Pan：允許批量運作由Spoon設計的ETL轉換，Pan是一個背景執行的程式，沒有圖形界面

Chef：允許建立任務(job)，任務通過允許每個轉換，任務，腳本等等，更有利于自動化更新資料倉庫的複雜工作。任務通過允許每個轉換，任務，腳本等等，任務将會被檢查，看是否正确的執行。

Kitchen：允許批量使用由Chef設計的任務，Kitchen是一個背景運作的程式

2.2 kettle下載下傳安裝

下載下傳位址：https://sourceforge.net/projects/pentaho/files/Data Integration/

kettle綠色安裝，下載下傳kettle的zip包，解壓即可使用，下面就是解壓以後的安裝目錄，其中lib檔案夾中放的都是jar包，如果kettle要連結mysql配置，需要将資料庫連接配接驅動包放到這裡，要引用自定義java代碼jar包也放到這裡

Windows系統直接運作Spoon.bat檔案，就可以打開運作spoon可視化界面，通過這個圖形界面可以進行你想做的ETL操作。界面如下：

kettle的控件有2種，分為：作業（job）和轉換（Transform)

轉換：主要是針對資料的各種處理，一個轉換可以包含多個步驟（Step)，我目前的操作都是根據轉換完成的。

作業：更加關心更為宏觀的資料處理，比如檔案和目錄操作等等。一個作業可以包含多個作業項（Job Entry）。作業和轉換均可作為一個作業項，也就是說，一個作業可以包含多個子作業和轉換。一個作業中的作業項之間是順序執行的。對于一個作業項來說，隻有當該作業項之前的所有作業項執行完畢後，才會執行該作業項。

3、kettle的使用

3.1 kettle之轉換的基本概念

建立了一個轉換後會有主對象樹和核心對象這兩個

主對象樹：

• DB連接配接是資料庫的連接配接，這裡我建立了一個mysql的連接配接命名為user
• Steps(步驟)：是這個轉換的步驟，我在這裡建立了三個步驟，表輸入 / java代碼 / 插入更新
• Hops(節點連接配接)：是每個步驟之間的連接配接，建立連接配接以後是一個串行的結構，執行了表輸入拿到資料 ----> 在執行java代碼，對資料進行處理 -----> 處理完以後執行更新資料步驟

這張表中展示的是所有的核心對象中的控件，上面的步驟都是一個個控件，表輸入是輸入控件中的，java代碼是腳本控件，插入/更新是輸出控件。

3.2 輸入控件的使用

上面是所有的輸入控件中包含的步驟，可以進行csv檔案和Excel檔案的輸入等等，功能還是很強大

簡單案例：輸入一個csv檔案

選擇檔案 ------> 建立 --------> 轉換 -------> ctrl+s儲存重命名

選擇 -------> 核心對象 -------> 輸入 --------> 輕按兩下csv檔案輸入控件，在右邊就會出現csv檔案輸入的步驟

• 1、選擇csv檔案輸入右鍵選中編輯步驟，彈出CSV檔案輸入彈窗
• 2、浏覽選擇要輸入的檔案
• 3、選擇檔案後擷取字段會自動識别檔案中的字段填充到上圖的表中
• 4、點選預覽，預覽檔案中指定行數的資料
• 5、點選确定儲存資料

3.3 輸出控件的使用

以上是輸出控件中包含的，可以輸出excel檔案，輸出表，插入/更新表，輸出配置檔案都可以

輸出步驟必須在輸入步驟後面，因為一定要有輸入流以後才能輸出資料，否則不能輸出資料

簡單案例：輸入一個csv檔案後，輸出Excel檔案 ，輸入步驟參考上面

• 點選輸出 ------> 輕按兩下Excel輸出 ------> 右邊出現Excel輸出步驟後
• 按住shift鍵，滑鼠右鍵建立csv檔案輸入和Excel輸出的節點連接配接，選擇主輸出步驟
• 右鍵Excel輸出，選擇編輯步驟 --------> 浏覽指定輸出檔案目錄 -------> 點選确認儲存
• 點選運作會執行這個轉換，如果報錯會在日志中輸出報錯資訊

3.4 腳本控件的使用

腳本控件可以寫java代碼sql腳本還有JavaScript代碼，我常用的就是java腳本

簡單案例：添加java腳本對資料進行轉換，這裡也是需要先使用輸入控件擷取資料，然後java腳本對資料進行處理，我直接講java腳本處理，就省略掉前面輸入的步驟

• 點選腳本 ------> 輕按兩下java代碼 -------> 選中java代碼右鍵選擇編輯步驟
• 點選展開Code Snippits ------> 點選展開Common use ------> 點選Main，出現右邊的代碼塊
• 這是一個processRow()方法，在這裡面根據模闆添加自己的java代碼，處理資料，上面的框框就是我自定義的java代碼，通過code字段是否為空，給newcode字段指派
• 下面是在資料流中建立了一個字段newcode，String類型，上面的代碼就是給newcode的指派
• 完成代碼後點選測試類，如果彈出上面的彈窗說明代碼沒問題，可以選擇預覽資料，如果報錯會彈出報錯資訊

3.5 案例1

根據我們時間的因為需求，将tb_product_cash_sale_sku新增三個未稅價格字段，并且根據不同的公司現在是含稅價還是不含稅價，及其稅率，計算出另外三個價格

思路：1、在表中建立三個不含稅字段，建立輸入表控件，将資料輸入到流中

​ 2、建立java腳本，在資料流中建立含稅價和未稅價共6個字段，然後用java代碼根據公司重新計算每一個價格并指派給資料流中建立的價格字段

​ 3、建立插入/更新步驟，将資料庫中的價格字段重新對應資料流中建立的價格字段進行更新操作

步驟：

• 1、建立轉換以後， 建立資料庫連接配接

現在對表操作，建立完轉換以後要建立資料庫連接配接，選擇mysql連接配接，主機名稱是mysql的主機IP，資料庫名稱是要連接配接的資料庫的名稱，下面就是mysql的端口，使用者名和密碼，連接配接方式使用的是jdbc連接配接池，這裡需要注意的是連接配接mysql之前要在kettle的lib檔案夾下放入mysql連接配接驅動jar包，點選測試會告知是否連接配接成功，以及連接配接失敗的報錯原因

選擇選項可以配置其他資料庫連接配接參數，這些參數可以影響讀取和寫入表的速度

• 2、建立輸入表步驟，在編輯步驟中擷取tb_product_cash_sale_sku資料

點選擷取SQL查詢語句選擇要輸入的表，這個sql是自動填充的，如果要連表查詢，這裡可以寫自定義sql擷取資料，框框中的三個字段是新增的未稅價格，後期存儲的就是未稅的價格

• 3、建立java腳本，并且建立輸入表和java腳本直接的節點連接配接

  

  在資料流中建立6個字段，重新儲存價格，上面的java代碼是根據公司不同計算了每個公司的6種價格，并指派給資料流中新建立的這幾個字段，上面是未稅價格，下面是對應的含稅價
• 4、建立插入/更新步驟

  

• 目标表：選擇要修改的表
• 送出記錄資料：是每次修改送出的資料，預設值是1000，因為這張表資料有4萬多，我設定5萬，一次性送出
• 用來查詢的關鍵字：是根據那個字段鎖定資料并更新資料，這裡標明的是id
• 流字段：是輸入流中的字段，也包含java代碼中建立的字段都是資料流中的字段
• 表字段：是目标表中的字段

我隻修改了價格字段，是以上面其他字段都沒變，值是将6個價格字段重新指派更新為，我在java代碼那裡建立的6個資料流字段