Hive-05之查询分组、排序、casewhen、什么情况下Hive可以避免进行MapReduce

一、目标 掌握hive中select查询语句中的基本语法掌握hive中select查询语句的分组掌握hive中select查询语句中的join掌握hive中select查询语句中的排序 二、要点 1. 基本查询 注意 SQL 语言大小写不敏感SQL 可以写在一行或者多行关键字不能被缩写也不能分行各子句一般要分行写使用缩进提高语句的可读性 1.1 全表和特定列查询 全表查询 select * from student; 选择特定列查询 select empno, ename from emp; 1.2 列起别名

重命名一个列

紧跟列名，也可以在列名和别名之间加入关键字 ‘as’

案例实操

select ename AS name, deptno dn from emp; 1.3 常用函数 1．求总行数（count） select count(*) cnt from emp; 2、求工资的最大值（max） select max(sal) max_sal from emp; 3、求工资的最小值（min） select min(sal) min_sal from emp; 4、求工资的总和（sum） select sum(sal) sum_sal from emp; 5、求工资的平均值（avg） select avg(sal) avg_sal from emp; 1.4 limit 语句 典型的查询会返回多行数据。limit子句用于限制返回的行数。 select * from emp limit 5; 1.5 where 语句 1、使用 where 子句，将不满足条件的行过滤掉2、where 子句紧随from子句3、案例实操 select * from emp where sal >1000; 1.6 算术运算符 运算符描述A+BA和B 相加A-BA减去BA*BA和B 相乘A/BA除以BA%BA对B取余A&BA和B按位取与A|BA和B按位取或A^BA和B按位取异或~AA按位取反 1.7 比较运算符 操作符支持的数据类型描述A=B基本数据类型如果A等于B则返回true，反之返回falseA<=>B基本数据类型如果A和B都为NULL，则返回true，其他的和等号（=）操作符的结果一致，如果任一为NULL则结果为NULLA<>B, A!=B基本数据类型A或者B为NULL则返回NULL；如果A不等于B，则返回true，反之返回falseA<B基本数据类型A或者B为NULL，则返回NULL；如果A小于B，则返回true，反之返回falseA<=B基本数据类型A或者B为NULL，则返回NULL；如果A小于等于B，则返回true，反之返回falseA>B基本数据类型A或者B为NULL，则返回NULL；如果A大于B，则返回true，反之返回falseA>=B基本数据类型A或者B为NULL，则返回NULL；如果A大于等于B，则返回true，反之返回falseA [NOT] BETWEEN B AND C基本数据类型如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于等于B而且小于或等于C，则结果为true，反之为false。如果使用NOT关键字则可达到相反的效果。A IS NULL所有数据类型如果A等于NULL，则返回true，反之返回falseA IS NOT NULL所有数据类型如果A不等于NULL，则返回true，反之返回falseIN(数值1, 数值2)所有数据类型使用 IN运算显示列表中的值A [NOT] LIKE BSTRING 类型B是一个SQL下的简单正则表达式，如果A与其匹配的话，则返回true；反之返回false。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母’x’结尾，而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。like不是正则，而是通配符A RLIKE B, A REGEXP BSTRING 类型B是一个正则表达式，如果A与其匹配，则返回true；反之返回false。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。 1.8 逻辑运算符 操作符操作描述A AND B逻辑并如果A和B都是true则为true，否则falseA OR B逻辑或如果A或B或两者都是true则为true，否则falseNOT A逻辑否如果A为false则为true,否则false 2. 分组 2.1 Group By 语句

​ Group By 语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。

案例实操：

（1）计算emp表每个部门的平均工资 select t.deptno, avg(t.sal) as avg_sal from emp t group by t.deptno; （2）计算emp每个部门中每个岗位的最高薪水 select t.deptno, t.job, max(t.sal) as max_sal from emp t group by t.deptno, t.job; 2.2 Having语句

having 与 where 不同点

where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据

where后面不能写分组函数，而having后面可以使用分组函数

having只用于group by分组统计语句

案例实操

求每个部门的平均工资 select deptno, avg(sal) from emp group by deptno; 求每个部门的平均薪水大于2000的部门 select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000; 3. join语句（25分钟） 3.1 等值 join

Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接。

案例实操

根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称； select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno; 3.2 表的别名

好处

使用别名可以简化查询。

使用表名前缀可以提高执行效率。

案例实操

合并老师与课程表 select * from teacher t join course c on t.id = c.id; 3.3 内连接 inner join 内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。 join默认是inner join 案例实操 select * from teacher t inner join course c on t.id = c.id; 3.4 左外连接 left outer join

