KETTLE詳細設計

1、KETTLE詳細設計說明書V0.1變更記錄版本文件內(nèi)容描述日期編寫審核批準V0.1創(chuàng)建2014-7-3王小龍1 Kettle界面介紹雙擊Kettle.exe或者Spoon.bat打開Kettle圖形界面化如下：(圖1.1)Kettle中有兩類設計分別是：Transformation（轉(zhuǎn)換）與Job（作業(yè)），Transformation完成針對數(shù)據(jù)的基礎轉(zhuǎn)換，Job則完成整個工作流的控制。Kettle常用三大家族：Spoon、Pan、Kitchen。Spoon:通過圖形界面方式設計、運行、調(diào)試Job與Transformation。Pan: 通過腳本命令方式來運行Transformation。Ki

2、tchen: 通過腳本命令方式來運行Job，一般就是通過調(diào)用Kitchen腳本來完成定時任務。說明：當打開Kettle時顯示的是Kettle的相關(guān)版本信息與GNU相關(guān)協(xié)議說明。(圖1.2)說明：打開Kettle時彈出的窗口提示以資源庫方式登錄相關(guān)信息，關(guān)于烏江水電項目工程存儲方式是以XML文件方式來進行存儲，所以我就直接點擊“沒有資源庫”。圖(1.3)說明：進入Kettle設計界面。提示關(guān)于Spoon相關(guān)信息，這里直接點擊“關(guān)閉”按鈕。(圖1.4)說明：圖片中所使用到的字母位置標識說明。(A)：Kettle所使用到的菜單欄。(B)：在使用Kettle時所涉及使用到的對象。(C)：Kettle中

3、所有的組件。(D)：根據(jù)選擇(B)或者(C)顯示相應的結(jié)果。(E)：Kettle設計界面。(圖1.5)說明：文件：是對Kettle所生成的Job與Trans進行相關(guān)的操作，如：新建、打開、保存、導入、導出等相關(guān)操作。(圖1.6)說明：編輯：是對Kettle當前打開的Job與Trans進行相關(guān)的操作，如：復制、撤銷、環(huán)境變量顯示、資源庫查看、圖形界面自定義調(diào)整(顏色、字體、樣式)等相關(guān)操作。(圖1.7)說明：視圖：是對Kettle當前打開的Job與Trans進行放大、縮小相關(guān)操作。(圖1.8)說明：資源庫：是對Kettle中所使用到的資源庫進行操作，如：資源庫連接、斷開、當前用戶編輯等操作。(圖

4、1.9)說明：轉(zhuǎn)換：是對Kettle當前所打開的Trans進行相關(guān)測試功能。(圖1.10)說明：作業(yè)：是對Kettle當前所打開的Job進行操作，如：運行、復制、參數(shù)設置等相關(guān)操作。(圖1.11)說明：向?qū)В菏菍ettle當前所打開的Job或者Trans對其進行一步一步的指導性操作。(圖1.12)說明：幫助：查看當前Kettle版本相關(guān)信息。2 Kettle數(shù)據(jù)源連接配置Kettle中對于數(shù)據(jù)源有作用域的定義，也就是說有全局數(shù)據(jù)源與局部數(shù)據(jù)源兩種。 首先打開Kettle UI界面，使用快鍵方式(Ctrl+N)創(chuàng)建一個轉(zhuǎn)換，新建數(shù)據(jù)源如下圖：(圖2.0)通過點擊“新建”，則出現(xiàn)(圖2.1)(圖

5、2.1)說明：通過(圖2.1)我們可以看到創(chuàng)建數(shù)據(jù)源時需要配置相應的參數(shù)：Connection Name(必填)：配置數(shù)據(jù)源使用名稱，如：wjsd_srcHost Name(必填)：數(shù)據(jù)庫主機IP地址，如：Database Name(必填)：數(shù)據(jù)庫實例名稱，如：WJSDTablespace for Data(可選)：數(shù)據(jù)表空間名稱Tablespace for indices(可選)：數(shù)據(jù)索引名稱Port Number(必填)：端口號User Name(必填)：用戶名Password(必填)：密碼Access：選擇數(shù)據(jù)庫連接方式，ODBC方式則需要配置系統(tǒng)ODBC，JNDI則使用配置jdbc.p

