ETL利器Kettle實(shí)戰(zhàn)應(yīng)用解析系列二【應(yīng)用場景和實(shí)戰(zhàn)DEMO】

1、1、應(yīng)用場景這里簡單概括一下幾種具體的應(yīng)用場景，按網(wǎng)絡(luò)環(huán)境劃分主要包括：表視圖模式：這種情況我們經(jīng)常遇到，就是在同一網(wǎng)絡(luò)環(huán)境下，我們對各種數(shù)據(jù)源的表數(shù)據(jù)進(jìn)行抽取、過濾、清洗等，例如歷史數(shù)據(jù)同步、異構(gòu)系統(tǒng)數(shù)據(jù)交互、數(shù)據(jù)對稱發(fā)布或備份等都?xì)w屬于這個(gè)模式；傳統(tǒng)的實(shí)現(xiàn)方式一般都要進(jìn)行研發(fā)一小局部例如兩個(gè)相同表結(jié)構(gòu)的表之間的數(shù)據(jù)同步，如果sqlserver數(shù)據(jù)庫可以通過發(fā)布/訂閱實(shí)現(xiàn)，涉及到一些復(fù)雜的一些業(yè)務(wù)邏輯如果我們研發(fā)出來還容易出各種bug；前置機(jī)模式：這是一種典型的數(shù)據(jù)交換應(yīng)用場景，數(shù)據(jù)交換的雙方A和B網(wǎng)絡(luò)不通，但是A和B都可以和前置機(jī)C連接，一般的情況是雙方約定好前置機(jī)的數(shù)據(jù)結(jié)構(gòu)，這個(gè)結(jié)構(gòu)跟A

2、和B的數(shù)據(jù)結(jié)構(gòu)根本上是不一致的，這樣我們就需要把應(yīng)用上的數(shù)據(jù)按照數(shù)據(jù)標(biāo)準(zhǔn)推送到前置機(jī)上，這個(gè)研發(fā)工作量還是比擬大的；文件模式: 數(shù)據(jù)交互的雙方A和B是完全的物理隔離，這樣就只能通過以文件的方式來進(jìn)行數(shù)據(jù)交互了，例如XML格式，在應(yīng)用A中我們開發(fā)一個(gè)接口用來生成標(biāo)準(zhǔn)格式的XML，然后用優(yōu)盤或者別的介質(zhì)在某一時(shí)間把XML數(shù)據(jù)拷貝之后，然后接入到應(yīng)用B上，應(yīng)用B上在按照標(biāo)準(zhǔn)接口解析相應(yīng)的文件把數(shù)據(jù)接收過來；綜上3種模式如果我們都用傳統(tǒng)的模式無疑工作量是巨大的，那么怎么做才能更高效更節(jié)省時(shí)間又不容易出錯(cuò)呢？答案是我們可以用一下Kettle-_-！2、DEMO實(shí)戰(zhàn)2、1 實(shí)例1：數(shù)據(jù)庫TestA中的Us

3、erA表到數(shù)據(jù)庫TestB的UserB表1為方便演示，我這邊把Sql腳本貼出來，大家直接復(fù)制在sqlserver中運(yùn)行即可,sql腳本如下：簡單表之間交換use masterIF EXISTS(SELECT 1 FROM sysdatabases WHERE NAME=NTestA)BEGIN DROP DATABASE TestA -如果數(shù)據(jù)庫存在先刪掉數(shù)據(jù)庫ENDGOCREATE DATABASE TestAONPRIMARY -創(chuàng)立主數(shù)據(jù)庫文件( NAME=TestA, FILENAME=E:ETLDBTestA.dbf, SIZE=5MB, MaxSize=20MB, FileGrow

4、th=1MB)gouse TestA- 建立一個(gè)測試的用戶表create table usersA( id int not null, userid varchar(50) not null, username varchar(200) null)go- 插入3條記錄insert into usersA values(1,lisi,李四);insert into usersA values(2,zhangsan,張三);insert into usersA values(3,lisi,李四);gouse masterIF EXISTS(SELECT 1 FROM sysdatabases WH

5、ERE NAME=NTestA)BEGIN DROP DATABASE TestA -如果數(shù)據(jù)庫存在先刪掉數(shù)據(jù)庫ENDGOCREATE DATABASE TestBONPRIMARY -創(chuàng)立主數(shù)據(jù)庫文件( NAME=TestB, FILENAME=E:ETLDBTestB.dbf, SIZE=5MB, MaxSize=20MB, FileGrowth=1MB)gouse TestB- 建立一個(gè)測試的用戶表create table usersB( id int not null, userid varchar(50) not null, username varchar(200) null)go

