第0章 ETL簡介

ETL (Extract-Transform-Load 的縮寫，即資料抽取、轉換、裝載的程序)，對于企業或行業應用來說，我們經常會遇到各種資料的處理，轉換，遷移，所以了解并掌握一種ETL工具的使用，必不可少，

市面上常用的ETL工具有很多，比如Sqoop，DataX, Kettle, Talend 等，作為一個大資料工程師，我們最好要掌握其中的兩到三種，這里我們要學習的ETL工具是Kettle!

第1章 Kettle簡介

1.1 Kettle是什么

Kettle是一款國外開源的ETL工具，純java撰寫，可以在Window、Linux、 Unix.上運行，綠色無需安裝，資料抽取高效穩定，

Kettle中文名稱叫水壺，該專案的主程式員MATT希望把各種資料放到一個壺里，然后以一種指定的格式流出，

Kettle這個ETL工具集，它允許你管理來自不同資料庫的資料，通過提供一個圖形化的用戶環境來描述你想做什么，而不是你想怎么做，

Kettle中有兩種腳本檔案，transformation 和 job，transformation 完成針對資料的基礎轉換，job則完成整個作業流的控制，

Kettle(現在已經更名為PDI, Pentaho Data Integration Pentaho資料集成)

1.2 Kettle的兩種設計

簡述：

• Transformation (轉換) ：完成針對資料的基礎轉換，

• Job (作業) ：完成整個作業流的控制，

區別：

• (1) 作業是步驟流，轉換是資料流，這是作業和轉換最大的區別，
• (2)作業的每一個步驟，必須等到前面的步驟都跑完了，后面的步驟才會執行；而轉換會一次性把所有控制元件全部先啟動(一個控制元件對應啟動一個執行緒)，然后資料流會從第一個控制元件開始，一條記錄、一條記錄地流向最后的控制元件;

1.3 Kettle的核心組件

• Spoon.bat / spoon.sh（重點）：是一個圖形化界面，可以讓我們用圖形化的方式開發轉換和作業（Windows選擇Spoon.bat；Linux選擇Spoon.sh）
• Pan.bat / pan.sh：利用Pan可以用命令列的形式執行由Spoon編輯的轉換和作業
• Kitchen.bat / kitchen.sh：利用Kitchen可以使用命令呼叫由Spoon編輯好的Job
• Carte.bat / Carte.sh：Carte是一個輕量級的Web容器，用于建立專用、遠程的ETL Server
  

1.4 Kettle的特點

第2章 Kettle安裝部署

2.1 Kettle 下載

2.1.1 下載地址

官網：官網地址

下載地址：各版本下載鏈接

百度網盤：百度網盤地址 ，提取碼：beeo

2.1.2 Kettle目錄說明

2.1.2 Kettle 檔案說明

2.2 Kettle 安裝部署

2.2.1 概述

在實際企業開發中，都是在本地Windows環境下進行 kettle 的 job 和 Transformation 開發的，可以在本地運行，也可以連接遠程機器運行

2.2.2 安裝

1）安裝 jdk，版本建議1.8及以上

2）下載kettle壓縮包，因kettle為綠色軟體，解壓縮到任意本地路徑即可

3）雙擊Spoon.bat,啟動圖形化界面工具，就可以直接使用了

2.3 Kettle 界面介紹

2.3.1 主界面

2.3.2 轉換

2.3.3 作業

2.4 Kettle轉換初次體驗

體驗案例：將 csv 檔案用 Kettle 轉換成 excel 檔案

1）在 Kettle 中新建一個轉換，然后選擇轉換下面的 “csv檔案輸入” 和 “excel檔案輸出” 拖至作業區

2）雙擊CSV檔案輸入檔案控制元件，在彈出的設定框里找到對應的csv檔案(test.csv).然后點擊下面的獲取欄位按鈕，將我需要的欄位加載到kettle中

3）按住鍵盤 shift 鍵，并且點擊滑鼠左鍵將兩個控制元件鏈接起來,鏈接時選擇 “主輸出步驟”

4）雙擊Excel輸出控制元件，在彈出的設定框里設定檔案輸出路徑和檔案名稱，然后點擊上的欄位框，依次點擊下面的獲取欄位和最小寬度，獲取到輸出欄位

5）點擊運行，啟動，查看轉換好的檔案

轉換成功：

2.5 Kettle 核心概念

2.5.1 可視化編程

Kettle可以被歸類為可視化編程語言，因為Kettle可以使用圖形化的方式定義復雜的ETL程式和作業流，

可視化編程一直是Kettle里的核心概念，它可以讓你快速構建復雜的ETL作業和減低維護作業量，它通過隱藏很多技術細節，使IT領域更貼近于商務領域，

Kettle里的代碼就是轉換和作業，

2.5.2 轉換

轉換(transaformation)負責資料的輸入、轉換、校驗和輸出等作業，Kettle 中使用轉換完成資料 ETL 全部作業，轉換由多個步驟(Step)組成，如文本檔案輸入，過濾輸出行，執行SQL腳本等，各個步驟使用跳(Hop)（連接箭頭） 來鏈接，跳定義了一個資料流通道，即資料由一個步驟流（跳）向下一個步驟，在 Kettle中資料的最小單位是資料行(row),資料流中流動其實是快取的行集(RowSet)

