ETL培訓(xùn)詳解PPT精選文檔

1、1 提綱KDSP平臺(tái)概述ETL架構(gòu)原理ETL介紹ETL工具Kettle介紹Kettle使用及練習(xí)Kettle場景實(shí)例2KDSP金蝶決策支持平臺(tái)架構(gòu)圖金蝶決策支持平臺(tái)架構(gòu)圖3ETL架構(gòu)原理架構(gòu)原理4ETL介紹介紹ETL模型主要描述了從各個(gè)數(shù)據(jù)源中抽取數(shù)據(jù)、轉(zhuǎn)換數(shù)據(jù)并加載到數(shù)據(jù)倉庫的各個(gè)環(huán)節(jié)及流程，主要功能有數(shù)據(jù)抽取規(guī)則、數(shù)據(jù)抽取頻率、數(shù)據(jù)校驗(yàn)、數(shù)據(jù)轉(zhuǎn)換規(guī)則、數(shù)據(jù)質(zhì)量檢查、錯(cuò)誤處理、記錄ETL日志、調(diào)度任務(wù)設(shè)置等。 1.從哪里抽取什么樣的數(shù)據(jù)，即抽取規(guī)則。要支持增量抽取，即每次抽取只抽取上次抽取后變化的數(shù)據(jù)。在復(fù)雜情況下，還需要檢查上次抽取后修改或者刪除的數(shù)據(jù)，并依據(jù)數(shù)據(jù)安全策略進(jìn)行相應(yīng)的處理；

2、2.數(shù)據(jù)抽取頻率即什么時(shí)間抽取，即抽取時(shí)間設(shè)置，確定每天晚上12點(diǎn)抽取，或者每1小時(shí)正點(diǎn)時(shí)抽取1次，等等； 3.數(shù)據(jù)校驗(yàn)，確定每個(gè)抽取的數(shù)據(jù)是否是有效的，是否是沒有缺陷的，是否需要補(bǔ)充內(nèi)容等； 4.數(shù)據(jù)轉(zhuǎn)換規(guī)則，即源數(shù)據(jù)怎樣轉(zhuǎn)化成需要的數(shù)據(jù)的，經(jīng)過什么樣的計(jì)算、拆分、合并等等；本數(shù)據(jù)轉(zhuǎn)換完后，需要觸發(fā)哪些數(shù)據(jù)的ETL過程； 5.數(shù)據(jù)質(zhì)量檢查，可以采用對賬等方式對轉(zhuǎn)換完后的數(shù)據(jù)進(jìn)行統(tǒng)一檢查，保證數(shù)據(jù)的抽取質(zhì)量； 6.錯(cuò)誤處理，如果轉(zhuǎn)換過程中出現(xiàn)錯(cuò)誤，需要進(jìn)行統(tǒng)一的、相應(yīng)的處理，給出明確的業(yè)務(wù)描述，記錄錯(cuò)誤日志，并發(fā)到系統(tǒng)信息中心； 7.記錄ETL日志，包括轉(zhuǎn)換的時(shí)間，數(shù)據(jù)源是哪個(gè)，轉(zhuǎn)化的數(shù)據(jù)種類

3、，轉(zhuǎn)換的源數(shù)據(jù)是哪些，對應(yīng)的目標(biāo)數(shù)據(jù)是哪些，等等。5ETL介紹介紹ETL大致分為兩部分 設(shè)計(jì)期：ETL過程，Extract即是從業(yè)務(wù)數(shù)據(jù)庫中抽取數(shù)據(jù),Transform即是根據(jù)業(yè)務(wù)邏輯規(guī)則對數(shù)據(jù)進(jìn)行加工的過程，Load即是把數(shù)據(jù)加載到數(shù)據(jù)倉庫的過程 (Extract-Transform-Load )，關(guān)鍵就在T的處理上，這個(gè)過程的實(shí)現(xiàn)，可以用可以用perl、shell、存儲(chǔ)過程等來實(shí)現(xiàn)，也可以用類似Kettle等ETL工具實(shí)現(xiàn) 運(yùn)行期：ETL的調(diào)度過程，所謂調(diào)度，就是執(zhí)行定時(shí)任務(wù)，對以上腳本、job的調(diào)度，主要是這其中的依賴如何配置的問題，還有就是對于日增、日全、月增、月全等數(shù)據(jù)如何加載。 6