6、roperties文件，默認為Native(JDBC)連接方式，。注：在Kettle中如果在填寫框末尾出現(xiàn)$符號標識說明此處可以使用變量參數(shù)來定義。填寫好數(shù)據(jù)庫連接信息后，點擊“Test”按鈕，出現(xiàn)效果如(圖2.2)(圖2.2)說明：配置成功數(shù)據(jù)源后默認情況下數(shù)據(jù)源作用域為局部數(shù)據(jù)源，如果需要把數(shù)據(jù)源修改成為全局數(shù)據(jù)源，則選擇數(shù)據(jù)源wjsd_src右擊Share保存即可。3 Kettle全量抽取 對于緯表、字典表或者是沒有時間戳的表，一般我們都采用全量抽取的方式將業(yè)務系統(tǒng)庫中的數(shù)據(jù)抽取到數(shù)據(jù)倉庫中。烏江水電項目需要用到全量抽取的源表有：CIM.MeasurementCIM.Measuremen

7、t_TypeCIM.d_ReservoirCIM.Center_Sys_CodeCIM.CURVE_SCHED_DATACIM.d_dateCIM.CURVE_SCHEDULECIM.WJ_CURVE_TYPECIM.power_System_resourcePSR_TYPE下面以CIM下面的d_Reservoir為例詳細描述Kettle全量抽取的過程1、 創(chuàng)建轉(zhuǎn)換(Ctrl+N)，轉(zhuǎn)換名稱為：d_Reservoir2、 創(chuàng)建數(shù)據(jù)源連接wjsd_src、wjsd_tag，可參考【Kettle數(shù)據(jù)源連接配置】3、 在Kettle設計盤中拖入“表輸入”、“表輸出”兩個組件即可4、 執(zhí)行轉(zhuǎn)換對數(shù)據(jù)進

8、行抽取5、 查看結(jié)果是否與數(shù)據(jù)源中的數(shù)據(jù)是否一致步驟1、創(chuàng)建轉(zhuǎn)換(圖3.1)說明：當創(chuàng)建轉(zhuǎn)換時點擊“核心對象”下方則顯示可以在轉(zhuǎn)換中能使用的所有組件。點擊“輸入”-單擊“表輸入”把“表輸入”組件往空白處拖入即可。(圖3.2)說明：雙擊“表輸入”組件彈出(圖4.2)，輸入或選擇相應的參數(shù)信息。步驟名稱：默認為“表輸入”，如果想規(guī)范化建議填寫源表名稱信息，以便清楚查詢是某張表結(jié)構(gòu)信息數(shù)據(jù)庫連接：選擇數(shù)據(jù)來源數(shù)據(jù)源名稱SQL：編寫查詢源數(shù)據(jù)SQL腳本，可以通過“獲取SQL查詢語句”獲取。允許延遲轉(zhuǎn)換：強烈建議默認替換SQL語句里的變量：如果SQL語句中有使用到變量則需要勾選從步驟插入數(shù)據(jù)：默認執(zhí)行每

9、一行：默認記錄數(shù)量限制：0表示默認，如果有特殊需求可以自定義只查詢出多少條記錄數(shù)注：1、 在SQL語句中一定不能加入分號(;)，這是初學者很容易犯錯的一個地方。2、 如果無法確定SQL語句是否正常時可以通過“預覽”來查看SQL語句是否正確。(圖3.3)說明：有了源之后則需要有目標，也就是說有我水我們則需要把水通過渠道通向目標如(圖4.3)拖入“表輸出”后，我們通過按住“Shift”鍵單擊源劃向目標“表輸出”，則會出現(xiàn)源指向目標的箭頭線。雙擊“表輸出”如(圖4.3)，另外可以選擇兩個或者兩個以上的組件進行位置對齊，可以通過Ctrl+向上箭頭、Ctrl+向下箭頭、Ctrl+向左箭頭、Ctrl+向右

