ETL及kettle介紹參考模板

1、目錄1. ETL知識21.1. ETL定義21.1.1. 定義21.1.2. 前提21.1.3. 原則21.2. 模式及比較31.3. ETL過程61.3.1. 總流程61.3.2. 數(shù)據(jù)抽取流程71.3.3. 數(shù)據(jù)清洗流程71.3.4. 數(shù)據(jù)轉(zhuǎn)換流程91.3.5. 數(shù)據(jù)加載流程101.4. 問題分析111.4.1. 字符集問題111.4.2. 緩慢變化維處理121.4.3. 增量、實時同步的處理121.4.4. 斷點續(xù)傳131.5. ETL工具132. Kettle簡介及使用142.1. 什么Kettle？142.2. 下載及安裝Kettle142.3. Kettle簡單例子162.3.1.

2、 啟動Kettle162.3.2. 創(chuàng)建transformation過程172.3.3. 創(chuàng)建job過程372.3.4. 命令行運行ktr和kjb411 / 431. ETL知識1.1. ETL定義1.1.1. 定義l 定義：數(shù)據(jù)的抽取(Extract)、轉(zhuǎn)換(Transform)、裝載(Load)的過程。l 目標：數(shù)據(jù)優(yōu)化。以最小代價(包括對日常操作的影響和對技能的要求) 將針對日常業(yè)務(wù)操作的數(shù)據(jù)轉(zhuǎn)化為針對數(shù)據(jù)倉庫而存儲的決策支持型數(shù)據(jù) 。1.1.2. 前提l 確定ETL范圍通過對目標表信息的收集，確定ETL的范圍l 選擇ETL工具考慮資金運行的平臺、對源和目標的支持程度、可編程的靈活性、對

3、源數(shù)據(jù)變化的監(jiān)測、數(shù)據(jù)處理時間的控制、管理和調(diào)度功能、對異常情況的處理l 確定解決方案抽取分析、變化數(shù)據(jù)的捕獲、目標表的刷新策略、數(shù)據(jù)的轉(zhuǎn)換及數(shù)據(jù)驗證1.1.3. 原則l 應(yīng)盡量利用數(shù)據(jù)中轉(zhuǎn)區(qū)對運營數(shù)據(jù)進行預(yù)處理。保證數(shù)據(jù)的安全性、集成與加載的高效性。 l ETL的過程應(yīng)是主動“拉取”，而不是從內(nèi)部“推送”，其可控性將大為增強。 l 流程化的配置管理和標準協(xié)議 l 數(shù)據(jù)質(zhì)量的保證正確性（Accuracy）：數(shù)據(jù)是否正確體現(xiàn)在現(xiàn)實或可證實的來源 完整性（Integrity）：數(shù)據(jù)之間的參照完整性是否存在或一致 一致性（Consistency）：數(shù)據(jù)是否被一致的定義或理解 完備性（Complete

4、ness）：所有需要的數(shù)據(jù)是否都存在 有效性（Validity）：數(shù)據(jù)是否在企業(yè)定義的可接受的范圍之內(nèi)時效性（Timeliness）：數(shù)據(jù)在需要的時間是否有效可獲取性（Accessibility）：數(shù)據(jù)是否易于獲取、易于理解和易于使用 數(shù)據(jù)格式錯誤（如缺失數(shù)據(jù)、數(shù)據(jù)值超出范圍或數(shù)據(jù)格式非法等）數(shù)據(jù)一致性：數(shù)據(jù)源系統(tǒng)為了性能的考慮，會在一定程度上舍棄外鍵約束，這通常會導(dǎo)致數(shù)據(jù)不一致。例如在帳務(wù)表中會出現(xiàn)一個用戶表中沒有的用戶ID，在例如有些代碼在代碼表中找不到等。1.2. 模式及比較l 兩種模式異構(gòu)同構(gòu)l 模式比較的維度：特點環(huán)境1.3. ETL過程1.3.1. 總流程l 數(shù)據(jù)抽取l 數(shù)據(jù)清洗l

