第6章 查询
查询语句语法：
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]
6.1 基本查询（Select…From）
6.1.1 全表和特定列查询
6.1.1.1 数据准备
`dept.txt`:
10 ACCOUNTING 1700
20 RESEARCH 1800
30 SALES 1900
40 OPERATIONS 1700
`emp.txt`：
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 600.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
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';
hive (default)> load data local inpath '/opt/module/hive/datas/dept.txt' into table dept;
hive (default)> load data local inpath '/opt/module/hive/datas/emp.txt' into table emp;
6.1.1.2 全表查询
hive (default)> select * from emp;
hive (default)> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;
注意：
- (1)
SQL 语言大小写不敏感。 - (2)
SQL 可以写在一行或者多行
6.1.1.3 选择特定列查询
hive (default)> select empno,ename from emp;
6.1.2 列别名
- (1) 重命名一个列
- (2) 便于计算
- (3) 紧跟列名，也可以在列名和别名之间加入关键字‘AS’
查询名称和部门
hive (default)> select ename AS name, deptno dept from emp;
6.1.3 算术运算符
| 运算符 | 描述 |
|---|
A+B | A 和 B 相加 | A-B | A 减去 B | A*B | A 和 B 相乘 | A/B | A 除以 B | A%B | A 对 B 取余 | A&B | A 和 B 按位取与 | A|B | A 和 B 按位取或 | A^B | A 和 B 按位取异或 | ~A | A 按位取反 |
案例实操：查询出所有员工的薪水后加 1 显示。
hive (default)> select sal+1 from emp;
6.1.4 常用函数
hive (default)> select count(*) cnt from emp;
hive (default)> select max(sal) max_sal from emp;
hive (default)> select min(sal) min_sal from emp;
hive (default)> select sum(sal) sum_sal from emp;
hive (default)> select avg(sal) avg_sal from emp;
6.1.5 Limit 语句
典型的查询会返回多行数据。LIMIT 子句用于限制返回的行数。
hive (default)> select * from emp limit 5;
hive (default)> select * from emp limit 2;
6.1.6 Where 语句
- (1) 使用
WHERE 子句，将不满足条件的行过滤掉 - (2)
WHERE 子句紧随 FROM 子句
查询出薪水大于 1000 的所有员工
hive (default)> select * from emp where sal>2000;
注意：where 子句中不能使用字段别名。having 子句中可以使用字段别名???
6.1.7 比较运算符（Between/In/Is Null）
6.1.7.1 谓词操作符
注:这些操作符同样可以用于 JOIN…ON 和 HAVING 语句中。
| 操作符 | 支持的数据类型 | 描述 |
|---|
| A=B | 基本数据类型 | 如果 A 等于 B 则返回 TRUE，反之返回 FALSE | | A<=>B | 基本数据类型 | 如果 A 和 B 都为 NULL，则返回 TRUE，如果一边为 NULL，返回 False | | A<>B, A!=B | 基本数据类型 | A 或者 B 为 NULL 则返回 NULL；如果 A 不等于 B，则返回TRUE，反之返回 FALSE | | A<B | 基本数据类型 | A 或者 B 为 NULL，则返回 NULL；如果 A 小于 B，则返回TRUE，反之返回 FALSE | | A<=B | 基本数据类型 | A 或者 B 为 NULL，则返回 NULL；如果 A 小于等于 B，则返回 TRUE，反之返回 FALSE | | A>B | 基本数据类型 | A 或者 B 为 NULL，则返回 NULL；如果 A 大于 B，则返回TRUE，反之返回 FALSE | | A>=B | 基本数据类型 | A 或者 B 为 NULL，则返回 NULL；如果 A 大于等于 B，则返回 TRUE，反之返回 FALSE | | A [NOT] BETWEEN B AND C | 基本数据类型 | 如果 A，B 或者 C 任一为 NULL，则结果为 NULL。如果 A 的值大于等于 B 而且小于或等于 C，则结果为 TRUE，反之为 FALSE。如果使用 NOT 关键字则可达到相反的效果。 | | A IS NULL | 所有数据类型 | 如果 A 等于 NULL，则返回 TRUE，反之返回 FALSE | | A IS NOT NULL | 所有数据类型 | 如果 A 不等于 NULL，则返回 TRUE，反之返回 FALSE | | IN(数值 1, 数值 2) | 所有数据类型 | 使用 IN 运算显示列表中的值 | | A [NOT] LIKE B | STRING 类型 | B 是一个 SQL 下的简单正则表达式，也叫通配符模式，如 果 A 与其匹配的话，则返回 TRUE；反之返回 FALSE。B 的表达式说明如下：‘x%’表示 A 必须以字母‘x’开头，‘%x’表示 A必须以字母’x’结尾，而‘%x%’表示 A 包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用 NOT 关键字则可达到相反的效果。 | | A RLIKE B, A REGEXP B | STRING 类型 | B 是基于 java 的正则表达式，如果 A 与其匹配，则返回TRUE；反之返回 FALSE。匹配使用的是 JDK 中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串 A 相匹配，而不是只需与其字符串匹配。 |
6.1.7.2 案例实操
hive (default)> select * from emp where sal=5000;
- (2) 查询工资在
500 到 1000 的员工信息
hive (default)> select * from emp where sal between 500 and 1000;
hive (default)> select * from emp where comm is null;
- (4) 查询工资是
1500 或 5000 的员工信息
hive (default)> select * from emp where sal IN (1500, 5000);
6.1.8 Like 和 RLike
6.1.8.1 使用 LIKE 运算选择类似的值
6.1.8.2 选择条件可以包含字符或数字:
% 代表零个或多个字符(任意个字符)。
_ 代表一个字符。
6.1.8.3 RLIKE 子句
RLIKE 子句是 Hive 中这个功能的一个扩展，其可以通过 Java 的正则表达式这个更强大的语言来指定匹配条件。
6.1.8.4 案例实操
hive (default)> select * from emp where ename LIKE 'A%';
hive (default)> select * from emp where ename LIKE '_A%';
hive (default)> select * from emp where ename RLIKE '[A]';
6.1.9 逻辑运算符（And/Or/Not）
6.1.9.1 案例实操
hive (default)> select * from emp where sal>1000 and deptno=30;
hive (default)> select * from emp where sal>1000 or deptno=30;
- (3) 查询除了 20 部门和 30 部门以外的员工信息
hive (default)> select * from emp where deptno not IN(30, 20);
6.2 分组
6.2.1 Group By 语句
GROUP BY 语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。
6.2.1.1 案例实操
hive (default)> select t.deptno, avg(t.sal) avg_sal from emp AS t group by t.deptno;
- (2) 计算
emp 每个部门中每个岗位的最高薪水
hive (default)> select t.deptno, t.job, max(t.sal) max_sal from emp AS t group byt.deptno, t.job;
6.2.2 Having 语句
6.2.2.1 having 与 where 不同点
- (1)
where 不能写在分组函数后面，而 having 可以写在分组函数后面。 - (2)
having 只用于 group by 分组统计语句。
6.2.2.2 案例实操
- (1) 求每个部门的平均薪水大于
2000 的部门
求每个部门的平均工资
hive (default)> select deptno, avg(sal) from emp group by deptno;
求每个部门的平均薪水大于 2000 的部门
hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;
6.3 Join 语句
6.3.1 等值 Join
Hive 支持通常的 SQL JOIN 语句。
6.3.3.1 案例实操
- (1) 根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称；
hive (default)> select e.empno, e.ename, d.deptno, d.dname from emp AS e
join dept AS d on e.deptno = d.deptno;
6.3.2 表的别名
6.3.2.1 好处
- (1) 使用别名可以简化查询。
- (2) 使用表名前缀可以提高执行效率。
6.3.2.2 案例实操
合并员工表和部门表
hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d
on e.deptno = d.deptno;
6.3.3 内连接
内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d
on e.deptno = d.deptno;
6.3.4 左外连接
左外连接：JOIN 操作符左边表中符合 WHERE 子句的所有记录将会被返回。
hive (default)> select e.empno, e.ename, d.deptno from emp e left join
dept d on e.deptno = d.deptno;
6.3.5 右外连接
右外连接：JOIN 操作符右边表中符合 WHERE 子句的所有记录将会被返回。
hive (default)> select e.empno, e.ename, d.deptno from emp e right join
dept d on e.deptno = d.deptno;
6.3.6 满外连接
满外连接：将会返回所有表中符合 WHERE 语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用 NULL 值替代。
hive (default)> select e.empno, e.ename, d.deptno from emp e full join
dept d on e.deptno = d.deptno;
6.3.7 多表连接
注意：连接 n 个表，至少需要 n-1 个连接条件。例如：连接三个表，至少需要两个连接条件。
数据准备
1700 Beijing
1800 London
1900 Tokyo
create table if not exists location(
loc int,
loc_name string
)
row format delimited fields terminated by '\t';
hive (default)> load data local inpath '/opt/module/datas/location.txt'
into table location;
hive (default)>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 任务。本例中会首先启动一个 MapReduce job 对表 e 和表 d 进行连接操作，然后会再启动一个MapReduce job 将第一个 MapReduce job 的输出和表 l;进行连接操作。
注意：为什么不是表 d 和表 l 先进行连接操作呢？这是因为 Hive 总是按照从左到右的顺序执行的。优化：当对 3 个或者更多表进行 join 连接时，如果每个 on 子句都使用相同的连接键的话，那么只会产生一个 MapReduce job。
6.3.8 笛卡尔积
- (1) 省略连接条件
- (2) 连接条件无效
- (3) 所有表中的所有行互相连接
hive (default)> select empno, dname from emp, dept;
6.4 排序
6.4.1 全局排序（Order By）
Order By：全局排序，只有一个 Reducer
6.4.1.1 使用 ORDER BY 子句排序
ASC（ascend）: 升序（默认）
DESC（descend）: 降序
6.4.1.2 ORDER BY 子句在 SELECT 语句的结尾
6.4.1.3 案例实操
hive (default)> select * from emp order by sal;
hive (default)> select * from emp order by sal desc;
6.4.2 按照别名排序
按照员工薪水的 2 倍排序
hive (default)> select ename, sal*2 twosal from emp order by twosal;
6.4.3 多个列排序
按照部门和工资升序排序
hive (default)> select ename, deptno, sal from emp order by deptno, sal;
6.4.4 每个 Reduce 内部排序（Sort By）
Sort By：对于大规模的数据集 order by 的效率非常低。在很多情况下，并不需要全局排序，此时可以使用 sort by。
Sort by 为每个 reducer 产生一个排序文件。每个 Reducer 内部进行排序，对全局结果集来说不是排序。
hive (default)> set mapreduce.job.reduces=3;
hive (default)> set mapreduce.job.reduces;
hive (default)> select * from emp sort by deptno desc;
- (4) 将查询结果导入到文件中（按照部门编号降序排序）
hive (default)> insert overwrite local directory '/opt/module/data/sortby-result'
> select * from emp sort by deptno desc;
6.4.5 分区（Distribute By）
Distribute By： 在有些情况下，我们需要控制某个特定行应该到哪个 reducer，通常是为了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by 类似 MR 中 partition（自定义分区），进行分区，结合 sort by 使用。- 对于
distribute by 进行测试，一定要分配多 reduce 进行处理，否则无法看到 distribute by 的效果。
6.4.5.1 案例实操：
- (1) 先按照部门编号分区，再按照员工编号降序排序。
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;
注意：
distribute by 的分区规则是根据分区字段的 hash码 与 reduce 的个数进行模除后，余数相同的分到一个区。Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前。
6.4.6 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;
注意：按照部门编号分区，不一定就是固定死的数值，可以是 20 号和 30 号部门分到一个分区里面去。
声明：本文是学习时记录的笔记，如有侵权请告知删除！
原视频地址:https://www.bilibili.com/video/BV1EZ4y1G7iL
|