CC00012.hadoop——|Hadoop&Hive.V12|——|Hive.v12|Hive_DQL之查询.v02|

yanqi_vip

已于 2022-04-10 16:31:36 修改

阅读量87

点赞数

分类专栏： bigdatav007——hadoop.v002 文章标签： hive hadoop 大数据 java mapreduce

于 2022-04-07 14:32:00 首次发布

不予转载

本文链接：https://blog.csdn.net/yanqi_vip/article/details/124031114

版权

bigdatav007——hadoop.v002 专栏收录该内容

59 篇文章 0 订阅

订阅专栏

一、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简化语法

yanqi_vip

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
CC00012.hadoop——|Hadoop&Hive.V12|——|Hive.v12|Hive_DQL之查询.v02|

一、group by子句### --- group by子句~~~ GROUP BY语句通常与聚组函数一起使用，~~~ 按照一个或多个列对数据进行分组，对每个组进行聚合操作。~~~ # 计算emp表每个部门的平均工资hive (mydb)> select deptno, avg(sal)from empgroup by deptno;...
复制链接

扫一扫