數(shù)據(jù)倉(cāng)庫(kù)Hive應(yīng)用實(shí)戰(zhàn)- 課件 模塊5 分析導(dǎo)出倉(cāng)庫(kù)數(shù)據(jù)

理解SELECT…FROM語(yǔ)句的詳細(xì)語(yǔ)法理解WHERE語(yǔ)句的詳細(xì)語(yǔ)法及其作用能使用SELECT…FROM語(yǔ)句熟練查詢(xún)?nèi)怼⒅付?，集合中元素能使用常用函?shù)結(jié)合SELECT…FROM玉壺熟練完成特定的查詢(xún)計(jì)算能使用WHERE語(yǔ)句中謂詞操作符和邏輯運(yùn)算符篩選出符合條件的查詢(xún)結(jié)果能使用Limit語(yǔ)句結(jié)合SELECT..FROM語(yǔ)句熟練輸出查詢(xún)結(jié)果的指定列智能選址目的Hive倉(cāng)庫(kù)中創(chuàng)建好了ODS層和DWD層倉(cāng)庫(kù)，并且在ODS層表中存儲(chǔ)了各業(yè)務(wù)數(shù)據(jù)，在DWD層中只有表定義而無(wú)表數(shù)據(jù)，在此任務(wù)中要分析并獲取到DWD層的行業(yè)信息分類(lèi)表數(shù)據(jù)、基站基礎(chǔ)屬性匯總表數(shù)據(jù)和基站指標(biāo)匯總表數(shù)據(jù)。本任務(wù)以“學(xué)生信息系統(tǒng)”項(xiàng)目、“大數(shù)據(jù)商業(yè)智能選址”項(xiàng)目為實(shí)操載體，完成Hive查詢(xún)分析語(yǔ)句的使用，包括SELECT語(yǔ)句查詢(xún)指定字段、LIMIT語(yǔ)句限定返回的查詢(xún)結(jié)果、CASE語(yǔ)句對(duì)查詢(xún)結(jié)果列數(shù)據(jù)進(jìn)行條件判斷、WHERE語(yǔ)句對(duì)查詢(xún)進(jìn)行條件過(guò)濾、GROUPBY語(yǔ)句實(shí)現(xiàn)字段分組、JOIN語(yǔ)句實(shí)現(xiàn)多張表連接等學(xué)習(xí)目標(biāo)。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.1SELECT語(yǔ)句 SELECT是SQL中的射影算子，F(xiàn)ROM子句標(biāo)識(shí)了從哪個(gè)表、視圖或嵌套查詢(xún)中選擇記錄。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.1

基本語(yǔ)法SELECT[ALL|DISTINCT]select_expr,select_expr,...FROMtable_reference[WHEREwhere_condition][GROUPBYcol_list[HAVINGcondition]][ORDERBYcol_list][CLUSTERBYcol_list|[DISTRIBUTEBYcol_list][SORTBYcol_list]][LIMIT[offset,]rows];任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.1基本語(yǔ)法–查詢(xún)?nèi)砣聿樵?xún)即查詢(xún)表中所有的列的所有行數(shù)據(jù)，如下例所示：【例5-1】查詢(xún)studentdb數(shù)據(jù)庫(kù)中student表中的所有列hive(studentdb)>select*fromstudent;任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.1基本語(yǔ)法-查詢(xún)指定列、指定列別名1）查詢(xún)指定列【例5-2】查詢(xún)studentdb數(shù)據(jù)庫(kù)中student表中的stname和class列。SELECTstname,classFROMstudent;任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.1基本語(yǔ)法-查詢(xún)指定列、指定列別名2）查詢(xún)指定列并指定列別名：

列別名，通常用于重命名一個(gè)列，便于計(jì)算，別名可以緊跟列名，也可以在列名和別名之間加入關(guān)鍵字as?！纠?-3】查詢(xún)studentdb數(shù)據(jù)庫(kù)中student表中的course_scores并指定為別名cs。SELECTcourse_scoresAScsFROMstudent;任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.1基本語(yǔ)法-查詢(xún)指定列、指定列別名3）查詢(xún)處理后產(chǎn)生的新列并指定別名

在查詢(xún)時(shí)經(jīng)常使用運(yùn)算符或者函數(shù)對(duì)某些數(shù)據(jù)進(jìn)行處理，處理后會(huì)新產(chǎn)生結(jié)果列，這個(gè)結(jié)果列在原表中是不存在的，通常有必要給這些新產(chǎn)生的列起一個(gè)別名。【例5-4】查詢(xún)studentdb數(shù)據(jù)庫(kù)中student表中學(xué)生總?cè)藬?shù)SELECTcount(stname)AStotalFROMstudent;任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.1基本語(yǔ)法-查詢(xún)集合數(shù)據(jù)類(lèi)型當(dāng)用戶(hù)選擇的列是集合數(shù)據(jù)類(lèi)型時(shí)，Hive會(huì)使用JSON語(yǔ)法應(yīng)用于輸出。1）查詢(xún)Array元素 student表中的roommate列為一個(gè)string類(lèi)型的數(shù)組，數(shù)組下標(biāo)從0開(kāi)始，其值使用一個(gè)被括在[...]內(nèi)的以逗號(hào)分隔的列表表示。

查詢(xún)引用一個(gè)不存在的元素將會(huì)返回NULL。提取出的STRING數(shù)據(jù)類(lèi)型的值將不再加引號(hào)。注意：集合中的字符串元素有引號(hào)，而基本數(shù)據(jù)類(lèi)型STRING的列值沒(méi)有引號(hào)任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.1基本語(yǔ)法-查詢(xún)集合數(shù)據(jù)類(lèi)型當(dāng)用戶(hù)選擇的列是集合數(shù)據(jù)類(lèi)型時(shí)，Hive會(huì)使用JSON語(yǔ)法應(yīng)用于輸出。1）查詢(xún)Array元素

【例5-5】查詢(xún)studentdb數(shù)據(jù)庫(kù)中student表中每條數(shù)據(jù)的stname和roommate列的第一個(gè)元素。SELECTstname,roommate[0]ASfirst_roommateFROMstudentlimit2;任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.1基本語(yǔ)法-查詢(xún)集合數(shù)據(jù)類(lèi)型2）查詢(xún)Map元素

student表中course_scores列是MAP數(shù)據(jù)類(lèi)型，其使用JSON格式來(lái)表示MAP，即使用一個(gè)被括在{}內(nèi)的以逗號(hào)分隔的“鍵:值”對(duì)列表表示。

查詢(xún)一個(gè)MAP元素，可以使用ARRAY數(shù)組查詢(xún)?cè)氐恼Z(yǔ)法，但[]中使用的是鍵而不是整數(shù)索引。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.1基本語(yǔ)法-查詢(xún)集合數(shù)據(jù)類(lèi)型2）查詢(xún)Map元素

【例5-6】查詢(xún)studentdb數(shù)據(jù)庫(kù)中student表中的stname和每位學(xué)生的Hive課程成績(jī)。SELECTstname,course_scores['Hive']ASHive_scoreFROMstudentlimit2;

任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.1基本語(yǔ)法-查詢(xún)集合數(shù)據(jù)類(lèi)型3）查詢(xún)Struct元素 student表中address列是STRUCT數(shù)據(jù)類(lèi)型，其也是使用JSON格式表示數(shù)據(jù)。

查詢(xún)STRUCT中的某個(gè)元素，應(yīng)使用“點(diǎn)”符號(hào)，類(lèi)似于“表名.列名”的用法。

WHERE子句中同樣可以使用這些引用方式，將在后續(xù)“WHERE語(yǔ)句”中討論。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.1基本語(yǔ)法-查詢(xún)集合數(shù)據(jù)類(lèi)型3）查詢(xún)Struct元素

【例5-7】查詢(xún)studentdb數(shù)據(jù)庫(kù)中student表中的stname和每個(gè)學(xué)生來(lái)自的city信息

