ch03數(shù)據(jù)的導(dǎo)入與導(dǎo)出

數(shù)據(jù)的導(dǎo)入與導(dǎo)出新工科建設(shè)之路·數(shù)據(jù)科學(xué)與大數(shù)據(jù)系列樣本數(shù)據(jù)處理第三章01基于文件的數(shù)據(jù)導(dǎo)入與導(dǎo)出文本文件可能是使用ETL工具處理的最簡(jiǎn)單的一種數(shù)據(jù)。文本文件易于交換，壓縮比高，任何文本編輯器都可打開(kāi)?？傮w來(lái)說(shuō)，文本文件可分為兩類(lèi)。（1）分隔符文件；（2）固定寬度文件。基于文件的數(shù)據(jù)導(dǎo)入與導(dǎo)出最基本的文本文件輸入步驟就是“CSV文件輸入”步驟。CSV文件是一種用分隔符分隔的文本文件。在處理這種文件之前，需要通過(guò)文本編輯器打開(kāi)查看，以確定此文件的分隔符和字段?！癈SV文件輸入”步驟和與之類(lèi)似的“固定寬度文件輸入”步驟其實(shí)都是“文本文件輸入”步驟的簡(jiǎn)化版，都不適合一次處理多個(gè)文件。這3個(gè)步驟是處理文本文件的首選步驟，“文本文件輸入”步驟具備的功能如下?；谖募臄?shù)據(jù)導(dǎo)入與導(dǎo)出①?gòu)那耙粋€(gè)步驟讀取文件名。②一次運(yùn)行讀取多個(gè)文件名。③從.zip或.gzip壓縮文件中讀取文件。④不用指定文件結(jié)構(gòu)就可以顯示文件內(nèi)容。⑤指定逃逸字符。⑥錯(cuò)誤處理。⑦過(guò)濾。⑧指定本地化的日期格式。文本文件的導(dǎo)入與導(dǎo)出案例1.（1）轉(zhuǎn)換的輸入/輸出需求讀取student.csv文件，輸出固定寬度為12字節(jié)的student.txt文件。student.csv文件以逗號(hào)為分隔符，內(nèi)容如圖3-2所示。文本文件的導(dǎo)入與導(dǎo)出案例期望輸出的student.txt文件的內(nèi)容如圖3-3所示。文本文件的導(dǎo)入與導(dǎo)出案例（2）轉(zhuǎn)換的設(shè)計(jì)圖參考2.2.2節(jié)的操作，新建轉(zhuǎn)換文件，并開(kāi)始可視化編程。該轉(zhuǎn)換所需要的步驟及步驟之間的連接流程如圖3-4所示。文本文件的導(dǎo)入與導(dǎo)出案例（3）步驟的配置“CS∨文件輸入”的配置如圖3-5所示。文本文件的導(dǎo)入與導(dǎo)出案例（4）運(yùn)行轉(zhuǎn)換如圖3-9所示，單擊卜按鈕開(kāi)始運(yùn)行程序，在打開(kāi)的對(duì)話(huà)框中單擊“啟動(dòng)”按鈕運(yùn)行此轉(zhuǎn)換。文本文件的導(dǎo)入與導(dǎo)出案例2.（1）轉(zhuǎn)換的輸入/輸出需求讀取通過(guò)“固定寬度文件輸入”的student.txt文件，輸出分隔符為分號(hào)（；）的stu.txt文件。student.txt為本節(jié)案例1的輸出文件，內(nèi)容如圖3-10所示。文本文件的導(dǎo)入與導(dǎo)出案例期望輸出的stu.txt文件的內(nèi)容如圖3-11所示。文本文件的導(dǎo)入與導(dǎo)出案例（2）轉(zhuǎn)換的設(shè)計(jì)圖參考2.2.2節(jié)的操作，新建轉(zhuǎn)換文件，并開(kāi)始可視化編程。該轉(zhuǎn)換所需要的步驟及步驟之間的連接流程如圖3-12所示。文本文件的導(dǎo)入與導(dǎo)出案例（3）步驟的配置“固定寬度文件輸入”的配置如圖3-13所示。文本文件的導(dǎo)入與導(dǎo)出案例4）運(yùn)行轉(zhuǎn)換如圖3-17所示，單擊按鈕開(kāi)始運(yùn)行程序，在打開(kāi)的對(duì)話(huà)框中，單擊“啟動(dòng)”按鈕運(yùn)行此轉(zhuǎn)換。Excel文件的導(dǎo)入與導(dǎo)出Excel的數(shù)據(jù)可分為結(jié)構(gòu)化的表格數(shù)據(jù)和非結(jié)構(gòu)化的表格數(shù)據(jù)。對(duì)于非結(jié)構(gòu)化的表格數(shù)據(jù)，有可能表里包含多個(gè)字段值的列或者重復(fù)的一組字段等。使用Kettle讀取后還需要轉(zhuǎn)化為結(jié)構(gòu)化的表格數(shù)據(jù)，才能進(jìn)一步處理，而且，Excel作為常用的辦公軟件，很難規(guī)范所有的人員按數(shù)據(jù)的格式要求，規(guī)范地輸入數(shù)據(jù)。因此，在數(shù)據(jù)導(dǎo)入時(shí)，應(yīng)盡量避免把Excel文件作為輸入數(shù)據(jù)源。Excel文件的導(dǎo)入與導(dǎo)出案例1.轉(zhuǎn)換的輸入/輸出需求讀取student.xlsx文件，輸出分隔符為逗號(hào)的student.csv文件和名為stuout.xlsx的文件。student.xlsx文件的內(nèi)容如圖3-18所示。Excel文件的導(dǎo)入與導(dǎo)出案例輸出的student.csv文件的內(nèi)容如圖3-19所示。Excel文件的導(dǎo)入與導(dǎo)出案例2.轉(zhuǎn)換的設(shè)計(jì)圖參考2.2.2節(jié)的操作，新建轉(zhuǎn)換文件，并開(kāi)始可視化編程。該轉(zhuǎn)換所需要的步驟及步驟之間的連接流程如圖3-20所示。Excel文件的導(dǎo)入與導(dǎo)出案例3.步驟的配置“文件”選項(xiàng)卡的配置如圖3-21所示。Excel文件的導(dǎo)入與導(dǎo)出案例“其他輸出字段”選項(xiàng)卡的配置如圖3-29所示，采用默認(rèn)配置即可。Excel文件的導(dǎo)入與導(dǎo)出案例4.運(yùn)行轉(zhuǎn)換如圖3-35所示，單擊按鈕開(kāi)始運(yùn)行程序，在打開(kāi)的對(duì)話(huà)框中單擊“啟動(dòng)”按鈕運(yùn)行此轉(zhuǎn)換。XML是擴(kuò)展標(biāo)識(shí)語(yǔ)言（eXtensibleMarkupLanguage）的縮寫(xiě)，是一種在平面文本文件中定義數(shù)據(jù)結(jié)構(gòu)和內(nèi)容的開(kāi)放標(biāo)準(zhǔn)。在互聯(lián)網(wǎng)上，很多軟件和系統(tǒng)都使用XML格式來(lái)交換數(shù)據(jù)，這使XML格式的文件非常流行。XML實(shí)際上是文本文件，它可以使用NotePad、vi等文本編輯器打開(kāi)。XML文件的導(dǎo)入與導(dǎo)出XML文件不是普通的文本文件，而是一種遵循規(guī)范的半結(jié)構(gòu)化的文本文件。關(guān)于XML的語(yǔ)法規(guī)則及相關(guān)的知識(shí)內(nèi)容，本書(shū)不進(jìn)行介紹，請(qǐng)不熟悉的讀者自找資料完成XML相關(guān)知識(shí)的學(xué)習(xí)。XML文件的導(dǎo)入與導(dǎo)出案例1.轉(zhuǎn)換的輸入/輸出需求讀取XML文件.XML文件，輸出XmltoExcel.xlsx文件和xmlout.xml文件。XML文件.XML文件的內(nèi)容如圖3-36所示。XML文件的導(dǎo)入與導(dǎo)出案例輸出的XmltoExcel.xlsx文件的內(nèi)容如圖3-37所示。XML文件的導(dǎo)入與導(dǎo)出案例輸出的xmlout.xml文件的內(nèi)容如圖3-38所示。XML文件的導(dǎo)入與導(dǎo)出案例2.轉(zhuǎn)換的設(shè)計(jì)圖參考2.2.2節(jié)的操作，新建轉(zhuǎn)換文件，并開(kāi)始可視化編程。該轉(zhuǎn)換所需要的步驟及步驟之間的連接流程如圖3-39所示。XML文件的導(dǎo)入與導(dǎo)出案例3.步驟的配置“文件”選項(xiàng)卡的配置如圖3-40所示。XML文件的導(dǎo)入與導(dǎo)出案例4.運(yùn)行轉(zhuǎn)換如圖3-48所示，單擊按鈕開(kāi)始運(yùn)行程序，在打開(kāi)的對(duì)話(huà)框中單擊“啟動(dòng)”按鈕運(yùn)行此轉(zhuǎn)換。JSON（JavaScriptObjectNotation）是目前Web應(yīng)用中使用越來(lái)越多的一種數(shù)據(jù)交換標(biāo)準(zhǔn)，甚至超過(guò)XML格式。JSON格式最初由DouglasCrockford提出，關(guān)于JSON的更多描述請(qǐng)參考JSON的官方網(wǎng)站。對(duì)JSON文件，Kettle可以用“JSONInput”和“JSONOutput”步驟完成文件的讀取與輸出。JSON文件的導(dǎo)入與導(dǎo)出JSON文件的導(dǎo)入與導(dǎo)出案例1.轉(zhuǎn)換的輸入/輸出需求JsonToExcel.xlsx文件的內(nèi)容如圖3-50所示。JSON文件的導(dǎo)入與導(dǎo)出案例2.轉(zhuǎn)換的設(shè)計(jì)圖參考2.2.2節(jié)的操作，新建轉(zhuǎn)換文件，并開(kāi)始可視化編程。該轉(zhuǎn)換所需要的步驟及步驟之間的連接流程如圖3-51所示。JSON文件的導(dǎo)入與導(dǎo)出案例3.步驟的配置“文件”選項(xiàng)卡的配置如圖3-52所示。JSON文件的導(dǎo)入與導(dǎo)出案例4.運(yùn)行轉(zhuǎn)換如圖3-58所示，單擊按鈕開(kāi)始運(yùn)行程序，在彈出的對(duì)話(huà)框中單擊“啟動(dòng)”按鈕運(yùn)行此轉(zhuǎn)換。02基于數(shù)據(jù)庫(kù)的數(shù)據(jù)導(dǎo)入與導(dǎo)出連接名稱(chēng)：設(shè)定一個(gè)在作業(yè)或轉(zhuǎn)換范圍內(nèi)唯一的名稱(chēng)。連接類(lèi)型：從數(shù)據(jù)庫(kù)列表中選擇要連接的數(shù)據(jù)庫(kù)類(lèi)型。連接方式：在列表里可以選擇可用的連接方式。關(guān)系數(shù)據(jù)庫(kù)的數(shù)據(jù)導(dǎo)入與導(dǎo)出關(guān)系數(shù)據(jù)庫(kù)的數(shù)據(jù)導(dǎo)入與導(dǎo)出（1）主機(jī)名稱(chēng)：數(shù)據(jù)庫(kù)服務(wù)器的主機(jī)名稱(chēng)或IP地址。（2）數(shù)據(jù)庫(kù)名稱(chēng)：要訪(fǎng)問(wèn)的數(shù)據(jù)庫(kù)的名稱(chēng)。（3）端口號(hào)：默認(rèn)是選中的數(shù)據(jù)庫(kù)服務(wù)器的默認(rèn)端口號(hào)。（4）用戶(hù)名和密碼：數(shù)據(jù)庫(kù)服務(wù)器的用戶(hù)名和密碼?！耙话恪边x項(xiàng)卡常用的連接參數(shù)如下MySQL數(shù)據(jù)庫(kù)的數(shù)據(jù)導(dǎo)入與導(dǎo)出案例1.轉(zhuǎn)換的輸入/輸出需求期望輸出的StuOut表的內(nèi)容如圖3-63所示。MySQL數(shù)據(jù)庫(kù)的數(shù)據(jù)導(dǎo)入與導(dǎo)出案例2.轉(zhuǎn)換的設(shè)計(jì)圖參考2.2.2節(jié)的操作，新建轉(zhuǎn)換文件，并開(kāi)始可視化編程。該轉(zhuǎn)換所需要的步驟及步驟之間的連接流程如圖3-64所示。MySQL數(shù)據(jù)庫(kù)的數(shù)據(jù)導(dǎo)入與導(dǎo)出案例3.步驟的配置“元數(shù)據(jù)”選項(xiàng)卡的配置如圖3-65所示。MySQL數(shù)據(jù)庫(kù)的數(shù)據(jù)導(dǎo)入與導(dǎo)出案例4.運(yùn)行轉(zhuǎn)換如圖3-78所示，單擊按鈕開(kāi)始運(yùn)行程序，在打開(kāi)的對(duì)話(huà)框中單擊“啟動(dòng)”按鈕運(yùn)行此轉(zhuǎn)換。03基于Web的數(shù)據(jù)導(dǎo)入與導(dǎo)出Kettle提供了“HTTPClient”和“HTTPPost”步驟從Web獲取數(shù)據(jù)?！癏TTPClient”步驟發(fā)出一個(gè)HTTPGET請(qǐng)求或者直接訪(fǎng)問(wèn)HTML頁(yè)面，調(diào)用此步驟里的URL從Web獲取數(shù)據(jù)。HTTPGET請(qǐng)求主要從服務(wù)器獲取數(shù)據(jù)，不會(huì)對(duì)服務(wù)器數(shù)據(jù)進(jìn)行更改。最常見(jiàn)的HTTPGET請(qǐng)求就是頁(yè)面里的各種查詢(xún)操作?；赪eb的數(shù)據(jù)導(dǎo)入與導(dǎo)出“HTTPPost”步驟發(fā)出一個(gè)HTTPPOST請(qǐng)求，調(diào)用此步驟里的URL從Web獲取數(shù)據(jù)。HTTPPOST請(qǐng)求主要向服務(wù)器提交數(shù)據(jù)，對(duì)服務(wù)器端的數(shù)據(jù)有影響。最常見(jiàn)的HTTPPOST請(qǐng)求就是用戶(hù)登錄操作，以及各種修改操作。該步驟可以通過(guò)HTTPPOST請(qǐng)求把參數(shù)作為消息體進(jìn)行提交，也可以把整個(gè)文件作為消息體進(jìn)行提交。HTML數(shù)據(jù)的導(dǎo)入與導(dǎo)出1.轉(zhuǎn)換的輸入/輸出需求讀取數(shù)據(jù)，輸出HTML源碼并保存在E：教材案例\第3章\webout.html文件中。其中，需要注意網(wǎng)頁(yè)的字符編碼（見(jiàn)圖3-79）。HTML數(shù)據(jù)的導(dǎo)入與導(dǎo)出2.轉(zhuǎn)換的設(shè)計(jì)圖參考2.2.2節(jié)的操作，新建轉(zhuǎn)換文件，并開(kāi)始可視化編程。該轉(zhuǎn)換所需要的步驟及步驟之間的連接流程如圖3-81所示。HTML數(shù)據(jù)的導(dǎo)入與導(dǎo)出3.步驟的配置在“數(shù)據(jù)”選項(xiàng)卡中，WebURL的值按案例的需求填入目標(biāo)分析地址，如圖3-82所示。HTML數(shù)據(jù)的導(dǎo)入與導(dǎo)出4.運(yùn)行轉(zhuǎn)換如圖3-87所示，單擊按鈕開(kāi)始運(yùn)行程序，在打開(kāi)的對(duì)話(huà)框中單擊“啟動(dòng)”按鈕運(yùn)行此轉(zhuǎn)換。HTTPGET請(qǐng)求返回的結(jié)構(gòu)化數(shù)據(jù)主要有XML和JSON，本例使用的API返回的是JSON格式的數(shù)據(jù)。通過(guò)對(duì)返回?cái)?shù)據(jù)的后續(xù)處理，我們可以獲取需要的數(shù)據(jù)?；贖TTPGET請(qǐng)求的導(dǎo)入與導(dǎo)出基于HTTPGET請(qǐng)求的導(dǎo)入與導(dǎo)出案例1.轉(zhuǎn)換的輸入/輸出需求期望輸出的httpGetJson.xls文件的內(nèi)容如圖3-88所示?；贖TTPGET請(qǐng)求的導(dǎo)入與導(dǎo)出案例2.轉(zhuǎn)換的設(shè)計(jì)圖參考2.2.2節(jié)的操作，新建轉(zhuǎn)換文件，并開(kāi)始可視化編程。該轉(zhuǎn)換所需要的步驟及步驟之間的連接流程如圖3-89所示?；贖TTPGET請(qǐng)求的導(dǎo)入與導(dǎo)出案例3.步驟的配置配置完的效果如圖3-90所示?；贖TTPGET請(qǐng)求的導(dǎo)入與導(dǎo)出案例4.運(yùn)行轉(zhuǎn)換單擊按鈕開(kāi)始運(yùn)行程序，在打開(kāi)的對(duì)話(huà)框（見(jiàn)圖3-94）中單擊“啟動(dòng)”按鈕運(yùn)行此轉(zhuǎn)換。04基于CDC變更數(shù)據(jù)的導(dǎo)入與導(dǎo)出時(shí)間戳這種屬性列最少需要一個(gè)更新時(shí)間，最好有兩個(gè)時(shí)間，即一個(gè)插入時(shí)間和一個(gè)更新時(shí)間。自增序列大多數(shù)據(jù)庫(kù)都有自增序列，如果數(shù)據(jù)庫(kù)表中使用了這種序列，就可以通過(guò)序列識(shí)別出新插入的數(shù)據(jù)?；谠磾?shù)據(jù)的CDC基于源數(shù)據(jù)的CDC案例1.（1）轉(zhuǎn)換的輸入/輸出需求其中，輸入數(shù)據(jù)studentcdc表的內(nèi)容如圖3-95所示。基于源數(shù)據(jù)的CDC案例（2）轉(zhuǎn)換的設(shè)計(jì)圖參考2.2.2節(jié)的操作，新建轉(zhuǎn)換文件，并開(kāi)始可視化編程。該轉(zhuǎn)換所需要的步驟及步驟之間的連接流程如圖3-101所示?；谠磾?shù)據(jù)的CDC案例（3）步驟的配置在當(dāng)前轉(zhuǎn)換畫(huà)布上空白的地方單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“轉(zhuǎn)換設(shè)置CTL-L”命令，打開(kāi)“轉(zhuǎn)換屬性”對(duì)話(huà)框，在“命名參數(shù)”選項(xiàng)卡中配置命名參數(shù)的名字（curtime）和默認(rèn)值（2018-08-04），如圖3-102所示?；谠磾?shù)據(jù)的CDC案例（4）運(yùn)行轉(zhuǎn)換單擊按鈕開(kāi)始運(yùn)行程序，在打開(kāi)的對(duì)話(huà)框中設(shè)置命名參數(shù)“curtime”的“值”為“2018-08-06”，如圖3-107所示。基于源數(shù)據(jù)的CDC案例（2）轉(zhuǎn)換的設(shè)計(jì)圖參考2.2.2節(jié)的操作，新建轉(zhuǎn)換文件，并開(kāi)始可視化編程。該轉(zhuǎn)換所需要的步驟及步驟之間的連接流程如圖3-114所示?；谠磾?shù)據(jù)的CDC案例（3）步驟的配置在當(dāng)前轉(zhuǎn)換畫(huà)布上空白的地方單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“轉(zhuǎn)換設(shè)置CTL-L”命令，系統(tǒng)打開(kāi)“轉(zhuǎn)換屬性”對(duì)話(huà)框。在“命名參數(shù)”選項(xiàng)卡中配置命名參數(shù)的名字（cur_no）和默認(rèn)值（1），如圖3-115所示?；谠磾?shù)據(jù)的CDC案例（4）運(yùn)行轉(zhuǎn)換單擊按鈕開(kāi)始運(yùn)行程序，系統(tǒng)打開(kāi)“執(zhí)行轉(zhuǎn)換”對(duì)話(huà)框，單擊“命令參數(shù)”選項(xiàng)卡，設(shè)置命名參數(shù)“cur_no”的“值”為“2”，如圖3-120所示。當(dāng)INSERT、UPDATE、DELETE等SQL執(zhí)行時(shí)，系統(tǒng)會(huì)觸發(fā)數(shù)據(jù)庫(kù)自有的觸發(fā)器，并執(zhí)行某些動(dòng)作。一般的設(shè)計(jì)思路是源表進(jìn)行INSERT、UPDATE、DELETE操作時(shí)，利用數(shù)據(jù)庫(kù)自有的觸發(fā)器，將操作的數(shù)據(jù)記錄到日志表中，根據(jù)日志表的記錄對(duì)目標(biāo)表進(jìn)行相應(yīng)的處理。因?yàn)橐儎?dòng)源數(shù)據(jù)庫(kù)，服務(wù)協(xié)議或者數(shù)據(jù)庫(kù)管理員不允許，所以在大多數(shù)情況下，系統(tǒng)不允許向數(shù)據(jù)添加觸發(fā)器，而且變動(dòng)源數(shù)據(jù)庫(kù)還會(huì)降低系統(tǒng)的性能，因此人們較少使用這種方法?；谟|發(fā)器的CDC基于觸發(fā)器的CDC案例1.（1）轉(zhuǎn)換的輸入/輸出需求期望輸出的student_cdcsync表的內(nèi)容如圖3-123所示。基于觸發(fā)器的CDC案例（2）轉(zhuǎn)換的設(shè)計(jì)圖參考2.2.2節(jié)的操作，新建轉(zhuǎn)換文件，并開(kāi)始可視化編程。該轉(zhuǎn)換所需要的步驟及步驟之間的連接流程如圖3-124所示?；谟|發(fā)器的CDC案例（3）步驟的配置“CDC日志表輸入”的配置如圖3-125所示。基于觸發(fā)器的CDC案例（4）運(yùn)行轉(zhuǎn)換單擊按鈕開(kāi)始運(yùn)行程序，系統(tǒng)打開(kāi)“執(zhí)行轉(zhuǎn)換”對(duì)話(huà)框（見(jiàn)圖3-129），單擊“啟動(dòng)”按鈕運(yùn)行此轉(zhuǎn)換。如果源數(shù)據(jù)中沒(méi)有時(shí)間戳，又不能使用觸發(fā)器，就可以使用不同版本的快照表進(jìn)行對(duì)比，來(lái)獲得數(shù)據(jù)的增量變化。我們將源數(shù)據(jù)中的所有數(shù)據(jù)加載到數(shù)據(jù)倉(cāng)庫(kù)的緩沖區(qū)形成源數(shù)據(jù)的第一個(gè)快照版本，下一次需要同步時(shí)，再將源數(shù)據(jù)的所有數(shù)據(jù)加載到數(shù)據(jù)倉(cāng)庫(kù)的緩沖區(qū)形成源數(shù)據(jù)的第二個(gè)快照版本，然后比較這兩個(gè)版本的數(shù)據(jù)，就可以找到增量變化?；诳煺盏腃DCKettle里的“合并記錄”就可以用來(lái)比較兩個(gè)表的差異。這個(gè)步驟讀取兩個(gè)使用關(guān)鍵字排序的輸入流數(shù)據(jù)，并根據(jù)輸入流里的關(guān)鍵字比較其他字段?；诳煺盏腃DC案例1.轉(zhuǎn)換的輸入/輸出需求期望輸出的studentcdcsync表的內(nèi)容如圖3-143所示?；诳煺盏腃DC案例2.轉(zhuǎn)換的設(shè)計(jì)圖參考2.2.2節(jié)的操作，新建轉(zhuǎn)換文件，并開(kāi)始可視化編程。該轉(zhuǎn)換所需要的步驟及步驟之間的連接流程如圖3-144所示。基于快照的CDC案例3.步驟的配置“快照1輸入”的配置如圖3-145所示。基于快照的CDC案例4.運(yùn)行轉(zhuǎn)換如圖3-143所示。基于日志的方式是最高級(jí)的、最沒(méi)有侵入性的CDC方法。數(shù)據(jù)庫(kù)會(huì)把插入、更新、刪除操作都記入日志中。MySQL提供了一個(gè)日志