2023學(xué)年完整公開(kāi)課版HiveQL-數(shù)據(jù)操作

HiveQL—數(shù)據(jù)操作創(chuàng)建數(shù)據(jù)集創(chuàng)建表：createtablewordcount(wordstring,freqint)rowformatdelimitedfieldsterminatedby'\t'storedastextfile;生成數(shù)據(jù):在hdfs上創(chuàng)建input目錄，將數(shù)據(jù)文件上傳到input目錄下，執(zhí)行程序，生成wordcount數(shù)據(jù)hadoopjar$HADOOP_HOME/share/hadoop/mapreduce/hadoop-mapreduce-examples-2.7.2.jarwordcount/input/yarn*/output裝入數(shù)據(jù)：loaddatainpath"/output/part-r-00000"intotablewordcount;HiveQL查詢SELECTnameFROMsalesWHEREamount>8ANDregion="shanghai";SELECTcount(*)counter,regionFROMsalesGROUPBYregionHAVINGcounter>2;select*fromwordcountlimit10;select*fromwordcountwherefreq>10orderbyfreqdesclimit10;HiveQL查詢數(shù)組列：selectname,subordinatesfromemployees;selectname,subordinates[0]fromemployees;map

selectname,deductions["grtaxes"]fromemployees;struct

selectname,address.cityfromemployees;使用列值進(jìn)行計(jì)算selectsalary,deductions["grtaxes"],salary*(1-deductions["grtaxes"])fromemployees;嵌套select語(yǔ)句select,e.salary,e.salary_shfrom(selectname,salary,deductions["grtaxes"]asgr_taxes,round(salary*(1-deductions["grtaxes"]))assalary_shfromemployees)ewheree.salary_sh>2000;通過(guò)查詢語(yǔ)句向表中插入數(shù)據(jù)insert允許用戶通過(guò)查詢語(yǔ)句向目標(biāo)表中插入數(shù)據(jù)insertoverwrite[into]tablestock_tpartition(ymd='20170929')selectsymbol,price_open,price_high,price_low,price_close,volumefromstockswhereymd='20170929';通過(guò)查詢語(yǔ)句向表中插入數(shù)據(jù)fromstocksinsertoverwritetablestock_tpartition(ymd='20170928')selectsymbol,price_open,price_high,price_low,price_close,volumewhereymd='20170928'insertoverwritetablestock_tpartition(ymd='20170929')selectsymbol,price_open,price_high,price_low,price_close,volumewhereymd='20170929'insertoverwritetablestock_tpartition(ymd='20170930')selectsymbol,price_open,price_high,price_low,price_close,volumewhereymd='20170930';動(dòng)態(tài)分區(qū)插入Hive提供了一個(gè)動(dòng)態(tài)分區(qū)功能，可以基于查詢參數(shù)推斷出需要?jiǎng)?chuàng)建的分區(qū)名稱insertoverwritetablestock_tpartition(ymd)selectsymbol,price_open,price_high,price_low,price_close,volume,ymdfromstocks;動(dòng)態(tài)分區(qū)功能默認(rèn)情況下是沒(méi)有開(kāi)啟的，需要將hive.exec.dynamic.partition.mode設(shè)置為nonstrict導(dǎo)出數(shù)據(jù)從表中導(dǎo)出數(shù)據(jù)如果文件恰好是用戶需要的格式，那么只需要簡(jiǎn)單拷貝文件夾或者文件就可以了。

hadoopfs-cpsource_pathtarget_path否則，用戶可以使用insert…directory…insertoverwrite[local]directory'/home/hadoop/emp'selectname,salary,addressfromemployees;關(guān)系運(yùn)算空值判斷selectname,salaryfromemployeeswheresalaryisnull;LIKE比較selectname,salaryfromemployeeswherenamelike'l%';浮點(diǎn)數(shù)比較（可選）浮點(diǎn)數(shù)比較的一個(gè)常見(jiàn)問(wèn)題是出現(xiàn)在不同類(lèi)型做比較（float和double比較）selectname,salary,deductions["grtaxes"]fromemployeeswheredeductions[“grtaxes”]>0.2;對(duì)指定的值進(jìn)行顯示的類(lèi)型轉(zhuǎn)換selectname,salary,deductions["grtaxes"]fromemployeeswheredeductions["grtaxes"]>cast(0.2asfloat);數(shù)學(xué)函數(shù)取整函數(shù)selectround(3.1415)fromemployees;指定精度取整函數(shù)selectround(3.1415,2)fromemployees;向下取整函數(shù)selectfloor(3.1415)fromemployees;向上取整函數(shù)selectceil(3.1415)fromemployees;取隨機(jī)數(shù)函數(shù)selectrand()fromemployees;

字符串函數(shù)