4、ETL工具工具Kettle介紹介紹Kettle是一款國外開源的etl工具，純java編寫，綠色無需安裝，數(shù)據(jù)抽取高效穩(wěn)定。Kettle中有兩種腳本文件，transformation和job，transformation完成針對數(shù)據(jù)的基礎(chǔ)轉(zhuǎn)換，job則完成整個(gè)工作流的控制。Kettle功能非常豐富,主要包括轉(zhuǎn)換、作業(yè)、資源庫、日志、權(quán)限、分區(qū)、集群、性能監(jiān)控等模塊。Kettle轉(zhuǎn)換功能全，使用簡潔。作業(yè)項(xiàng)豐富，流程合理 ，java代碼，支持的數(shù)據(jù)源范圍廣，所以，跨平臺(tái)性較好 。 7ETL工具工具Kettle元數(shù)據(jù)介紹元數(shù)據(jù)介紹元數(shù)據(jù)的定義，就是描述數(shù)據(jù)的數(shù)據(jù)，元數(shù)據(jù)是ETL項(xiàng)目組面對的一個(gè)非常重

5、要的主題，對于整個(gè)數(shù)據(jù)倉庫項(xiàng)目也是非常重要的一部分。在kettle的元數(shù)據(jù)存儲(chǔ)在資源庫（repository）中，資源庫保存在數(shù)據(jù)庫中。下面按照不同的類別介紹。8ETL工具工具Kettle元數(shù)據(jù)介紹元數(shù)據(jù)介紹9ETL工具工具Kettle資源庫介紹資源庫介紹資源庫用來保存連接信息和轉(zhuǎn)換信息。用戶通過圖形界面創(chuàng)建的的數(shù)據(jù)庫連接和數(shù)據(jù)轉(zhuǎn)換任務(wù)可以保存在資源庫中。資源庫可以是各種常見的數(shù)據(jù)庫，用戶通過用戶名/密碼來訪問資源庫中的資源，默認(rèn)的用戶名/密碼是admin/admin。資源庫并不是必須的，如果沒有資源庫，用戶還可以把轉(zhuǎn)換任務(wù)保存在 xml 文件中。資源庫可以使多用戶共享轉(zhuǎn)換任務(wù)，轉(zhuǎn)換任務(wù)在資源

6、庫中是以文件夾形式分組管理的，用戶可以自定義文件夾名稱。有了資源庫,就可以將 transformation/job 保存在數(shù)據(jù)庫里,這樣方便共享。 10ETL工具工具Kettle轉(zhuǎn)換作業(yè)介紹轉(zhuǎn)換作業(yè)介紹Kettle以任務(wù)的方式存在，每個(gè)任務(wù)就是一個(gè)轉(zhuǎn)換流程，以流程圖的方式表現(xiàn)，支持各種流程處理模式，包括條件跳轉(zhuǎn)、分支、循環(huán)等等，每一個(gè)節(jié)點(diǎn)就是業(yè)務(wù)處理單元。業(yè)務(wù)處理單元可以是SQL語句，也可以是存儲(chǔ)過程，還可以是Java程序等等。Kettle中有兩種腳本文件，transformation和job，transformation完成針對數(shù)據(jù)的基礎(chǔ)轉(zhuǎn)換，job則完成整個(gè)工作流的控制。主要描述了從各個(gè)數(shù)

7、據(jù)源中抽取數(shù)據(jù)、轉(zhuǎn)換數(shù)據(jù)并加載到數(shù)據(jù)倉庫的各個(gè)環(huán)節(jié)及流程。主要功能有數(shù)據(jù)校驗(yàn)、數(shù)據(jù)轉(zhuǎn)換規(guī)則、數(shù)據(jù)質(zhì)量檢查、Bug調(diào)試錯(cuò)誤處理，定時(shí)功能、日志跟蹤等。 11Kettle使用及練習(xí)使用及練習(xí)安裝部署安裝部署Kettle的下載可以在 / 網(wǎng)站下載。要求jdk.5以上版本，設(shè)置JAVA_HOME 環(huán)境變量，.kettle不需要安裝,直接解壓。運(yùn)行spoon.bat即能看到kettle的歡迎界面，選擇沒有資源庫，打開kettle主界面，在左邊的Main tree,雙擊transaction，配置DB Connection，點(diǎn)擊test,提示成功則表示DB已