10、箭頭。步驟名稱：默認為“表輸入”，如果想規(guī)范化建議填寫目標表名稱信息，以便清楚查詢是某張表結(jié)構(gòu)信息數(shù)據(jù)庫連接：選擇數(shù)據(jù)來目標數(shù)據(jù)源名稱目標模式：schema，Oracle數(shù)據(jù)庫則是用戶名。目標表：從源數(shù)據(jù)輸出到目標表名提交記錄數(shù)：設置數(shù)據(jù)庫提交大小裁剪表：清空目標表數(shù)據(jù)，相當于Oracle數(shù)據(jù)庫Truncate語法忽略插入錯誤：當插入數(shù)據(jù)庫出錯時則可以忽略，默認忽略18條記錄數(shù)，記不太清楚Specify database fields：手動指定源與目標字段映射關(guān)系，如(圖4.4)Main options：如果是表分區(qū)則可以指定表分區(qū)，一般為默認Database fields：如(圖3.4)(圖

11、3.4)說明：指定源與目標字段映射關(guān)系，點擊Get fields匹配字段映射關(guān)系。注：在沒有勾選“Specify databasefields”情況下，源表字段不能多于目標表的字段數(shù)量，否則會出錯。(圖3.5)說明：點擊運行按鈕，或按F9，運行這個轉(zhuǎn)換，根據(jù)(圖3.5)可以查看出轉(zhuǎn)換運行的日志情況。(圖3.6)說明：根據(jù)(圖3.6)可以查看出每個步驟執(zhí)行的記錄數(shù)、讀、寫、輸入、輸出、更新、拒絕、錯誤、激活、時間、速度?？梢酝ㄟ^查看此處分析當前轉(zhuǎn)換運行效率而斷定轉(zhuǎn)換是否需要進行優(yōu)化。總結(jié)：1、 在運行轉(zhuǎn)換時，當前轉(zhuǎn)換是處于并發(fā)狀態(tài)，如果有多個表輸入則會同時運行。2、 在設計轉(zhuǎn)換時最好是一個數(shù)據(jù)流

12、，不要有多條數(shù)據(jù)流。如果有多條數(shù)據(jù)流則有可能發(fā)生鎖表問題。4 Kettle增量抽取對于CIM.WJ_Day_DataCIM.WJ_Run_Crenet_Powerhd.target_dayAPP.DNN_POINT_DAY_VALUEAPP.DNN_OBJECT_DAY_VALUESOA_WJ_AGC_RECORDS_TSOA_WJ_DISPATCH_MON_PLAN_TSOA_WJ_DISPATCH_YEAR_PLAN_TSOA_WJ_REPAIR_TSOA_WJ_RUNDAY_REPORT_Tdaydbplanload_factory下面以traget_day為例，詳細描述增量抽取數(shù)據(jù)。因

13、為是增量的抽取，所以參數(shù)的傳遞是必要的。增量抽取一般都是抽取昨天的數(shù)據(jù)裝載到目標表中。Kettle中參數(shù)使用方法有兩種：一種是%變量名%，一種是$變量名。這兩種方法變量數(shù)據(jù)類型都是數(shù)字類型。1、 創(chuàng)建轉(zhuǎn)換(Ctrl+N)，轉(zhuǎn)換名稱為：traget_day2、 創(chuàng)建數(shù)據(jù)源連接wjsd_src、wjsd_tag，可參考【Kettle數(shù)據(jù)源連接配置】3、 在Kettle設計盤中拖入執(zhí)行SQL腳本”、“表輸入”、“表輸出”三個組件4、 執(zhí)行轉(zhuǎn)換對數(shù)據(jù)進行抽取5、 查看結(jié)果是否與數(shù)據(jù)源中的數(shù)據(jù)是否一致 （圖 4.1）注：在SQL中使用變量時需要把“是否替換變量”勾選上，否則無法使變量生效。說明：這里需要