6、2Kettle實(shí)現(xiàn)方式功能簡述：數(shù)據(jù)庫TestA中的UserA表到數(shù)據(jù)庫TestB的UserB表；實(shí)現(xiàn)流程：建立一個(gè)轉(zhuǎn)換和一個(gè)作業(yè)Job；A:建立一個(gè)轉(zhuǎn)換：翻開Kettle.exe，選擇沒有資源庫，進(jìn)入主界面，新建一個(gè)轉(zhuǎn)換，轉(zhuǎn)換的后綴名為ktr，轉(zhuǎn)換建立的步驟如下：步驟1：創(chuàng)立DB連接，選擇新建DB連接，如下列圖，我們輸入相應(yīng)的Sqlserver配置信息之后點(diǎn)擊Test按鈕測試是否配置正確！我們需要建立兩個(gè)DB連接，分別為TestA和TestB；步驟2：建立步驟和步驟關(guān)系，點(diǎn)擊核心對象，我們從步驟樹中選擇【表輸入】，如下列圖，這樣拖拽一個(gè)表輸入之后，我們雙擊表輸入之后，我們自己可以隨意寫一個(gè)s

7、ql語句，這個(gè)語句表示可以在這個(gè)庫中隨意組合，只要sql語句沒有錯(cuò)誤即可，我這里只是最簡單的把TestA中的所有數(shù)據(jù)查出來，語句為select * from usersA。接下來我們創(chuàng)立另外一個(gè)步驟【插入/ 更新】，然后在【表輸入】上同時(shí)按住shift鍵和鼠標(biāo)左鍵滑向【插入/ 更新】，這樣建立兩個(gè)步驟之間的連接，【插入/ 更新】執(zhí)行的邏輯是如果UserA表中的記錄在UserB中不存在那么就插入，如果存在就更新，如下列圖，在插入更新中我們可以做一些關(guān)鍵條件和字段映射，這里我們是最簡單的！點(diǎn)擊保存，把我們建立的轉(zhuǎn)換保存一下。建立好轉(zhuǎn)換之后，我們可以直接運(yùn)行這個(gè)轉(zhuǎn)換，檢查一下是否有錯(cuò)，如圖，有錯(cuò)誤都

8、會(huì)在下面的控制臺(tái)上輸出。B:如果我們需要讓這個(gè)轉(zhuǎn)換定時(shí)執(zhí)行怎么辦呢，那么我們需要建立一個(gè)作業(yè)job，見下列圖，在簡單表同步這個(gè)轉(zhuǎn)換中，我們把在A步驟中建立的ktl配置上，注意路徑的正確性；這樣我們在【Start】步驟上面雙擊，如圖：這樣這個(gè)作業(yè)就制定好了，點(diǎn)擊保存之后，我們就可以在圖形化界面上點(diǎn)擊開始執(zhí)行了！2、2 實(shí)例2：全面進(jìn)階的一個(gè)稍微復(fù)雜的例子根據(jù)客戶，帳戶，交易表中的數(shù)據(jù)，生成對應(yīng)的數(shù)據(jù)文件，將數(shù)據(jù)文件可以導(dǎo)入到對應(yīng)表中，并且可以用job來調(diào)用整個(gè)流程。目標(biāo)：1)，提交對應(yīng)的Kettle文件2)，Kettle流程可以正確執(zhí)行，不報(bào)錯(cuò)3)，對應(yīng)的數(shù)據(jù)文件生成并格式無誤，對應(yīng)表中有數(shù)據(jù)并

