《Hive数据仓库应用与实战》课件 项目四-任务一-HiveQL实现复杂查询

《Hive数据仓库应用与实战》Hive数据仓库应用与实战项目四深入HiveQL任务一HiveQL实现复杂查询回顾知识点1变量和属性回顾知识点2HiveQL执行方式回顾知识点3

Hiveshell的其他用法回顾任务引入HiveQL是Hive的核心，前面我们已经学习了简单的查询，本任务将逐步加深，学习更加复杂的查询方法。任务概要任务描述:在前面的内容中，我们学习了基本的HiveQL语法，从这里开始，我们将学习更加复杂的HiveQL语句，包括聚合函数、分组、排序、连接、分桶及抽样查询，这些概念可能我们在之前的数据库学习中已经有所了解，现在我们就来看看HiveQL中的这些语法和之前学习的有什么区别。任务教学目标：理解各种连接之间的区别。掌握聚合函数、分组、排序、连接、分桶、抽样查询的用法。一聚合函数二分组教学内容三排序四连接五分桶及抽样查询聚合函数知识点一（一）聚合函数1、常用的聚合函数聚合函数可以概括为：多行合并为一行。常用的聚合函数返回类型函数名描述BIGINTcount(*)count(expr)count(DISTINCTexpr[,expr_.])count(*)–返回检索到的行的总数，包括含有NULL值的行。count(expr)–返回expr表达式不是NULL的行的数量count(DISTINCTexpr[,expr])–返回expr是唯一的且非NULL的行的数量DOUBLEsum(col)sum(DISTINCTcol)对组内某列求和（包含重复值）或者对组内某列求和（不包含重复值）DOUBLEavg(col)avg(DISTINCTcol)对组内某列元素求平均值者（包含重复值或不包含重复值）DOUBLEmin(col)返回组内某列的最小值DOUBLEmax(col)返回组内某列的最大值（一）聚合函数例：使用我们之前创建的emp表（一）聚合函数1.查询员工的最大工资hive>selectmax(salary)fromemp;结果：5000.0（一）聚合函数2.查询员工的最小工资hive>selectmin(salary)fromemp;结果：800.0（一）聚合函数3.查询员工的平均工资hive>selectavg(salary)fromemp;结果：2073.214285714286（一）聚合函数4.查询员工所有工资的和hive>selectsum(salary)fromemp;结果：29025.0（一）聚合函数5.查询记录数hive>selectcount(*)fromemp;hive>selectcount(1)fromemp;结果：14注意：count(*)和count(1)这两种方式是一样的。分组知识点二（二）分组GroupBy语句通常会和聚合函数一起使用，按照一个或者多个列进行分组，然后对每个组执行聚合操作。GroupBy语句注意：使用GroupBy时，在GroupBy后面出现的字段也要出现在select后面。且会执行mr程序。（二）分组例：还是使用emp表1.按照部门进行分组hive>selectdeptnofromempgroupbydeptno;结果：102030（二）分组2.查询每个部门的平均工资hive>selectdeptno,avg(salary)avg_salfromempgroupbydeptno;结果：102916.6666666666665202175.0301566.6666666666667（二）分组3.查询平均工资大于2000的部门（使用having子句限定分组查询）hive>selectdeptno,avg(salary)fromempgroupbydeptnohavingavg(salary)>2000;结果：102916.6666666666665202175.0（二）分组4.按照部门和入职时间进行分组（先按照部门进行分组，然后针对每组按照入职时间进行分组）hive>selectdeptno,hiredatefromempgroupbydeptno,hiredate;（二）分组5.按照部门和入职时间进行分组并计算出每组的人数hive>selectdeptno,hiredate,count(ename)fromempgroupbydeptno,hiredate;排序知识点三（三）排序1、OrderByOrderBy会对数据进行全局排序，和oracle和mysql等数据库中的OrderBy效果一样，它只在一个reduce中进行所以数据量特别大的时候效率非常低。OrderBy语句而且当设置：sethive.mapred.mode=strict的时候不指定limit，执行select会报错，如下：LIMITmustalsobespecified。（三）排序ASC（ascend）:升序（默认）例：查询员工信息按工资升序排列hive(default)>select*fromemporderbysalary;1、orderby（三）排序DESC（descend）:降序例：查询员工信息按工资降序排列hive(default)>select*fromemporderbysalarydesc;1、orderby（三）排序按照别名排序例：按照员工薪水的2倍排序hive(default)>selectename,salary*2twosalfromemporderbytwosal;1、orderby（三）排序多个列排序例：按照部门和工资升序排序hive(default)>selectename,deptno,salaryfromemporderbydeptno,salary;1、orderby（三）排序2、DistributeBy、SortBysortby是单独在各自的reduce中进行排序，所以并不能保证全局有序，一般和distributeby一起执行，而且distributeby要写在sortby前面。distributeby和sortby一般用于map输出的文件大小不均、reduce输出的文件大小不均、小文件过多、文件超大等场景。DistributeBy、SortBy语句（三）排序(1)设置reduce个数当Reducer数量设置为1时，等于ORDERBYhive(default)>setmapreduce.job.reduces=3;(2)查看设置reduce个数hive(default)>setmapreduce.job.reduces;2、DistributeBy、SortBy（三）排序例：使用emp表按照员工编号降序排列hive(default)>setmapreduce.job.reduces=3;hive(default)>select*fromempdistributebydeptnosortbyempnodesc;2、DistributeBy、SortBy（三）排序3、clusterbyclusterby除了具有distributeby的功能外还兼具sortby的功能。但是排序只能是倒序排序，不能指定排序规则为ASC或者DESC。OrderBy语句当distributeby和sortsby字段相同时，可以使用clusterby方式。（三）排序例子：使用emp表按照部门编号降序排列select*fromempclusterbydeptno;select*fromempdistributebydeptnosortbydeptno;3、clusterby连接知识点四（四）连接连接指对多表进行联合查询。JOIN用于将两个或多个表中的行组合在一起查询，类似于SQLJOIN，但是Hive仅支持等值连接。Hive中除了支持和传统数据库中一样的内关联、左关联、右关联、全关联，还支持LEFTSEMIJOIN和CROSSJOIN，但这两种JOIN类型也可以用前面的代替。Hive中join的关联键必须在ON()中指定，不能在Where中指定，否则就会先做笛卡尔积，再过滤。（四）连接hive>createtabletest_a(idstring,namestring)ROWFORMATDELIMITEDFIELDSTERMINATEDBY'\t';hive>loaddatalocalinpath'/opt/module/datas/test_a.txt'intotabletest_a;1、数据准备（四）连接hive>createtabletest_b(idstring,ageint)ROWFORMATDELIMITEDFIELDSTERMINATEDBY'\t';hive>loaddatalocalinpath'/opt/module/datas/test_b.txt'intotabletest_b;1、数据准备（四）连接Hive支持通常的SQLJOIN语句，但是只支持等值连接，不支持非等值连接。内连接只返回能关联上的结果。例：SELECTa.id,,b.ageFROMtest_aajointest_bbON(a.id=b.id);2、内连接（JOIN）（四）连接以LEFT[OUTER]JOIN关键字前面的表作为主表，和其他表进行关联，返回记录和主表的记录数一致，关联不上的字段置为NULL。例：SELECTa.id,,b.ageFROMtest_aaleftjointest_bbON(a.id=b.id);3、左外连接（LEFT[OUTER]JOIN）（四）连接4、右外连接（RIGHT[OUTER]JOIN）和左外关联相反，以RIGTH[OUTER]JOIN关键词后面的表作为主表，和前面的表做关联，返回记录数和主表一致，关联不上的字段为NULL。例：SELECTa.id,,b.ageFROMtest_aaRIGHTOUTERJOINtest_bbON(a.id=b.id);（四）连接5、全外连接（FULL[OUTER]JOIN）以两个表的记录为基准，返回两个表的记录去重之和，关联不上的字段为NULL。是否指定OUTER关键字对查询结果无影响。例：SELECTa.id,,b.ageFROMtest_aaFULLOUTERJOINtest_bbON(a.id=b.id);（四）连接6、LEFTSEMIJOIN以LEFTSEMIJOIN关键字前面的表为主表，返回主表的KEY也在副表中的记录。例：SELECTa.id,FROMtest_aaLEFTSEMIJOINtest_bbON(a.id=b.id);（四）连接7、笛卡尔积关联（CROSSJOIN）返回两个表的笛卡尔积结果，不需要指定关联键。例：SELECTa.id,,b.ageFROMtest_aaCROSSJOINtest_bb;（四）连接8、MapJoinmap端连接其实不算是Hive连接的一种，它是对HiveSQL的优化。开启MapJoin（默认开启）sethive.auto.convert.join=true（默认值）MAPJOIN操作不支持：在UNIONALL,LATERALVIEW,GROUPBY/JOIN/SORTBY/CLUSTERBY/DISTRIBUTEBY等操作后面；在UNION、JOIN以及其他MAPJOIN之前。（四）连接9、关于内连接、左连接、右连接、全连接的区分1.内连接AreaC=Circle1JOINCircle22.左连接AreaA=Circle1LEFTOUTERJOINCircle23.右连接AreaB=Circle1RIGHTOUTERJOINCircle24.全连接AUBUC=Circle1FULLOUTERJOINCircle2分组及抽样查询知识点五（五）分桶及抽样查询1、分桶和分区的区别分区针对的数据存储路径，分桶针对的是数据文件。分区提供一个隔离数据和优化查询的便利方式,不过并非所有的数据集都可以形成合理的分区,特别是确定合适的划分大小疑虑。分桶和分区的区别（五）分桶及抽样查询1、分桶和分区的区别下面我们通过一个示例来讲解分桶和分区的区别：1.首先创建分桶表，通过直接导入数据文件的方式，创建student.txt，内容如下（五）分桶及抽样查询1、分桶和分区的区别创建分桶表。hive(default)>createtablestu_buck(idint,namestring)clusteredby(id)into4bucketsrowformatdelimitedfieldsterminatedby'\t';查看表的详细结构。hive(hive)>descformattedstu_buck;...NumBuckets: 4（五）分桶及抽样查询1、分桶和分区的区别加载数据到分桶表中。hive(default)>>loaddatalocalinpath'/opt/module/datas/stu_buck.txt'intotablestu_buck;查看创建的分桶表中是否分成4个桶。（五）分桶及抽样查询1、分桶和分区的区别2.接下来创建分桶表时，数据通过子查询的方式导入。先建一个普通的stu表hive(default)>createtablestu_buck1(idint,namestring)rowformatdelimitedfieldsterminatedby'\t';向普通的stu_buck1表中导入数据。hive(default)>loaddatalocalinpath'/opt/module/datas/stu_buck.txt'intotablestu_b