38-57-hive-DML-查询

38-hive-数据类型-DML-查询：

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select

查询语句语法：

SELECT [ALL | DISTINCT] select_expr, select_expr, ... FROM table_reference [WHERE where_condition]
[GROUP BY col_list]	[ORDER BY col_list]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]	[LIMIT number]
1
2
3
4
5

测试

1、准备数据

dept
10 ACCOUNTING 1700
20 RESEARCH 1800
30 SALES 1900
40 OPERATIONS 1700
emp

7369    SMITH   CLERK   7902    1980-12-17      800.00          20
7499    ALLEN   SALESMAN        7698    1981-2-20       1600.00 300.00  30
7521    WARD    SALESMAN        7698    1981-2-22       1250.00 500.00  30
7566    JONES   MANAGER 7839    1981-4-2        2975.00         20
7654    MARTIN  SALESMAN        7698    1981-9-28       1250.00 1400.00 30
7698    BLAKE   MANAGER 7839    1981-5-1        2850.00         30
7782    CLARK   MANAGER 7839    1981-6-9        2450.00         10
7788    SCOTT   ANALYST 7566    1987-4-19       3000.00         20
7839    KING    PRESIDENT               1981-11-17      5000.00         10
7844    TURNER  SALESMAN        7698    1981-9-8        1500.00 0.00    30
7876    ADAMS   CLERK   7788    1987-5-23       1100.00         20
7900    JAMES   CLERK   7698    1981-12-3       950.00          30
7902    FORD    ANALYST 7566    1981-12-3       3000.00         20
7934    MILLER  CLERK   7782    1982-1-23       1300.00         10
7934    MILLER  CLERK   7782    1982-1-23       1300.00         50
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

2、创建相关表

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

create table if not exists emp(empno int,ename string,job string,mgr int,hiredate string, sal double, 
comm double,deptno int)	row format delimited fields terminated by '\t';
1
2
3
4

3、导入数据

load data local inpath '/opt/module/datas/dept.txt' into table	dept;
load data local inpath '/opt/module/datas/emp.txt' into table emp;
1
2

算数运算符和逻辑运算法。

Like 和 RLike

% 代表零个或多个字符(任意个字符)。

_ 代表一个字符。

RLIKE 子句是 Hive 中这个功能的一个扩展，其可以通过 Java 的正则表达式这个更强大

的语言来指定匹配条件。

Group By 语句

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

having 与 where 不同点

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

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

select deptno, avg(sal) from emp group by deptno;
select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;
1
2

相关查询语句（大小写不敏感）

select * from emp;#全查
select empno,ename from emp ;#特定列
select ename AS name, deptno dn from emp;#别名as，也可省略
select sal +1 from emp;#算数运算
select count(*) cnt from emp;	#求总行数（count）
select max(sal) max_sal from emp;#max
select min(sal) min_sal from emp;#min
select sum(sal) sum_sal from emp;#sum
select avg(sal) avg_sal from emp;#avg
select * from emp limit 5;#返回行数
select * from emp where sal >1000;#where 条件过滤
select * from emp where sal between 500 and 1000;
select * from emp where comm is null;#is null
select * from emp where sal IN (1500, 5000);#in /or
select * from emp where ename LIKE 'A%';#以A开头
select * from emp where ename LIKE '_A%';#第二个为A的
select * from emp where ename RLIKE '[A]';#所有带A的
select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;#计算 emp 表每个部门的平均工资
select t.deptno, t.job, max(t.sal) max_sal from emp t group by t.deptno, t.job;#每个部门中每个岗位的最高薪水
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

Join 语句

select e.empno,e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;#内连接

#查所有员工所在的部门
select e.empno,e.ename,e.deptno,d.dname from emp e left join dept d on e.deptno=d.deptno;#左外连接左表完整

#查所有部门中对应的员工信息
select e.empno,e.ename,d.deptno,d.dname from emp e right join dept d on e.deptno=d.deptno;#右外连接右表完整