5、 數(shù)據(jù)轉(zhuǎn)換l 數(shù)據(jù)加載1.3.2. 數(shù)據(jù)抽取流程l 數(shù)據(jù)來源文件系統(tǒng)，業(yè)務(wù)系統(tǒng)l 抽取方式根據(jù)具體業(yè)務(wù)進行全量或增量抽取l 抽取效率將數(shù)據(jù)按一定的規(guī)則拆分成幾部分進行并行處理l 抽取策略根據(jù)具體業(yè)務(wù)制定抽取的時間、頻度，以及抽取的流程1.3.3. 數(shù)據(jù)清洗流程清洗規(guī)則：l 數(shù)據(jù)補缺對空數(shù)據(jù)、缺失數(shù)據(jù)進行數(shù)據(jù)補缺操作，無法處理的作標記l 數(shù)據(jù)替換對無效數(shù)據(jù)進行數(shù)據(jù)的替換l 格式規(guī)范化將源數(shù)據(jù)抽取的數(shù)據(jù)格式轉(zhuǎn)換成為便于進入倉庫處理的目標數(shù)據(jù)格式l 主外鍵約束通過建立主外鍵約束，對非法數(shù)據(jù)進行替換或?qū)С龅藉e誤文件重新處理1.3.4. 數(shù)據(jù)轉(zhuǎn)換流程轉(zhuǎn)換規(guī)則l 數(shù)據(jù)合并多用表關(guān)聯(lián)實現(xiàn)，大小表關(guān)聯(lián)用loo

6、kup，大大表相交用join（每個字段加索引，保證關(guān)聯(lián)查詢的效率）l 數(shù)據(jù)拆分按一定規(guī)則進行數(shù)據(jù)拆分l 行列互換l 排序/修改序號l 去除重復(fù)記錄l 數(shù)據(jù)驗證：lookup，sum，count1.3.5. 數(shù)據(jù)加載流程實現(xiàn)方式優(yōu)點缺點時戳方式在業(yè)務(wù)表中統(tǒng)一添加字段作為時戳，當(dāng)OLTP系統(tǒng)更新修改業(yè)務(wù)數(shù)據(jù)時，同時修改時戳字段值源數(shù)據(jù)抽取相對簡單清楚，速度快，適合數(shù)據(jù)的增量加載需要修改業(yè)務(wù)表中的數(shù)據(jù)結(jié)構(gòu)，業(yè)務(wù)數(shù)據(jù)變動時工作量比較大，相對風(fēng)險較大日志表方式在OLTP系統(tǒng)中添加日志表，業(yè)務(wù)數(shù)據(jù)發(fā)生變化時，更新維護日志表內(nèi)容不需要修改業(yè)務(wù)表中的數(shù)據(jù)結(jié)構(gòu)。源數(shù)據(jù)抽取簡單清楚，速度快，適合數(shù)據(jù)的增量加載業(yè)務(wù)

7、系統(tǒng)中更新記錄日志操作麻煩全表對比方式抽取所有源數(shù)據(jù)，在更新目標表之前先根據(jù)主鍵和字段進行數(shù)據(jù)比對，有更新的進行update或insert對系統(tǒng)表結(jié)構(gòu)沒有任何影響，管理維護統(tǒng)一，可以實現(xiàn)數(shù)據(jù)的增量加載數(shù)據(jù)比對復(fù)雜，設(shè)計比較復(fù)雜，執(zhí)行速度慢全表刪除插入方式刪除目標表數(shù)據(jù)，將源數(shù)據(jù)全部插入ETL規(guī)則簡單，速度快對維表加代理健不適應(yīng)，OLTP系統(tǒng)有刪除數(shù)據(jù)時，不能在數(shù)據(jù)倉庫體現(xiàn)被刪數(shù)據(jù)，不能實現(xiàn)增量加載1.4. 問題分析1.4.1. 字符集問題1. 字符集定義字符集是字符(包含字母，數(shù)字，符號和非打印字符等)以及所指定的內(nèi)碼所組成的特定的集合。是基于某種操作系統(tǒng)平臺和某種語言集支持的。語言集的集合被

8、稱為語言組，它可能包含一種或多種語言。2. C/S字符集轉(zhuǎn)換l 直接轉(zhuǎn)換對于同一語言組的不同字符集之間，可以直接進行字符的轉(zhuǎn)換，不會產(chǎn)生亂碼l 通過Unicode轉(zhuǎn)換Unicode支持超過650種語言的國際字符集 Unicode系統(tǒng)缺省字符集utf-81.4.2. 緩慢變化維處理1. 緩慢變化維定義在現(xiàn)實世界中，維度的屬性并不是靜態(tài)的，會隨著時間的流失發(fā)生緩慢的變化。這種隨時間發(fā)生變化的維度我們一般稱之為緩慢變化維。2. 處理方式l 不保留歷史數(shù)據(jù)l 保留歷史數(shù)據(jù)起始-結(jié)束日期字段標識真/假狀態(tài)字段標識版本號字段標識代理鍵字段標識n 自增序列n 構(gòu)造算法l 保留且分析歷史信息添加新的維度列（數(shù)

