Hive 中常用的查询语句解读及应用（分组、Join、排序语句）

Hive 中常用的查询语句

2 、分组

2.1 Group By 语句

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

1）案例实操：

（1）计算emp表每个部门的平均工资（表的数据详见博文 hive的基本查询的解读及应用）

0: jdbc:hive2://hadoop105:10000> select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;

（2）计算emp每个部门中每个岗位的最高薪水

0: jdbc:hive2://hadoop105:10000> select t.deptno, t.job, max(t.sal) max_sal from emp t group by t.deptno, t.job;

2.2 Having 语句

1）having 与 where 不同点

• （1）where 后面不能写分组聚合函数，而 having 后面可以使用分组聚合函数。
• （2）having 只用于 group by 分组统计语句。

2）案例实操

（1）求每个部门的平均薪水大于2000的部门

• 求每个部门的平均工资

0: jdbc:hive2://hadoop105:10000> select deptno, avg(sal) from emp group by deptno;

• 求每个部门的平均薪水大于2000的部门

0: jdbc:hive2://hadoop105:10000> select deptno, avg(sal) avg_sal from emp group by deptno  having avg_sal > 2000;

3、Join 语句

3.1 等值 Join

• Hive 支持通常的 SQL JOIN 语句，支持等值连接，非等值连接的支持情况和hive的版本有关（当前版本支持）

3.1.1 案例实操

• 根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称；

0: jdbc:hive2://hadoop105:10000> select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;

3.2 表的别名

• 优点：
• 使用别名可以简化查询；
• 使用表名前缀可以提高执行效率；

合并员工表和部门表

0: jdbc:hive2://hadoop105:10000> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;

3.3 内连接

• 内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

  0: jdbc:hive2://hadoop105:10000> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;
  

3.4 左外连接

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

  0: jdbc:hive2://hadoop105:10000> select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;
  

3.5 右外连接

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

  0: jdbc:hive2://hadoop105:10000> select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;
  

3.6 满外连接

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

  0: jdbc:hive2://hadoop105:10000> select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;
  

3.7 多表连接

• 注意：连接 n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。
• 数据准备 在/opt/module/hive-3.1.2/datas/下：vim location.txt

1700	guangdong
1800	shanghai
1900	beijing

3.7.1 创建位置表

create table if not exists location(
loc int,
loc_name string
)
row format delimited fields terminated by '\t';

3.7.2 导入数据

0: jdbc:hive2://hadoop105:10000> load data local inpath '/opt/module/hive-3.1.2/datas/location.txt' into table location;

3.7.3 多表连接查询

0: jdbc:hive2://hadoop105:10000>
SELECT e.ename, d.dname, l.loc_name
FROM   emp e 
JOIN   dept d
ON     d.deptno = e.deptno 
JOIN   location l
ON     d.loc = l.loc;

• 小结：大部分情况下，Hive 会对每对 JOIN 连接对象启动一个 MapReduce 任务。

  注意：Hive 总是按照从左到右的顺序执行的。

3.8 笛卡尔积

• 发生的情况：
• （1）省略连接条件（只有 join，没有 on）
• （2）连接条件无效（on 后免得连接条件恒成立 1=1）
• （3）所有表中的所有行互相连接

4、排序

4.1 全局排序（order by ）

• Order By: 全局排序，只有一个 reducer

4.1.1 使用 order by 子句排序

• asc(ascend): 升序（默认）
• desc(descend): 降序

4.1.2 order by 子句在 select 语句的结尾

4.1.3 练习

• （1）查询员工信息按工资升序排列

0: jdbc:hive2://hadoop105:10000> select * from emp order by sal;

• （2）查询员工信息按工资降序排列

0: jdbc:hive2://hadoop105:10000> select * from emp order by sal desc;

4.2 采用别名排序

• 按照员工薪水的2倍排序

0: jdbc:hive2://hadoop105:10000> select ename, sal*2 doublesal from emp order by doublesal;

4.3 多个列排序

• 按照部门和工资升序排序

0: jdbc:hive2://hadoop105:10000> select ename, deptno, sal from emp order by deptno, sal ;

4.4 每个 reduce 内部排序（sort by）

• Sort By：对于大规模的数据集 order by 的效率非常低。在很多情况下，并不需要全局排序，此时可以使用sort by。
• Sort by 为每个 reducer 产生一个排序文件。每个 Reducer 内部进行排序，对全局结果集来说不是排序。
• Sort by 对于每个 reduce 而言是 有序，但是在设置分区数过后，他是随机进入分区的 （不会单独使用）

1）设置 reduce 个数（默认为-1，动态调整）

0: jdbc:hive2://hadoop105:10000> set mapreduce.job.reduces=3;	--其实就是设置当前的分区号

2）查看设置 reduce 个数

0: jdbc:hive2://hadoop105:10000> set mapreduce.job.reduces;

3）根据部门编号降序查看员工信息

0: jdbc:hive2://hadoop105:10000> select * from emp sort by deptno desc;

4）将查询结果导入到文件中（按照部门编号降序排序）

0: jdbc:hive2://hadoop105:10000> insert overwrite local directory '/opt/module/hive-3.1.2/datas/sortby-result' select * from emp sort by deptno desc;

4.5 分区（distribute by）

• Distribute By： 在有些情况下，为了进行后续的聚集操作，我们需要控制某个特定行应该到哪个 reducer。distribute by 子句可以完成。**distribute by **类似MR中 partition（自定义分区），进行分区，结合sort by使用。 （distribute by A sort by B asc/desc）
• 对于 distribute by 进行测试，一定要分配多 reduce 进行处理，否则无法看到 distribute by 的效果。

1）案例：

（1）先按照部门编号分区，再按照员工编号降序排序。

0: jdbc:hive2://hadoop105:10000> set mapreduce.job.reduces=3;

0: jdbc:hive2://hadoop105:10000> insert overwrite local directory '/opt/module/hive/datas/distribute-result' select * from emp distribute by deptno sort by sal desc;

• 注意：
• distribute by 的分区规则是根据分区字段的 hash 码与 reduce 的个数进行模除后，余数相同的分到一个区。
• Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前。

4.6 分区排序（Cluster By）

• 当 distribute by 和s ort by 字段相同时，可以使用 cluster by方式。
• cluster by除了具有 distribute by 的功能外还兼具 sort by 的功能。但是==排序只能是升序排序==，不能指定排序规则为 ASC 或者 DESC。

（1）以下两种写法等价

0: jdbc:hive2://hadoop105:10000> select * from emp cluster by deptno;
0: jdbc:hive2://hadoop105:10000> select * from emp distribute by deptno sort by deptno;