|NO.Z.00012|——————————|BigDataEnd|——|Hadoop&Hive.V12|——|Hive.v12|Hive_DQL之查询.v02|
一、group by子句
### --- group by子句
~~~ GROUP BY语句通常与聚组函数一起使用,
~~~ 按照一个或多个列对数据进行分组,对每个组进行聚合操作。
~~~ # 计算emp表每个部门的平均工资
hive (mydb)> select deptno, avg(sal)
from emp
group by deptno;
--输出参数
deptno _c1
10 2916.6666666666665
20 2175.0
30 1566.6666666666667
~~~ # 计算emp每个部门中每个岗位的最高薪水
hive (mydb)> select deptno, job, max(sal)
from emp
group by deptno, job;
--输出参数
deptno job _c2
20 ANALYST 3000
10 CLERK 1300
20 CLERK 1100
30 CLERK 950
10 MANAGER 2450
20 MANAGER 2975
30 MANAGER 2850
10 PRESIDENT 5000
30 SALESMAN 1600
~~~ where子句针对表中的数据发挥作用;having针对查询结果(聚组以后的结果)发挥作用
~~~ where子句不能有分组函数;having子句可以有分组函数
~~~ having只用于group by分组统计之后
~~~ # 求每个部门的平均薪水大于2000的部门
hive (mydb)> select deptno, avg(sal)
from emp
group by deptno
having avg(sal) > 2000;
--输出参数
deptno _c1
10 2916.6666666666665
20 2175.0
五、表连接
### --- 表连接
~~~ Hive支持通常的SQL JOIN语句。默认情况下,仅支持等值连接,不支持非等值连接。
~~~ JOIN 语句中经常会使用表的别名。使用别名可以简化SQL语句的编写,使用表名前缀可以提高SQL的解析效率。
~~~ # 连接查询操作分为两大类:内连接和外连接,而外连接可进一步细分为三种类型:
~~~ 内连接: [inner] join
~~~ 外连接 (outer join)
~~~ - 左外连接。 left [outer] join,左表的数据全部显示
~~~ - 右外连接。 right [outer] join,右表的数据全部显示
~~~ - 全外连接。 full [outer] join,两张表的数据都显示

