ETL數(shù)據(jù)預(yù)處理技術(shù)主講人：趙美枝任務(wù)二部署ETL環(huán)境ETL是將業(yè)務(wù)系統(tǒng)的數(shù)據(jù)經(jīng)過抽取、清洗轉(zhuǎn)換之后加載到數(shù)據(jù)倉庫的過程ETL初識ETL現(xiàn)如今，數(shù)據(jù)已然成為一種戰(zhàn)略資源，為了充分利用數(shù)據(jù)資源，使用ETL技術(shù)進行數(shù)據(jù)分析已成為企業(yè)決策的主要工作內(nèi)容之一01.ETL簡介02.03.目錄ETL主要技術(shù)Kettle環(huán)境部署CONTENTS子任務(wù)2.1ETL簡介隨著企業(yè)的發(fā)展，各業(yè)務(wù)線、產(chǎn)品線、部門都會承建各種信息化系統(tǒng)方便開展自己的業(yè)務(wù)。隨著信息化建設(shè)的不斷深入，由于業(yè)務(wù)系統(tǒng)之間各自為政、相互獨立造成的數(shù)據(jù)孤島”現(xiàn)象尤為普遍，業(yè)務(wù)不集成、流程不互通、數(shù)據(jù)不共享。這給企業(yè)進行數(shù)據(jù)的分析利用、報表開發(fā)、分析挖掘等帶來了巨大困難。在此情況下，為了實現(xiàn)企業(yè)全局?jǐn)?shù)據(jù)的系統(tǒng)化運作管理（信息孤島、數(shù)據(jù)統(tǒng)計、數(shù)據(jù)分析、數(shù)據(jù)挖掘），為DSS（決策支持系統(tǒng)）、BI（商務(wù)智能）、經(jīng)營分析系統(tǒng)等深度開發(fā)應(yīng)用奠定基礎(chǔ)，挖掘數(shù)據(jù)價值，企業(yè)會開始著手建立數(shù)據(jù)倉庫，數(shù)據(jù)中臺。將相互分離的業(yè)務(wù)系統(tǒng)的數(shù)據(jù)源整合在一起，建立一個統(tǒng)一的數(shù)據(jù)采集、處理、存儲、分發(fā)、共享中心。1、ETL概念實現(xiàn)商務(wù)智能（BusinessIntelligence，即BI）的核心和靈魂，一般情況下ETL會花費掉整個BI項目三分之一的時間，因此ETL設(shè)計的好壞直接影響到BI項目的成敗。ETLExtract-Transform-Load用來描述將數(shù)據(jù)從源端經(jīng)過抽取（extract）、轉(zhuǎn)換（transform）、加載（load）至目的端的過程，它能夠?qū)Ω鞣N分布的、異構(gòu)的源數(shù)據(jù)（如關(guān)系數(shù)據(jù)）進行抽取，按照預(yù)先設(shè)計的規(guī)則將不完整數(shù)據(jù)、重復(fù)數(shù)據(jù)以及錯誤數(shù)據(jù)等臟數(shù)據(jù)內(nèi)容進行清洗，得到符合要求的“干凈”數(shù)據(jù)，并加載到數(shù)據(jù)倉庫中進行存儲，這些“干凈”數(shù)據(jù)就成為了數(shù)據(jù)分析、數(shù)據(jù)挖掘的基石。ETLBusinessIntelligence1、ETL概念1、ETL概念借助ETL工具借助Pentaho，Kettle工具，可快速建立ETL工程，屏蔽復(fù)雜的編碼任務(wù)、加快速度和降低難度，但缺少靈活性01編寫SQL語句該方式的優(yōu)點是靈活，可以提高ETL的運行效率，但是編碼復(fù)雜，對技術(shù)要求比較高。02ETL工具與SQL語句結(jié)合該方式綜合了前面兩種方式的優(yōu)點，可以極大地提高ETL的開發(fā)速度和效率。03ETL實現(xiàn)方式2、ETL體系結(jié)構(gòu)ETL主要是用來實現(xiàn)異構(gòu)數(shù)據(jù)源數(shù)據(jù)集成的。多種數(shù)據(jù)源的所有原始數(shù)據(jù)大部分未作修改就被載入到ETL，因而，無論數(shù)據(jù)源在關(guān)系型數(shù)據(jù)庫、非關(guān)系型數(shù)據(jù)庫還是外部文件，集成后的數(shù)據(jù)都將被置于數(shù)據(jù)庫的數(shù)據(jù)表或數(shù)據(jù)倉庫的維度表中，以便在數(shù)據(jù)庫內(nèi)或數(shù)據(jù)倉庫中作進一步地轉(zhuǎn)換。2、ETL體系結(jié)構(gòu)若數(shù)據(jù)源1和數(shù)據(jù)源2均為功能較強大的DBMS，可使用SQL語句完成一部分?jǐn)?shù)據(jù)清洗工作。但是，如果數(shù)據(jù)源為外部文件，無法使用SQL語句進行數(shù)據(jù)清洗工作，只能直接從數(shù)據(jù)源中抽取出來，然后在數(shù)據(jù)轉(zhuǎn)換時進行數(shù)據(jù)清洗工作。因此，數(shù)據(jù)倉庫中的數(shù)據(jù)清洗工作主要是在數(shù)據(jù)轉(zhuǎn)換時進行。清洗好的數(shù)據(jù)將保存到目標(biāo)數(shù)據(jù)庫中，用于后續(xù)數(shù)據(jù)分析、數(shù)據(jù)挖掘以及商業(yè)智能。ETL體系結(jié)構(gòu)示意圖3、數(shù)據(jù)倉庫糧倉→糧食貨倉→貨物數(shù)據(jù)倉庫？3、數(shù)據(jù)倉庫簡稱DW，是一個很大的數(shù)據(jù)存儲集合，出于企業(yè)的分析性報告和決策支持目的而創(chuàng)建，并對多樣的業(yè)務(wù)數(shù)據(jù)進行篩選與整合數(shù)據(jù)倉庫DataWarehouse3、數(shù)據(jù)倉庫1分析用戶行為數(shù)據(jù)，通過數(shù)據(jù)挖掘來降低投入成本，提高投入效果2可以作為各個業(yè)務(wù)的數(shù)據(jù)域，形成業(yè)務(wù)數(shù)據(jù)瓦相反饋的良性循環(huán)3整合公司所有業(yè)務(wù)數(shù)據(jù)，建立統(tǒng)一的數(shù)據(jù)中心4產(chǎn)生業(yè)務(wù)報表，用于做出決策5為網(wǎng)站運營提供運營商的數(shù)據(jù)支持?jǐn)?shù)據(jù)倉庫的作用3、數(shù)據(jù)倉庫數(shù)據(jù)倉庫與數(shù)據(jù)庫等價嗎？？3、數(shù)據(jù)倉庫數(shù)據(jù)庫也稱面向交易的處理系統(tǒng)，它是針對具體業(yè)務(wù)在數(shù)據(jù)庫聯(lián)機的日常操作，通常對少數(shù)記錄進行查詢、修改。用戶較為關(guān)心操作的響應(yīng)時間、數(shù)據(jù)的安全性、完整性和并發(fā)的支持用戶數(shù)等問題。傳統(tǒng)的數(shù)據(jù)庫系統(tǒng)作為數(shù)據(jù)管理的主要手段，主要用于操作型處理。一般針對某些主題歷史數(shù)據(jù)進行分析，支持管理決策。數(shù)據(jù)倉庫3、數(shù)據(jù)倉庫操作型處理（數(shù)據(jù)庫）分析型處理（數(shù)據(jù)倉庫）細(xì)節(jié)的綜合或者提煉的實體-關(guān)系（E-R）模型星型模型或雪花模型存儲瞬間數(shù)據(jù)存儲歷史數(shù)據(jù)，不包含最近的數(shù)據(jù)一次操作一個單元一次操作一個集合性能要求高，響應(yīng)時間短性能要求寬松可更新的只讀、只追加面向事務(wù)面向分析一次操作數(shù)據(jù)量小支持決策需求數(shù)據(jù)量小數(shù)據(jù)量大客戶訂單、庫存水平和銀行賬戶查詢客戶收益分析、市場細(xì)分3、數(shù)據(jù)倉庫ETL是數(shù)據(jù)倉庫應(yīng)用中非常重要的一環(huán)，是承前啟后的必要的一步。子任務(wù)2.2ETL主要技術(shù)1、數(shù)據(jù)抽取數(shù)據(jù)抽取就是從異構(gòu)數(shù)據(jù)源抽取數(shù)據(jù)，但是并不是所有的數(shù)據(jù)源中的數(shù)據(jù)都有實際的價值。1、數(shù)據(jù)抽取方式說明觸發(fā)器方式根據(jù)抽取要求，在要被抽取的數(shù)據(jù)源表上建立插入、修改、刪除三個觸發(fā)器，每當(dāng)數(shù)據(jù)源表中數(shù)據(jù)發(fā)生變化，就被相應(yīng)觸發(fā)器將變化數(shù)據(jù)寫入一個增量日志表中，ETL增量抽取是從增量日志中抽取，而不是直接在源表中抽取數(shù)據(jù)，同時增量日志表中抽取過的數(shù)據(jù)要及時被標(biāo)記或者刪除。時間戳方式在增量抽取時，抽取進程通過比較指定抽取時間與抽取源表的時間戳字段的值來決定抽取哪些數(shù)據(jù)。這種方式需要在源表中增加一個時間戳字段，系統(tǒng)中更新或修改源表數(shù)據(jù)的時候，也會同時修改時間戳字段的值。插入數(shù)據(jù)的時間戳是由系統(tǒng)時間指定。全表對比方式在增量抽取時，ETL進行逐條比較源表和目標(biāo)表的每次從源表中讀取所有記錄，然后逐條比較源表和目標(biāo)表的記錄，將變化的記錄過濾讀取出來。日志表方式對于建立了業(yè)務(wù)系統(tǒng)的生產(chǎn)數(shù)據(jù)庫的企業(yè)來說，可以在數(shù)據(jù)庫中創(chuàng)建業(yè)務(wù)日志表，當(dāng)特定需要監(jiān)控的業(yè)務(wù)數(shù)據(jù)發(fā)生變化時，由相應(yīng)的業(yè)務(wù)系統(tǒng)程序模塊更新維護日志表的內(nèi)容。增量抽取時，通過讀日志表數(shù)據(jù)決定加載哪些數(shù)據(jù)及如何加載。日志表的維護需要由業(yè)務(wù)系統(tǒng)程序編寫代碼來完成。數(shù)據(jù)抽取的方式2、數(shù)據(jù)清洗數(shù)據(jù)的清洗轉(zhuǎn)換是ETL中最為復(fù)雜的部分，主要任務(wù)是過濾掉不符合要求的數(shù)據(jù)，不符合要求的數(shù)據(jù)主要有不完整的數(shù)據(jù)、錯誤的數(shù)據(jù)、重復(fù)的數(shù)據(jù)三大類2、數(shù)據(jù)清洗