SELECTstname,address.cityFROMstudentLIMIT2;任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.1基本語(yǔ)法-算數(shù)運(yùn)算符 Hive中支持所有典型的算術(shù)運(yùn)算符，算術(shù)運(yùn)算符接受任意的數(shù)值類(lèi)型。如果數(shù)據(jù)類(lèi)型不同，兩種類(lèi)型中值范圍較小的那個(gè)數(shù)據(jù)類(lèi)型將轉(zhuǎn)換為其他范圍更廣的數(shù)據(jù)類(lèi)型。范圍更廣在某種意義上就是指一個(gè)類(lèi)型具有更多的字節(jié)從而可以容納更大范圍的值。例如，對(duì)于INT類(lèi)型和BIGINT類(lèi)型運(yùn)算，INT類(lèi)型會(huì)轉(zhuǎn)換提升為BIGINT類(lèi)型；對(duì)于INT類(lèi)型和FLOAT類(lèi)型運(yùn)算，INT類(lèi)型會(huì)轉(zhuǎn)換提升為FLOAT類(lèi)型。下表5-1描述了在Hive中可用的算術(shù)運(yùn)算符：任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.1基本語(yǔ)法-算數(shù)運(yùn)算符

運(yùn)算符操作描述A+B所有數(shù)字類(lèi)型A加B的結(jié)果A-B所有數(shù)字類(lèi)型A減去B的結(jié)果A*B所有數(shù)字類(lèi)型A乘以B的結(jié)果A/B所有數(shù)字類(lèi)型A除以B的結(jié)果A%B所有數(shù)字類(lèi)型A除以B產(chǎn)生的余數(shù)A&B所有數(shù)字類(lèi)型A和B的按位與結(jié)果A|B所有數(shù)字類(lèi)型A和B的按位或結(jié)果A^B所有數(shù)字類(lèi)型A和B的按位異或結(jié)果~A所有數(shù)字類(lèi)型A按位取反的結(jié)果表5-1算術(shù)運(yùn)算符任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.1基本語(yǔ)法-算數(shù)運(yùn)算符

【例5-8】求student表中每個(gè)學(xué)生的課程平均分。

SELECTstname,(course_scores['Hive']+course_scores['Python'])/2ASav_scoreFROMstudentLIMIT2;任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.1基本語(yǔ)法-常用函數(shù) Hive中有很多函數(shù)，將在后續(xù)任務(wù)中專(zhuān)門(mén)講述各函數(shù)，這里只介紹常用的求總行數(shù)count函數(shù)、求最大值max函數(shù)、求最小值min函數(shù)、求總和sum函數(shù)、求平均值avg函數(shù)。

注意，默認(rèn)在執(zhí)行count(*)操作時(shí)返回結(jié)果為0。其原因是hive中設(shè)置項(xiàng)pute.query.using.stats，其默認(rèn)值為true，表示讀取表級(jí)統(tǒng)計(jì)信息中的數(shù)據(jù)；設(shè)置為false，將不讀取統(tǒng)計(jì)數(shù)據(jù)，而執(zhí)行MR任務(wù)。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.1基本語(yǔ)法-常用函數(shù)

【例5-9】使用count函數(shù)求出student表中總的數(shù)據(jù)行數(shù)。

setpute.query.using.stats=false;

SELECTcount(*)countFROMstudent;

任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.1基本語(yǔ)法-常用函數(shù)【例5-10】使用count函數(shù)求出student表中參加了Hive課程學(xué)習(xí)的人數(shù)。SELECTcount(course_scores['Hive'])hive_scoreFROMstudent;

任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.1基本語(yǔ)法-常用函數(shù)【例5-11】使用count函數(shù)統(tǒng)計(jì)出學(xué)生是來(lái)自幾個(gè)不同的城市。SELECTcount(DISTINCTaddress.city)citysFROMstudent;DISTINCT關(guān)鍵字用于過(guò)濾掉重復(fù)記錄只保留一條。因此，student表中相同的city在統(tǒng)計(jì)時(shí)只算一條。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.1基本語(yǔ)法-常用函數(shù)【例5-12】使用avg函數(shù)求出student表中Hive課程和Python課程的平均分。SELECTavg(course_scores['Hive'])ASavg_Hive, avg(course_scores['Python'])ASavg_PythonFROMstudent;任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.1基本語(yǔ)法-常用函數(shù)【例5-13】使用min函數(shù)和max函數(shù)求出student表中Python課程的最低分和最高分。SELECTmin(course_scores['Python'])ASmin_Python, max(course_scores['Python'])ASmax_PythonFROMstudent;任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.2

