工業(yè)大數(shù)據(jù)采集處理與應(yīng)用-項(xiàng)目3

彭振云唐昭琳工業(yè)大數(shù)據(jù)采集、處理與應(yīng)用課程目錄1三、工業(yè)大數(shù)據(jù)預(yù)處理理解數(shù)據(jù)清洗、轉(zhuǎn)換和加載（ETL）的作用與過程理解數(shù)據(jù)倉庫的基本概念和構(gòu)建方法知識(shí)目標(biāo)技能目標(biāo)掌握ETL工具Kettle的應(yīng)用，能夠?qū)?shù)據(jù)進(jìn)行清洗、轉(zhuǎn)換處理掌握數(shù)據(jù)倉庫工具Hive的使用，能夠創(chuàng)建數(shù)據(jù)倉庫、加載數(shù)據(jù)掌握Hive查詢操作學(xué)習(xí)目標(biāo)2三、工業(yè)大數(shù)據(jù)預(yù)處理（一）數(shù)據(jù)ETL（二）建立數(shù)據(jù)倉庫（三）查詢大數(shù)據(jù)3什么是ETL？三、工業(yè)大數(shù)據(jù)預(yù)處理E：Extract，抽取T：Transform，轉(zhuǎn)換L：Load，加載4三、工業(yè)大數(shù)據(jù)預(yù)處理將分散的、異構(gòu)的數(shù)據(jù)源中的數(shù)據(jù)，從各種原始業(yè)務(wù)系統(tǒng)抽取到目標(biāo)庫增量抽取只抽取自上次抽取以來新增或修改的數(shù)據(jù)。全量抽取將數(shù)據(jù)源中的表或視圖的數(shù)據(jù)原封不動(dòng)的從數(shù)據(jù)庫中抽取出來。數(shù)據(jù)抽取5三、工業(yè)大數(shù)據(jù)預(yù)處理數(shù)據(jù)轉(zhuǎn)換替換（補(bǔ)缺失）過濾（去重）平滑（去噪）標(biāo)準(zhǔn)化（規(guī)范化）異常檢測(cè)（去異常）數(shù)據(jù)校驗(yàn)清洗：刪除或者更正臟數(shù)據(jù)的過程轉(zhuǎn)換：數(shù)據(jù)格式轉(zhuǎn)換數(shù)據(jù)粒度轉(zhuǎn)換數(shù)據(jù)降維業(yè)務(wù)規(guī)則計(jì)算6三、工業(yè)大數(shù)據(jù)預(yù)處理數(shù)據(jù)加載7三、工業(yè)大數(shù)據(jù)預(yù)處理啟動(dòng)：Windows環(huán)境下，運(yùn)行spoon.batETL工具Kettle8三、工業(yè)大數(shù)據(jù)預(yù)處理認(rèn)識(shí)kettle的界面核心對(duì)象視圖9三、工業(yè)大數(shù)據(jù)預(yù)處理認(rèn)識(shí)Kettle的界面主對(duì)象視圖10三、工業(yè)大數(shù)據(jù)預(yù)處理執(zhí)行按鈕執(zhí)行結(jié)果認(rèn)識(shí)Kettle的界面11三、工業(yè)大數(shù)據(jù)預(yù)處理認(rèn)識(shí)Kettle的要素步驟（Step）轉(zhuǎn)換（Tranformation）數(shù)據(jù)流（Stream）步驟轉(zhuǎn)換數(shù)據(jù)流跳跳（Hop）12核心對(duì)象功能數(shù)據(jù)庫連接建立與數(shù)據(jù)庫的連接。表輸入從數(shù)據(jù)庫表中讀取數(shù)據(jù)。表輸出將處理結(jié)果輸出到數(shù)據(jù)庫表。CSV文件輸入從CSV文件讀取數(shù)據(jù)。Excel輸出將處理結(jié)果輸出到電子表格。過濾記錄根據(jù)條件對(duì)數(shù)據(jù)流劃分為若干部分。流查找在數(shù)據(jù)流里查找符合條件的數(shù)據(jù)。值映射把某個(gè)數(shù)值映射為另一個(gè)數(shù)值。值替換把一列數(shù)據(jù)值替換為另一列數(shù)據(jù)值。排序記錄按條件排序數(shù)據(jù)。插入\更新根據(jù)處理結(jié)果對(duì)數(shù)據(jù)庫表進(jìn)行插入更新。如果數(shù)據(jù)庫中不存在相關(guān)記錄則插入，否則為更新。根據(jù)查詢條件中字段進(jìn)行判斷。數(shù)據(jù)庫查詢根據(jù)設(shè)定的查詢條件，對(duì)目標(biāo)表進(jìn)行查詢，返回需要的結(jié)果字段。三、工業(yè)大數(shù)據(jù)預(yù)處理認(rèn)識(shí)Kettle的常用功能13三、工業(yè)大數(shù)據(jù)預(yù)處理認(rèn)識(shí)Kettle的使用方法14三、工業(yè)大數(shù)據(jù)預(yù)處理如何新建“轉(zhuǎn)換“15三、工業(yè)大數(shù)據(jù)預(yù)處理如何創(chuàng)建“步驟“16如何創(chuàng)建步驟之間的“跳“三、工業(yè)大數(shù)據(jù)預(yù)處理17如何連接數(shù)據(jù)庫三、工業(yè)大數(shù)據(jù)預(yù)處理測(cè)試連接是否成功18訂單號(hào)產(chǎn)品名稱產(chǎn)品型號(hào)購買數(shù)量單價(jià)金額訂購日期10107MotorcyclesS10_16783095.728712/24/200310121ClassicS10_19493481.352765.95/7/200310134MotorcyclesS10_20164194.743884.347/1/2003……

