ETL-Kettle學習

kettle

• 學習視訊
• 一、ETL 了解(資料倉庫技術)
• 二、Kettle(工具)
• 三、Kettle腳本流程
• 四、Kettle元件
• 五、Kettle目錄
• 六、Kettle界面
• 七、操作
• • 1.轉換
  • 2.步驟
  • 3.跳
  • 4.資料類型
  • 5.并行
  • 6.作業
• 八、Kettle轉換
• • 1.Kettle輸入控件
  • • csv檔案輸入
    • 文本檔案輸入
    • Excl輸入
    • XML輸入
    • JSON輸入
    • 表輸入-資料庫
  • 2.Kettle輸出控件
  • • Excl輸出
    • 文本檔案輸出
    • SQL檔案輸出
    • 表輸出
    • 插入-更新 、 更新
    • 删除
  • 3.轉換控件 transform
  • • concat fields 合并字段
    • 值映射
    • 增加常量、增加序列
    • 字段選擇
    • 電腦
    • 字元串剪切、操作、替換
    • 排序記錄、去除重複記錄。
    • 唯一行(哈希值)
    • 拆分字段
    • 列拆分為多行
    • 行扁平化
    • 列轉行
    • 行轉列
  • 4.應用控件
  • • 替換null值
    • 寫日志
  • 5.kettle流程控件
  • • switch/case 控件
    • 過濾記錄
    • 空操作
    • 終止-中止
  • 6.查詢控件
  • • 資料庫查詢
    • 流查詢
  • 7.連接配接控件
  • • 合并記錄
    • 記錄集連接配接
  • 8.統計控件
  • • 分組
  • 9.映射控件
  • 10.腳本控件
  • • 執行SQL腳本
• 九、kettle作業
• • 1.作業項
  • 2.作業跳
  • 3.郵件
• 問題記錄
• • 1.kettle8 擷取時間多九個0
  • 2.kettle-sql輸出-navicat導入sql檔案中文亂碼
  • 3.kettle8 表輸出 出現中文亂碼

學習視訊

https://www.bilibili.com/video/BV1NT4y1c7o8?p=1

一、ETL 了解(資料倉庫技術)

etl是 extract(抽取)-transform(轉換)-load(加載)的縮寫。對應的是資料處理的過程，抽取=》轉換=》加載。

二、Kettle(工具)

是一款etl 工具，etl工具還有很多。

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

Kettle 中文名稱叫水壺，該項目的主程式員MATT 希望把各種資料放到一個壺裡，然後以一種指定的格式流出。

Kettle這個ETL工具集，它允許你管理來自不同資料庫的資料，通過提供一個圖形化的使用者環境來描述你想做什麼，而不是你想怎麼做。——就是你想達到什麼樣的目的，不需要管背景如何操作。就是你想要房子，不用管如何建造的。

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

Kettle(現在已經更名為PDI，Pentaho Data Integration-Pentaho資料內建)。

三、Kettle腳本流程

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

作業是一步一步的執行，必須等前面的執行完成才能進行下一步。轉換是全部啟動。轉換屬于多個步驟中的一步。

四、Kettle元件

五、Kettle目錄

還有一些主要的腳本，使用spoon.bat/spoon.sh 啟動Kettle 工具，第一次啟動可能會慢一些

六、Kettle界面

connet連結區，設定連接配接資源庫資料庫，密碼賬戶都是admin。登入後，打開本地檔案再儲存，就存到伺服器資料庫。

還有本地資源庫可以生成

輕按兩下空白工作區擷取跳的資訊。

七、操作

拖入操作步驟，建立輸入輸出子產品，按住shift連接配接步驟。連接配接線也叫跳。

輸入子產品使用

輸出子產品使用

注意檔案名字尾，如果擴充名和檔案名都有字尾會添加兩個字尾，可以删除檔案名字尾

可以擷取字段，進行格式調整，設定寬度。0是取到整數位

1.轉換

在轉換中，以行為機關進行轉換，一行資料從輸入跳到輸出，再開始第二行資料跳。

2.步驟

分發是把總的資料分開發送，總5，會給1兩條，給2三條資料。複制是都能有5條資料

3.跳

4.資料類型

5.并行

6.作業

八、Kettle轉換

在處理資料的時候能擷取字段，預覽到資料基本沒問題。轉換是多線程的

1.Kettle輸入控件

常用控件

csv檔案輸入

csv檔案資料預設用逗号隔開

檔案大可以調NIO

文本檔案輸入

Excl輸入

Excel輸入控件也是很常用的輸入控件，一般企業裡會用此控件對大量的Excel檔案進行ETL操作。使用Excel輸入控件步驟如下：

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

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

3）擷取excel的sheet工作表e

4）擷取字段，并給每個字段設定合适的格式

5）預覽資料

XML輸入

擷取包含資料的路徑，例如下面的student。

JSON輸入

json檔案如果擷取不到資料可能和路徑有關系，不能存在中文目錄

JSONPath

第一個輸入json，先擷取指定的json資料，id和data，在進行第二步輸入json，從data中擷取field和value，需要用JSONpath擷取節點路徑。最後再輸出excel。