2.5.3 步驟

步驟(控制元件)是轉換里的基本的組成部分，快速入「]的案例中就存在兩個步驟，“CSV檔案輸入” 和 “Excel輸出”，

一個步驟有如下幾個關鍵特性：

• 1.步驟需要有一個名字，這個名字在同一個轉換范圍內唯一

• 2.每個步驟都會讀、寫資料行(唯一例外是 “生成記錄”步驟，該步驟只寫資料

• 3.步驟將資料寫到與之相連的一個或多個輸出跳(hop),再傳送到跳的另一端的步驟，

• 4.大多數的步驟都可以有多個輸出跳，–個步驟的資料發送可以被設定為分發和復制，
  分發是目標步驟輪流接收記錄，復制是所有的記錄被同時發送到所有的目標步驟
  

2.5.4 跳（Hop）

跳就是步驟之間帶箭頭的連線，跳定義了步驟之間的資料通路

跳實際上是兩個步驟之間的被稱之為行集的資料行快取，行集的大小可以在轉換的設定里定義，當行集滿了，向行集寫資料的步驟將停止寫入，直到行集里又有了空間，當行集空了，叢行集讀取資料的步驟停止讀取，直到行集里又有可讀的資料行

2.5.5 元資料

每個步驟在輸出資料行時都有對欄位的描述，這種描述就是資料行的元資料，

通常包含下面一些資訊：

• 名稱：資料行里的欄位名是唯一的，
• 資料型別：欄位的資料型別，
• 格式：資料顯示的方式，如 Integer 的 #、0.00
• 長度：字串的長度或者 BigNumber 型別的長度，
• 精度：BigNumber資料型別的十進制精度，
• 貨幣符號：￥
• 小數點符號：十進制資料的小數點格式，不同文化背景下小數點符號是不同的，一般是點“.”或 逗號“，”
• 分組符號：數值型別資料的分組符號，不同文化背景下數字里的分組符號也是不同的，一般是點“.”或逗號“,”或單引號 ’
  

2.5.6 資料型別

資料以資料行的形式沿著步驟移動，一個資料行是零到多個欄位的集合，欄位包含下面幾種資料型別，

• String:字符型別資料
• Number：雙精度浮點數，
• Integer：帶符號長整型(64位)，
• BigNumber：任意精度資料，
• Date：帶毫秒精度的日期時間值，
• Boolean：取值為true和false的布林值，
• Binary：二進制欄位可以包含影像、聲音、視頻及其他型別的二進制資料，
  

2.5.7 并行

跳的這種基王行集緩在的規則允許每個步驟都是由一個獨立的執行緒運行，這樣并發程度最高，這一規則也允許資料以最小消耗記憶體的資料流的方式來處理，在資料倉庫里，我們經常要處理大量資料，所以這種高并發低消耗的方式也是 ETL 工具的核心需求，

對于 kettle 的轉換，不能定義一個執行順序，因為所有步驟都以并發方式執行：當轉換啟動后,所有步驟都同時啟動，從它們的輸入跳中讀取資料，并把處理過的資料寫到輸出跳，直到輸入跳里不再有資料,就中止步驟的運行，當所有的步驟都中止了,整個轉換就中止了，

如果你想要一個任務沿著指定的順序執行，那么就要使用下面所講的“作業”!

2.5.8 作業

作業(Job)，負責定義一-個完成整個作業流的控制，比如將轉換的結果發送郵件給相關人員，因為轉換(transformation) 以并行方式執行，所以必須存在一個串行的調度工具來執行轉換，這就是Kettle 中的作業，

第3章 Kettle的轉換

3.1 Kettle輸入控制元件

輸入是轉換里面的第一個分類， 輸入控制元件也是轉換中的第一大控制元件, 用來抽取資料或者生成資料，輸入是ETL里面的E (Extract)，主要做資料提取的作業，

由于Kettle中自帶的輸入控制元件比較多，本文只挑出開發中經常使用的幾個輸入控制元件來進行講解，詳情如下圖：

3.1.1 CSV檔案輸入

CSV 檔案是一個用逗號分隔的固定格式的文本檔案，這種檔案后綴名為.csv，可以用Excel或者文本編輯器打開，在企業里面一般最常見的 ETL 需求就是將 csv 檔案轉換為 excel 檔案，如果用 Kettle 來做這個 ETL作業，就需要用到本章節講解的CSV檔案輸入控制元件，

任務：熟悉CSV檔案輸入控制元件，并嘗試將CSV檔案轉換成Excel檔案(可參考上面的快速體驗案例)，

• 步驟名稱：可以修改，但是在同一個轉換里面要保證唯一 性， 見名知意

• 檔案名：選擇對應的csv檔案

• 列分隔符：默認是逗號（不用改）

• 封閉符：結束行資料的讀寫（不用改）

• NIO 快取大小：檔案如果行數過多，需要調整此引數

• 包含列頭行：意思是檔案中第一行是欄位名稱行，表頭不進行讀寫

• 行號欄位：如果檔案第- -行不是欄位名稱或者需要從某行開始讀寫，可在此輸入行號，⑧并發運行? :選擇并發，可提高讀寫速度

• 欄位中有回車換行? ：不要選擇，會將換行符做資料讀出

• 檔案編碼：如果預覽資料出現亂碼，可更換檔案編碼

3.1.2 文本檔案輸入

提取服務器上的日志資訊是公司里 ETL開發很常見的操作，日志資訊基本上都是文本型別,因此文本檔案輸入控制元件是kettle中常用的一個輸入控制元件，

任務：熟悉文本檔案輸入控制元件，并新建轉換，將txt日志檔案轉換為Excel檔案

使用文本檔案輸入控制元件步驟：

1） 添加需要轉換的日志檔案

2）按照日志檔案格式，指定分隔符

3）獲取下欄位，并給欄位設定合適的格式（數字型別的資料盡量選Integer，因為number型別有兩位小數點）

4）最后點下預覽記錄，看看能否讀到資料

3.1.3 Excel檔案輸入

Excel輸入控制元件也是很常用的輸入控制元件，一般企業里會用此控制元件對大量的Excel檔案進.行ETL操作，

任務：兩張sheet表合二為一

使用Excel輸入控制元件步驟如下：

原始資料：

1）按照讀取的源檔案格式指定對應的表格型別為 xls 還是 xlsx

2）選擇并添加對應的excel檔案

3）獲取excel的sheet作業表

4）獲取欄位，并給每個欄位設定合適的格式

5）預覽資料

3.1.4 XML輸入

1）XML簡介

XML可擴展標記語言eXtensible MarkupLanguage，由W3C組織發布，目前推薦遵守的是W3C組織于2000年發布的XML1.0規范，XML用來傳輸和存盤資料，就是以一個統一的格式，組織有關系的資料，為不同平臺下的應用程式服務，

2）XPath簡介

XPath即為XML路徑語言(XML Path Language)，它是一種用來確定XML檔案中某部分位置的語言，XPath基于XML的樹狀結構，提供在資料結構樹中找尋節點的能力，θXPath使用路徑運算式在XML檔案中選取節點，下面列出了最有用的路徑運算式

3）XML輸入控制元件

了解XML和XPath概念以后，我們要開始學習Kettle的XML輸入控制元件，企業里經常用此控制元件進行XML檔案的ETL操作，

任務：熟悉XML輸入控制元件，將XML檔案的學生資料寫到excel檔案中

1.瀏覽獲取xml檔案，將xml檔案添加到kettle中

2.獲取 xml檔案的所有路徑，設定合適的回圈讀取路徑

3.獲取欄位，獲得自己想要讀取的所有欄位，并且設定適當的格式

4.預覽資料，看看能否讀取到自己想要的資料

3.1.5 JSON輸入

1）JSON介紹

JSON(JavaScript Object Notation,JS物件簡譜)是一種輕量級的資料交換格式，JSON物件本質上就是一個JS物件，但是這個物件比較特殊，它可以直接轉換為字串，在不同語言中進行傳遞，通過工具又可以轉換為其他語言中的物件，

JSON核心概念：

• 陣列：[]

• 物件：{}

• 屬性：key:value

2）JSON Path

JSONPath 類似于 XPath 在 xml 檔案中的定位，JsonPath 運算式通常是用來路徑檢索或設定Json的，其運算式可以接受“dot - notation”(點記法)和“bracket -notation”(括號記法)

格式：

• 點記法：$.store.book[0]title

• 括號記法：$[‘store’][‘book’ ][0][ ‘ title’ ]
  

3）JSON 輸入控制元件

了解JSON格式和JSON Path以后，我們要學習使用JSON輸入控制元件，JSON控制元件也是企業里做ETL常用的控制元件之一

任務：獲取到JSON檔案里面的id，field，value欄位，寫到excel檔案中

原始資料：

1.瀏覽獲取JSON檔案（注意檔案路徑不能有中文），將json檔案獲取到kettle中

2.根據JSON Path點記法，獲取到需要的欄位，并且設定合適格式

3）新建JSON輸入控制元件2

第二步的資料內容為：

3.1.6 表輸入

表輸入可以說是kettle中用到最多的一種輸入控制元件, 因為企業中大部分的資料都會存在資料庫中，kettle可以連接市面上常見的各種資料庫，比如Oracle，Mysql, SqlServer等，但是在連接各個資料庫之前，我們需要先配置好對應的資料庫驅動，本教程以mysql為例，給大家講解kettle連接mysql資料庫的程序，

1）創建資料庫連接

MySQL驅動下載（一定要下載對應資料庫版本）：官網下載地址

首先我們要將對應版本的mysql連接驅動放到kettle 安裝目錄下面的lib檔案夾下，然后重啟kettle 的客戶端Spoon

重啟Spoon客戶端以后，我們就可以創建對應的資料庫連接了，在轉換視圖的主物件樹目錄下，有個DB連接，右鍵然后選擇新建，在打開資料庫連接框里，填寫正確的資料庫資訊，然后測驗，測驗無誤后，可以保存此資料庫連接，

資料庫連接默認只對本轉換有效，換一個轉換以后，這個連接就沒法用了，還需要新建資料庫連接，所以我們需要將建好的這個資料庫連接進行共享下，共享以后，其他的轉換也能用我們提前建好的這個資料庫連接了，

2）表輸入

創建好資料庫連接以后，我們就可以使用表輸入控制元件了，雙擊表輸入控制元件，選擇剛剛創建的資料庫連接，然后在SQL框里輸入合適的查詢陳述句，然后點擊預覽按鈕，看能否預覽到我們期望的資料

3.2 Kettle輸出控制元件

輸出是轉換里面的第二個分類，輸出控制元件也是轉換中的第二大控制元件，用來存盤資料，輸出是ETL里面的L（Load），主要做資料加載的作業，

由于Kettle中自帶的輸出控制元件比較多，本文只挑出開發中經常使用的幾個輸出控制元件來進行講解，詳情如下圖

3.2.1 Excel輸出

Kettle中自帶了兩個Excel輸出，一個Excel輸出，另一個是Microsoft Excel輸出，Excel輸出只能輸出xls檔案（適合Excel2003），Microsoft Excel輸出可以輸出xls和xlsx檔案（適合Excel2007及以后）

Excel輸出大家已經很熟悉了，本章不再贅述，接下來給大家講下Microsoft Excel輸出，

1）選擇合適的擴展名

2）點擊瀏覽，補全輸出檔案的路徑已經檔案名

3.2.2 文本檔案輸出

文本檔案輸出控制元件，顧名思義，這是一個能將資料輸出成文本的控制元件，比較簡單，在企業里面也比較常用，

1．設定對應的目錄和檔案名

2．設定合適的擴展名，比如txt，csv等

3．在內容框里設定合適的分隔符，比如分號，逗號，TAB等

4．在欄位框里獲取欄位，并且給每個欄位設定合適的格式

3.2.3 SQL檔案輸出

SQL檔案輸出一般跟表輸入做連接，然后將資料庫表的表結構和資料以sql檔案的形式匯出，然后做資料庫備份的這么一個作業，（Kettle里面沒varchar型別盡量少用）

1．選擇合適的資料庫連接

2．選擇目標表

3．勾選增加創建表陳述句和每個陳述句另起一行

4．填寫輸出檔案的路徑和檔案名

5．擴展名默認為sql，這個不需要更改

3.2.4 表輸出

表輸出控制元件可以將kettle資料行中的資料直接寫入到資料庫中的表中，企業里做ETL作業會經常用到此控制元件，

1．選擇合適的資料庫連接

2．選擇目標表，目標表可以提前在資料庫中手動創建好，也可以輸入一個資料庫不存在的表，然后點擊下面的SQL按鈕，利用kettle現場創建

3．如果目標表的表結構和輸入的資料結構不一致，還可以自己指定資料庫欄位

3.2.5 更新&插入/更新

更新和插入/更新，這兩個控制元件是kettle提供的將資料庫已經存在的記錄與資料流里面的記錄進行對比的控制元件，企業級ETL 經常會用到這兩個控制元件來進行資料庫更新的操作

兩者區別：

• 更新是將資料庫表中的資料和資料流中的資料做對比，如果不同就更新，如果資料流中的資料比資料庫表中的資料多，那么就報錯，
• 插入/更新的功能和更新一樣，只不過優化了資料不存在就插入的功能，因此企業里更多的也是使用插入/更新，

步驟：

1．選擇正確的資料庫連接

2．選擇目標表

3．輸入兩個表來進行比較的欄位，一般來說都是用主鍵來進行比較

4．輸入要更新的欄位

3.2.6 洗掉

洗掉控制元件可以洗掉資料庫表中指定條件的資料，企業里一般用此控制元件做資料庫表資料洗掉或者跟另外一個表資料做對比，然后進行去重的操作，

1．選擇資料庫連接

2．選擇目標表

3．設定資料流跟目標表要洗掉資料的對應欄位

3.3 Kettle轉換控制元件

轉換控制元件是轉換里面的第四個分類，轉換控制元件也是轉換中的第三大控制元件，用來轉換資料，轉換是ETL里面的T（Transform），主要做資料轉換，資料清洗的作業，ETL整個程序中，Transform的作業量最大，耗費的時間也比較久，大概可以占到整個ETL的三分之二，

由于Kettle中自帶的轉換控制元件比較多，本文只挑出開發中經常使用的幾個轉換控制元件來進行講解，詳情如下圖，

3.3.1 Concat fields

轉換控制元件Concat fields，顧名思義，就是將多個欄位連接起來形成一個新的欄位

任務：將staff表的firstname和lastname拼接起來，形成name欄位，然后再將資料插入到新表emp中

原始資料：

3.3.2 值映射

值映射就是把欄位的一個值映射（轉換）成其他的值，在資料質量規范上使用非常多，比如很多系統對應性別sex欄位的定義不同，所以我們需要利用此控制元件，將同一個欄位的不同的值，映射轉換成我們需要的值，

任務：將staff表的sex欄位，映射成男or女，然后再插入到emp表中

原始資料：

1．選擇映射的欄位

2．還可以自定義映射完以后的新欄位名

3．可以設定不匹配時的默認值

4．設定映射的值

3.3.3 增加常量&增加序列

增加常量就是在本身的資料流里面添加一列資料，該列的資料都是相同的值，

增加序列是給資料流添加一個序列欄位，可以自定義該序列欄位的遞增步長，

任務：給表staff的資料加一列固定值slary和一個遞增的number序列，在控制臺預覽下資料即可，不用輸出

3.3.4 欄位選擇

欄位選擇是從資料流中選擇欄位、改變名稱、修改資料型別，

任務：在上一章節的轉換之后，添加欄位選擇控制元件，移除掉firstname欄位，并且將lastname重命名為name，將slary重命名為money，然后再次預覽資料，查看資料的變化

3.3.5 計算器

計算器是一個函式集合來創建新的欄位，還可以設定欄位是否移除（臨時欄位），我們可以通過計算器里面的多個計算函式對已有欄位進行計算，得出新欄位，

任務：在上一節的任務基礎之上，添加計算器控制元件對money和number欄位進行相乘，得出新欄位acount，然后預覽資料

執行結果：

3.3.6 字串剪切&替換&操作

轉換控制元件中有三個關于字串的控制元件，分別是剪切字串，字串操作，字串替換

剪切字串是指定輸入流欄位裁剪的位置剪切出新的欄位

字串替換是指定搜索內容和替換內容，如果輸入流的欄位匹配上搜索內容就進行替換生成新欄位

字串操作是去除字串兩端的空格和大小寫切換，并生成新的欄位

執行結果：

3.3.7 排序記錄&去除重復記錄

去除重復記錄是去除資料流里面相同的資料行，但是此控制元件使用之前要求必須先對資料進行排序，對資料排序用的控制元件是排序記錄，排序記錄控制元件可以按照指定欄位的升序或者降序對資料流進行排序，因此排序記錄+去除重復記錄控制元件常常配合組隊使用，

任務：利用excel輸入控制元件讀取input目錄下的06_去除重復記錄.xlsx，然后對里面重復的資料進行按照id排序并去重

原始資料：

執行結果：

3.3.8 唯一行（哈希值）

唯一行（哈希值）就是洗掉資料流重復的行，此控制元件的效果和（排序記錄+去除重復記錄）的效果是一樣的，但是實作的原理不同，排序記錄+去除重復記錄對比的是每兩行之間的資料，而唯一行（哈希值）是給每一行的資料建立哈希值，通過哈希值來比較資料是否重復，因此唯一行（哈希值）去重效率比較高，也更建議大家使用，

任務：利用唯一行（哈希值）控制元件對06_去除重復記錄.xlsx去重，并且查看最后輸出的資料跟上個任務有何區別

執行結果：

3.3.9 拆分欄位

拆分欄位是把欄位按照分隔符拆分成兩個或多個欄位，需要注意的是，欄位拆分以后，原欄位就會從資料流中消失，

任務：將拆分欄位.xlsx里面的NBA球星的姓名，拆分成姓跟名

檔案內容：

執行結果：

3.3.10 列拆分為多行

列拆分為多行就是把指定欄位按指定分隔符進行拆分為多行，然后其他欄位直接復制，具體效果如下圖：

任務：對08_列拆分為多行.xlsx的資料按照hobby欄位進行拆分為多行，然后將新資料輸出到excel檔案中，查看資料

原始資料：

1．選擇要拆分的欄位

2．設定合適的分割符

3．設定分割以后的新欄位名

4．選擇是否輸出新資料的排列行號，行號是否重置


執行結果：

3.3.11 行扁平化

行扁平化就是把同一組的多行資料合并成為一行，可以理解為列拆分為多行的逆向操作

但是需要注意的是行扁平化控制元件使用有兩個條件：

1)使用之前需要對資料進行排序