字符串長(zhǎng)度函數(shù)selectlength(name)fromemployees;字符串連接函數(shù)selectconcat(name,'@')fromemployees;字符串截取函數(shù)selectsubstr(name,2)fromemployees;selectsubstr(name,1,2)fromemployees;字符串轉(zhuǎn)大寫(xiě)函數(shù)selectupper(name)fromemployees;去空格函數(shù)

selecttrim(name)fromemployees;

聚合函數(shù)個(gè)數(shù)統(tǒng)計(jì)函數(shù)selectcount(*)fromemployees;總和統(tǒng)計(jì)函數(shù)selectsum(salary)fromemployees;平均值統(tǒng)計(jì)函數(shù)

selectavg(salary)fromemployees;最小最大值selectmin(salary),max(salary)fromemployees;數(shù)據(jù)表的join—innerjoinselecta.ymd,a.symbol,a.price_close,b.symbol,b.price_closefromstocksajoinstocksbona.ymd=b.ymdwherea.symbol='600718'andb.symbol='002230';ON子句指定了兩個(gè)表之間數(shù)據(jù)連接的條件數(shù)據(jù)表的join—innerjoin多表連接selecta.ymd,a.symbol,a.price_close,b.symbol,b.price_close,

c.symbol,c.price_close

fromstocksajoinstocksbona.ymd=b.ymd

joinstockscona.ymd=c.ymd

wherea.symbol='600718'andb.symbol='002230'andc.symbol='000977';數(shù)據(jù)表的join—innerjoin練習(xí)產(chǎn)生數(shù)據(jù)集countword,將countword和之前產(chǎn)生的另外一個(gè)wordcount數(shù)據(jù)集進(jìn)行聯(lián)合查詢創(chuàng)建一個(gè)中間數(shù)據(jù)表merged，用于存儲(chǔ)查詢結(jié)果將兩個(gè)數(shù)據(jù)集的交集保存在數(shù)據(jù)表merged中數(shù)據(jù)表的join—leftouterjoin左外連接通過(guò)關(guān)鍵字leftouter進(jìn)行標(biāo)識(shí)selects.ymd,s.symbol,s.price_close,d.dividendfromstockssleftouterjoinstocks_divdons.ymd=d.ymdands.symbol=d.symbol;數(shù)據(jù)表的join—Rightouterjoin右外連接將返回右邊表所有符合where語(yǔ)句的記錄，左邊表中匹配不上的字段值用null代替。selects.ymd,s.symbol,s.price_close,d.dividendfromstocks_divdrightouterjoinstockssons.ymd=d.ymdands.symbol=d.symbol;數(shù)據(jù)表的join—Fullouterjoin完全外連接會(huì)返回所有表中符合where語(yǔ)句條件的所有記錄，如果任一表的指定字段沒(méi)有符合條件的值，那么就會(huì)使用null值代替。selects.ymd,s.symbol,s.price_close,d.dividendfromstocks_divdfullouterjoinstockssons.ymd=d.ymdands.symbol=d.symbol;數(shù)據(jù)表的join—leftsemijoinleftsemijoin（左半開(kāi)連接）類(lèi)似exists。即查找a表中的數(shù)據(jù)，是否在b表中存在，找出存在的數(shù)據(jù)。selects.ymd,s.symbol,s.price_closefromstockssleftsemijoinstocks_divdons.ymd=d.ymdands.symbol=d.symbol;笛卡爾積join笛卡爾積是一種連接，表示左邊表的行數(shù)乘以右邊表的行數(shù)等于笛卡爾結(jié)果集的大小。select*fromstocksjoinstocks_div;Orderby和SortbyOrderby會(huì)對(duì)查詢結(jié)果集執(zhí)行一個(gè)全局排序，所有的數(shù)據(jù)都通過(guò)一個(gè)reducer進(jìn)行處理。Sortby會(huì)在每個(gè)reducer中對(duì)數(shù)據(jù)進(jìn)行排序，保證每個(gè)reducer的輸出數(shù)據(jù)都是有序的，這樣可以提高后面進(jìn)行的全局排序的效率。select*fromwordcountwherefreq>10orderbyfreqdesclimit10;select*fromwordcountwherefreq>10sortbyfreqdesclimit10;DistributebyDistributeby控制map的輸出在reducer中的劃分使用Distributeby來(lái)保證具有相同值的數(shù)據(jù)被分發(fā)到同一個(gè)reducer中進(jìn)行處理，然后使用sort

by對(duì)數(shù)據(jù)進(jìn)行排序。selectymd,symbol,price_closefromstocksdistributebysymbolsortbysymbol,ymd;抽樣查詢對(duì)于非常大的數(shù)據(jù)集，用戶有時(shí)需要使用的是一個(gè)具有代表性的查詢