左外连接：join操作符左边表中符合where子句的所有记录将会被返回。

案例实操

查询老师对应的课程 select * from teacher t left outer join course c on t.id = c.id; 3.5 右外连接 right outer join

右外连接：join操作符右边表中符合where子句的所有记录将会被返回。

案例实操

select * from teacher t right outer join course c on t.id = c.id; 3.6 满外连接 full outer join

满外连接：将会返回所有表中符合where语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用null值替代。

案例实操

select * from teacher t full outer join course c on t.id = c.id; 3.7 多表连接

多个表使用join进行连接

注意：连接 n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。

案例实操

多表连接查询，查询老师对应的课程，以及对应的分数，对应的学生 select * from teacher t left join course c on t.id = c.id left join score s on s.sid = c.cid left join student stu on s.s_id = stu.s_id; 4. 排序 4.1 order by 全局排序

order by 说明

全局排序，只有一个reduce使用 ORDER BY 子句排序 asc ( ascend) 升序 (默认) desc (descend) 降序 order by 子句在select语句的结尾

案例实操

查询学生的成绩，并按照分数降序排列 select * from student s order by score desc ; 4.2 按照别名排序 按照学生分数的平均值排序 select s.sid,s.tname, avg(score) as score_avg from student s group by s.sid,s.tname order by score_avg desc; 4.3 多个列排序 按照学生分数和年龄升序排序 select * from student s order by score,age; 4.4 每个MapReduce内部排序（Sort By）局部排序

sort by：每个reducer内部进行排序，对全局结果集来说不是排序。

1、设置reduce个数

set mapreduce.job.reduces=3;

2、查看设置reduce个数

set mapreduce.job.reduces;

3、查询成绩按照成绩降序排列

select * from student s sort by s.score;

4、将查询结果导入到文件中（按照成绩降序排列）

insert overwrite local directory '/home/hadoop/hivedata/sort' select * from student s sort by s.score; 4.5 distribute by 分区排序

distribute by：类似MR中partition，采集hash算法，在map端将查询的结果中hash值相同的结果分发到对应的reduce文件中。结合sort by使用。

注意

Hive要求 distribute by 语句要写在 sort by 语句之前。

案例实操

先按照学生 sid 进行分区，再按照学生成绩进行排序

设置reduce的个数 set mapreduce.job.reduces=3; 通过distribute by 进行数据的分区,，将不同的sid 划分到对应的reduce当中去 insert overwrite local directory '/home/hadoop/hivedata/distribute' select * from student distribute by sid sort by score; 4.6 cluster by

当distribute by和sort by字段相同时，可以使用cluster by方式

除了distribute by 的功能外，还会对该字段进行排序，所以cluster by = distribute by + sort by

--以下两种写法等价 insert overwrite local directory '/home/hadoop/hivedata/distribute_sort' select * from student distribute by score sort by score; insert overwrite local directory '/home/hadoop/hivedata/cluster' select * from student cluster by score; 五、拓展 5.1 case … when … then 语句

case …when …then 语句和if 条件语句类似，用于处理单个列的查询结果

案例实战

创建表 create table employee( empid int, deptid int, sex string, salary double )row format delimited fields terminated by ' '; 数据文件 employee.txt 1 10 female 5500.0 2 10 male 4500.0 3 20 female 1900.0 4 20 male 4800.0 5 40 female 6500.0 6 40 female 14500.0 7 40 male 44500.0 8 50 male 6500.0 9 50 male 7500.0

查询统计

将员工按照性别打上标识 select *, case sex when "female" then 1 when "male" then 0 end as flag from employee; 将员工按照薪资待遇划分等级 select *, case when salary < 5000 then "低等收入" when salary>= 5000 and salary < 10000 then "中等收入" when salary > 10000 then "高等收入" end as level from employee; ⭐️5.2 什么情况下Hive可以避免进行MapReduce

hive 为了执行效率考虑，简单的查询，就是只是select，不带count,sum,group by这样的，都不走map/reduce，直接读取hdfs目录中的文件进行filter过滤。也就是所谓的本地模式。

1、直接查询表的数据 不会进行Mapreduce

select * from employee;

2、查询语句中的过滤条件只是分区字段的情况下 不会进行Mapreduce。

select * from order_partition where month='2019-03';

此外，如果设置属性 set hive.exec.mode.local.auto=true; hive还是会尝试使用本地模式。

六、总结