2)每個分組的資料條數要保證一致，否則資料會有錯亂

任務：將09_行扁平化.xlsx的資料按照hobby欄位進行扁平化

原始資料：


1．選擇扁平化的欄位

2．填寫目標欄位，欄位個數跟每個分組的資料一致

3.3.12 列轉行

列轉行，顧名思義多列轉一行，就是如果資料一列有相同的值，按照指定的欄位，將其中一列的欄位內容變成不同的列，然后把多行資料轉換為一行資料的程序，具體效果如下圖

：

注意：列轉行之前資料流必須按照分組欄位進行排序，否則資料會錯亂！

任務：將input目錄下的10_列轉行.xlsx的資料進行列轉行，熟悉列轉行控制元件的使用

原始資料：

1．關鍵欄位：從資料內容變成列名的欄位

2．分組欄位：列轉行，轉變以后的分組欄位

3．目標欄位：增加的列的列名欄位

4．資料欄位：目標欄位的資料欄位

5．關鍵字值：資料欄位查詢時的關鍵字，也可以理解為key

6．型別：要給目標欄位設定合適的型別，否則會報錯

執行結果：

3.3.13 行轉列

行轉列，一行轉多列，就是把資料欄位的欄位名轉換為一列，把資料行變為資料列，我們也可以簡單理解為行轉列控制元件是列轉行控制元件的逆向操作，具體如下圖：