三、工業(yè)大數(shù)據(jù)預(yù)處理認(rèn)識(shí)數(shù)據(jù)庫1.關(guān)系數(shù)據(jù)庫2.非關(guān)系數(shù)據(jù)庫關(guān)系數(shù)據(jù)庫，由行、列組成，可以簡單理解為二維表格行：記錄列：字段19三、工業(yè)大數(shù)據(jù)預(yù)處理認(rèn)識(shí)數(shù)據(jù)倉庫數(shù)據(jù)倉庫：面向主題的、集成的、相對(duì)穩(wěn)定的、反映歷史變化的數(shù)據(jù)集合。用于支持管理決策。數(shù)據(jù)庫主題舉例：商品客戶20功能數(shù)據(jù)倉庫數(shù)據(jù)庫數(shù)據(jù)范圍存儲(chǔ)歷史的、完整的、反應(yīng)歷史變化的數(shù)據(jù)當(dāng)前數(shù)據(jù)狀態(tài)數(shù)據(jù)變化可添加，無刪除、無變更頻繁的增加、刪除應(yīng)用場(chǎng)景面向分析、決策支持面向日常的事務(wù)處理設(shè)計(jì)理論適當(dāng)冗余，不遵循范式遵循范式，避免冗余處理量非頻繁，大批量，高吞吐，有延時(shí)頻繁，小批量，高并發(fā)，低延時(shí)三、工業(yè)大數(shù)據(jù)預(yù)處理數(shù)據(jù)庫與數(shù)據(jù)倉庫21三、工業(yè)大數(shù)據(jù)預(yù)處理大數(shù)據(jù)查詢分析工具HiveHive的存儲(chǔ)單元：數(shù)據(jù)庫（Database）表（內(nèi)部表Table、外部表ExternalTable）分區(qū)（Partition）分桶（Bucket）Hive是一個(gè)基于Hadoop的大數(shù)據(jù)查詢分析工具，能對(duì)存儲(chǔ)在文件系統(tǒng)HDFS中的數(shù)據(jù)進(jìn)行操作、查詢和分析，可以用于創(chuàng)建基于Hadoop的數(shù)據(jù)倉庫。22三、工業(yè)大數(shù)據(jù)預(yù)處理創(chuàng)建數(shù)據(jù)庫hive>createdatabase[ifnotexists]

數(shù)據(jù)庫名[comment注釋][location

文件路徑][with

dbproperties

(屬性名=屬性值,...)];

例：創(chuàng)建1個(gè)數(shù)據(jù)庫，庫名為：targethive>createdatabasetarget;語句中的方括號(hào)表示該項(xiàng)是可選項(xiàng)，斜體字表示是關(guān)鍵字。comment，表示注釋行l(wèi)ocation，表示數(shù)據(jù)庫所在的實(shí)際文件路徑。withdbproperties，表示設(shè)定該數(shù)據(jù)庫的某些屬性。語句結(jié)束符號(hào)23三、工業(yè)大數(shù)據(jù)預(yù)處理創(chuàng)建數(shù)據(jù)表hive>create[external]

table[ifnotexists]數(shù)據(jù)表名[(列名

數(shù)據(jù)類型

[comment

列注釋],...)][comment 表注釋][partitionedby (列名

數(shù)據(jù)類型,...)][clusteredby (列名,列名,...)][storedby (列名

[ASC|DESC],...)]into

分桶數(shù)量

buckets][rowfromat row_format][storedas file_format][location

文件路徑];語句中的方括號(hào)表示該項(xiàng)是可選項(xiàng)，藍(lán)色斜體字表示是關(guān)鍵字。ifnotexists，表示如果相同名字的表已經(jīng)存在，則拋出異常。external，表示創(chuàng)建一個(gè)外部表。partitionedby，表示數(shù)據(jù)按某些屬性分區(qū)存放。clusteredby，表示數(shù)據(jù)按某些屬性分桶存放。storedby，表示分桶時(shí)的排序順序。rowformat，表示數(shù)據(jù)行間隔方式。storedas，表示數(shù)據(jù)存儲(chǔ)格式，比如txtfile。comment，注釋。語句結(jié)束符號(hào)24三、工業(yè)大數(shù)據(jù)預(yù)處理創(chuàng)建數(shù)據(jù)表——例子1例：創(chuàng)建一個(gè)用戶瀏覽網(wǎng)頁的記錄表，表名為：par_table。數(shù)據(jù)按用戶瀏覽日期和所在地分區(qū)存放。hive>createtablepar_table( viewTime int,comment'瀏覽時(shí)間' userid bgint,comment'用戶ID' page_url string,comment'頁面URL地址' referrer_url string,comment'來源URL地址' ip stringcomment'IP地址') comment'瀏覽記錄表' partitionedby(datestring,posstring)'按日期和地區(qū)分區(qū)存放