LIMIT語(yǔ)句使用查詢(xún)會(huì)返回表中符合查詢(xún)條件的所有數(shù)據(jù)。那如何只顯示出查詢(xún)結(jié)果中的某幾條數(shù)據(jù)呢？通過(guò)LIMIT子句可以限制返回的行數(shù)。顯示查詢(xún)結(jié)果中限定的前n條數(shù)據(jù)，或從索引n開(kāi)始之后的m條數(shù)據(jù)語(yǔ)法：SELECTcol_name,[col_name]FROMtable_nameLIMITn[,m];【例5-14】查詢(xún)student表中的第3/4/5條數(shù)據(jù)的stname和class。SELECTstname,classFROMstudentLIMIT2,3;任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.3SELECT嵌套語(yǔ)句使用avg函數(shù)能分別統(tǒng)計(jì)student表中每個(gè)班級(jí)Hive課程的平均分。當(dāng)更進(jìn)一步，需要獲取Hive課程平均分最高的班級(jí)時(shí)，如何實(shí)現(xiàn)這個(gè)功能呢？以下按照先求班級(jí)平均分后再求最大值的順序執(zhí)行發(fā)生了錯(cuò)誤：輸入語(yǔ)句：hive(studentdb)>selectclass,max(avg(course_scores['Hive'])asavg_Hive)fromstudentgroupbyclass;結(jié)果報(bào)錯(cuò)：FAILED:ParseExceptionline1:16cannotrecognizeinputnear'max''(''count'inexpressionspecification任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.3SELECT嵌套語(yǔ)句原因和解決辦法： Hive和MySQL一樣，不支持聚合函數(shù)的嵌套，我們需要使用嵌套查詢(xún)?nèi)蝿?wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.3SELECT嵌套語(yǔ)句【例5-15】使用嵌套查詢(xún)統(tǒng)計(jì)student表中各班級(jí)Hive課程平均分的最高分。FROM(SELECTclass,avg(course_scores['Hive'])ASavg_HiveFROMstudentdb.studentGROUPBYclass)ASrsSELECTmax(rs.avg_Hive)ASmax_avg_Hive;代碼中首先將查詢(xún)語(yǔ)句selectclass,avg(course_scores['Hive'])asavg_Hivefromstudentgroupbyclass所查詢(xún)到的班級(jí)及個(gè)班級(jí)的平均分存入臨時(shí)結(jié)果表rs中，然后再對(duì)臨時(shí)結(jié)果表使用max函數(shù)獲取各班級(jí)平均分中的最大值。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.3SELECT嵌套語(yǔ)句【例5-16】使用嵌套查詢(xún)統(tǒng)計(jì)student表中來(lái)自不同城市的學(xué)生Python課程平均分的最高分。FROM(SELECTaddress.city,avg(course_scores['Python'])ASavg_PythonFROMstudentdb.studentGROUPBYaddress.city)ASrsSELECTmax(rs.avg_Python)ASmax_avg_Python;任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.4CASE---WHEN---THEN---ELSE--END語(yǔ)句CASE…WHEN…THEN…ELSE…END語(yǔ)句和if語(yǔ)句類(lèi)似，用來(lái)處理單個(gè)列的查詢(xún)結(jié)果?？梢詫?duì)某列的結(jié)果進(jìn)行條件判斷。語(yǔ)法如下：SELECTcol_name_1,col_name_2,...CASE WHENcol_name_n滿(mǎn)足條件1THEN結(jié)果1 WHENcol_name_n滿(mǎn)足條件2THEN結(jié)果2 ... ELSE結(jié)果nENDas列別名FROMtable_name；任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.4CASE---WHEN---THEN---ELSE--END語(yǔ)句【例5-17】查詢(xún)student表中的stname、class及Hive課程等級(jí)，課程等級(jí)劃分條件為課程分?jǐn)?shù)大于等于90的為“A”，課程分?jǐn)?shù)大于等于80而小于90的為“B”，課程分?jǐn)?shù)大于等于60而小于80的為“C”，其余的為“D”。SELECTstname,class,CASE WHENcourse_scores['Hive']>=90THEN'A’ WHENcourse_scores['Hive']<90ANDcourse_scores['Hive']>=80THEN'B’ WHENcourse_scores['Hive']<80ANDcourse_scores['Hive']>=60THEN'C’ ELSE'D'ENDASHive_levelFROMstudentdb.student;任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.5WHERE語(yǔ)句如果使用SELECT語(yǔ)句但不使用WHERE子句在表中查詢(xún)數(shù)據(jù)，則會(huì)獲取表中的所有行記錄。但在很多場(chǎng)景下，只需要查詢(xún)一些特定的符合條件數(shù)據(jù)。比如學(xué)生表里保存的是歷年學(xué)生的數(shù)據(jù)，而通常只需要查詢(xún)當(dāng)前學(xué)年學(xué)期的學(xué)生數(shù)據(jù)，這時(shí)就需要使用WHERE語(yǔ)句，將不滿(mǎn)足條件的行過(guò)濾掉。SELECT語(yǔ)句用于選取字段，WHERE語(yǔ)句用于過(guò)濾條件，兩者結(jié)合使用可以查找到符合過(guò)濾條件的記錄。和SELECT語(yǔ)句一樣，在介紹WHERE語(yǔ)句之前已經(jīng)在很多簡(jiǎn)單例子中使用過(guò)該語(yǔ)句了。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.5WHERE語(yǔ)句WHERE語(yǔ)句使用謂詞表達(dá)式，當(dāng)有幾種謂詞表達(dá)式時(shí)使用AND和OR相連接。當(dāng)謂詞表達(dá)式計(jì)算結(jié)果為true時(shí)相應(yīng)的行將被保留并輸出。注意，WHERE子句必須緊隨FROM子句?！纠?-18】查詢(xún)出student表中BigData1802班級(jí)的所有學(xué)生信息。SELECT*FROMstudentWHEREclass='BigData1802';任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.5WHERE語(yǔ)句（1）謂詞操作符WHERE語(yǔ)句使用謂詞表達(dá)式，當(dāng)謂詞表達(dá)式的結(jié)果為false時(shí)，該行會(huì)被過(guò)濾掉，這些操作符同樣可以用于JOIN…ON和HAVING語(yǔ)句中。下表列出了hive中支持的謂詞操作符。表5-2謂詞操作符操作符支持的數(shù)據(jù)類(lèi)型描述A=B基本數(shù)據(jù)類(lèi)型如果A等于B則返回TRUE，反之返回FALSEA<=>B基本數(shù)據(jù)類(lèi)型如果A和B都為NULL，則返回TRUE，其他的和等號(hào)（=）操作符的結(jié)果一致，如果任一為NULL則結(jié)果為NULL任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.5WHERE語(yǔ)句表5-2謂詞操作符操作符支持的數(shù)據(jù)類(lèi)型描述A<>B,A!=B基本數(shù)據(jù)類(lèi)型A或者B為NULL則返回NULL；如果A不等于B，則返回TRUE，反之返回FALSEA<B基本數(shù)據(jù)類(lèi)型A或者B為NULL，則返回NULL；如果A小于B，則返回TRUE，反之返回FALSEA<=B基本數(shù)據(jù)類(lèi)型A或者B為NULL，則返回NULL；如果A小于等于B，則返回TRUE，反之返回FALSEA>B基本數(shù)據(jù)類(lèi)型A或者B為NULL，則返回NULL；如果A大于B，則返回TRUE，反之返回FALSE任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.5WHERE語(yǔ)句表5-2謂詞操作符操作符支持的數(shù)據(jù)類(lèi)型描述A>=B基本數(shù)據(jù)類(lèi)型A或者B為NULL，則返回NULL；如果A大于等于B，則返回TRUE，反之返回FALSEA[NOT]BETWEENBANDC基本數(shù)據(jù)類(lèi)型如果A，B或者C任一為NULL，則結(jié)果為NULL。如果A的值大于等于B而且小于或等于C，則結(jié)果為T(mén)RUE，反之為FALSE。如果使用NOT關(guān)鍵字則可達(dá)到相反的效果。AISNULL所有數(shù)據(jù)類(lèi)型如果A等于NULL，則返回TRUE，反之返回FALSE任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.5WHERE語(yǔ)句表5-2謂詞操作符操作符支持的數(shù)據(jù)類(lèi)型描述AISNOTNULL所有數(shù)據(jù)類(lèi)型如果A不等于NULL，則返回TRUE，反之返回FALSEIN(數(shù)值1,數(shù)值2)所有數(shù)據(jù)類(lèi)型使用IN運(yùn)算顯示列表中的值A(chǔ)[NOT]LIKEBSTRING類(lèi)型B是一個(gè)SQL下的簡(jiǎn)單正則表達(dá)式，如果A與其匹配的話(huà)，則返回TRUE；反之返回FALSE。B的表達(dá)式說(shuō)明如下：‘x%’表示A必須以字母‘x’開(kāi)頭，‘%x’表示A必須以字母’x’結(jié)尾，而‘%x%’表示A包含有字母’x’,可以位于開(kāi)頭，結(jié)尾或者字符串中間。類(lèi)似地，下劃線‘_’匹配單個(gè)字符。B必須要和整個(gè)字符串A相匹配才行。如果使用NOT關(guān)鍵字則可達(dá)到相反的效果。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.5WHERE語(yǔ)句表5-2謂詞操作符操作符支持的數(shù)據(jù)類(lèi)型描述ARLIKEB,AREGEXPBSTRING類(lèi)型B是一個(gè)正則表達(dá)式，如果A與其匹配，則返回TRUE；反之返回FALSE。匹配使用的是JDK中的正則表達(dá)式接口實(shí)現(xiàn)的，因?yàn)檎齽t也依據(jù)其中的規(guī)則。例如，正則表達(dá)式必須和整個(gè)字符串A相匹配，而不是只需與其子字符串匹配。注意，沒(méi)有“A==B”的語(yǔ)法，Hive中使用“=”表示等于，而不是“==”。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.5WHERE語(yǔ)句【例5-19】查詢(xún)出student表中address居住城市為“Zhuzhou”的stname和class。SELECTstname,classFROMstudentWHEREaddress.city='Zhuzhou';任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.5WHERE語(yǔ)句【例5-20】查詢(xún)出student表中Hive課程分?jǐn)?shù)在70到90的之間的學(xué)生姓名及Hive課程分?jǐn)?shù)。SELECTstname,course_scores['Hive']Hive_scoreFROMstudentWHEREcourse_scores['Hive']BETWEEN70AND90;任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.5WHERE語(yǔ)句【例5-21】查詢(xún)出student表中Python課程分?jǐn)?shù)是95或85或75的學(xué)生姓名及Python課程分?jǐn)?shù)。SELECTstname,course_scores['Python']Python_scoreFROMstudentWHEREcourse_scores['Python']IN(95,85,75);任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.5WHERE語(yǔ)句【例5-22】查詢(xún)出student表中除了Network1801班和Network1802班以外的學(xué)生信息。SELECTstname,classFROMstudentWHEREclassNOTIN('Network1801','Network1802');任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.5WHERE語(yǔ)句【例5-23】查詢(xún)出student表中有未參加課程考試的學(xué)生姓名及班級(jí)，即課程分?jǐn)?shù)為NULL，則表明該生未參加該課程考試。SELECTstname,class,course_scoresFROMstudentWHEREcourse_scores['Hive']ISNULLORcourse_scores['Python']ISNULL;任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.5WHERE語(yǔ)句2）邏輯運(yùn)算符運(yùn)算符是邏輯運(yùn)算符即為邏輯表達(dá)式，邏輯表達(dá)式返回TRUE或FALSE。Hive支持的邏輯運(yùn)算符如下表5-3。表5-3邏輯運(yùn)算符運(yùn)算符操作描述AANDBbooleanTRUE，如果A和B都是TRUE，否則FALSE。A&&Bboolean類(lèi)似于AANDB。AORBbooleanTRUE，如果A或B或兩者都是TRUE，否則FALSE。A||Bboolean類(lèi)似于

