05數(shù)據(jù)收集9.0相關(guān)etl工具kettlespoon學(xué)習(xí)總結(jié)

1、ETL工具Kettle Spoon學(xué)結(jié)Author:Creation Date: Last Updated:Ref:chen.l November 16, 2015November 16, 2015Ver:Approvals: Copy NumberControlChange RecordReviewersDistributionNote To Holders:If you receive an electronic copy of thisand prit out, please write yourname on the equivalent of the covage, forcontr

2、ol pures.If you receive a cover, forof this control pur, please write your name on the frontes.Copy No.NameLocation1Library Mastroject Library2Project Manager34NameitionDateAuthorVerChange Reference16-Nov-15LichenDraft 1aNo PreviousContentsControlii1.概述2使用背景2ETL工具簡介2Kettle-Spoon安裝、配置4安裝4啟動方式4界面介紹5轉(zhuǎn)換

3、7Kettle實現(xiàn)方式10建立一個轉(zhuǎn)換10創(chuàng)建定時任務(wù)（JOB）13運行、驗證16數(shù)據(jù)16更新數(shù)據(jù)......Open and Closed Ies for this Deliverable23Open Ies23Closed Ies231.概述1.1.使用背景項目上每天都需要從Oracle系統(tǒng)中的數(shù)據(jù)同步到其他系統(tǒng)中，由于客戶的特殊需求DB-link和WebService的方式都是不允許使用的，其中的原因很多，有安全性，后期的等等問題。最后通過查找網(wǎng)上的相關(guān)資料，找到了一種開源的ETL工具，可以實現(xiàn)數(shù)據(jù)的

4、推送，同時也是由JAVA編寫的，可以安裝到Oracle1服務(wù)器上面自動運行?，F(xiàn)在把一些安裝、配置、和推送等相關(guān)的內(nèi)容做一個小結(jié)。1.2.ETL工具簡介ETL，是英文 Extract-Transform-Load 的縮寫，將數(shù)據(jù)從來源端經(jīng)過抽?。╡xtract）、轉(zhuǎn)換（transform）、加載（load）至目的端的過程。ETL是構(gòu)建數(shù)據(jù)倉庫的重要一環(huán)，用戶從數(shù)據(jù)源抽取出所需的數(shù)據(jù)，經(jīng)過數(shù)據(jù),最終按照預(yù)先定義好的數(shù)據(jù)倉庫模型，將數(shù)據(jù)加載到數(shù)據(jù)倉庫中去。KettleKettle是一款國外開源的ETL工具，純java編寫，可以在Window、Linux、Unix上運行，綠色無需安裝，數(shù)據(jù)抽取高效穩(wěn)定

5、。Kettle 中文名稱叫水壺，該項目的主程序員MATT 希望把各種數(shù)據(jù)放到一個壺里，然后以一種指定的格式流出。Kettle這個ETL工具集，它允許你管理來自不同數(shù)據(jù)庫的數(shù)據(jù)，通過提供一個圖形化的用戶環(huán)境來描述你想做什么，而不是你想怎么做。Kettle中有兩種文件，transformation和job，transformation完成針對數(shù)據(jù)的基礎(chǔ)轉(zhuǎn)換，job則完成整個工作流的控制。Kettle 里有轉(zhuǎn)換（Transformation）和 作業(yè)（Job）兩個概念。轉(zhuǎn)換主要是針對數(shù)據(jù)的各種處理，一個轉(zhuǎn)換里可以包含多個步驟（Step），每個步驟就是一種數(shù)據(jù)處理方式，如表輸入，文件輸入，排序，分組，

6、過濾，選擇列，拆分列，增加列等等。Kettle 內(nèi)置了數(shù)十種步驟，另外還可以通過插件的方式由用戶自定義自己需要的步驟。作業(yè)是比轉(zhuǎn)換更高一級的處理流程，一個作業(yè)里包括多個作業(yè)項（JobEntry），一個作業(yè)項代表了一項工作， 常用的作業(yè)發(fā)送郵件， 接收郵件，執(zhí)行 s， FTP 等等。轉(zhuǎn)換也是作業(yè)項之一，即一個作業(yè)里可以包括多個轉(zhuǎn)換。一個作業(yè)項也可以是其他的一個作業(yè)，即作業(yè)可以嵌套。作業(yè)項也可以通過插件的方式由用戶自定義。轉(zhuǎn)換和作業(yè)的設(shè)計都是通過 spoon 來完成的，spoon 就是 kettle 的圖形界面設(shè)計器。設(shè)計好的作業(yè)可以保存在文件（轉(zhuǎn)換文件以 .ktr 為擴展名，作業(yè)文件以 .kjb