表輸入-資料庫

在Kettle目錄的lib下存放jar包，如果沒有jar包，後放入的包需要重新開機軟體。

kettle連接配接資料庫不成，需要注意kettle和mysql版本是否相容。還要注意lib放入mysql驅動。

連接配接資料庫報錯 5.4版本 ，jdk1.8

錯誤連接配接資料庫 [mysql-Kettle] : org.pentaho.di.core.exception.KettleDatabaseException:

Error occurred while trying to connect to the database

Error connecting to database: (using class org.gjt.mm.mysql.Driver)

java.lang.StackOverflowError

org.pentaho.di.core.exception.KettleDatabaseException:

Error occurred while trying to connect to the database

Error connecting to database: (using class org.gjt.mm.mysql.Driver)

java.lang.StackOverflowError

放入驅動後重新啟動軟體，發現還是連接配接資料庫失敗，更新了kettle版本8.2.

重新連接配接測試成功

資料庫連接配接隻支援1個轉換，如果想資料庫支援多個轉換，需要右鍵-共享，共享資料庫。

2.Kettle輸出控件

輸出常用控件

Excl輸出

Kettle中自帶了兩個Excel輸出，一個Excel輸出，另一個是Microsoft Excel輸出。

Excel輸出隻能輸出xls檔案（适合Excel2003)

Microsoft Excel輸出可以輸出xls和xlsx檔案（适合Excel2007及以後）xlsx是壓縮過的xls，體積小。

1）選擇合适的擴充名

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

在内容可以不擷取字段，也能成功生成資料。

文本檔案輸出

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

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

2.設定合适的擴充名，比如txt，csv等

3.在内容框設定合适的分隔符，比如分号，逗号，TAB等

4.在字段框裡擷取字段，并且給每個字段設定合适的格式

SQL檔案輸出

SQL檔案輸出一般跟表輸入做連接配接，然後将資料庫表的表結構和資料以sql檔案的形式。注意選擇編碼格式。

導出，然後做資料庫備份的這麼一個工作。

1.選擇合适的資料庫連接配接

2.選擇目标表-目标表是建立的表名，可以選擇資料庫的，也可以自己寫

3.勾選增加建立表語句和每個語句另起一行

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

5.擴充名預設為sql，這個不需要更改

表輸出

1.選擇合适的資料庫連接配接

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

3.如果目标表的表結構和輸入的資料結構不一緻，還可以自己指定資料庫字段

擷取資料庫字段，然後删除不需要字段。

表輸出需要提前建表，或者在表輸出中的sql 中建立新表。還要注意新表的字段類型是否與舊表的一緻。

插入-更新 、 更新

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

兩者差別：

更新是将資料庫表中的資料和資料流中的資料做對比，如果不同就更新，如果資料流中的資料比資料庫表中的資料多，那麼就報錯。

插入/更新的功能和更新一樣，隻不過優化了資料不存在就插入的功能，是以企業裡更多的也是使用插入/更新。

選擇目标表後，擷取字段，用來查詢的關鍵字，表字段是目标表的字段，流裡的字段是輸入流的字段，來自表輸入或其他。選擇要更新的字段

删除

删除控件可以删除資料庫表中指定條件的資料，企業裡一般用此控件做資料庫表資料删

除或者跟另外一個表資料做對比，然後進行去重的操作。

1.選擇資料庫連接配接

2.選擇目标表

3.設定資料流跟目标表要删除資料的對應字段

根據所選擇的字段，一般選擇主鍵，删除重複的資料。

3.轉換控件 transform

轉換控件是轉換裡面的第四個分類，轉換控件也是轉換中的第三大控件，用來轉換資料。

轉換是ETL裡面的T（Transform），主要做資料轉換，資料清洗的工作。ETL整個過程中，

Transform的工作量最大，耗費的時間也比較久，大概可以占到整個ETL的三分之二。

concat fields 合并字段

值映射

功能：用來替換字段裡的值

值映射就是把字段的一個值映射成其他的值。在資料品質規範上使用非常多，比如很多系統對應性别sex字段的定義不同。是以我們需要利用此控件，将同一個字段的不同的值，映射轉換成我們需要的值。 類似于替換

1.選擇映射的字段

2.還可以自定義映射完以後的新字段名e

3.可以設定不比對時的預設值

4.設定映射的值

增加常量、增加序列

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

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

字段選擇

字段選擇是從資料流中選擇字段，進行修改名稱，修改資料類型。

先擷取所有字段，需要改名的進行改名，需要删除的在移除中選擇字段。

電腦

字元串剪切、操作、替換

功能：

剪切用來截取某一部分的字元串

替換 用來替換字元串

排序記錄、去除重複記錄。

功能：先排序後去重

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

唯一行(哈希值)

功能：去重，且不用排序，輸出是亂序

排序記錄+去除重複記錄對比的是每兩行之間的資料，

而唯一行（哈希值）是給每一行的資料建立哈希值，通過哈希值來比較資料是否重複，是以唯一行（哈希值）去重效率比較高，也更建議大家使用。

拆分字段