14、注意的是后面的限定where條件，因為源表的時間字段是時間類型，我們的參數(shù)是數(shù)字類型，所以需要根據(jù)源表時間字段的不同注意轉(zhuǎn)換。 （圖 4.2）說明：這里與全量抽取的表輸出差不多。 （圖 4.3）說明：這里要考慮到數(shù)據(jù)出錯重跑的問題，如果需要重新插入輸入?yún)?shù)日期的數(shù)據(jù)，就必須要先刪除輸入?yún)?shù)日期的數(shù)據(jù)，不然會報錯。因為我參數(shù)在條件里面，變量替換要打上溝，不然設置的變量會失效。5 Kettle實時數(shù)據(jù)抽取烏江水電項目實時數(shù)據(jù)的表有hd.target_realhd.target_dayrtdbRTEMSrtcalcRTSQREALhourdb實時抽取與增量抽取基本上相差不大，只是對時間戳的處理方式不

15、同，以REAL這張表為例，詳細描述實時抽取過程的步驟。1、 創(chuàng)建轉(zhuǎn)換(Ctrl+N)，轉(zhuǎn)換名稱為：traget_day2、 創(chuàng)建數(shù)據(jù)源連接wjsd_src、wjsd_tag，可參考【Kettle數(shù)據(jù)源連接配置】3、 在Kettle設計盤中拖入執(zhí)行SQL腳本”、“表輸入”、“表輸出”三個組件4、 執(zhí)行轉(zhuǎn)換對數(shù)據(jù)進行抽取5、 查看結(jié)果是否與數(shù)據(jù)源中的數(shù)據(jù)是否一致 （圖 5-1）說明：因為實時抽取的表時間戳字段一般都為TIMESTAMP類型，這里where條件對參數(shù)的處理必須這樣，不然可能會在抽取數(shù)據(jù)的時候?qū)е聰?shù)據(jù)丟失。 （圖 5-2）說明：表輸出與增量抽取相同，只是目標表不同。 圖（5-3）說明：

16、這里與增量抽取相類似，回跑的時候參數(shù)日期的數(shù)據(jù)必須要刪除。同時注意變量替換打上勾。 圖（5-4）說明：依次把每個步驟連接起來，順序如圖5-4所示。6 Kettle定時任務在增量抽取過程中，ETL定時任務是一個必不可少的環(huán)節(jié)，因為定時任務取決與你的ETL程序抽取業(yè)務數(shù)據(jù)的頻率程度(日、周、季、月、年)，一般情況下都采用T+1方式來抽取數(shù)據(jù)到目標表中，也可以通過指定日期參數(shù)抽取到目標表。6.1 參數(shù)變量設置轉(zhuǎn)換首先創(chuàng)建相應的參數(shù)變量設置轉(zhuǎn)換(Set_Param.ktr)。對于怎樣獲取昨天日期，一般通過“獲取系統(tǒng)信息”組件獲取昨天日期，再通過字段選擇轉(zhuǎn)換成yyyy-mm-dd格式，最后設置成變量，設

17、置參數(shù)變量為$YESTERDAY。如下圖： （圖6.1-1）獲取相關(guān)的日期參數(shù)，昨天日期。 （圖 6.1-2）由于獲取日期是到時分秒，通過字段選擇轉(zhuǎn)換成年月日格式。 圖（6.1-3）設置參數(shù)，點確定時會提示大致意思是“設置的參數(shù)不能在當前轉(zhuǎn)換中使用”。 圖（6.1-4）依次連接每個步驟，簡單的參數(shù)設置轉(zhuǎn)換就算完成了。6.2 創(chuàng)建作業(yè)(Job)關(guān)于Kettle的Job是數(shù)據(jù)流抽取的重點環(huán)節(jié)，首先創(chuàng)建一個作業(yè)（Job），點擊文件新建作業(yè)。在作業(yè)里面放入START、Transform、Success,如圖（6.2-1）所示 圖（6.2-1）將事先完成的參數(shù)轉(zhuǎn)換Set_Param插入到作業(yè)中，添加作業(yè)