任務：將行轉列.xlsx用excel控制元件輸入，然后行轉列，熟悉行轉列控制元件的使用，

原始資料：


1．Key欄位：行轉列，生成的列名欄位名

2．欄位名稱：原本資料流中的欄位名

3．Key值：Key欄位的值，這個是自己自定義的，一般都跟前面的欄位名稱一樣

4．Value欄位：對應的Key值的資料列的列名

執行結果：

3.4 Kettle應用控制元件

應用是轉換控制元件里面的第五個分類，這個分類下是Kettle給我們自帶的一些工具類

3.4.1 替換NULL值

替換NULL值，顧名思義就是將資料里面的null值替換成其他的值，此控制元件比較簡單，但是在企業里面也會經常用到，

1．可以選擇替換資料流中所有欄位的null值

2．也可以選擇欄位，在下面的欄位框里面，根據不同的欄位，將null值替換成不同的值

任務：替換excel資料12_替換NULL值.xlsx的bonus列的null值為0

原始資料：

執行結果：

3.4.2 寫日志

寫日志控制元件主要是除錯的時候使用，此控制元件可以將資料流的每行資料列印到控制臺，方便我們除錯整個程式，

1．選擇日志級別

2．可以輸入自定義輸出的陳述句

3．選擇要輸出列印的欄位