9、格式無誤說明：源表：數(shù)據(jù)庫etltest中存在3張表：目標(biāo)表：etltest中一張表Kettle具體解決方式，步驟比擬繁瑣，大家可以直接到下載中下載Demo數(shù)據(jù)庫文件和ktr、kjb來實(shí)戰(zhàn)測試，這樣是最好的，下面的操作僅供參考，轉(zhuǎn)換預(yù)覽圖如下：操作步驟：在EtltestTrans頁面下，點(diǎn)擊左側(cè)的【Core Objects】，點(diǎn)擊【Input】，選中【表輸入】，拖動(dòng)到主窗口釋放鼠標(biāo)。雙擊【表輸入】圖標(biāo)數(shù)據(jù)庫連接選擇剛剛創(chuàng)立好的etltest數(shù)據(jù)庫連接，在主窗口寫入對應(yīng)的查詢語句Select * from trade ，如下列圖：點(diǎn)擊確定完成。點(diǎn)擊左側(cè)的【Lookup】，選中【數(shù)據(jù)庫查詢】，拖動(dòng)

10、到主窗口釋放鼠標(biāo)。按住shift鍵，用鼠標(biāo)點(diǎn)中剛剛創(chuàng)立的【表輸入】，拖動(dòng)到【數(shù)據(jù)庫查詢】上，那么建立了兩個(gè)環(huán)節(jié)之間的連接，如圖：雙擊【數(shù)據(jù)庫查詢】步驟名稱寫入account 表查詢，數(shù)據(jù)庫連接選擇剛剛創(chuàng)立好的etltest 數(shù)據(jù)庫連接，查詢的表寫入account，查詢所需的關(guān)鍵字中，表字段寫入acctno，比擬操作符寫入“=，字段1寫入acctno。在查詢表返回的值里面寫入custno，確定完成，如下列圖：同上，再創(chuàng)立一個(gè)數(shù)據(jù)庫查詢，命名為cust表查詢，查詢的表寫入cust，查詢所需的關(guān)鍵字寫入custno=custno，查詢表返回的值寫入custname，custid，custtype，如

11、下列圖：點(diǎn)擊左側(cè)的【Transform】，選中【過濾記錄】，拖動(dòng)到主窗口釋放鼠標(biāo)。點(diǎn)擊左側(cè)的【Scripting】，選中兩個(gè)【Modified Java Script Value】，拖動(dòng)到主窗口釋放鼠標(biāo)。分別雙擊翻開，重命名為“對公類型修改和“對私類型修改。同時(shí)，分別創(chuàng)立【過濾記錄】和【對公類型修改】，【對私類型修改】的連接。雙擊【規(guī)律記錄】翻開。第一個(gè)里面選擇custtype，點(diǎn)擊，在Enter value 里面寫入1，確定，如圖：在發(fā)送true數(shù)據(jù)給步驟里，選擇【對私類型修改】，在發(fā)送false數(shù)據(jù)給步驟里，選擇【對公類型修改】，確定保存，如圖：雙擊【對公類型修改】，在里面寫入javasc

12、ript 腳本語句var custtype_cn=對公客戶交易在字段中寫入custtype_cn，類型選為string。確定。同理，在【對私類型修改】中，在里面寫入javascript腳本語句var custtype_cn=對私客戶交易在字段中寫入custtype_cn，類型選為string。確定。點(diǎn)擊左側(cè)的【Transform】，選中兩個(gè)【增加常量】，拖動(dòng)到主窗口釋放鼠標(biāo)。分別雙擊翻開，重命名為“增加對公常量和“增加對私常量。分別建立【對公類型修改】和【對私類型修改】與【增加對公常量】和【增加對私常量】的連接，如圖：雙擊【增加對公常量】，名稱寫入value，類型選擇string，值寫入“這是一筆對公客戶發(fā)生的交易，確定保存。同理，雙擊【增加對私常量】，名稱寫入value，類型選擇string，值寫入“這是一筆對私客戶發(fā)生的交易，確定保存。點(diǎn)擊左側(cè)的【Output】，選中【文本文件輸出】，拖動(dòng)到主窗口釋放鼠標(biāo)。建立【增加對公常量】，【增加對私常量】和【文本文件輸出】的連接，如圖：雙擊翻開【文本文件輸出】，文件名稱寫