8、經(jīng)配置好了。注：1.Kettle支持跨平臺(tái)使用，Spoon.bat 是在windows 平臺(tái)運(yùn)行，Spoon.sh 是在Linux、Apple OSX、Solaris 平臺(tái)運(yùn)行。2. spoon.bat 文件中的set OPT=-Xmx256m 改成 set OPT=-Xmx512m，或者更大也可以，否則會(huì)在抽取過程中，可能會(huì)出現(xiàn)內(nèi)存溢出的異常12Kettle使用及練習(xí)使用及練習(xí)資源庫創(chuàng)建及導(dǎo)入資源庫創(chuàng)建及導(dǎo)入創(chuàng)建資源庫 如果用戶需要?jiǎng)?chuàng)建一個(gè)資源庫，在資源庫的登錄窗口（PDI 啟動(dòng)時(shí)的第一個(gè)窗口）中有 【新建】 按鈕，點(diǎn)擊該按鈕彈出新建資源庫窗口，在該窗口中選擇一個(gè)數(shù)據(jù)庫連接，如果沒有事先定義

9、的數(shù)據(jù)庫連接，則還要點(diǎn)擊【新建】按鈕，來創(chuàng)建一個(gè)數(shù)據(jù)庫連接。選擇數(shù)據(jù)庫連接后，要為該資源庫命名，作為這個(gè)資源庫的唯一標(biāo)志，最后選擇【創(chuàng)建或更新】按鈕來創(chuàng)建這個(gè)資源庫。 導(dǎo)入資源庫 找到【資源庫】菜單，選擇【探索資源庫】，在【探索資源庫】的界面上，點(diǎn)擊左上角的【文件】菜單，選擇【把資源庫從XML文件導(dǎo)入】，找到轉(zhuǎn)換或作業(yè)存放的路徑，導(dǎo)入。13Kettle使用及練習(xí)使用及練習(xí)Transformation轉(zhuǎn)換, 點(diǎn)擊頁面左上角的文件，創(chuàng)建一個(gè)新的transformation，點(diǎn)擊 保存到本地路徑，例如保存到D:/etltest下，保存文件名為EtltestTrans，kettle默認(rèn)transfor

10、mation文件保存后后綴名為ktr.轉(zhuǎn)換菜單介紹： A 主對象樹：Main Tree菜單列出的是一個(gè)transformation中基本的屬性，可以通過各個(gè)節(jié)點(diǎn)來查看。DB連接：顯示當(dāng)前transformation中的數(shù)據(jù)庫連接，每一個(gè)transformation的數(shù)據(jù)庫連接都需要單獨(dú)配置。Steps：一個(gè)transformation中應(yīng)用到的環(huán)節(jié)列表Hops：一個(gè)transformation中應(yīng)用到的節(jié)點(diǎn)連接列表 14Kettle使用及練習(xí)使用及練習(xí)Transformation轉(zhuǎn)換菜單介紹： B 核心對象： Core Objects菜單列出的是transformation中可以調(diào)用的環(huán)節(jié)列表

11、，可以通過鼠標(biāo)拖動(dòng)的方式對環(huán)節(jié)進(jìn)行添加。 Input：輸入環(huán)節(jié) Output：輸出環(huán)節(jié) Lookup：查詢環(huán)節(jié) Transform：轉(zhuǎn)化環(huán)節(jié) Joins：連接環(huán)節(jié) Scripting：腳本環(huán)15Kettle使用及練習(xí)使用及練習(xí)Transformation常用環(huán)節(jié)介紹類別類別 環(huán)節(jié)名稱環(huán)節(jié)名稱 功能說明功能說明 Input 文本文件輸入 從本地文本文件輸入數(shù)據(jù) 表輸入 從數(shù)據(jù)庫表中輸入數(shù)據(jù) 獲取系統(tǒng)信息 讀取系統(tǒng)信息輸入數(shù)據(jù) Output 文本文件輸出 將處理結(jié)果輸出到文本文件 表輸出 將處理結(jié)果輸出到數(shù)據(jù)庫表 插入/更新 根據(jù)處理結(jié)果對數(shù)據(jù)庫表機(jī)型插入更新，如果數(shù)據(jù)庫中不存在相關(guān)記錄則插入，否

