【大数据之Hive】十二、Hive-HQL查询之分组、join、排序

一、分组

1 group by 语句

  group by 通常和聚合函数一起使用，按照一个或多个列的结果进行分组，任何对每个租执行聚合操作。

  用group by时，select中只能用在group by中的字段和聚合函数。

--计算emp每个部门中每个岗位的最高薪水：
select t.deptnum,t.job,max(t.sal) max_sal from emp t group by t.deptnum,t.job;

hive sql执行过程：

2 having语句

having对分组聚合后的组进行过滤，针对一组数据。

having和where不同点：

（1）where后不能用分组聚合函数，having可以。

（2）having只用于group by 分组统计语句。

如：

--求每个部门的平均工资：
select deptnum,avg(sal) avg_sal from emp group by deptnum;
--求部门平均工资大于2000的部门：
select deptnum,avg(sal) avg_sal from emp group by deptnum having avg_sal > 2000;

HQL执行过程：

二、join语句

1 等值join

Hive支持通常的sql join语句，但是只支持等值连接，Hive2.x之前不支持非等值连接。

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

HQL执行过程：

2 表的别名

优点：别名能简化查询，可以区分字段来源。

select e.*,d.* from emp e join dept d on d.deptnum=e.deptnum;

3 内连接

只有进行连接的两个表都存在，与连接条件匹配的数据才会保留下来。

select e.empnum,e.ename,d.dname from emp e join dept d on d.deptnum=e.deptnum;

4 左外连接

返回join操作符左边表中符合where子句的所有记录。

select e.empnum,e.ename,d.deptnum from emp e left join dept d on e.deptnum=d.deptnum;

5 右外连接

返回join操作符右边表中符合where子句的所有记录。

select e.empnum,e.name,d.deptnum from emp e rigth join dept d on e.deptnum=d.deptnum;

6 满外连接

返回所有表中符合where语句条件的所有记录。如果任一表的指定字段没有符合条件的值就用null代替。

select e.empnum,e.ename,d.deptnum from emp e full join dept d on e.deptnum=d.deptnum;

7 内连接，左外、右外、满外连接的区别

  满外连接（全外连接）是左外连接和右外连接的结合。

数据集合上的区别：

（1）内连接：A join B，是A和B的公共数据集。

（2）左外连接：A left join B，是A和B的公共数据集，且连接A除公共数据集以外的所有数据。

（3）右外连接：A rigth join B，是A和B的公共数据集，且连接B除公共数据集以外的所有数据。

（4）满外连接：A full join B，是A和B的公共数据集，且连接A、B除公共数据集以外的所有数据。

计算方法上的区别：

（1）内连接：A join B = C

（2）左外连接：A left join B = C + A1

（3）右外连接：A rigth join B = C +B1

（4）满外连接：A full join B = A1 + C +B1

8 多表连接

连接n个表至少要n-1个条件。

在/opt/module/hive/datas/下创建一个location.txt文件

vim location.txt
#添加以下内容
1700 北京
1800 上海
1900 深圳

--创建表
create table location(
    locid int,
    locname string
)
row format delimited fields terminated by '\t';
--从本地导入数据
load data local inpath '/opt/module/hive/datas/location.txt' into table location;
--多表连接查询
select e.ename,d.dname,l.locname from emp e 
join dept d on e.deptnum=d.deptnum 
join location l on d.locid=l.locid;

  Hive会对每一个join连接对象启动一个MR任务。Hive执行顺序是从左到右的，上述例子首先启动一个MR任务对表e和表d连接，再启动一个MR任务连接第一个MR的输出和表l。

9 笛卡尔集

  A(n)表的所有行与B(m)表的所有行关联在一起，生成一个n*m行的表。

笛卡尔集产生条件：

（1）省略连接条件。

（2）连接条件无效。

（3）所有表中的所有行相互连接。

select empnum,dname from emp,dept;

HQL执行过程：

10 联合(union & union all)

  union和union all都是上下拼接sql的结果，而join是左右关联。union去重，union all不去重。

union和union all上下拼接要求：

（1）两个sql结果列的个数必须相同。

（2）两个sql结果上下所对应列的类型必须一致。

（3）必须连接两个select查询语句，结果的字段名以第一条select语句的字段名来显示。

--将员工表部门30的员工信息和部门40的员工信息用union拼接显示：
select * from emp where deptnum=30 union select * from emp where deptnum=40;

三、排序

1 全局排序(order by)

  全局排序order by只有一个reduce，且 order by 放在select句子末尾。order by常与limit连用，由于limit，在Map端进行order by时只需要传限制的部分数据给Reduce端，减小了reduce的压力。

（1）asc（默认升序）

（2）desc（降序）

--查询员工信息按工资升序排序：
select * from emp order by sal;
----查询员工信息按工资降序排序：
select * fromm emp order by sal desc;
--按列别名排序：
select ename,sal * 2 twosal from emp order by twosal;
--按部门和工资升序排序（多个列排序）：
select ename,deptnum，sal from emp order by deptnum,sal;

HQL执行过程：

order by 缺点：对于大规模数据集效率非常低。

解决：多数情况下不需要全局排序，可以用sort by进行内部排序。

2 每个reduce内部排序(sort by)

  sort by为每一个reduce产生一个排序文件，每个reduce内部进行排序（是Map到Reduce的排序字段）。

--用参数声明方式设置reduce个数：
hive>set mapreduce.job.reduce=3;
--用参数声明方式查看reduce个数：
hive>set mapreduce.job.reduce;
--根据部门编号降序查看员工信息：
select * from emp sort by deptnum desc;
--按部门编号对员工信息降序排序，并把查询结果导入到文件中：
insert overwrite local directory '/opt/module/hive/datas/sortby-result'
select * from emp sort by deptnum desc;

HQL执行过程：

3 分区(distribute by)

  分区 distribute by类似于MR中的partition（自定义分区），用于控制特定行到某个reducer，结合sort by 使用，通常是为了进行后续的聚集操作。

distribute by 分区规则：

（1）分区字段的hash码与reduce的个数进行取模运算，值相同的在同一个分区。

（2）distribute by 在 sort by 语句之前。

  对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by 的效果，测试完之后将mapreduce.job.reduces置-1，否则分区或分桶load数据运行MR任务会出错。

--用参数声明方式设置reduce个数：
hive>set mapreduce.job.reduce=3;
--先按照部门编号进行分区，再按员工薪资降序排序：
insert overwrite local directory '/opt/module/hive/datas/distribute-result'
select * from emp distribute by deptnum sort by sal desc;
hive>set mapreduce.job.reduce=-1;

HQL执行过程：

4 分区排序(cluster by)

  当 distribute by 和 sort by 字段相同时，可以使用 cluster by 方式 cluster by 同时具有 distribute by 和 sort by 功能，但 cluster by 排序只能升序排序，不能指定排序规则为asc或desc。

--查询员工信息按部门号分区且排序：
--分区时可能将部门20和30号分在一个区
select * from emp distribute by deptnum sort by deptnum;
--等价于：
select * from emp cluster by deptnum;

HQL执行过程：