任務：在上個任務的基礎之上，添加寫日志控制元件，在控制臺輸出查看資料

執行結果：

3.5 Kettle流程控制元件

流程是轉換里面的第六個分類，流程分類下的控制元件主要用來控制資料流程和資料流向，

3.5.1 Switch/case

Switch/case控制元件，最典型的資料分類控制元件，可以利用某一個欄位的資料的不同的值，讓資料流從一路到多路，

任務：將excel：13_Switch-Case.xlsx的資料按照部門欄位進行分類，將同一個部門的資料輸出到一個excel中

原始資料：

1．選擇需要判斷的欄位

2．選擇判斷欄位的值的型別

3．填寫分類資料的判斷條件和目標步驟


執行結果：

3 .5.2 過濾記錄

和Switch/case做對比的話，過濾記錄相當于if-else，可以自定義輸入一個判斷條件，然后將資料流中的資料一路分為兩路

任務：將資料按照工資欄位進行判斷，將工資在20000及以上的資料輸出到一個excel中，將工資小于20000的輸出到另外一個excel中

原始資料：

1.在下面先填寫資料的判斷條件

2.然后再上面選擇下判斷條件為true或者false的輸出步驟

執行結果：

3.5.3 空操作

空操作，顧名思義就是什么也不做，此控制元件一般作為資料流的終點，

