2023学年完整公开课版HiveQL-数据操作

HiveQL—数据操作创建数据集创建表：createtablewordcount(wordstring,freqint)rowformatdelimitedfieldsterminatedby'\t'storedastextfile;生成数据:在hdfs上创建input目录，将数据文件上传到input目录下，执行程序，生成wordcount数据hadoopjar$HADOOP_HOME/share/hadoop/mapreduce/hadoop-mapreduce-examples-2.7.2.jarwordcount/input/yarn*/output装入数据：loaddatainpath"/output/part-r-00000"intotablewordcount;HiveQL查询SELECTnameFROMsalesWHEREamount>8ANDregion="shanghai";SELECTcount(*)counter,regionFROMsalesGROUPBYregionHAVINGcounter>2;select*fromwordcountlimit10;select*fromwordcountwherefreq>10orderbyfreqdesclimit10;HiveQL查询数组列：selectname,subordinatesfromemployees;selectname,subordinates[0]fromemployees;map

selectname,deductions["grtaxes"]fromemployees;struct

selectname,address.cityfromemployees;使用列值进行计算selectsalary,deductions["grtaxes"],salary*(1-deductions["grtaxes"])fromemployees;嵌套select语句select,e.salary,e.salary_shfrom(selectname,salary,deductions["grtaxes"]asgr_taxes,round(salary*(1-deductions["grtaxes"]))assalary_shfromemployees)ewheree.salary_sh>2000;通过查询语句向表中插入数据insert允许用户通过查询语句向目标表中插入数据insertoverwrite[into]tablestock_tpartition(ymd='20170929')selectsymbol,price_open,price_high,price_low,price_close,volumefromstockswhereymd='20170929';通过查询语句向表中插入数据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';动态分区插入Hive提供了一个动态分区功能，可以基于查询参数推断出需要创建的分区名称insertoverwritetablestock_tpartition(ymd)selectsymbol,price_open,price_high,price_low,price_close,volume,ymdfromstocks;动态分区功能默认情况下是没有开启的，需要将hive.exec.dynamic.partition.mode设置为nonstrict导出数据从表中导出数据如果文件恰好是用户需要的格式，那么只需要简单拷贝文件夹或者文件就可以了。

hadoopfs-cpsource_pathtarget_path否则，用户可以使用insert…directory…insertoverwrite[local]directory'/home/hadoop/emp'selectname,salary,addressfromemployees;关系运算空值判断selectname,salaryfromemployeeswheresalaryisnull;LIKE比较selectname,salaryfromemployeeswherenamelike'l%';浮点数比较（可选）浮点数比较的一个常见问题是出现在不同类型做比较（float和double比较）selectname,salary,deductions["grtaxes"]fromemployeeswheredeductions[“grtaxes”]>0.2;对指定的值进行显示的类型转换selectname,salary,deductions["grtaxes"]fromemployeeswheredeductions["grtaxes"]>cast(0.2asfloat);数学函数取整函数selectround(3.1415)fromemployees;指定精度取整函数selectround(3.1415,2)fromemployees;向下取整函数selectfloor(3.1415)fromemployees;向上取整函数selectceil(3.1415)fromemployees;取随机数函数selectrand()fromemployees;

字符串函数

字符串长度函数selectlength(name)fromemployees;字符串连接函数selectconcat(name,'@')fromemployees;字符串截取函数selectsubstr(name,2)fromemployees;selectsubstr(name,1,2)fromemployees;字符串转大写函数selectupper(name)fromemployees;去空格函数

selecttrim(name)fromemployees;

聚合函数个数统计函数selectcount(*)fromemployees;总和统计函数selectsum(salary)fromemployees;平均值统计函数

selectavg(salary)fromemployees;最小最大值selectmin(salary),max(salary)fromemployees;数据表的join—innerjoinselecta.ymd,a.symbol,a.price_close,b.symbol,b.price_closefromstocksajoinstocksbona.ymd=b.ymdwherea.symbol='600718'andb.symbol='002230';ON子句指定了两个表之间数据连接的条件数据表的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';数据表的join—innerjoin练习产生数据集countword,将countword和之前产生的另外一个wordcount数据集进行联合查询创建一个中间数据表merged，用于存储查询结果将两个数据集的交集保存在数据表merged中数据表的join—leftouterjoin左外连接通过关键字leftouter进行标识selects.ymd,s.symbol,s.price_close,d.dividendfromstockssleftouterjoinstocks_divdons.ymd=d.ymdands.symbol=d.symbol;数据表的join—Rightouterjoin右外连接将返回右边表所有符合where语句的记录，左边表中匹配不上的字段值用null代替。selects.ymd,s.symbol,s.price_close,d.dividendfromstocks_divdrightouterjoinstockssons.ymd=d.ymdands.symbol=d.symbol;数据表的join—Fullouterjoin完全外连接会返回所有表中符合where语句条件的所有记录，如果任一表的指定字段没有符合条件的值，那么就会使用null值代替。selects.ymd,s.symbol,s.price_close,d.dividendfromstocks_divdfullouterjoinstockssons.ymd=d.ymdands.symbol=d.symbol;数据表的join—leftsemijoinleftsemijoin（左半开连接）类似exists。即查找a表中的数据，是否在b表中存在，找出存在的数据。selects.ymd,s.symbol,s.price_closefromstockssleftsemijoinstocks_divdons.ymd=d.ymdands.symbol=d.symbol;笛卡尔积join笛卡尔积是一种连接，表示左边表的行数乘以右边表的行数等于笛卡尔结果集的大小。select*fromstocksjoinstocks_div;Orderby和SortbyOrderby会对查询结果集执行一个全局排序，所有的数据都通过一个reducer进行处理。Sortby会在每个reducer中对数据进行排序，保证每个reducer的输出数据都是有序的，这样可以提高后面进行的全局排序的效率。select*fromwordcountwherefreq>10orderbyfreqdesclimit10;select*fromwordcountwherefreq>10sortbyfreqdesclimit10;DistributebyDistributeby控制map的输出在reducer中的划分使用Distributeby来保证具有相同值的数据被分发到同一个reducer中进行处理，然后使用sort

by对数据进行排序。selectymd,symbol,price_closefromstocksdistributebysymbolsortbysymbol,ymd;抽样查询对于非常大的数据集，用户有时需要使用的是一个具有代表性的查询