9、據(jù)增多，維度列增多）1.4.3. 增量、實時同步的處理l 整表匹配同一個庫中進行l(wèi) 寫觸發(fā)器客戶是否允許創(chuàng)建觸發(fā)器是否影響數(shù)據(jù)庫性能l 讀數(shù)據(jù)庫日志Oracle：設(shè)定物化視圖日志1.4.4. 斷點續(xù)傳l 利用源表的索引機制，抽取時按”數(shù)據(jù)塊”順序抽取 l 采取DBLink的機制，結(jié)合oracle自身機制優(yōu)化效率 l 生成本地文件塊，F(xiàn)TP傳輸減少對帶寬影響。若中斷，流程控制自動回滾加載當(dāng)前數(shù)據(jù)塊 l ETL工具大都支持異常中止后讀取斷點重新加載的處理l 支持對變化數(shù)據(jù)的捕獲l 與目標數(shù)據(jù)庫松耦合1.5. ETL工具目前ETL工具來源：1. 數(shù)據(jù)庫廠商自帶的ETL工具OWB、ODI：oracle

10、數(shù)據(jù)庫廠商提供的工具，有局限性，與oracle數(shù)據(jù)庫耦合太深SSIS：Microsoft SQL Server Integration Services的簡稱，是生成高性能數(shù)據(jù)集成解決方案（包括數(shù)據(jù)倉庫的提取、轉(zhuǎn)換和加載 (ETL) 包）的平臺。前身是mssql2000的DTS第三方數(shù)據(jù)庫源需要采用ODBC數(shù)據(jù)轉(zhuǎn)換，效率上有一定折扣若項目以msssqlserver數(shù)據(jù)庫為主，采用它肯定是最佳方案。2. 第三方工具提供商Ascential公司的Datastage：最專業(yè)的ETL工具，價格不菲，使用難度一般Informatica公司的Powercenter：專業(yè)程度如Datastage旗鼓相當(dāng)，價

11、格似乎比Datastage便宜。IBM SPSS Statistics：IBM SPSS Statistics 產(chǎn)品家族能夠幫助企業(yè)解決從計劃和數(shù)據(jù)收集到分析、報告和部署的整個分析過程中的問題。它由多個模塊集成，您可以根據(jù)需求從中選擇一個或多個模塊來實現(xiàn)您所希望達到的功能。這些模塊可單獨購買，也可以捆綁購買。3. 開源ETL工具kettle：業(yè)界最有名的開源ETL工具。開源當(dāng)然就免費，免費的有些東西使用就不是很方便。2. Kettle簡介及使用2.1. 什么Kettle？Kettle是一個開源的ETL（Extract-Transform-Load的縮寫，即數(shù)據(jù)抽取、轉(zhuǎn)換、裝載的過程）項目，項目

12、名很有意思，水壺。按項目負責(zé)人Matt的說法：把各種數(shù)據(jù)放到一個壺里，然后呢，以一種你希望的格式流出。Kettle包括三大塊：Spoon轉(zhuǎn)換/工作(transform/job)設(shè)計工具 (GUI方式)Kitchen工作(job)執(zhí)行器 (命令行方式) Span轉(zhuǎn)換(trasform)執(zhí)行器 (命令行方式)Kettle是一款國外開源的etl工具，純java編寫，綠色無需安裝，數(shù)據(jù)抽取高效穩(wěn)定。Kettle中有兩種腳本文件，transformation和job，transformation完成針對數(shù)據(jù)的基礎(chǔ)轉(zhuǎn)換，job則完成整個工作流的控制。2.2. 下載及安裝Kettle1. 下載地址：2. 本

13、地安裝jdk 1.5或以上版本。3. 配置java環(huán)境變量（1） 打開我的電腦-屬性-高級-環(huán)境變量（2） 新建系統(tǒng)變量JAVA_HOME和CLASSPATH變量名：JAVA_HOME變量值： 具體路徑以自己本機安裝目錄為準變量名：CLASSPATH變量值：.;%JAVA_HOME%libdt.jar;%JAVA_HOME%libtools.jar;（3） 選擇“系統(tǒng)變量”中變量名為“Path”的環(huán)境變量，雙擊該變量，把JDK安裝路徑中bin目錄的絕對路徑，添加到Path變量的值中，并使用半角的分號和已有的路徑進行分隔。變量名：Path變量值：%JAVA_HOME%bin;%JAVA_HOME