#查所有员工信息和所有部门信息
select e.empno,e.ename,nvl(e.deptno,d.deptno),d.dname from emp e full join dept d on e.deptno=d.deptno;#全连接

#查询员工信息，所在的部门信息为null,左连接，右为null
select e.empno,e.ename,e.deptno,d.dname from emp e left join dept d on e.deptno=d.deptno where d.deptno is null;#左连接
等同于
select e.empno,e.ename,e.deptno from emp e where e.deptno not in (select deptno from dept);

#查询部门不存在任何员工的部门信息
select d.deptno,d.deptname from emp e right join dept d on e.deptno=d.deptno where e.deptno is null;#右连接


#查询员工特有信息以及部门特有信息
select e.empno,e.ename,e.deptno,d.deptno,d.dname from emp e full join dept d on e.deptno=d.deptno where e.deptno is null or d.deptno is null;#全外连接
或者
select * from (select e.empno,e.ename,e.deptno,d.deptno,d.dname from emp e left join dept d on e.deptno=d.deptno where d.deptno is null union select e.empno,e.ename,e.deptno,d.deptno,d.dname from emp e right join dept d on e.deptno=d.deptno where e.deptno is null) temp;#union去重，union不去重。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LEDplkxh-1669556639948)(png/image-20210801192316002.png)]

多表连接

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

数据

1700	 Beijing
1800	 London
1900	 Tokyo
1
2
3

1、建表导入

#建表
create table if not exists location(loc int,loc_name string) row format delimited fields terminated by '\t';
#导入
load data local inpath '/opt/module/hive/datas/loc.txt' into table location;
#查询员工姓名，部门名称，以及部门所在城市名称
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;
1
2
3
4
5
6

大多数情况下，Hive 会对每对 JOIN 连接对象启动一个 MapReduce 任务。本例中会先对表 e 和表 d 进行连接操作，然后会再启动一个 MapReduce job 将第一个 MapReduce job 的输出和表 l;进行连接操作。因为 Hive 总是按照从左到右的顺序执行的。

优化：当对 3 个或者更多表进行 join 连接时，如果每个 on 子句都使用相同的连接键的话，那么只会产生一个 MapReduce job。

笛卡尔积（n*m）（避免产生，一般不会这么写，和mysql一样，了解即可）

1）笛卡尔集会在下面条件下产生

省略连接条件，连接条件无效，所有表中的所有行互相连接

2）hive (default)> select empno, dname from emp, dept;

**全局排序（Order By）**全局排序，只有一个 Reducer

select * from emp order by sal;#升序，默认
select * from emp order by sal desc;#降序
select ename, deptno, sal from emp order by deptno, sal;#多列排序
1
2
3

每个 Reduce 内部排序（Sort By）

Sort By：对于大规模的数据集 order by 的效率非常低。在很多情况下，并不需要全局排序，此时可以使用 sort by。Sort by 为每个 reducer 产生一个排序文件。每个 Reducer 内部进行排序，对全局结果集来说不是排序

hive (default)> set mapreduce.job.reduces=3;
hive (default)> select * from emp sort by deptno desc;
hive (default)> insert overwrite local directory '/opt/module/datas/sortby-result' select * from emp sort by deptno desc;
1
2
3

分区（Distribute By）

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

注意：

➢ distribute by 的分区规则是根据分区字段的 hash 码与 reduce 的个数进行模除后，余数相同的分到一个区。

➢ Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前。

hive (default)>set mapreduce.job.reduces=3;
hive (default)>insert overwrite local directory '/opt/module/data/distribute-result' select * from emp distribute by deptno sort by empno desc;
1
2

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6Bsh7XNG-1669556639949)(png/image-20210802160344356.png)]

Cluster By

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

hive (default)> select * from emp cluster by deptno;
等价于
hive (default)> select * from emp distribute by deptno sort by deptno;
1
2
3

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

hive (default)> select * from emp cluster by deptno;
等价于
hive (default)> select * from emp distribute by deptno sort by deptno;
1
2
3