12、則為更新。會(huì)根據(jù)查詢條件中字段進(jìn)行判斷 更新 根據(jù)處理結(jié)果對數(shù)據(jù)庫進(jìn)行更新，若需要更新的數(shù)據(jù)在數(shù)據(jù)庫表中無記錄，則會(huì)報(bào)錯(cuò)停止 刪除 根據(jù)處理結(jié)果對數(shù)據(jù)庫記錄進(jìn)行刪除，若需要?jiǎng)h除的數(shù)據(jù)在數(shù)據(jù)庫表中無記錄，則會(huì)報(bào)錯(cuò)停止Lookup 數(shù)據(jù)庫查詢 根據(jù)設(shè)定的查詢條件，對目標(biāo)表進(jìn)行查詢，返回需要的結(jié)果字段 流查詢 將目標(biāo)表讀取到內(nèi)存，通過查詢條件對內(nèi)存中數(shù)據(jù)集進(jìn)行查詢 調(diào)用DB存儲(chǔ)過程 調(diào)用數(shù)據(jù)庫存儲(chǔ)過程 Transform 字段選擇 選擇需要的字段，過濾掉不要的字段，也可做數(shù)據(jù)庫字段對應(yīng)過濾記錄 根據(jù)條件對記錄進(jìn)行分類排序記錄 將數(shù)據(jù)根據(jù)某以條件，進(jìn)行排序空操作 無操作增加常量 增加需要的常量字段Sc

13、ripting Modified Java Script Value擴(kuò)展功能，編寫JavaScript腳本，對數(shù)據(jù)進(jìn)行相應(yīng)處理Mapping 映射（子轉(zhuǎn)換） 數(shù)據(jù)映射Job Sat Variables設(shè)置環(huán)境變量Get Variables獲取環(huán)境變量16Kettle使用及練習(xí)使用及練習(xí)JobJob包，作為嵌套作業(yè)使用 ,點(diǎn)擊頁面左上角的文件，創(chuàng)建一個(gè)新的job，點(diǎn)擊 保存到本地路徑，例如保存到D:/etltest下，保存文件名為EtltestJob，kettle默認(rèn)job文件保存后后綴名為kjb任務(wù)菜單介紹： A 主對象樹： Main Tree菜單列出的是一個(gè)Job中基 本的屬性，可以通過各個(gè)

14、節(jié)點(diǎn)來查看。 DB連接：顯示當(dāng)前Job中的數(shù)據(jù)庫連接， 每一個(gè)Job的數(shù)據(jù)庫連接都需要單獨(dú)配置。17Kettle使用及練習(xí)使用及練習(xí)Job任務(wù)菜單介紹： B 核心對象： Job entries：一個(gè)Job中引用的環(huán)節(jié)列表 Job entries菜單列出的是Job中可以 調(diào)用的環(huán)節(jié)列表，可以通過鼠標(biāo)拖動(dòng) 的方式對環(huán)節(jié)進(jìn)行添加。 每一個(gè)環(huán)節(jié)可以通過鼠標(biāo)拖動(dòng)來將 環(huán)節(jié)添加到主窗口中。 并可通過shift+鼠標(biāo)拖動(dòng)， 實(shí)現(xiàn)環(huán)節(jié)之間的連接。18Kettle使用及練習(xí)使用及練習(xí)JOB常用環(huán)節(jié)介紹類別類別環(huán)節(jié)名稱環(huán)節(jié)名稱功能說明功能說明Job entriesSTART開始DUMMY結(jié)束Transformat