7、為擴展名）里，也可以保存在數(shù)據(jù)庫（）里。設(shè)計好的作業(yè)可以使用 kitchen 來執(zhí)行，設(shè)計好的轉(zhuǎn)換使用來 pan 執(zhí)行。Kettle 可以使用本地機器來執(zhí)行作業(yè)（Native Exec），也可以使用機器來執(zhí)行作業(yè)（Remote Exec），也可以使用多個機器以集群的方式共同執(zhí)行一個作業(yè)2.Kettle-Spoon安裝、配置2.1.安裝和中文社區(qū)均可以源代碼地址：svn:/source.pentaho./svnkettleroot/Kettle/trunkBug地址：http:/browse/PDI： 中文：運行環(huán)境Kettle 需要 JRE1.5.0 以上的運行環(huán)境，可以運行在windows

8、和Linux下。2.2.啟動方式Kettle 需要 JRE1.5.0 以上的運行環(huán)境，可以運行在windows 和Linux下。Kettle 包括的幾個子程序的功能和啟動方式如下：WindowsSpoon.bat: 圖形界面方式啟動作業(yè)和轉(zhuǎn)換設(shè)計器。Pan.bat: 命令行方式執(zhí)行轉(zhuǎn)換。Kitchen.bat: 命令行方式執(zhí)行作業(yè)。Carte.bat: 啟動 web 服務(wù)，用于 Kettle 的運行或集群運行。Encr.bat:加密Linuxspoon.sh 圖形界面方式啟動pan.sh: 命令行方式執(zhí)行轉(zhuǎn)換。kitchen.sh: 命令行方式執(zhí)行作業(yè)。carte.sh: 啟動web 服務(wù)，用

9、來運行或集群運行。encr.sh:加密。說明：1. 啟動時如果發(fā)生ClaotFoundException 請使用 java ver檢查 jre 的版本是否是 1.5 及以上。2. 使用機器上啟動Carte。執(zhí)行時，要在3. 使用集群方式運行時，在集群里的每個機器上都啟動Carte。jar cvf hand.jar com到安裝目錄下雙擊Spoon.bat文件打開圖形界面2.3.界面介紹界面如上圖所示Kettle提供了非常多的對象，這里只用到了轉(zhuǎn)換和任務(wù)2.4.轉(zhuǎn)換一個轉(zhuǎn)換任務(wù)里包括多個步驟和注釋，步驟基本可以分為三類，輸入步驟、輸出步驟、轉(zhuǎn)換步驟。輸入步驟：在該步驟里指定數(shù)據(jù)來源，如果是表輸入

10、要指定JDBC 連接參數(shù)，表名等信息，如果是文件輸入，要指定文件名，分隔符等參數(shù)。轉(zhuǎn)換步驟：轉(zhuǎn)換步驟用于數(shù)據(jù)的轉(zhuǎn)換，常見的轉(zhuǎn)換步驟包括：選擇列、行過濾、增加常量，增加序列、排序、分組、集連接（左連接/右連接/內(nèi)連接/外連接）、增加變量、拆分列、行轉(zhuǎn)列、列轉(zhuǎn)行、扁平化、值等輸出步驟：指定數(shù)據(jù)最終流向的目的地。步驟擴展：系統(tǒng)提供了用戶開發(fā)插件的接口，當現(xiàn)有的步驟不能滿足需求時，用戶可以根據(jù)具體的需求自己開發(fā)步驟，以插件的方式結(jié)成到系統(tǒng)中。系統(tǒng)的主操作界面分為基本步驟功能選擇區(qū)、歷史步驟區(qū)和主設(shè)計界面區(qū)，通過鼠標拖拽進行步驟設(shè)定。轉(zhuǎn)換常用環(huán)節(jié)介紹任務(wù)常用環(huán)節(jié)介紹類別環(huán)節(jié)名稱功能說明Input文本文件

11、輸入從本地文本文件輸入數(shù)據(jù)表輸入從數(shù)據(jù)庫表中輸入數(shù)據(jù)獲取系統(tǒng)信息系統(tǒng)信息輸入數(shù)據(jù)Output文本文件輸出將處理結(jié)果輸出到文本文件表輸出將處理結(jié)果輸出到數(shù)據(jù)庫表/更新根據(jù)處理結(jié)果對數(shù)據(jù)庫表機型更新，如果數(shù)據(jù)庫中不存在相關(guān)記錄則，否則為更新。會根據(jù)查詢條件中字段進行判斷更新根據(jù)處理結(jié)果對數(shù)據(jù)庫進行更新，若需要更新的數(shù)據(jù)在數(shù)據(jù)庫表中無，則會報錯停止刪除根據(jù)處理結(jié)果對數(shù)據(jù)庫進行刪除，若需要刪除的數(shù)據(jù)在數(shù)據(jù)庫表中無，則會報錯停止Lookup數(shù)據(jù)庫查詢根據(jù)設(shè)定的查詢條件，對目標表進行查詢，返回需要的結(jié)果字段流查詢將目標表到內(nèi)存，通過查詢條件對內(nèi)存中數(shù)據(jù)集進行查詢調(diào)用 DB過程調(diào)用數(shù)據(jù)庫過程Transfor

