5hive高级函数实战丨教程

慕课网首 免费课 实战课 金职 慕课专 手 从所有的词条中查询Java5Hive

徐老师·更新于2020-08- 上一节4Hive实 6Hive技巧与核…下一和msliv多的函数，我们可以通过showfunctions;来查看hive中的内置函数第10周-Spark快速上

hive(default)>show查看指定函数的描述信息我们可以使用descfunctionhive(default)>descfunctionyear(param)-ReturnstheyearcomponentoftheTimetaken:0.015seconds,Fetched:1显示函数的扩展内hive(default)>descfunctionextendedyear(param)-Returnstheyearcomponentoftheparamcanbeone1.Astringintheformatof'yyyy-MM-ddHH:mm:ss'or'yyyy-MM-2.Adate3.Atimestamp4.Ayear-monthinterval>SELECTyear('2009-07-30')FROMsrcLIMITFunctionFunctionHive高级普通的就不说了，msql中支持的函数这里面大部分都支持，并且hive支持的函数比mysql还要多，在这里我们主要挑几个典型的说一下一个典型的应用场景，分组排序取TopN操主要需要使用到ROW_NUMBER()和OVER()函数over可以理解为把数据划分到一个窗口内，里面可以加上priinby，表示按照字段对数据进行分组，还可以加上orderby表示对每个分组内的数据按照某个字段进行排序前三名学生的基础数据是这样 [root@bigdata04hivedata]#more21324354657687989建createexternaltableidnamesubscore)rowformatfieldsterminatedbylocation加载数[root@bigdata04hivedata]#hdfsdfs-put我们先使用row_number对数据编号，看一下是什么样子，row_numberselect*,row_number()over()from结果如下：在这里相当于给表里面的所有数据编了一个号，从1开112233445566797888979654321但是我们是希望对这些数据，先分组，再对组内数据进行排序，再编所以就需要在ovr函数内部添加pritnby进行分组，添加rdrby进行排序，最终给生成的编号起了换一个别名um执行select*,row_number()over(partitionbysuborderbyscoredesc)asnum结果如112231347454563176283994521823455select*fromselect*,row_number()over(partitionbysuborderbyscoredesc)asfrom 结果如下198129023180343english156english26english372 188 29 3前面SQL中的row_number()可以替换为rank()或者其中 表示上下两条记录的score相等时，记录的行号是一样的，但下一个score值的行号递增（是重复的次数），执行slselect*,rank()over(partitionbysuborderbyscoredesc)asnumfrom结果如下112231347454563176282994521823455dense_rank()表示上下两条记录的scre相等时，下一个scre值的行号递增1，比如：有两条并列第一，下一个是第二执行select*,dense_rank()over(partitionbysuborderbyscoredesc)as结果如下1981290231803477945460563english1993421823455总结一下row_number()over()是正常排rank()over()是跳跃排序，有两个第一名时接下来就是第三名（在各个分组内dense_rank()over()是连续排序，有两个第一名时仍然跟着第二名（在各个分组内行转列就是把多行数据转为一列数针对行转列这种需求主要需要使用到CONCAT_WS()、COLLECT_SET()、COLLECT_LIST()函数hive(default)>descfunctionCONCAT_WS(separator,[string|array(string)]+)-returnstheconcatenationTimetaken:0.019seconds,Fetched:1CONCAT_WS()函数可以实现根据指定的分隔符拼接多个字段的值，最终转化为一个带有分隔符的字符它可以接收多个参数，第一个参数是分隔符，后面的参数可以是字符串或者字符串数组，最终就隔符把后面的所有字符串拼接到一hive(default)>descfunctionCOLLECT_LIST(x)-ReturnsalistofobjectswithTimetaken:0.014seconds,Fetched:1这个函数可以返回一个list集合，集合中的元素会重复，一般和groupbyhive(default)>descfunctionCOLLECT_SET(x)-ReturnsasetofobjectswithduplicateelementsTimetaken:0.014seconds,Fetched:1这个函数可以返回一个set集合，集合汇中的元素不重复，一般和grupby结合在一起使用，一会再演示根据前面的分析，使用这几个函数我们就可以实现行转列这个需求了[root@bigdata04hivedata]#more 期望的结果是这样zs分析一下在这其实就是对数据进行了分组，组之后可以把相同人的保存一个数组中，再把数组中的数据成使用逗号分割的字符串开始对原始数据建createexternaltablenamefavor)rowformatfieldsterminatedbylocation上传数[root@bigdata04hivedata]#hdfsdfs-put查看数hive(default)>select*from Timetaken:0.175seconds,Fetched:5我们想要把数据转换为这种格式zs先对name字段进行分组，把favor转成一个数selectname,collect_list(favor)asfavor_listfromstudent_favorsgroupby结果如下然后再使用concat_ws执行行转列操selectname,concat_ws(',',collect_list(favor))asfavor_listfrom结果如下 我们发现这里面有一些是重复的，如果不希望出现重复的话可以使用COLLC_ST执行sqlselectname,concat_ws(',',collect_set(favor))asfavor_listfrom结果如下 这就是行转列的应列转行是和刚才的行转列反着来的，列转行可以把一列数据转主要使用到SPLIT()、EXPLODE()和 L看一下这几个函hive(default)>descfunctionSPLIT(str,regex)-SplitsstraroundoccurancesthatmatchTimetaken:0.011seconds,Fetched:1slit函数，接受一个字串符和切割规则，就类似于jva中的split函数，使用切割规则对字符串中的数行切割，最终返回一个array数组hive(default)>descfunctionEXPLODE(a)-separatestheelementsofarrayaintomultiplerows,ortheTimetaken:0.013seconds,Fetched:1 ：表示把map中每个key-value对，转成一行，key为一列，valueLAL lview通常和split，explodeslit可以对表中的某一列进行切割，返回一个数组类型的字段，plod可以对这个数组中的每一个元素转为一行，la lview可以对这份数据产生一个支持别名的虚拟表原始数据如[root@bigdata04hivedata]#more 希望的结果是这样12345接着建createexternaltablenamefavorlist)rowformatfieldsterminatedbylocation上传数[root@bigdata04hivedata]#hdfsdfs-put查看数hive(default)>select*from Timetaken:0.131seconds,Fetched:2先使用split对favorlist字段hive(default)>selectsplit(favorlist,',')fromTimetaken:0.224seconds,Fetched:2再使用explodehive(default)>selectexplode(split(favorlist,','))fromTimetaken:0.185seconds,Fetched:5其实到这里已经实现了列转行了，但是还需要把name字段拼接上，这时候就需要使用laterview了，否latrview相当于把plde返回的数据作为一个虚拟表来使用了，起名字为bl1，然后给这个表里面的那一列数据起一个名字叫fvr_new，如果有多个字段，可以再后面指定多个。这样在slct后面就可以使用这个名字了，有点类似jn操作了 selectname,favor_newfromstudent_favors_2lalview结果如下12345Hive排序ORDERiv中的rdrby跟传统的sql语言中的rdrby作用是一样的，会对查询的结果做一次全局排序，使用ruc任务只有一个SORTHive中指定了sortby，如果有多个reduce，那么在每个reducer端都会做排序，也就是证了局序（每个reducer出来的数据是有序的，但是不能保证所有的数据是全局有序的，除非只有reducer）使用前面的t2_bak表hive(default)>select*from 85912345Timetaken:3.491seconds,Fetched:10执行排序hive(default)>selectidfromt2_baksortbyHadoopjobinformationforStage-1:numberofmappers:1;numberof7 5Timetaken:24.562seconds,Fetched:10刚才我们说srt b是局部有序，为什么最终的结果还是全局有序呢？看里面的日志，现在只有一个rduce任务，所以最终结果还是有序的动态设置reduce任务数量为2，然后再执行排序的hive(default)>setmapreduce.job.reduces=hive(default)>selectidfromt2_baksortbyHadoopjobinformationforStage-1:numberofmappers:1;numberof852 Timetaken:27.943seconds,Fetched:10此时会发现数据就没有全局排序了，因为有多个reduce了不过针对 BY来说，你动态设置再多的reduce数量都没有用，最后还是只产生1个reduceDISTRIBUTE by：只会根据指定的key对数一般情况下可以和sortby结合使用，先对数据分区，再进行排序两者结合使用的时候distributeby必须要写在sortby之前先来看一下单独ditributebyhive(default)>setmapreduce.job.reduces=hive(default)>selectidfromt2_bakdistributebyNumberofreducetasksnotspecified.Defaultingtojobconfvalueof:424253 Timetaken:25.395seconds,Fetched:10可以结合 by实现分区内的排序，默认是升序，可以通过desc来设置倒hive(default)>setmapreduce.job.reduces=hive(default)>selectid