源頭：當(dāng)數(shù)據(jù)上報、接口調(diào)用時都會產(chǎn)生大量的不完整數(shù)據(jù)

事實：不完整數(shù)據(jù)的產(chǎn)生是不可避免的現(xiàn)象危害：不完整的數(shù)據(jù)對大數(shù)據(jù)環(huán)境下的決策具有一定的影響清洗流程：不完整數(shù)據(jù)2、數(shù)據(jù)清洗大數(shù)據(jù)環(huán)境下數(shù)據(jù)量的劇增使得獲取到的數(shù)據(jù)源會由于各種原因存在大量的錯誤數(shù)據(jù)。尤其是業(yè)務(wù)系統(tǒng)不夠健全的情況下，在接收輸入數(shù)據(jù)后沒有進行過濾判斷，而是直接將數(shù)據(jù)寫入到后臺數(shù)據(jù)庫，會造成大量錯誤數(shù)據(jù)產(chǎn)生。如數(shù)值數(shù)據(jù)輸成全角數(shù)字字符、字符串?dāng)?shù)據(jù)后面出現(xiàn)一個回車操作、日期格式不正確、日期越界等錯誤。錯誤數(shù)據(jù)清洗的過程如下圖：錯誤的數(shù)據(jù)2、數(shù)據(jù)清洗重復(fù)的數(shù)據(jù)源頭：數(shù)據(jù)集成、系統(tǒng)重復(fù)錄入等，通常表現(xiàn)為多條記錄所表達(dá)的含義相同，或同一目標(biāo)實體的記錄雖然在形式上有所不同，但其描述的目標(biāo)卻相同。危害：重復(fù)記錄的數(shù)據(jù)特征并不明顯，但是對數(shù)據(jù)識別和數(shù)據(jù)清洗造成很大的難度。清洗目的：提高數(shù)據(jù)庫使用率，降低系統(tǒng)消耗，并提高數(shù)據(jù)質(zhì)量。采用排序合并算法清洗重復(fù)數(shù)據(jù)的流程圖3、數(shù)據(jù)的加載數(shù)據(jù)加載是ETL的最后一個階段，它的主要任務(wù)是將數(shù)據(jù)從臨時數(shù)據(jù)表或文件中，加載到指定數(shù)據(jù)倉庫中。一般來說，可以通過編寫SQL語句和利用加載工具將數(shù)據(jù)加載到數(shù)據(jù)倉庫中。通常加載方式有：全量加載增量加載批量加載子任務(wù)2.3Kettle環(huán)境部署1、認(rèn)識kettle中文名稱為水壺，其設(shè)計理念是主程序員Matt希望將來自不同數(shù)據(jù)庫中的數(shù)據(jù)放到一個壺里，然后以一種指定的格式流出（即按照用戶要求的格式進行輸出）。Kettle支持管理來自不同數(shù)據(jù)庫的的數(shù)據(jù)，通過提供一個圖形化的用戶環(huán)境來描述用戶想要做什么，而不是用戶想要怎么做。kettle一款國外免費開源的輕量級ETL工具，是基于Java語言開發(fā)的，可以在Windows、Linux、Unix系統(tǒng)上運行，并且是綠色無需安裝的，它可以用于各種數(shù)據(jù)庫之間數(shù)據(jù)的遷移。kettle1、認(rèn)識kettleSpoonSpoon是Kettle的集成開發(fā)環(huán)境，它會提供一個基于SWT的圖形用戶界面，主要用于構(gòu)建ETLJobs和Transformtions，也可以用于執(zhí)行或調(diào)試作業(yè)、轉(zhuǎn)換，還可以用于監(jiān)控ETL操作的性能。windows選擇spoon.bat，linux選擇spoon.sh）PanPan是以命令行的方式執(zhí)行Spoon生成的Transformtions程序，運行在后臺，并且該組件沒有圖形化用戶界面。KetchenKitchen是以命令行的方式執(zhí)行Spoon生成的Jobs程序，運行在后臺，并且該組件沒有圖形化用戶界面。CarteCarte是Kettle中的一個重要組件，是一個輕量級的web容器，用于建立專用、遠(yuǎn)程的ETLserverKettle工具主要是由四個組件組成，分別是Spoon、Pan、Kitchen及Carte組件Kettle工具的執(zhí)行分為兩個層次，即轉(zhuǎn)換和作業(yè)，這兩個層次的最主要區(qū)別在于數(shù)據(jù)傳遞和執(zhí)行方式。Kettle的概念模型圖如右圖所示。1、認(rèn)識kettle概念轉(zhuǎn)換是ETL解決方案中重要的組成部分之一，主要用于數(shù)據(jù)的抽取、轉(zhuǎn)換以及加載等操作，其本質(zhì)是一組圖形化的數(shù)據(jù)轉(zhuǎn)換配置的邏輯結(jié)構(gòu)。一個轉(zhuǎn)換包括一個或多個步驟要素轉(zhuǎn)換中的步驟是通過跳來連接的，跳定義了一個單向通道，允許數(shù)據(jù)從一個步驟向另一個步驟流動。在Kettle中，數(shù)據(jù)的單位是行，數(shù)據(jù)流就是數(shù)據(jù)行從一個步驟到另一個步驟的移動1、認(rèn)識kettle轉(zhuǎn)換1、認(rèn)識kettle作業(yè)概念轉(zhuǎn)換目前，大多數(shù)的ETL項目都需要完成各種各樣的維護工作。例如，如何傳送文件、驗證數(shù)據(jù)庫中的數(shù)據(jù)表是否存在等操作，這些操作都必須按照一定順序完成，因此需要一個可以串行執(zhí)行的作業(yè)來處理這些操作。要素一個作業(yè)包含一個或者多個作業(yè)項，并且這些作業(yè)項都是以某種順序來進行執(zhí)行的。作業(yè)執(zhí)行的順序由作業(yè)項之間的跳（JobHop）和每個作業(yè)項的執(zhí)行結(jié)果來決定。2、kettle搭建先安裝jdk-8u151-windows-x64（直接下一步即可），安裝完成后，將安裝路徑復(fù)制在系統(tǒng)變量中第一步：搭建JDK環(huán)境2、kettle搭建注意：Kettle版本和JDK版本的兼容性2

安裝MySQL8.0，或安裝phpstudy，將MySQL的安裝路徑復(fù)制到系統(tǒng)變量中3下載Kettle安裝包：下載地址/projects/pentaho/files/Data%20Integration/。4Kettle工具pdi-ce--342.zip直接解壓，將安裝路徑復(fù)制到系統(tǒng)變量中2、kettle搭建5將數(shù)據(jù)庫驅(qū)動mysql-connector-java-5.1.46-bin拷貝到ket