任務：修改上節的轉換任務，將工資大于等于20000的資料輸出，小于20000的資料直接丟棄，熟悉空操作控制元件的使用，

3.5.3 中止

中止是資料流的終點，如果有資料流到此控制元件處，整個轉換程式將中止，并且在控制臺輸出報錯資訊，此控制元件一般用來校驗資料，或者除錯程式，

任務：使用中止控制元件判斷上節任務中是否有人的工資低于20000，如果發現有人的工資低于20000的話，中止程式，并在控制臺輸出資訊，

3.6 Kettle查詢控制元件

查詢是轉換里面的第九個分類，查詢控制元件是用來查詢資料源里面的資料，并合并到主資料流中，

3.6.1 資料庫查詢

資料庫查詢就是從資料庫里面查詢出資料，然后跟資料流中的資料進行左連接的一個程序，左連接的意思是資料流中原本的資料全部有，但是資料庫查詢控制元件查詢出來的資料不一定全部會列出，只能按照輸入的匹配條件來進行關聯，

任務：利用表輸入控制元件獲取到staff表的資料，然后利用資料庫查詢控制元件查詢到department表的資料，然后對兩個表按照dept_id欄位進行左連接，并預覽資料

原始資料：

1.選擇合適的資料庫鏈接

2.輸入要去資料庫里面查詢的表名

3.輸入兩個表進行左連接的連接條件

4.獲取回傳欄位，得到查詢表回傳的值

執行結果：

3.6.2 流查詢

流查詢控制元件就是查詢兩條資料流中的資料，然后按照指定的欄位做等值匹配，注意：流查詢在查詢前把資料都加載到記憶體中，并且只能進行等值查詢，

任務：用流查詢控制元件，將staff和department的資料按照dept_id欄位進行關聯起來

1.輸入查詢的資料流

2.輸入兩個流進行匹配的欄位（等值匹配）

3.輸入查詢出的欄位

執行結果：

3.7 Kettle連接控制元件

連接是轉換里面的第十個分類，連接分類下的控制元件一般都是將多個資料集通過關鍵字進行連接起來，形成一個資料集的程序，

3.7.1 合并記錄

合并記錄是用于將兩個不同來源的資料合并，這兩個來源的資料分別為舊資料和新資料，該步驟將舊資料和新資料按照指定的關鍵字匹配、比較、合并，注意舊資料和新資料需要事先按照關鍵欄位排序，并且舊資料和新資料要有相同的欄位名稱，
合并后的資料將包括舊資料來源和新資料來源里的所有資料，對于變化的資料，使用新資料代替舊資料，同時在結果里用一個標示欄位，來指定新舊資料的比較結果，