12、m字段選擇選擇需要的字段，過濾掉不要的字段，也可做數(shù)據(jù)庫字段對應(yīng)過濾根據(jù)條件對進行分類排序?qū)?shù)據(jù)根據(jù)某以條件，進行排序空操作無操作增加常量增加需要的常量字段ScriptingModified Java Script Value擴展功能，編寫 JavaScript，對數(shù)據(jù)進行相應(yīng)處理Map（子轉(zhuǎn)換）數(shù)據(jù)JobSat Variables設(shè)置環(huán)境變量Get Variables獲取環(huán)境變量類別環(huán)節(jié)名稱功能說明Job entriesSTART開始DUMMY結(jié)束TransformationTransformation 流程JobJob 流程S調(diào)用 SSQL執(zhí)行 sql 語句FTP通過 FTPTable e

13、xists檢查目標表是否存在，返回布爾值File exists檢查文件是否存在，返回布爾值Javascript執(zhí)行 JavaScriptCreate file創(chuàng)建文件Delete file刪除文件Wait for file等待文件，文件出現(xiàn)后繼續(xù)下一個環(huán)節(jié)File Compare文件比較，返回布爾值Wait for等待時間，設(shè)定一段時間，kettle 流程處于等待狀態(tài)Zip file壓縮文件為 ZIP 包3.Kettle實現(xiàn)方式實現(xiàn)概述：數(shù)據(jù)庫A的A表同步到數(shù)據(jù)庫B的B表實現(xiàn)流程：建立一個裝換和一個作業(yè)JOB3.1.建立一個轉(zhuǎn)換點擊文件新建轉(zhuǎn)換創(chuàng)建DB連接分別有兩個數(shù)據(jù)庫A（EBSPROD）和

14、B（MIDDEV）建立步驟和步驟關(guān)系點擊后，對象，從步驟樹中選擇【表輸入】，如下圖，這樣拖拽一個表輸入之雙擊表輸入之后，自己可以隨意寫一個sql語句，這個語句表示可以在這個庫中隨意組合，只要sql語句沒有錯誤即可之后會在這個sql加where條件控制數(shù)據(jù)量創(chuàng)建另外一個步驟【/ 更新】在【表輸入】上同時按住shift鍵和鼠標左鍵滑向【/ 更新】，這樣建立兩個步驟之間的連接，【/ 更新】執(zhí)行的邏輯是如果UserA表中的在UserB中不存在那么就，如果存在就更新，如下圖，在更新中可以做一些關(guān)鍵條件和字段，這里是最簡單的！點擊保存，把建立的轉(zhuǎn)換保存一下。3.2.創(chuàng)建定時任務(wù)（JOB）在簡單表同步這個轉(zhuǎn)

15、換中，把在test步驟中建立的ktl配置上，注意路徑的正確性；添加一個轉(zhuǎn)換，查詢路徑將剛才建立好的轉(zhuǎn)換添加在【Start】步驟上面雙擊，如圖：設(shè)置時間間隔這樣這個作業(yè)就制定好了，點擊保存之后，了！就可以在圖形化界面上點擊開始執(zhí)行4.運行、驗證將剛才建立好的轉(zhuǎn)換運行，并在兩個數(shù)據(jù)庫的表中查詢看是否產(chǎn)生了正確的數(shù)據(jù)驗證分兩種情況，新增數(shù)據(jù)和更新數(shù)據(jù)4.1.數(shù)據(jù)創(chuàng)建表MID（目標環(huán)境）也創(chuàng)建同樣的表向DEV表中數(shù)據(jù)，如下：啟動轉(zhuǎn)換：點擊開始箭頭，然后點運行下邊的日志框可以查看運行的結(jié)果，如下可以看到狀態(tài)是已完成，錯誤數(shù)據(jù)為0.登錄目標環(huán)境檢查數(shù)據(jù)目標環(huán)境，轉(zhuǎn)換前：目標環(huán)境，運行轉(zhuǎn)換后：數(shù)據(jù)測試完成。

16、4.2.更新數(shù)據(jù)更新一條數(shù)據(jù)，同時增加了一條新數(shù)據(jù)，如下：啟動轉(zhuǎn)換：成功，可以看到更新了三條數(shù)據(jù)（其實有一條數(shù)據(jù)并沒有改變），輸出了一條數(shù)據(jù)查看結(jié)果轉(zhuǎn)換前：轉(zhuǎn)換后的結(jié)果：5.Open and Closed Ies for this DeliverableAdd open iest you identify while writing or reviewing thisto the es sectionopen ies section. As you resolve ies, move them to the closed iand keep the ie ID the same. Include an explanation of the resolution.When this deliverable is complete, any open ies should be transferred to theproject- or pros-level Risk and Ie Log (PJM.CR.040) and managed using aproject level Risk