18、變量： 圖（6.2-2）作業(yè)設置，快捷鍵Ctrl+J，設置變量名為：YESTERDAY，默認值為：NULL。添加Set_Param轉(zhuǎn)換變量如下圖： 圖（6.2-3）然后我們把作業(yè)中的YESTERDAY變量值傳遞給Set_Param轉(zhuǎn)換 圖（6.2-4）轉(zhuǎn)換設置，快捷鍵Ctrl+T，設置變量名為：YESTERDAY，默認值為：NULL。設置完成轉(zhuǎn)換變量后，我們通過“Get Variables”組件來獲取YESTERDAY變量的值，如下圖： 圖（6.2-5）再通過“過濾記錄”組件來判斷YESTERDAY變量是否有等于NULL。如下圖：(圖6.2-5)如果等于NULL數(shù)據(jù)流則走“獲取系統(tǒng)信息組件”，

19、反之則直接設置變量。如下圖：(圖6.2-6)注：由于自定義的變量與獲取系統(tǒng)信息中的變量重名，產(chǎn)生沖突，無法獲取到相應的數(shù)值，需要通過“字段選擇”排除掉YESTERDAY變量，而Yesterday則自動改成Yesterday_1然后再改成Yesterday名，如下圖：(圖6.2-7)最終Set_Param轉(zhuǎn)換數(shù)據(jù)流程如下圖：(圖6.2-8)設置好Set_Param后，在job中還需要對參數(shù)進行設置，參數(shù)名稱YESTERDAY，參數(shù)值$YESTERDAY 圖（6.2-9）將事先完成好的抽取數(shù)據(jù)流轉(zhuǎn)換添加到JOB中，如下圖 圖（6.2-10）Transformation2的名稱可以自己更改，這里為E

20、TL_DAYDB，點擊雪花形狀的綠色按鈕，找到ETL_DAYDB這個轉(zhuǎn)換，點擊確定即可完成對作業(yè)的設置。6.3 Kitchen腳本完成作業(yè)與轉(zhuǎn)換的開發(fā)，下一步將編寫Kitchen腳本，Kitchen腳本程序分為增量腳本、全量腳本與實時腳本，腳本程序如下：名稱：Etl_Sch_day echo offset kettle_path=C:Kettle4.2set JOB_PATH=C:kettle_repset YE=%date:0,4%set MO=%date:5,2%set DA=%date:8,2%set DG=1set/a vY1=%YE% % 400set/a vY2=%YE% % 4s

21、et/a vY3=%YE% % 100if %vY1%=0 (set var=true) else (if %vY2%=0 (if %vY3%=0 (set var=false) else (set var=true) else (set var=false)set LY=%YE%set LM=%MO%if %MO:0,1%=0 (set MO=%MO:1,1%)if %DA:0,1%=0 (set DA=%DA:1,1%)if %DA% GTR %DG% (set/a LD=%DA%-%DG%) else (if %MO%=1 (set/a LY=%YE%-1) & (set/a LM=12

22、) & (set/a LD=31+%DA%-%DG%) else (set/a LM=%MO%-1if %MO%=3 (if %var%=false (set/a LD=28+%DA%-%DG%) else (set/a LD=29+%DA%-%DG%)for %a in (2 4 6 8 9 11) do (if %MO%=%a (set/a LD=31+%DA%-%DG%)for %b in (5 7 10 12) do (if %MO%=%b (set/a LD=30+%DA%-%DG%)if %LM% LSS 10 set LM=0%LM:-1%if %LD% LSS 10 set LD=0%LD:-1%set strLstDt=%LY%LM%LD%