功能：拆分一個字段

拆分字段是把字段按照分隔符拆分成兩個或多個字段。需要注意的是，字段拆分以後，原字段就會從資料流中消失。 拆分字段的時候注意設定拆分字段的類型

列拆分為多行

功能：

列拆分為多行就是把指定字段按指定分隔符進行拆分為多行，然後其他字段直接複制。

就是把一列裡面的多組資料，拆分為多行。

效果圖

行扁平化

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

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

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

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

每組要合并的資料條數要一樣。

列轉行

功能：将一列的資料作為字段轉為行。

行轉列

功能：把一行字段的資料轉換成一列。

4.應用控件

替換null值

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

1.可以選擇替換資料流中所有字段的null值

2.也可以選擇字段，在下面的字段框裡面，根據不同的字段，将null值替換成不同的值

寫日志

寫日志控件主要是調試的時候使用，此控件可以将資料流的每行資料列印到控制台，友善我們調試整個程式。 可以加在任何一個資料流上，顯示目前資料。

1.選擇日志級别

2.可以輸入自定義輸出的語句

3.選擇要輸出列印的字段

5.kettle流程控件

switch/case 控件

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

過濾記錄

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

空操作

什麼也不做

終止-中止

第一個：終止運作中的流程

第二個：直接終止流程

第三個：一條條的運作，不符合的終止，複合的繼續。

6.查詢控件

資料庫查詢

資料庫查詢就是從資料庫裡面查詢出資料，然後跟資料流中的資料進行左連接配接的一個過程。

左連接配接的意思是資料流中原本的資料全部有，但是資料庫查詢控件查詢出來的資料不一定全部會列出，隻能按照輸入的比對條件來進行關聯。

左查詢，以流中的資料為主表查詢。

流查詢

流查詢控件就是查詢兩條資料流中的資料，然後按照指定的字段做等值比對。注意：流查詢在查詢前把資料都加載到記憶體中，并且隻能進行等值查詢。

流裡的值查詢

7.連接配接控件

合并記錄

功能：合并更新資料

合并記錄是用于将兩個不同來源的資料合并，這兩個來源的資料分别為舊資料和新資料，該步驟将舊資料和新資料按照指定的關鍵字比對、比較、合并。

注意：舊資料和新資料需要事先按照關鍵字段排序，并且舊資料和新資料要有相同的字段名稱。

合并後的資料将包括舊資料來源和新資料來源裡的所有資料，對于變化的資料，使用新資料代替舊資料，同時在結果裡用一個标示字段，來指定新舊資料的比較結果。

記錄集連接配接

功能：連表查詢

記錄集連接配接可以對兩個步驟中的資料流進行左連接配接，右連接配接，内連接配接，外連接配接。

此控件功能比較強大，企業做ETL開發會經常用到此控件，但是需要注意在進行記錄集連接配接之前，需要對記錄集的資料進行排序，并且排序的字段還一定要選兩個表關聯的字段，否則資料錯亂，出現null值。兩個表用相同的字段進行排序，然後再連接配接查詢

8.統計控件

分組控件，類似于group by，分組之前進行排序，按分組字段排序最好。

分組

功能：對資料分組和計算值。

9.映射控件

映射類似于一個方法，輸入規範類似于入參，輸出規範類似于輸出的資料。

建立映射方法：輸出映射規範不用寫。

調用映射(子映射)方法

10.腳本控件

執行SQL腳本

就是連接配接到資料庫，寫一些sql語句執行。可單獨執行，也可以複合使用

九、kettle作業

大多數ETL項目都需要完成各種各樣的維護工作。例如，如何傳送檔案；驗證資料庫表是否存在等等。而這些操作都是按照一定順序完成。因為轉換以并行方式執行，就需要一個可以串行執行的作業來處理這些操作。一個作業包含一個或者多個作業項，這作業項以某種順序來執行。 作業執行順序由作業項之間的跳（job hop）和每個作業項的執行結果來決定。

1.作業項

作業項是作業的基本構成部分。如同轉換的步驟，作業項也可以使用圖示的方式圖形化展示。但是，作業項和轉換步驟有下面幾點不同：

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

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

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

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

2.作業跳

3.郵件

問題記錄

1.kettle8 擷取時間多九個0

需要在查詢時間的時候對時間格式進行格式化

DATE_FORMAT(Sdate,"%Y-%m-%d %H:%i:%s") AS endTime
           

2.kettle-sql輸出-navicat導入sql檔案中文亂碼

從kettle sql輸出檔案，在navicat查詢sql檔案，發現中文亂碼。一開始以為是kettle或者資料庫的編碼格式不對，後來檢視資料庫編碼格式是utf8，kettle連接配接資料庫的時候在進階設定字元集，也沒有用。後來打開編輯發現中文顯示正常，發現右下角編碼格式不正确，重新儲存 設定編碼格式utf-8.

在sql輸出的内容裡選擇編碼格式即可。

3.kettle8 表輸出 出現中文亂碼

可以在資料庫連接配接中，設定命名參數 characterEncoding utf8。

或者檢視mysql中文亂碼解決方案