rowformatdelimited‘\t’ fieldsterminatedby'\n' storedasSEQUENCEFILE;數(shù)據(jù)類型列名注釋25三、工業(yè)大數(shù)據(jù)預(yù)處理創(chuàng)建數(shù)據(jù)表——例子2

例：創(chuàng)建1個(gè)名為sales的表。序號(hào)列名列類型描述1ordernumberstring訂單號(hào)2orderlinenumstring訂單明細(xì)號(hào)3quantityorderedint訂購數(shù)量4priceeachdouble單價(jià)5salesdouble金額6qtr_idstring季度7month_idstring月8year_idstring年9productlinestring產(chǎn)品名稱10productcodestring產(chǎn)品型號(hào)11statusstring訂單狀態(tài)12countrystring國家hive>createtableifnotexixtssales( ordernumber string, orderlinenumber string, quantityordered int, priceeach double, sales double, qtr_id string, month_id string, year_id string, productline string, productcode string, status string, country string);rowformatdelimited,fieldsterminatedby‘\t’;26三、工業(yè)大數(shù)據(jù)預(yù)處理加載數(shù)據(jù)進(jìn)表hive>loaddata[local]inpath

‘filepath’[overwrite]

intotable表名

[partition(partcol1=val1,partcol2=val2...)];local，表示源數(shù)據(jù)文件在本地文件系統(tǒng)。filepath，表示目標(biāo)數(shù)據(jù)存放的文件路徑。overwrite，表示如果有同名的數(shù)據(jù)表，則覆蓋原有數(shù)據(jù)。partition，表示分區(qū)。hive>loaddatalocalinpath'/home/hadoop/products.txt'overwriteintotableproTable;例：把/home/hadoo目錄下的產(chǎn)品數(shù)據(jù)文件products.txt的據(jù)裝進(jìn)表proTable。27三、工業(yè)大數(shù)據(jù)預(yù)處理Hive創(chuàng)建分區(qū)——例子數(shù)據(jù)能夠按照分區(qū)（Partition）來管理，即按照數(shù)據(jù)的某列或某些列分為多個(gè)區(qū)存放，例如，按日期存放監(jiān)控?cái)?shù)據(jù)。分區(qū)可以極大地提高數(shù)據(jù)查詢效率。hive>createtabledeviceLog(idint,statusint)partitioinedby(datestring)rowformatdelemitedfieldsterminatedby‘\t’;hive>loaddatalocalinpath‘/home/hadoop/data/device.txt’intotabledeviceLogpartition（date=‘20200801’）;hive>selectid,statusfromdeviceLogwherename=’20200801’;創(chuàng)建一個(gè)表，按日期分區(qū)存放數(shù)據(jù)把device.txt里的數(shù)據(jù)加載到日期為20200801的分區(qū)查詢?nèi)掌跒?0200801的監(jiān)控?cái)?shù)據(jù)例：把監(jiān)控日志數(shù)據(jù)device.txt保存到分區(qū)表28三、工業(yè)大數(shù)據(jù)預(yù)處理Hive創(chuàng)建桶——例子分桶是相對(duì)分區(qū)進(jìn)行更細(xì)粒度的劃分，就是把大表化成了“小表”。將數(shù)據(jù)按照某列屬性值的哈希值進(jìn)行區(qū)分。hive>hive.enforce.bucketing=true;

hive>createtablepartLog(idint,statusint)clusteredby(id) into3bucktesrowformatdelemitedfieldsterminatedby‘\t’;hive>insert overwrite tablepartLogselect*fromtable1;hive>select*frompartLogtablesample(bucket1outof3onid);

例：把零部件的檢測(cè)日志數(shù)據(jù)分桶存放創(chuàng)建桶表，按桶方式存放數(shù)據(jù)查第1個(gè)桶的數(shù)據(jù)把table1的數(shù)據(jù)存放到桶表29三、工業(yè)大數(shù)據(jù)預(yù)處理使用Hive查詢數(shù)據(jù)hive>select[all|distinct]select_expr,select_expr,...from

table_reference[where

where_condition][groupby

col_list[havingcondition]][clusterby

col_list[distributeby

col_list][sortby|orderby

col_list]][limit

number];語句中的方括號(hào)表示該項(xiàng)是可選項(xiàng)，斜體字表示是關(guān)鍵字。all，表示查詢所有數(shù)據(jù)。默認(rèn)是all。distinct，表示去掉重復(fù)的數(shù)據(jù)行。from，表示查詢的數(shù)據(jù)表。where，表示查詢條件。groupby，表示查詢的分組條件。orderby，表示全局排序。sortby，表示局部排序。clusterby，