AORB。NOTAbooleanTRUE，如果A是FALSE，否則FALSE。!Aboolean類(lèi)似于

NOTA。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.5WHERE語(yǔ)句【例5-24】查詢(xún)student表中Network1802班來(lái)自“Hunan”省的學(xué)生姓名和所在省份。SELECTstname,vinceFROMstudent

WHEREclass='Network1802'ANDvince='Hunan';【例5-25】查詢(xún)student表中Hive課程大于80分，或者Python課程大于80分的學(xué)生姓名及Hive和Python兩門(mén)課程的分?jǐn)?shù)。SELECTstname,course_scores['Hive']Hive_score,course_scores['Python']Python_scoreFROMstudentWHEREcourse_scores['Hive']>80ORcourse_scores['Python']>80;任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.5WHERE語(yǔ)句（3）Like和RLike1、like表5-2描述了LIKE和RLIKE謂詞操作符。LIKE是一個(gè)標(biāo)準(zhǔn)的SQL操作符，其可以讓我們通過(guò)字符串的開(kāi)頭或結(jié)尾，以及指定特定的子字符串，或當(dāng)子字符串出現(xiàn)在字符串內(nèi)的任何位置時(shí)進(jìn)行匹配。使用LIKE運(yùn)算選擇類(lèi)似的值，選擇條件可以包含字符或數(shù)字，比如：%代表零個(gè)或多個(gè)字符(任意個(gè)字符)。_代表一個(gè)字符。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.5WHERE語(yǔ)句【例5-26】查詢(xún)出student表中姓名以“Wang”開(kāi)頭的學(xué)生信息。SELECTstnameFROMstudentWHEREstnameLIKE'Wang%';【例5-27】查詢(xún)出student表中姓名的第二個(gè)字母為“h”的學(xué)生信息。SELECTstnameFROMstudentWHEREstnameLIKE'_h%';任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.5WHERE語(yǔ)句（2）rlikeRLIKE子句是Hive中這個(gè)功能的一個(gè)擴(kuò)展，其可以通過(guò)更強(qiáng)大的Java正則表達(dá)式語(yǔ)言來(lái)指定匹配條件?！纠?-28】查詢(xún)student表中姓名中含有‘a(chǎn)’字母的學(xué)生信息。SELECTstnameFROMstudentWHEREstnameRLIKE'[a]';任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.5WHERE語(yǔ)句【例5-29】查詢(xún)student表中居住城市包含‘zhou’和‘sha’字符的學(xué)生信息。SELECTstname,address.cityFROMstudentWHEREaddress.cityRLIKE'.*(zhou|sha).*';任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.5WHERE語(yǔ)句上例中關(guān)鍵字RLIKE后面的的字符串表達(dá)如下含義:字符串中的點(diǎn)號(hào)(.)表示和任意的字符匹配，星號(hào)(*)表示重復(fù)“左邊的字符串”零次到無(wú)數(shù)次。表達(dá)式(x|y)表示和x或者y匹配。不過(guò)，‘zhou’或‘sha’字符串前可能沒(méi)有其他任何字符，而且它們后面也可能不含有其他任何字符。當(dāng)然，上例中也可以通過(guò)2個(gè)LIKE子句來(lái)改寫(xiě)為如下所示：SELECTstname,address.cityFROMstudentWHEREaddress.cityLIKE'%zhou%'ORaddress.cityLIKE'%sha%';任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.6GROUPBY語(yǔ)句1、GROUPBY語(yǔ)句GROUPBY從字面上理解，GROUP表示分組，BY后面跟字段名，且可以是多個(gè)字段名，表示根據(jù)哪些字段進(jìn)行分組。該語(yǔ)句通常和聚合函數(shù)一起使用，按照一個(gè)或者多個(gè)列隊(duì)結(jié)果進(jìn)行分組，然后對(duì)每個(gè)組執(zhí)行聚合操作。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.6GROUPBY語(yǔ)句1、GROUPBY語(yǔ)句【例5-30】查詢(xún)student表中所有的班級(jí)名稱(chēng)。SELECTclassFROMstudentGROUPBYclass;任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.6GROUPBY語(yǔ)句但是在執(zhí)行以下語(yǔ)句時(shí)將會(huì)報(bào)錯(cuò)：由于數(shù)據(jù)分組后，如下圖5-33中下方的結(jié)果每一行記錄除了GROUPBY后的字段外，其他的字段每一行可以理解為有多個(gè)值。比如class為BigData1801的stname對(duì)應(yīng)有“Zhangdan”和“Zhoumei”共2人。而SELECT語(yǔ)法只允許每行有一個(gè)值，因此語(yǔ)句SELECT*FROMstudentGROUPBYclass;報(bào)錯(cuò)。那么如何處理由于GROUPBY后產(chǎn)生的每一行的多個(gè)值呢？就需要使用聚合函數(shù)：比如count、sum、avg等函數(shù)。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.6GROUPBY語(yǔ)句任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.6GROUPBY語(yǔ)句【例5-31】查詢(xún)student表中學(xué)生信息，按班級(jí)進(jìn)行分組，求每個(gè)班級(jí)中Hive課程的最高分。SELECTclass,max(course_scores['Hive’])FROMstudentGROUPBYclass;任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.6GROUPBY語(yǔ)句【例5-32】查詢(xún)student表中學(xué)生信息，按班級(jí)進(jìn)行分組，使用groupby+collect_list函數(shù)顯示student表中每個(gè)班級(jí)的所有學(xué)生Hive成績(jī)。SELECTclass,collect_list(course_scores['Hive'])Hive_scoreFROMstudentGROUPBYclass;上述collect_list函數(shù)實(shí)現(xiàn)將分組中的某列不去重并轉(zhuǎn)為一個(gè)數(shù)組返回。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.6GROUPBY語(yǔ)句2、HAVING語(yǔ)句HAVING語(yǔ)句允許用戶(hù)通過(guò)一個(gè)簡(jiǎn)單的語(yǔ)法完成原本需要通過(guò)子查詢(xún)才能對(duì)GROUPBY語(yǔ)句產(chǎn)生的分組進(jìn)行條件過(guò)濾的任務(wù)。【例5-33】查詢(xún)student表中學(xué)生信息，按班級(jí)進(jìn)行分組，輸出班級(jí)中Hive課程的最高分在85分以上的。SELECTclass,max(course_scores['Hive'])max_Hive_85FROMstudentGROUPBYclassHAVIGmax_Hive_85>85;任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.6GROUPBY語(yǔ)句如果沒(méi)使用HAVING子句，該查詢(xún)將需要使用一個(gè)嵌套SELECT子查詢(xún)實(shí)現(xiàn)，代碼如下：SELECTrs.class,rs.maxFROM(SELECTclass,max(course_scores['Hive'])ASmaxFROMstudentGROUPBYclass)rsWHERErs.max>85;任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.7JOIN語(yǔ)句JOIN語(yǔ)句是基于兩個(gè)或多個(gè)表中列之間的關(guān)系，將這些表進(jìn)行連接。JOIN語(yǔ)句的語(yǔ)法格式如下SELECT...FROMtable_reference[join_condition][INNER]JOIN|{LEFT|RIGHT|FULL}[OUTER]JOIN|CROSSJOINtable_reference[join_condition]ONexpression語(yǔ)法解釋如下：任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.7JOIN語(yǔ)句關(guān)鍵字語(yǔ)法解釋table_reference表示連接的表名join_condition表示連接的條件，例如使用WHERE子句等[INNER]JOIN表示內(nèi)連接，其中INNER為可選。根據(jù)關(guān)聯(lián)列將左表和右表中能關(guān)聯(lián)起來(lái)的數(shù)據(jù)連接后返回，返回的結(jié)果就是兩個(gè)表中所有相匹配的數(shù)據(jù)。LEFT[OUTER]JOIN表示左外連接，其中OUTER為可選。根據(jù)關(guān)聯(lián)列保留左表完全值，若右表中存在與左表中匹配的值，則保留；若右表中不存在與左表中匹配的值，則以NULL代替。RIGHT[OUTER]JOIN表示右外連接，其中OUTER為可選。根據(jù)關(guān)聯(lián)列保留右表完全值，若左表中存在與右表中匹配的值,則保留；若左表中不存在與右表中匹配的值，則以NULL代替。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.7JOIN語(yǔ)句表5-4JOIN語(yǔ)句關(guān)鍵字解釋關(guān)鍵字語(yǔ)法解釋FULL[OUTER]JOIN表示全外連接，其中OUTER為可選。根據(jù)關(guān)聯(lián)列返回左表和右表中的所有數(shù)據(jù)，若關(guān)聯(lián)不上則以NULL代替。CROSSJOIN表示笛卡兒積關(guān)聯(lián)，返回左表與右表的笛卡兒積結(jié)果，兩張表的所有行都會(huì)交叉連接。ONexpression通過(guò)ON子句指定表之間的共同列expression。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.7JOIN語(yǔ)句1、內(nèi)連接內(nèi)連接，即INNERJOIN，只有進(jìn)行連接的兩個(gè)表中都存在與連接條件相匹配的數(shù)據(jù)才會(huì)被保留下來(lái)。【例5-34】根據(jù)數(shù)據(jù)te_courses.txt，在studentdb數(shù)據(jù)庫(kù)中創(chuàng)建表te_courses，并導(dǎo)入數(shù)據(jù)te_courses.txt到te_courses表中。第1步，創(chuàng)建表te_courses。CREATETABLEstudentdb.te_courses(tenameSTRING,te_coursesSTRING,te_classSTRING)ROWFORMATDELIMITEDFIELDSTERMINATEDBY'\t'LINESTERMINATEDBY'\n'STOREDASTEXTFILETBLPROPERTIES("skip.header.line.count"="1");任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.7JOIN語(yǔ)句第2步，導(dǎo)入數(shù)據(jù)te_courses.txt到te_courses表。LOADDATALOCALINPATH'/home/hadoop/hivedata/student/te_courses.txt'OVERWRITEINTOTABLEstudentdb.te_courses;第3步，查詢(xún)表te_courses中數(shù)據(jù)。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.7JOIN語(yǔ)句【例5-35】查詢(xún)student表和te_courses表，找到每位學(xué)生的任課教師姓名。SELECTt1.class,t2.te_class,t1.stname,t2.tenameFROMstudentdb.studentt1INNERJOINstudentdb.te_coursest2ONt1.class=t2.te_class;上述命令中，通過(guò)關(guān)聯(lián)表student中的class列和te_courses表中的te_class列進(jìn)行內(nèi)連接。從圖5-39可以看出，內(nèi)連接會(huì)根據(jù)左表和右表的關(guān)聯(lián)列，只返回相匹配的數(shù)據(jù)。因此，再查詢(xún)結(jié)果集中不會(huì)出現(xiàn)te_class為BigData1803和Network1803的數(shù)據(jù)。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.7JOIN語(yǔ)句2、左外連接左外連接，即LEFTOUTERJOIN，會(huì)返回左邊表所有符合WHERE語(yǔ)句的記錄。右表中匹配不上的字段值用NULL代替?！纠?-36】查詢(xún)student表和te_courses表，左外連接找到每位學(xué)生的任課教師姓名。SELECTt1.class,t2.te_class,t1.stname,t2.tenameFROMstudentdb.studentt1LEFTOUTERJOINstudentdb.te_coursest2ONt1.class=t2.te_class;從圖5-40可以看出，左外連接返回左表student的全部數(shù)據(jù)，右表te_courses通過(guò)關(guān)聯(lián)匹配不到的數(shù)據(jù)以NULL代替。如在右表te_courses中沒(méi)有關(guān)于班級(jí)Network1802的任課教師信息，因此左表student中來(lái)自Network1802的Zhaolin和Yuanwei兩位學(xué)生所在行的te_class、tename列為NULL。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.7JOIN語(yǔ)句3、右外連接右外連接，即RIGHTOUTERJOIN，會(huì)返回右表所有符合WHERE語(yǔ)句的記錄。左表中匹配不上的字段值用NULL代替?！纠?-37】查詢(xún)student表和te_courses表，右外連接找到每位學(xué)生的任課教師姓名。SELECTt1.class,t2.te_class,t1.stname,t2.tenameFROMstudentdb.studentt1RIGHTOUTERJOINstudentdb.te_coursest2ONt1.class=t2.te_class;從圖5-41可以看出，右外連接返回右表的全部數(shù)據(jù)，左表student通過(guò)關(guān)聯(lián)匹配不到的數(shù)據(jù)以NULL代替。如在右表te_courses中有班級(jí)BigData1803和Network1803的任課教師信息，而左表student中沒(méi)有班級(jí)BigData1803和Network1803的學(xué)生信息，因此返回結(jié)果中班級(jí)BigData1803和Network1803的class和stname列為NULL。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.7JOIN語(yǔ)句4、完全外連接完全外連接，即FULLOUTERJOIN，會(huì)返回左表和右表的全部數(shù)據(jù)，兩個(gè)表中通過(guò)關(guān)聯(lián)列匹配不上的字段值用NULL代替?！纠?-38】查詢(xún)student表和te_courses表，完全外連接找到每位學(xué)生的任課教師姓名。SELECTt1.class,t2.te_class,t1.stname,t2.tenameFROMstudentdb.studentt1FULLOUTERJOINstudentdb.te_coursest2ONt1.class=t2.te_class;從圖5-42可以看出，完全外連接返回左表student和右表te_courses的全部數(shù)據(jù)，兩個(gè)表中通過(guò)關(guān)聯(lián)匹配不到的字段值以NULL代替。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.7JOIN語(yǔ)句5、笛卡爾積笛卡爾積，即CROSSJOIN，左表的每一行都會(huì)與右表的所有行進(jìn)行交叉連接。左表的行數(shù)乘以右表的行數(shù)等于笛卡爾結(jié)果集的行數(shù)。注意，首先，執(zhí)行笛卡爾積連接時(shí)不需要指定關(guān)聯(lián)列。然后，Hive為保障集群的穩(wěn)定性，避免join操作導(dǎo)致網(wǎng)絡(luò)癱瘓，Hive默認(rèn)不支持笛卡爾積連接，因此需要先執(zhí)行“setduct=false;”命令開(kāi)啟笛卡爾積連接功能。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.7JOIN語(yǔ)句【例5-39】笛卡爾積連接方式查詢(xún)student表和te_courses表。SELECTt1.class,t2.te_class,t1.stname,t2.tenameFROMstudentdb.studentt1CROSSJOINstudentdb.te_coursest2;任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.7JOIN語(yǔ)句任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)【例5-39】笛卡爾積連接方式查詢(xún)student表和te_courses表。5.1.8排序語(yǔ)句1、ORDERBYORDERBY語(yǔ)句會(huì)對(duì)查詢(xún)結(jié)果集執(zhí)行全局排序，即所有數(shù)據(jù)都通過(guò)一個(gè)Reducer進(jìn)行處理的過(guò)程。對(duì)于大數(shù)據(jù)集，建議ORDERBY和LIMIT子句一同使用，以控制排序后的輸出顯示條數(shù)。因?yàn)槿峙判蛑挥幸粋€(gè)Reducer處理最終的排序輸出，當(dāng)輸出結(jié)果集行數(shù)過(guò)大時(shí)，要消耗漫長(zhǎng)的時(shí)間來(lái)執(zhí)行。在ORDERBY子句中可以指定任意字段進(jìn)行排序，字段后添加ASC關(guān)鍵字表示按升序排序，也是默認(rèn)值，或添加DESC關(guān)鍵字，表示按降序排序。ORDERBY子句寫(xiě)在SELECT語(yǔ)句的結(jié)尾處。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.8排序語(yǔ)句【例5-40】查詢(xún)studentdb數(shù)據(jù)庫(kù)中student表的學(xué)生姓名、Python成績(jī)及來(lái)自省份信息，并按Python成績(jī)升序排列。SELECTstname,course_scores['Python’]ASPython,vinceFROMstudentORDERBYPython;任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.8排序語(yǔ)句【例5-41】查詢(xún)studentdb數(shù)據(jù)庫(kù)中student表的學(xué)生姓名和課程平均成績(jī)，按課程平均成績(jī)進(jìn)行降序排列。SELECTstname,(course_scores['Hive']+course_scores['Python'])/2ASavgFROMstudentORDERBYavgDESC;【例5-42】查詢(xún)studentdb數(shù)據(jù)庫(kù)中student表的學(xué)生姓名、班級(jí)和Hive課程成績(jī)，先按班級(jí)升序排序，再按Hive課程成績(jī)降序排列。SELECTstname,class,course_scores['Hive']ASHiveFROMstudentORDERBYclass,HiveDESC;任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.8排序語(yǔ)句2、SORTBYSORTBY用于對(duì)查詢(xún)結(jié)果做局部排序，其只會(huì)在每個(gè)Reducer中對(duì)數(shù)據(jù)進(jìn)行排序，也就是執(zhí)行一個(gè)局部排序過(guò)程。這可以保證每個(gè)Reducer的輸出數(shù)據(jù)都是有序的，即局部有序，但是不能保證所有數(shù)據(jù)都是有序的，除非Reducer的個(gè)數(shù)為1。SORTBY子句中，與ORDERBY子句相同，在字段后添加ASC關(guān)鍵字表示按升序排序，也是默認(rèn)值，或添加DESC關(guān)鍵字，表示按降序排序。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.8排序語(yǔ)句【例5-43】查詢(xún)studentdb數(shù)據(jù)庫(kù)中student表的學(xué)生姓名、Python成績(jī)、來(lái)自省份，使用SORTBY按Python成績(jī)升序排列。SELECTstname,course_scores['Python’]ASPython,vinceFROMstudentSORTBYPython;由于默認(rèn)情況下Reduce的個(gè)數(shù)為1，局部排序也就是全局排序，所以此題與例5-40的查詢(xún)結(jié)果一樣。但是當(dāng)使用的Reduce的個(gè)數(shù)大于1時(shí)，輸出結(jié)果的排序就大不一樣了。Mapreduce參數(shù)中的mapreduce.job.reduces用于設(shè)置默認(rèn)啟動(dòng)的Reduce數(shù)，其默認(rèn)值為1，可以手動(dòng)修改Reduce的個(gè)數(shù)。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.8排序語(yǔ)句【例5-44】將mapreduce.job.reduces參數(shù)設(shè)置為3后，查詢(xún)studentdb數(shù)據(jù)庫(kù)中student表的學(xué)生姓名、Python成績(jī)、來(lái)自省份，使用SORTBY按Python成績(jī)升序排列。從以上輸出結(jié)果發(fā)現(xiàn)，由于Reducer的個(gè)數(shù)設(shè)置為3，因此查詢(xún)顯示的數(shù)據(jù)排序在全局上是無(wú)序的。為了能看到每個(gè)Reducer的局部排序結(jié)果，我們將以上每個(gè)Reducer排序結(jié)果輸出保存到本地的/home/hadoop/dataoutput/e.g.5-44中。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.8排序語(yǔ)句第1步，執(zhí)行以下代碼。INSERTOVERWRITELOCALDIRECTORY'/home/hadoop/dataoutput/e.g.5-44'ROWFORMATDELIMITEDFIELDSTERMINATEDBY","SELECTstname,course_scores['Python']ASPython,vinceFROMstudentSORTBYPython;第2步，查看/home/hadoop/dataoutput/e.g.5-44目錄下文件數(shù)據(jù)排序情況。從上述輸出結(jié)果可以看到，SORTBY對(duì)三個(gè)Reducer進(jìn)行了單獨(dú)排序，三個(gè)Reducer的輸出結(jié)果按照Python成績(jī)升序排列。但是排序結(jié)果中每個(gè)省份學(xué)生的Python課程是無(wú)序的，如果需要對(duì)各個(gè)省份的學(xué)生Python課程分?jǐn)?shù)進(jìn)行排序，則需要使用DISTRIBUTEBY子句。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.8排序語(yǔ)句【例5-45】將mapreduce.job.reduces參數(shù)設(shè)置為3后，查詢(xún)studentdb數(shù)據(jù)庫(kù)中student表的學(xué)生姓名、Python成績(jī)及所來(lái)自的省份，使用distributeby按省份劃分，使用sortby按Python成績(jī)升序排列結(jié)果，并將輸出結(jié)果保存到本地目錄/home/hadoop/dataoutput/e.g.5-45中。第1步，執(zhí)行以下代碼。INSERTOVERWRITELOCALDIRECTORY'/home/hadoop/dataoutput/e.g.5-45'ROWFORMATDELIMITEDFIELDSTERMINATEDBY","SELECTstname,course_scores['Python']ASPython,vinceFROMstudentDISTRIBUTEBYprovinceSORTBYPython;任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.8排序語(yǔ)句第2步，查看/home/hadoop/dataoutput/e.g.5-45目錄下文件數(shù)據(jù)排序情況。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.8排序語(yǔ)句從上述輸出結(jié)果可以看到，DISTRIBUTEBY子句和SORTBY子句成功對(duì)不同省份的學(xué)生Python成績(jī)進(jìn)行了排序，其中Guangdong省最高分為95分，Hunan省最高分為85分。以上在SORTBY子句中添加DISTRIBUTEBY子句是根據(jù)分區(qū)字段province將相同省份的數(shù)據(jù)分發(fā)到同一個(gè)Reducer中，由于數(shù)據(jù)中只有Guangdong和Hunan兩個(gè)省份，因而輸出結(jié)果中文件000001_0為空。DISTRIBUTEBY語(yǔ)句通常與sortby語(yǔ)句結(jié)合使用。DISTRIBUTEBY與GROUPBY類(lèi)似，控制著Reducer如何接受一行行數(shù)據(jù)，而SORTBY則控制著Reducer內(nèi)數(shù)據(jù)如何進(jìn)行排序。注意，Hive要求DISTRIBUTEBY語(yǔ)句要寫(xiě)在SORTBY語(yǔ)句之前。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.8排序語(yǔ)句3、ClusterBy當(dāng)DISTRIBUTEBY和SORTSBY兩個(gè)子句中涉及到的列完全一致，而且是采用升序排序方式，則可以使用CLUSTERBY代替。默認(rèn)情況下，CLUSTERBY只支持升序排序，不支持降序排序。例如，以下命令1與命令2等價(jià)。命令1：命令2：SELECTstname,vince

SELECTstname,vinceFROMstudent

FROMstudentDISTRIBUTEBYprovince

CLUSTERBYprovince;SORTBYprovince;任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)在第3章任務(wù)1中已經(jīng)在dwd_site倉(cāng)庫(kù)中創(chuàng)建好了基站指標(biāo)匯總表dwd_bts_factor、基站基礎(chǔ)屬性匯總表dwd_bts_info、行業(yè)信息分類(lèi)表dwd_industry_category共3個(gè)表。本任務(wù)將根據(jù)智能選址的實(shí)際業(yè)務(wù)需要進(jìn)行數(shù)據(jù)分析后將獲得的數(shù)據(jù)載入至此三張表中，為下個(gè)一個(gè)任務(wù)做好DWD層的數(shù)據(jù)準(zhǔn)備。任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)5.1.9獲取dwd_site的行業(yè)信息分類(lèi)表數(shù)據(jù)任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)ods_site層的ods_code_industry_category表存儲(chǔ)著行業(yè)大類(lèi)和行業(yè)小類(lèi)信息，ods_industry_info表存儲(chǔ)著各商業(yè)的詳細(xì)信息，包括商業(yè)所屬的行業(yè)小類(lèi)、商業(yè)名稱(chēng)、地址、經(jīng)度和緯度。根據(jù)ods_industry_info表的行業(yè)編碼字段與ods_code_industry_category表的行業(yè)小類(lèi)編碼字段關(guān)聯(lián)做LEFTJOIN操作，獲取每個(gè)商業(yè)的行業(yè)大類(lèi)編碼及名稱(chēng)，匯總生成dwd_site層的dwd_industry_category行業(yè)信息分類(lèi)表數(shù)據(jù)。5.1.9獲取dwd_site的行業(yè)信息分類(lèi)表數(shù)據(jù)任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)執(zhí)行以下代碼獲取數(shù)據(jù)并將數(shù)據(jù)載入dwd_industry_category表：INSERTOVERWRITETABLEdwd_site.dwd_industry_categorySELECTb.category_code,b.category_name,b.sub_category_code,b.sub_category_name, ,a.address,a.longitude,a.latitudeFROMods_site.ods_industry_infoaLEFTJOINods_site.ods_code_industry_categorybONa.sub_category_code=b.sub_category_code;驗(yàn)證并查看行業(yè)信息分類(lèi)表dwd_industry_category中的數(shù)據(jù)：5.1.10獲取dwd_site的基站基礎(chǔ)屬性匯總表數(shù)據(jù)任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)ods_site層的ods_bts_info表存儲(chǔ)著每個(gè)基站的詳細(xì)信息，包括基站歸屬地市編碼、歸屬地市、歸屬區(qū)縣編碼、歸屬區(qū)縣、基站編碼、基站名稱(chēng)、位置區(qū)編碼、位置區(qū)編碼4G、經(jīng)度、緯度、基站類(lèi)型編碼、基站類(lèi)型名稱(chēng)、高鐵標(biāo)識(shí)、公園標(biāo)識(shí)、區(qū)域類(lèi)型等信息，ods_bts_score表存儲(chǔ)著各基站的分?jǐn)?shù)。根據(jù)ods_bts_info表的基站編碼字段與ods_bts_score表的基站編碼字段關(guān)聯(lián)做JOIN操作，獲取每個(gè)基站的詳細(xì)信息及基站分?jǐn)?shù)，匯總生成dwd_site層的dwd_bts_info基站基礎(chǔ)屬性匯總表數(shù)據(jù)。執(zhí)行以下代碼獲取數(shù)據(jù)并將數(shù)據(jù)載入dwd_bts_info表：5.1.10獲取dwd_site的基站基礎(chǔ)屬性匯總表數(shù)據(jù)任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)INSERTOVERWRITETABLEdwd_site.dwd_bts_infoSELECTa.city_code,a.city_name, a.district_code,a.district_name,a.bts_id,a.enodebid,a.longitude,a.latitude,a.lac,a.tac,a.area_type, a.bts_type,a.bts_type_name,b.bts_score,a.is_highrail,a.is_parkFROMods_site.ods_bts_infoaJOINods_site.ods_bts_scorebONa.bts_id=b.bts_id;驗(yàn)證并查看基站基礎(chǔ)屬性匯總表dwd_bts_info中的數(shù)據(jù)：5.1.11獲取dwd_site的基站指標(biāo)匯總表數(shù)據(jù)任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)1、創(chuàng)建mid_bts_industry基站行業(yè)中間表ods_site層的ods_bts_industry表存儲(chǔ)著每個(gè)基站所對(duì)應(yīng)的行業(yè)數(shù)量信息，表中包含基站編碼、行業(yè)分類(lèi)編碼、行業(yè)數(shù)量三個(gè)字段。其中行業(yè)分類(lèi)編碼字段的詳細(xì)信息如下表：字段值字段值詳細(xì)信息024商業(yè)消費(fèi)025餐飲026銀行027酒店賓館028生活便捷030大學(xué)/政府044五大銀行數(shù)據(jù)量045其他銀行數(shù)據(jù)量5.1.11獲取dwd_site的基站指標(biāo)匯總表數(shù)據(jù)任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)ods_bts_industry表中數(shù)據(jù)按照基站編碼字段分類(lèi)匯總，統(tǒng)計(jì)出各基站對(duì)應(yīng)的行業(yè)數(shù)量總和，并將數(shù)據(jù)存入臨時(shí)倉(cāng)庫(kù)site_temp的基站對(duì)應(yīng)行業(yè)信息匯總表mid_bts_industry中。mid_bts_industry數(shù)據(jù)表字段名定義如下列表所示：字段名稱(chēng)字段類(lèi)型描述bts_idString基站編碼commercial_numint商業(yè)消費(fèi)catering_numint餐飲bank_numint銀行hotel_numint酒店賓館life_convenientInt生活便捷traffic_convenient_numint交通便捷gov_numint大學(xué)/政府five_bank_numint五大銀行數(shù)據(jù)量other_bank_numint其他銀行數(shù)據(jù)量5.1.11獲取dwd_site的基站指標(biāo)匯總表數(shù)據(jù)任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)創(chuàng)建臨時(shí)倉(cāng)庫(kù)temp_site：CREATEDATABASEtemp_site;創(chuàng)建基站行業(yè)中間表mid_bts_industry：CREATETABLEIFNOTEXISTStemp_site.mid_bts_industry( bts_idstringCOMMENT'基站編碼',commercial_numintCOMMENT'商業(yè)消費(fèi)',catering_numintCOMMENT'餐飲’, bank_numintCOMMENT'銀行',hotel_numintCOMMENT'酒店賓館',life_convenient_numintCOMMENT'生活便捷’, traffic_convenient_numintCOMMENT'交通便捷’, gov_numintCOMMENT'大學(xué)/政府’, five_bank_numintCOMMENT'五大銀行數(shù)據(jù)量',other_bank_numintCOMMENT'其他銀行數(shù)據(jù)量')COMMENT'基站行業(yè)中間表'ROWFORMATDELIMITEDFIELDSTERMINATEDBY'\u0001'NULLDEFINEDAS''STOREDASRCFILE;5.1.11獲取dwd_site的基站指標(biāo)匯總表數(shù)據(jù)任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)執(zhí)行以下代碼獲取數(shù)據(jù)并將數(shù)據(jù)載入基站行業(yè)中間表mid_bts_industry表：INSERTOVERWRITETABLEtemp_site.mid_bts_industrySELECTa.bts_id,sum(CASEWHENa.category_code='024'THENnvl(a.num,0)ELSE0END)AScommercial_num,sum(CASEWHENa.category_code='025'THENnvl(a.num,0)ELSE0END)catering_num,sum(CASEWHENa.category_code='026'THENnvl(a.num,0)ELSE0END)bank_num,sum(CASEWHENa.category_code='027'THENnvl(a.num,0)ELSE0END)hotel_num,sum(CASEWHENa.category_code='028'THENnvl(a.num,0)ELSE0END)life_convenient_num,sum(CASEWHENa.category_code='029'THENnvl(a.num,0)ELSE0END)traffic_convenient_num,sum(CASEWHENa.category_code='030'THENnvl(a.num,0)ELSE0END)gov_num,sum(CASEWHENa.category_code='044'THENnvl(a.num,0)ELSE0END)five_bank_num,sum(CASEWHENa.category_code='045'THENnvl(a.num,0)ELSE0END)other_bank_numFROMods_site.ods_bts_industryaGROUPBYa.bts_id;5.1.11獲取dwd_site的基站指標(biāo)匯總表數(shù)據(jù)任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)上述代碼中的nvl函數(shù)用于判斷是否為空值，例如：nvl(expr1,expr2)，當(dāng)expr1為NULL，返回值為expr2，否則返回expr1。nvl函數(shù)適用于數(shù)字型、字符型和日期型，且expr1和expr2的數(shù)據(jù)類(lèi)型必須為同類(lèi)型。驗(yàn)證并查看基站行業(yè)中間表mid_bts_industry中的數(shù)據(jù)：5.1.11獲取dwd_site的基站指標(biāo)匯總表數(shù)據(jù)任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)2、獲取dwd_bts_factor基站指標(biāo)匯總表數(shù)據(jù)依次匯總銀行App明細(xì)ods_bank_app表、基站常住人口ods_resident_pop表、基站流動(dòng)人口ods_floating_pop、消費(fèi)信息ods_consumption表及基站行業(yè)中間mid_bts_industry表，得到基站指標(biāo)匯總表。由于前四個(gè)表為按月分區(qū)表，在本實(shí)驗(yàn)中只匯總201805即2018年5月份的數(shù)據(jù)，執(zhí)行代碼如下所示： INSERTOVERWRITETABLEdwd_site.dwd_bts_factorSELECTa.bts_id,a.floating_app_num,a.resident_app_num,b.resident_num,c.floating_num,d.high_num,d.mid_num,d.low_num,e.Commercial_num,e.catering_num,e.bank_num,e.hotel_num,e.life_convenient_num,e.traffic_convenient_num,_num,e.five_bank_num,e.other_bank_numFROM(SELECT*FROMods_site.ods_bank_appWHEREmonth='201805')aLEFTJOIN(SELECT*FROMods_site.ods_resident_popWHEREmonth='201805')bONa.bts_id=b.bts_idLEFTJOIN(SELECT*FROMods_site.ods_floating_popWHEREmonth='201805')cONa.bts_id=c.bts_idLEFTJOIN(SELECT*FROMods_site.ods_consumptionWHEREmonth='201805')dONa.bts_id=d.bts_idLEFTJOINtemp_site.mid_bts_industryeONa.bts_id=e.bts_id;任務(wù)小結(jié)任務(wù)1分析倉(cāng)庫(kù)數(shù)據(jù)首先，通過(guò)任務(wù)我們已經(jīng)掌握到所有的數(shù)據(jù)分析技能，并且開(kāi)始應(yīng)用分析技獲取到了智能選址中的DWD層數(shù)據(jù)；然后，在獲取DWD層數(shù)據(jù)時(shí)，我們遇到了相對(duì)復(fù)雜的寬表和多表聯(lián)合查詢(xún)，在難度提升時(shí)學(xué)習(xí)者需要調(diào)整好心態(tài)，分析技術(shù)本身不難，但結(jié)合業(yè)務(wù)需求處理問(wèn)題時(shí)必然會(huì)復(fù)雜，著力理解好數(shù)據(jù)本身的業(yè)務(wù)信息是要點(diǎn)；最后，通過(guò)本模塊的“實(shí)踐創(chuàng)新”深入研讀“大數(shù)據(jù)智慧旅游”數(shù)據(jù)處理流程及邏輯模型設(shè)計(jì)文檔，根據(jù)已有的業(yè)務(wù)數(shù)據(jù)完成分析任務(wù)，在獨(dú)立實(shí)踐探索中創(chuàng)新并磨練數(shù)據(jù)分析的技能。本任務(wù)的業(yè)務(wù)目標(biāo)是以DWD層數(shù)據(jù)為基礎(chǔ)，在DM層數(shù)據(jù)庫(kù)首先統(tǒng)計(jì)出已建址銀行的經(jīng)緯度信息，然后以已建址銀行經(jīng)緯度坐標(biāo)為圓心，1200米為覆蓋半徑，統(tǒng)計(jì)此覆蓋范圍內(nèi)的基站信息，最后根據(jù)統(tǒng)計(jì)的覆蓋范圍內(nèi)的基站信息，實(shí)現(xiàn)計(jì)算出已建址銀行的評(píng)價(jià)要素值的任務(wù)目標(biāo)。數(shù)據(jù)處理流程如下圖所示：任務(wù)2計(jì)算倉(cāng)庫(kù)數(shù)據(jù)任務(wù)2計(jì)算倉(cāng)庫(kù)數(shù)據(jù)為了分析智能選址中已建址銀行要素，在此任務(wù)實(shí)施中將依次創(chuàng)建用于存儲(chǔ)分析結(jié)果數(shù)據(jù)的DM層數(shù)據(jù)庫(kù)，統(tǒng)計(jì)出已建址銀行的經(jīng)緯度信息，統(tǒng)計(jì)出已建址銀行覆蓋基站信息，最后計(jì)算出已建址銀行評(píng)價(jià)要素值。5.2.1創(chuàng)建“大數(shù)據(jù)商業(yè)智能選址”DM層數(shù)據(jù)庫(kù)任務(wù)2計(jì)算倉(cāng)庫(kù)數(shù)據(jù)根據(jù)模塊二的圖2-5“大數(shù)據(jù)商業(yè)智能選址”數(shù)據(jù)倉(cāng)庫(kù)模型構(gòu)建流程圖，在數(shù)據(jù)倉(cāng)庫(kù)Hiv中創(chuàng)建DM庫(kù)，經(jīng)過(guò)分析計(jì)算已建址要素、分析清除不可建址區(qū)域、分析計(jì)算選址點(diǎn)、算并評(píng)分選址點(diǎn)四大任務(wù)，將最終在DM中的獲得選址結(jié)果表。創(chuàng)建“大數(shù)據(jù)商業(yè)智能選址”DM層數(shù)據(jù)庫(kù)：CREATEDATABASEdm_siteCOMMENT'Businessintelligencesiteselectionproject'LOCATION'/project/warehouse/intelligent_site/dm_site.db'WITHDBPROPERTIES('ProjectLeader'='Ms.Wang','Editor'='Mr.Liu','Date'='2022-02-10');5.2.2統(tǒng)計(jì)已建址銀行經(jīng)緯度信息任務(wù)2計(jì)算倉(cāng)庫(kù)數(shù)據(jù)從dwd_site倉(cāng)庫(kù)的行業(yè)信息分類(lèi)表dwd_industry_category中，根據(jù)名稱(chēng)name字段找到已建工商銀行的經(jīng)度和緯度位置信息，不包含自助ATM機(jī)，并將數(shù)據(jù)寫(xiě)入到dm_site庫(kù)中的已建址銀行經(jīng)緯度表dm_existing_bank，該數(shù)據(jù)表字段名定義如下列表所示：字段名稱(chēng)字段類(lèi)型描述bank_nameSTRING銀行名稱(chēng)longitudeDECIMAL(10,6)經(jīng)度latitudeDECIMAL(10,6)緯度5.2.2統(tǒng)計(jì)已建址銀行經(jīng)緯度信息任務(wù)2計(jì)算倉(cāng)庫(kù)數(shù)據(jù)創(chuàng)建已建址銀行經(jīng)緯度信息表dm_existing_bank，代碼如下所示：CREATETABLEdm_site.dm_existing_bank( bank_namestringCOMMENT'銀行名稱(chēng)’, longitudeDECIMAL(10,6)COMMENT'經(jīng)度’, latitudeDECIMAL(10,6)COMMENT'緯度')COMMENT'已建址銀行表'ROWFORMATDELIMITEDFIELDSTERMINATEDBY'\u0001'NULLDEFINEDAS''STOREDASRCFILE;5.2.2統(tǒng)計(jì)已建址銀行經(jīng)緯度信息任務(wù)2計(jì)算倉(cāng)庫(kù)數(shù)據(jù)查詢(xún)dwd_site層dwd_industry_category表中工商銀行建址信息的名稱(chēng)、經(jīng)度、緯度三個(gè)字段數(shù)據(jù)，導(dǎo)入到已建址銀行經(jīng)緯度信息表dm_existing_bank中，代碼如下：INSERTINTOdm_site.dm_existing_bankSELECTname,longitude,latitudeFROMdwd_site.dwd_industry_cate