任務：利用合并記錄控制元件比較合并記錄-新舊excel的資料，并預覽資料，查看標志欄位的內容

原始資料：

1.舊資料源：選擇舊資料來源的步驟

2.新資料源：選擇新資料來源的步驟

3.標志欄位：設定標志欄位的名稱，標志欄位用于保存比較的結果，比較結果有下列幾種：

• ①“identical” – 舊資料和新資料一樣
• ②“changed” – 資料發生了變化;
• ③“new” – 新資料中有而舊資料中沒有的記錄
• ④“deleted” –舊資料中有而新資料中沒有的記錄

4.關鍵欄位：用于定位判斷兩個資料源中的同一條記錄的欄位，

5.比較欄位：對于兩個資料源中的同一條記錄，指定需要比較的欄位

執行結果：

3.7.2 記錄集連接

記錄集連接可以對兩個步驟中的資料流進行左連接，右連接，內連接，外連接，此控制元件功能比較強大，企業做ETL開發會經常用到此控制元件，但是需要注意在進行記錄集連接之前，需要對記錄集的資料進行排序，并且排序的欄位還一定要選兩個表關聯的欄位，否則資料錯亂，出現null值，

任務：使用記錄集連接控制元件對資料庫表satff和department按照部門id分別進行內連接，左連接，右連接，外連接，查看資料的不同

原始資料：

注意：兩個表進行排序記錄的時候，排序的欄位一定要選擇部門id，否則資料會不正確

1.選擇需要連接的兩個資料流的步驟

2.選擇連接型別，一共有四個：INNER，LEFT OUTER，RIGHT OUTER，FULL OUTER

3.從兩個資料流步驟里面選出連接欄位

執行結果：

3.8Kettle統計控制元件

統計是轉換里面的第十三個分類，統計控制元件可以提供資料的采樣和統計功能，

3.8.1 分組

分組控制元件的功能類似于GROUP BY，可以按照指定的一個或者幾個欄位進行分組，然后其余欄位可以按照聚合函式進行合并計算，注意，在進行分組之前，資料最好先進行排序，

任務：給表staff的資料按照部門進行分組，求出各部門人數以及各部門員工的平均年齡，

原始資料：


1.選擇分組欄位

2.給其余欄位選擇合適的聚合函式進行計算

執行結果：

3.9 Kettle映射控制元件

映射是轉換里面的第十八個分類，映射可以用來定義子轉換，方便代碼封裝和重用，

3.9.1 映射

映射（子轉換）是用來配置子轉換，對子轉換進行呼叫的一個步驟，

映射輸入規范是輸入欄位，由呼叫的轉換輸入，

映射輸出規范是向呼叫的轉換輸出所有列，不做任何處理

任務：封裝一個子轉換能夠通過dept_id求出dept_name，然后使用另外一個轉換呼叫此子轉換，求出資料庫staff表id=3的員工的姓名，年齡，部門id，部門姓名，并輸出到控制臺，

執行結果：

3.10 Kettle腳本控制元件

腳本是轉換的第七個分類，腳本就是直接通過寫程式代碼完成一些復雜的操作，

3.10.1執行SQL腳本

執行sql腳本控制元件就是連接到資料庫里面，然后執行自己寫的一些sql陳述句

任務：利用執行sql腳本控制元件將student表資料的atguigu的年齡更新為18

1.選擇合適的資料庫連接

2.填入要執行的sql陳述句

第4章 Kettle作業

4.1 作業簡介

大多數ETL專案都需要完成各種各樣的維護作業，例如，如何傳送檔案；驗證資料庫表是否存在等等，而這些操作都是按照一定順序完成，因為轉換以并行方式執行，就需要一個可以串行執行的作業來處理這些操作，

一個作業包含一個或者多個作業項，這些作業項以某種順序來執行，作業執行順序由作業項之間的跳（job hop）和每個作業項的執行結果來決定，

4.1.1作業項

作業項是作業的基本構成部分，如同轉換的步驟，作業項也可以使用圖示的方式圖形化展示，

但是，作業項和轉換步驟有下面幾點不同：

1.轉換步驟與步驟之間是資料流，作業項之間是步驟流，

2.轉換啟動以后，所有步驟一起并行啟動等待資料行的輸入，而作業項是嚴格按照執行順序啟動，一個作業項執行完以后，再執行下一個作業項，

3.在作業項之間可以傳遞一個結果物件（result object），這個結果物件里面包含了資料行，它們不是以資料流的方式來傳遞的，而是等待一個作業項執行完了，再傳遞個下一個作業項，

4.因為作業順序執行作業項，所以必須定義一個起點，有一個叫“開始”的作業項就定義了這個點，一個作業只能定一個開始作業項，

4.1.2 作業跳

作業的跳是作業項之間的連接線，他定義了作業的執行路徑，作業里每個作業項的不同運行結果決定了做作業的不同執行路徑，

作業跳一共分為下面三種情況：

①無條件執行：不論上一個作業項執行成功還是失敗，下一個作業項都會執行，這是一種藍色的連接線，上面有一個鎖的圖示，

②當運行結果為真時執行：當上一個作業項的執行結果為真時，執行下一個作業項，通常在需要無錯誤執行的情況下使用，這是一種綠色的連接線，上面有一個對鉤號的圖示，