15、ion引用Transformation流程Job引用Job流程Shell調(diào)用Shell腳本SQL執(zhí)行sql語句FTP通過FTP下載Table exists檢查目標(biāo)表是否存在，返回布爾值File exists檢查文件是否存在，返回布爾值Javascript執(zhí)行JavaScript腳本Create file創(chuàng)建文件Delete file刪除文件Wait for file等待文件，文件出現(xiàn)后繼續(xù)下一個(gè)環(huán)節(jié)File Compare文件比較，返回布爾值Wait for等待時(shí)間，設(shè)定一段時(shí)間，kettle流程處于等待狀態(tài)Zip file壓縮文件為ZIP包19Kettle使用及練習(xí)使用及練習(xí)Job實(shí)踐練習(xí)實(shí)

16、踐練習(xí)點(diǎn)擊左側(cè)的【核心對象樹】，點(diǎn)擊【通用】，選中【START】，點(diǎn)擊左側(cè)的【核心對象樹】，點(diǎn)擊【通用】，選中【Transformation】，連接兩節(jié)點(diǎn)，按住 shift 鍵，從“開始”節(jié)點(diǎn)開始，拖動(dòng)鼠標(biāo)到“Transformation”節(jié)點(diǎn)后，釋放鼠標(biāo)。轉(zhuǎn)換文件是一個(gè)絕對路徑，我們需要把它改成相對路徑，為了方便移植，就需要把“file:/D:/ETLTEST” 改成 “$Internal.Job.Filename.Directory” 20Kettle使用及練習(xí)使用及練習(xí)Job實(shí)踐練習(xí)實(shí)踐練習(xí)注：Job非常靈活，可以Job中調(diào)轉(zhuǎn)換，也可以Job中調(diào)小Job，多個(gè)Job，Job和Transf

17、ormation任意組合。如上圖，Job和Transformation混合執(zhí)行。但注意在項(xiàng)目的運(yùn)用中注意JOB定制的規(guī)范性。21Kettle使用及練習(xí)使用及練習(xí)Kettle定時(shí)任務(wù)定時(shí)任務(wù) Kitchen 調(diào)度功能，Kettle中的Kitchen(作業(yè)執(zhí)行器)，是一個(gè)作業(yè)執(zhí)行引擎，用來執(zhí)行作業(yè)。用來定時(shí)執(zhí)行Kettle的數(shù)據(jù)轉(zhuǎn)換任務(wù)的軟件模塊，周期性的運(yùn)行，使得各個(gè)集成模塊可以實(shí)時(shí)、準(zhǔn)確、高效的從子系統(tǒng)的數(shù)據(jù)庫中取得需要的數(shù)據(jù)。其中的任務(wù)時(shí)間排程，可以用來定時(shí)執(zhí)行由Spoon設(shè)計(jì)的數(shù)據(jù)轉(zhuǎn)換任務(wù)，并能監(jiān)控任務(wù)的執(zhí)行狀態(tài)和結(jié)果，以保證任務(wù)策略能夠正確、穩(wěn)定運(yùn)行，管理員可以通過日志信息發(fā)現(xiàn)錯(cuò)誤及異常。22Kettle使用及練習(xí)使用及練習(xí)Kettle定時(shí)任務(wù)定時(shí)任務(wù)Kitchen是一個(gè)作業(yè)執(zhí)行引擎，是一個(gè)命令行執(zhí)行工具，是用來執(zhí)行作業(yè)的。23Kettle使用及練習(xí)使用及練習(xí)Kettle如何設(shè)置定時(shí)任務(wù)如何設(shè)置定時(shí)任務(wù) 24Kettle場景實(shí)例場景實(shí)例全量抽取全量抽取 A .簡單基礎(chǔ)維表場景實(shí)例 25Kettle場景實(shí)例場景實(shí)例全量抽取全量抽取 A .組織維表場景實(shí)例 組織單元 26Kettle場景實(shí)例場景實(shí)例全量抽取全量抽取 A .組織維表場景實(shí)例 分析組織27Kettle場景實(shí)例場景實(shí)例全量抽取全量抽取 A.資產(chǎn)負(fù)債