14、%jrebin;4. 配置kettle環(huán)境變量在系統(tǒng)的環(huán)境變量中添加KETTLE_HOME變量，目錄指向kettle的安裝目錄：D:kettledata-integration5. 以上步驟完成后直接啟動kettle即可Windows直接雙擊批處理文件 Spoon.bat具體路徑為：kettledata-integrationSpoon.batLinux 則是執(zhí)行spoon.sh,具體路徑為：/kettle/data-integration/spoon.sh2.3. Kettle簡單例子2.3.1. 啟動Kettle點擊D:data-integration下面的Spoon.bat，過一會兒，就

15、會出現(xiàn)Kettle的歡迎界面：2.3.2. 創(chuàng)建transformation過程. 配置數(shù)據(jù)環(huán)境在做這個例子之前，我們需要先配置一下數(shù)據(jù)源，這個例子中，我們用到了三個數(shù)據(jù)庫，分別是：Oracle、MySql、SQLServer，以及一個文本文件。而且都放置在不同的主機上。Oralce：ip地址為03,Oracle的實例名為scgtoa,創(chuàng)建語句為：create table userInfo( id int primary key, name varchar2(20) unique, age int not null, address varchar2(20)

16、;insert into userInfo values(1,aaa,22,成都市二環(huán)路);insert into userInfo values(2,東方紅,25,中國北京);insert into userInfo values(3,123,19,廣州白云區(qū));MySql：ip地址為07,數(shù)據(jù)庫名為test2,創(chuàng)建語句為：create database test2;use test2;create table login( id int primary key, realname varchar(20) unique, username varchar(20) uni

17、que, password varchar(20) not null, active int default 0);insert into login values(1,aaa,admin,admin,0);insert into login values(2,東方紅,test,test,1);insert into login values(3,123,xxx123,123456,1);SQLServer：本機，ip為15,創(chuàng)建語句為：create database test3;use test3;create table student( sid varchar(20

18、) primary key, sname varchar(20) unique, teacher varchar(20) not null,);insert into student values(078,aaa,李老師);insert into student values(152,東方紅,Mr Wu);insert into student values(034,123,徐老師);文本文件：名為dbtest.log,位于03zhang上，即跟Oracle同一個主機。數(shù)據(jù)倉庫：位于07上，跟MySql一臺主機，而且數(shù)據(jù)庫也是MySql，也就是說，M

19、ySql上有2個數(shù)據(jù)庫，test2和test4。創(chuàng)建語句為：create database test4;use test4;create table inforTotal( id int primary key, name varchar(20) unique, age int not null, address varchar(20), username varchar(20) unique, password varchar(20) not null, active int default 0, sid varchar(20) unique, teacher varchar(20) not

20、 null, cat varchar(20), dog varchar(20);. Spoon界面進入工作空間。. 創(chuàng)建transformation文件（1） 新建transformation文件雙擊左邊轉(zhuǎn)換將創(chuàng)建一個新的transformation(也可以通過菜單里面的文件-新建-轉(zhuǎn)換方式新建文件)，點擊另存為，保存到本地路徑，例如保存到D:/etltest下，保存文件名為EtltestTrans，kettle默認transformation，文件保存后后綴名為ktr。（2） 創(chuàng)建數(shù)據(jù)庫連接在transformation頁面下，點擊左邊的【主對象樹】，雙擊【DB連

21、接】，進行數(shù)據(jù)庫連接配置。在Connection Name下面的數(shù)據(jù)庫里輸入連接的名字，如oracle在Settings里的Host Name里輸入主機名，如03在Settings里的Database Name里輸入數(shù)據(jù)庫名，如scgtoa在 Settings里的User Name里輸入數(shù)據(jù)庫用戶名,如bbs在Settings里的Password里輸入數(shù)據(jù)庫密碼，如bbs然后點擊”Test”按鈕測試連接是否成功，如果成功后，點擊OK按鈕。同樣地，我們創(chuàng)建MySql和SQLServer的連接，如：最后我們看到這樣的界面：. 抽取、轉(zhuǎn)換、裝載過程（1） 表輸入在

22、EtltestTrans頁面下，點擊左側(cè)的【核心對象】，點擊【輸入】，選中【表輸入】，拖動到主窗口釋放鼠標。雙擊工作區(qū)中【表輸入】圖標，數(shù)據(jù)庫連接選擇剛剛創(chuàng)建好的連接名為Oracle的數(shù)據(jù)庫連接，在主窗口寫入對應(yīng)的查詢語句：SELECT ID, NAME, AGE, ADDRESSFROM USERINFO ORDER BY NAME步驟名稱寫為：oracle - userInfo表輸入可以通過預(yù)覽，查看這個步驟輸出的數(shù)據(jù)。同樣地，我們建立一個MySql的表輸入組件：查詢語句為SELECT id, realname, username, password, activeFROM login O

23、RDER BY realname步驟名稱為：mysql - login表輸入（2） 表連接查詢在EtltestTrans頁面下，點擊左側(cè)的【核心對象】，點擊【連接】，選中【Merge Join】，拖動到主窗口釋放鼠標。如圖：然后，點擊表輸入，按住shift鍵，再拖動鼠標到Merge Join，這時就實現(xiàn)了在兩個組建之間的連線。如圖：雙擊工作區(qū)中的Merge Join，在出現(xiàn)的窗口里：步驟名稱命名為：表連接查詢第一個步驟選擇：oracle - userInfo表輸入第二個步驟選擇：mysql - login表輸入連接類型選擇：FULL OUTER在連接字段里，第一個步驟寫：NAME，第二個步驟寫

24、：realname（3） 表連接查詢與表輸入再次連接查詢SQLServer表輸入，如圖：（4） 遠程文本文件輸入在EtltestTrans頁面下，點擊左側(cè)的【核心對象】，點擊【輸入】，選中【文本文件輸入】，拖動到主窗口釋放鼠標。雙擊這個圖標，進入編輯窗口：輸入步驟名稱：遠程文本文件輸入在選中的文件里的文件/目錄下面輸入，文件所在的路徑，注：這個路徑可以通過本機的網(wǎng)上鄰居訪問。（5） 第三次表連接查詢再次拖入一個表連接查詢圖標，將上次的查詢結(jié)果與遠程文本文件相連，再進行一次表連接查詢。（6） 插入/更新在EtltestTrans頁面下，點擊左側(cè)的【核心對象】，點擊【輸出】，選中【插入/更新】，拖

25、動到主窗口釋放鼠標。將它與第三次表連接查詢進行連線。雙擊”插入/更新”圖標，進入編輯窗口。數(shù)據(jù)庫連接選擇數(shù)據(jù)倉庫的數(shù)據(jù)庫，目標表選擇對應(yīng)的infortaotal表，在查詢關(guān)鍵字里，表字段寫name(表示倉庫表里的字段)，流里的字段1里寫入NAME(即上一個步驟輸入的內(nèi)容里的NAME)。比較符用”=”號。點擊Edit mapping按鈕，將表字段，與流利的字段進行對應(yīng)，即流里字段里的值輸入到表里的哪個字段里去。. 執(zhí)行transformation這樣，我們的transformation就創(chuàng)建完成了，這時，我們可以點擊運行，測試創(chuàng)建的transformation是否成功。點擊這個按鈕

26、，就會執(zhí)行ETL過程，這時我們可以查看數(shù)據(jù)倉庫的表，如果有數(shù)據(jù)，并符合我們的要求，那么說明我們的transformation成功了。2.3.3. 創(chuàng)建job過程Job就是把一個或多個transformation按照一定的順序組合起來，形成一個流程。. 創(chuàng)建job文件通過菜單里的“文件-新建-作業(yè)”就可以創(chuàng)建一個job文件，點擊另存為D:etltestjobtest.kjb。. job流程1. 拖入圖標并連線在EtlscriptJob頁面，點擊【核心對象】，點擊【通用】，選中【START】拖動到主窗口釋放鼠標，再選中一個【Transformation】，拖動到主窗口釋

27、放鼠標，建立【START】與【Transformation】之間的連接。2. 編輯”開始”流程雙擊”START”圖標，就進入編輯窗口。我們可以選擇這個job是否是重復(fù)的，如果是重復(fù)的，需要選擇重復(fù)的類型，在根據(jù)類型判斷是否需要編輯時間間隔。3. 編輯transformation流程雙擊”transformation”圖標，就進入編輯窗口。其中，我們要輸入job名稱：如jobOK，點擊轉(zhuǎn)換文件名后面的按鈕，瀏覽我們我們要轉(zhuǎn)換的文件，并選中。其他的選項可以根據(jù)實際需要再進行配置。點擊確定按鈕，退出編輯窗口。. 運行job這時一個簡單的job流程就建立好了，這時我們點擊按鈕，就可以運行這個job了。如圖：. 什么時候用Job這個job跟上面的轉(zhuǎn)換其實是完成了同樣的工作，只不過job可以把多個transformation