③當運行結果為假時執行：當上一個作業項的執行結果為假或者沒有成功執行是，執行下一個作業項，這是一種紅色的連接線，上面有一個紅色的停止圖示，

在圖示上單擊就可以對跳進行設定，

4.2 作業初體驗

作業案例：將3.10.1章節的轉換嵌入作業中執行，執行成功或者失敗都發送郵件提醒

1）點擊左上角的檔案，新建一個作業

2）按照下圖設定作業項和作業跳

3）轉換作業項設定，選擇要嵌入的轉換檔案

4）發送郵件作業項設定


5）分別嘗試作業執行成功和失敗，查看kettle發送的郵件資訊

第5章 Kettle使用案例

5.1 轉換案例

案例一：把stu1的資料按id同步到stu2，stu2有相同id則更新資料

(1) 在mysql中創建兩張表

mysql> create database kettle;
mysql> use kettle;
mysql> create table stu1(id int,name varchar(20),age int);
mysql> create table stu2(id int,name varchar(20));

(2) 往兩張表中插入一些資料

mysql> insert into stu1 values(1001,'zhangsan',20),(1002,'lisi',18), (1003,'wangwu',23);
mysql> insert into stu2 values(1001,'wukong');

(3) 在kettle中新建轉換

(4) 分別在輸入和輸出中拉出表輸入和插入/更新

(5) 雙擊表輸入物件，填寫相關配置，測驗是否成功

(6) 雙擊 更新/插入物件，填寫相關配置

(7) 保存轉換，啟動運行，去mysql表查看結果

執行結果：

5.2 作業案例

案例二：使用作業執行上述轉換，并且額外在表stu2中添加一條資料，整個作業運行成功的話發郵件提醒

(1)新建一個作業

(2) 按圖示拉取組件

(3) 雙擊Start編輯Start

(4) 雙擊轉換，選擇案例1保存的檔案

(5) 雙擊SQL，編輯SQL陳述句

(6) 雙擊發送郵件，編輯發送郵件的設定資訊


(7) 保存作業并執行，然后去mysql查看結果和郵件資訊

第6章 Kettle資源庫

6.1 資料庫資源庫

資料庫資源庫是將作業和轉換相關的資訊存盤在資料庫中，執行的時候直接去資料庫讀取資訊，很容易跨平臺使用

1）點擊右上角connect，選擇Other Resporitory

2) 選擇Database Repository

3) 建立新連接


4) 填好之后，點擊finish，會在指定的庫中創建很多表，至此資料庫資源庫創建完成

5) 連接資源庫，默認賬號密碼為admin

6) 將之前做過的轉換匯入資源庫

(1)選擇從xml檔案匯入

(2)隨便選擇一個轉換

(3)點擊保存，選擇存盤位置及檔案名

(4)打開資源庫查看保存結果

第7章 Kettle調優

1、調整JVM大小進行性能優化，修改Kettle根目錄下的Spoon腳本，

引數參考：

-Xmx2048m：設定JVM最大可用記憶體為2048M，

-Xms1024m：設定JVM促使記憶體為1024m，此值可以設定與-Xmx相同，以避免每次垃圾回收完成后JVM重新分配記憶體，

-Xmn2g：設定年輕代大小為2G，整個JVM記憶體大小=年輕代大小 + 年老代大小 + 持久代大小，持久代一般固定大小為64m，所以增大年輕代后，將會減小年老代大小，此值對系統性能影響較大，Sun官方推薦配置為整個堆的3/8，

-Xss128k：設定每個執行緒的堆疊大小，JDK5.0以后每個執行緒堆疊大小為1M，以前每個執行緒堆疊大小為256K，更具應用的執行緒所需記憶體大小進行調整，在相同物理記憶體下，減小這個值能生成更多的執行緒，但是作業系統對一個行程內的執行緒數還是有限制的，不能無限生成，經驗值在3000~5000左右，

2、 調整提交（Commit）記錄數大小進行優化，Kettle默認Commit數量為：1000，可以根據資料量大小來設定Commitsize：1000~50000

3、盡量使用資料庫連接池；

4、盡量提高批處理的commit size；

5、盡量使用快取，快取盡量大一些（主要是文本檔案和資料流）；

6、Kettle是Java做的，盡量用大一點的記憶體引數啟動Kettle；

7、可以使用sql來做的一些操作盡量用sql；Group , merge , stream lookup,split field這些操作都是比較慢的，想辦法避免他們.，能用sql就用sql；

8、插入大量資料的時候盡量把索引刪掉；

9、盡量避免使用update , delete操作，尤其是update,如果可以把update變成先delete, 后insert；

10、能使用truncate table的時候，就不要使用deleteall row這種類似sql合理的磁區，如果洗掉操作是基于某一個磁區的，就不要使用delete row這種方式（不管是deletesql還是delete步驟）,直接把磁區drop掉，再重新創建；

11、盡量縮小輸入的資料集的大小（增量更新也是為了這個目的）；

12、盡量使用資料庫原生的方式裝載文本檔案(Oracle的sqlloader, mysql的bulk loader步驟)，