### --- 案例演示:
~~~ # 准备数据
u1.txt数据:
[root@linux123 ~]# vim /home/hadoop/data/u1.txt
1,a
2,b
3,c
4,d
5,e
6,f
u2.txt数据:
[root@linux123 ~]# vim /home/hadoop/data/u2.txt
4,d
5,e
6,f
7,g
8,h
9,i
~~~ # 创建表并导入数据
hive (mydb)> create table if not exists u1(
id int,
name string)
row format delimited fields terminated by ',';
hive (mydb)> create table if not exists u2(
id int,
name string)
row format delimited fields terminated by ',';
load data local inpath '/home/hadoop/data/u1.txt' into table u1;
load data local inpath '/home/hadoop/data/u2.txt' into table u2;
~~~ # 内连接
hive (mydb)> select * from u1 join u2 on u1.id = u2.id;
u1.id u1.name u2.id u2.name
4 d 4 d
5 e 5 e
6 f 6 f
~~~ # 左外连接
hive (mydb)> select * from u1 left join u2 on u1.id = u2.id;
u1.id u1.name u2.id u2.name
1 a NULL NULL
2 b NULL NULL
3 c NULL NULL
4 d 4 d
5 e 5 e
~~~ # 右外连接
select * from u1 right join u2 on u1.id = u2.id;
u1.id u1.name u2.id u2.name
4 d 4 d
5 e 5 e
6 f 6 f
NULL NULL 7 g
NULL NULL 8 h
NULL NULL 9 i
~~~ # 全外连接
select * from u1 full join u2 on u1.id = u2.id;
u1.id u1.name u2.id u2.name
1 a NULL NULL
2 b NULL NULL
3 c NULL NULL
4 d 4 d
5 e 5 e
6 f 6 f
NULL NULL 7 g
NULL NULL 8 h
NULL NULL 9 i
### --- 多表连接
~~~ 连接 n张表,至少需要 n-1 个连接条件。例如:连接四张表,至少需要三个连接条件。
~~~ 多表连接查询,查询老师对应的课程,以及对应的分数,对应的学生:
select *
from techer t left join course c on t.t_id = c.t_id
left join score s on s.c_id = c.c_id
left join student stu on s.s_id = stu.s_id;
~~~ Hive总是按照从左到右的顺序执行,Hive会对每对 JOIN 连接对象启动一个MapReduce 任务。
~~~ 上面的例子中会首先启动一个 MapReduce job 对表 t 和表 c 进行连接操作;
~~~ 然后再启动一个 MapReduce job 将第一个 MapReduce job 的输出和表 s 进行连接操作;
~~~ 然后再继续直到全部操作;
### --- 笛卡尔积
~~~ 满足以下条件将会产生笛卡尔集:
~~~ 没有连接条件
~~~ 连接条件无效
~~~ 所有表中的所有行互相连接
~~~ 如果表A、B分别有M、N条数据,其笛卡尔积的结果将有 M*N 条数据;
~~~ 缺省条件下hive不支持笛卡尔积运算;
hive (mydb)> set hive.strict.checks.cartesian.product=false;
hive (mydb)> select * from u1, u2;
u1.id u1.name u2.id u2.name
1 a 4 d
2 b 4 d
3 c 4 d
4 d 4 d
5 e 4 d
6 f 4 d
1 a 5 e
三、排序子句【重点】
### --- 全局排序(order by)
~~~ order by 子句出现在select语句的结尾;
~~~ order by子句对最终的结果进行排序;
~~~ 默认使用升序(ASC);可以使用DESC,跟在字段名之后表示降序;
~~~ # ORDER BY执行全局排序,只有一个reduce;
~~~ # 普通排序
hive (mydb)> select * from emp order by deptno;
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7934 MILLER CLERK 7782 2012-01-23 1300 NULL 10
7839 KING PRESIDENT NULL 2011-11-07 5000 NULL 10
7782 CLARK MANAGER 7839 2011-06-09 2450 NULL 10
7876 ADAMS CLERK 7788 2017-07-13 1100 NULL 20
7788 SCOTT ANALYST 7566 2017-07-13 3000 NULL 20
7369 SMITH CLERK 7902 2010-12-17 800 NULL 20
7566 JONES MANAGER 7839 2011-04-02 2975 NULL 20
7902 FORD ANALYST 7566 2011-12-03 3000 NULL 20
7844 TURNER SALESMAN 7698 2011-09-08 1500 0 30
7499 ALLEN SALESMAN 7698 2011-02-20 1600 300 30
7698 BLAKE MANAGER 7839 2011-05-01 2850 NULL 30
7654 MARTIN SALESMAN 7698 2011-09-28 1250 1400 30
7521 WARD SALESMAN 7698 2011-02-22 1250 500 30
7900 JAMES CLERK 7698 2011-12-03 950 NULL 30
~~~ # 按别名排序
hive (mydb)> select empno, ename, job, mgr, sal + nvl(comm, 0) salcomm,
deptno
from emp
order by salcomm desc;
--输出参数
empno ename job mgr salcomm deptno
7839 KING PRESIDENT NULL 5000 10
7902 FORD ANALYST 7566 3000 20
7788 SCOTT ANALYST 7566 3000 20
7566 JONES MANAGER 7839 2975 20
7698 BLAKE MANAGER 7839 2850 30
7654 MARTIN SALESMAN 7698 2650 30
7782 CLARK MANAGER 7839 2450 10
7499 ALLEN SALESMAN 7698 1900 30
7521 WARD SALESMAN 7698 1750 30
7844 TURNER SALESMAN 7698 1500 30
7934 MILLER CLERK 7782 1300 10
7876 ADAMS CLERK 7788 1100 20
7900 JAMES CLERK 7698 950 30
7369 SMITH CLERK 7902 800 20
~~~ # 多列排序
hive (mydb)> select empno, ename, job, mgr, sal + nvl(comm, 0) salcomm,
deptno
from emp
order by deptno, salcomm desc;
--输出参数
empno ename job mgr salcomm deptno
7839 KING PRESIDENT NULL 5000 10
7782 CLARK MANAGER 7839 2450 10
7934 MILLER CLERK 7782 1300 10
7788 SCOTT ANALYST 7566 3000 20
7902 FORD ANALYST 7566 3000 20
7566 JONES MANAGER 7839 2975 20
7876 ADAMS CLERK 7788 1100 20
7369 SMITH CLERK 7902 800 20
7698 BLAKE MANAGER 7839 2850 30
7654 MARTIN SALESMAN 7698 2650 30
7499 ALLEN SALESMAN 7698 1900 30
7521 WARD SALESMAN 7698 1750 30
7844 TURNER SALESMAN 7698 1500 30
7900 JAMES CLERK 7698 950 30
~~~ # 排序字段要出现在select子句中。以下语句无法执行(因为select子句中缺少deptno):
hive (mydb)> select empno, ename, job, mgr, sal + nvl(comm, 0) salcomm
from emp
order by deptno, salcomm desc;
### --- 每个MR内部排序(sort by)
~~~ 对于大规模数据而言order by效率低;
~~~ 在很多业务场景,我们并不需要全局有序的数据,此时可以使用sort by;
~~~ sort by为每个reduce产生一个排序文件,在reduce内部进行排序,得到局部有序的结果;
~~~ # 设置reduce个数
hive (mydb)> set mapreduce.job.reduces=2;
~~~ # 按照工资降序查看员工信息
hive (mydb)> select * from emp sort by sal desc;
--输出参数
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7902 FORD ANALYST 7566 2011-12-03 3000 NULL 20
7788 SCOTT ANALYST 7566 2017-07-13 3000 NULL 20
7566 JONES MANAGER 7839 2011-04-02 2975 NULL 20
7844 TURNER SALESMAN 7698 2011-09-08 1500 0 30
7521 WARD SALESMAN 7698 2011-02-22 1250 500 30
7654 MARTIN SALESMAN 7698 2011-09-28 1250 1400 30
7876 ADAMS CLERK 7788 2017-07-13 1100 NULL 20
7900 JAMES CLERK 7698 2011-12-03 950 NULL 30
7369 SMITH CLERK 7902 2010-12-17 800 NULL 20
7839 KING PRESIDENT NULL 2011-11-07 5000 NULL 10
7698 BLAKE MANAGER 7839 2011-05-01 2850 NULL 30
7782 CLARK MANAGER 7839 2011-06-09 2450 NULL 10
7499 ALLEN SALESMAN 7698 2011-02-20 1600 300 30
7934 MILLER CLERK 7782 2012-01-23 1300 NULL 10
~~~ # 将查询结果导入到文件中(按照工资降序)。生成两个输出文件,每个文件内部数据
~~~ 按工资降序排列
hive (mydb)> insert overwrite local directory '/home/hadoop/output/sortsal'
select * from emp sort by sal desc;
--输出参数
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
### --- 分区排序(distribute by)
~~~ distribute by 将特定的行发送到特定的reducer中,便于后继的聚合 与 排序操作;
~~~ distribute by 类似于MR中的分区操作,可以结合sort by操作,使分区数据有序;
~~~ distribute by 要写在sort by之前;
~~~ # 启动2个reducer task;先按 deptno 分区,在分区内按 sal+comm 排序
hive (mydb)> set mapreduce.job.reduces=2;
~~~ # 将结果输出到文件,观察输出结果
hive (mydb)> insert overwrite local directory '/home/hadoop/output/distBy'
select empno, ename, job, deptno, sal + nvl(comm, 0) salcomm
from emp
distribute by deptno
sort by salcomm desc;
--输出参数
empno ename job deptno salcomm
~~~ # 上例中,数据被分到了统一区,看不出分区的结果
~~~ # 将数据分到3个区中,每个分区都有数据
hive (mydb)> set mapreduce.job.reduces=3;
hive (mydb)> insert overwrite local directory '/home/hadoop/output/distBy1'
select empno, ename, job, deptno, sal + nvl(comm, 0) salcomm
from emp
distribute by deptno
sort by salcomm desc;
--输出参数
empno ename job deptno salcomm
### --- Cluster By
~~~ 当distribute by 与 sort by是同一个字段时,可使用cluster by简化语法;
~~~ cluster by 只能是剩下,不能指定排序规则;
~~~ # 语法上是等价的
hive (mydb)> select * from emp distribute by deptno sort by deptno;
--输出参数
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7654 MARTIN SALESMAN 7698 2011-09-28 1250 1400 30
7900 JAMES CLERK 7698 2011-12-03 950 NULL 30
7698 BLAKE MANAGER 7839 2011-05-01 2850 NULL 30
7521 WARD SALESMAN 7698 2011-02-22 1250 500 30
7844 TURNER SALESMAN 7698 2011-09-08 1500 0 30
7499 ALLEN SALESMAN 7698 2011-02-20 1600 300 30
7934 MILLER CLERK 7782 2012-01-23 1300 NULL 10
7839 KING PRESIDENT NULL 2011-11-07 5000 NULL 10
7782 CLARK MANAGER 7839 2011-06-09 2450 NULL 10
7788 SCOTT ANALYST 7566 2017-07-13 3000 NULL 20
7566 JONES MANAGER 7839 2011-04-02 2975 NULL 20
7876 ADAMS CLERK 7788 2017-07-13 1100 NULL 20
7902 FORD ANALYST 7566 2011-12-03 3000 NULL 20
7369 SMITH CLERK 7902 2010-12-17 800 NULL 20
hive (mydb)> select * from emp cluster by deptno;
--输出参数
emp.empno emp.ename emp.job emp.mgr emp.hiredate emp.sal emp.comm emp.deptno
7654 MARTIN SALESMAN 7698 2011-09-28 1250 1400 30
7900 JAMES CLERK 7698 2011-12-03 950 NULL 30
7698 BLAKE MANAGER 7839 2011-05-01 2850 NULL 30
7521 WARD SALESMAN 7698 2011-02-22 1250 500 30
7844 TURNER SALESMAN 7698 2011-09-08 1500 0 30
7499 ALLEN SALESMAN 7698 2011-02-20 1600 300 30
7934 MILLER CLERK 7782 2012-01-23 1300 NULL 10
7839 KING PRESIDENT NULL 2011-11-07 5000 NULL 10
7782 CLARK MANAGER 7839 2011-06-09 2450 NULL 10
7788 SCOTT ANALYST 7566 2017-07-13 3000 NULL 20
7566 JONES MANAGER 7839 2011-04-02 2975 NULL 20
7876 ADAMS CLERK 7788 2017-07-13 1100 NULL 20
7902 FORD ANALYST 7566 2011-12-03 3000 NULL 20
7369 SMITH CLERK 7902 2010-12-17 800 NULL 20
### --- 排序小结:
~~~ order by。执行全局排序,效率低。生产环境中慎用
~~~ sort by。使数据局部有序(在reduce内部有序)
~~~ distribute by。按照指定的条件将数据分组,常与sort by联用,使数据局部有序
~~~ cluster by。当distribute by 与 sort by是同一个字段时,可使用cluster by简化语法
Walter Savage Landor:strove with none,for none was worth my strife.Nature I loved and, next to Nature, Art:I warm'd both hands before the fire of life.It sinks, and I am ready to depart
——W.S.Landor