hive 基础知识及查询三

第 6 章 查询

官方链接https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select

6.1 基本查询（Select…From）

6.1.1 全表和特定列查询

1）全表查询
hive (default)> select * from emp;
2）选择特定列查询
hive (default)> select empno, ename from emp;
注意：

（1）SQL 语言大小写不敏感。

（2）SQL 可以写在一行或者多行

（3）关键字不能被缩写也不能分行

（4）各子句一般要分行写。

（5）使用缩进提高语句的可读性。

6.1.2 列别名

1）重命名一个列

2）便于计算

3）紧跟列名，也可以在列名和别名之间加入关键字‘AS’

4）案例实操

查询名称和部门

hive (default)> select ename AS name, deptno dn 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 按位取异或

6.1.4 常用函数

1）求总行数（count）
hive (default)> select count(*) cnt from emp;

2）求工资的最大值（max）
hive (default)> select max(sal) max_sal from emp;

3）求工资的最小值（min）
hive (default)> select min(sal) min_sal from emp;

4）求工资的总和（sum）
hive (default)> select sum(sal) sum_sal from emp;

5）求工资的平均值（avg）
hive (default)> select avg(sal) avg_sal from emp;

6.1.5 Limit 语句

典型的查询会返回多行数据。LIMIT 子句用于限制返回的行数。

hive (default)> select * from emp limit 5;

6.2 Where 语句

1）使用 WHERE 子句，将不满足条件的行过滤掉
2）WHERE 子句紧随 FROM 子句

3）案例实操

查询出薪水大于 1000 的所有员工

hive (default)> select * from emp where sal >1000;

6.2.1 比较运算符（Between/In/ Is Null）

1）下面表中描述了谓词操作符，这些操作符同样可以用于 JOIN…ON 和 HAVING 语句中。

操作符	支持的数据类型	描述
A=B	基本数据类型	如果 A 等于 B 则返回 TRUE，反之返回 FALSE
A<=>B	基本数据类型	如果 A 和 B 都为 NULL，则返回 TRUE，其他的和等号（=）操作 符的结果一致，如果任一为 NULL 则结果为 NULL
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 是一个正则表达式，如果 A 与其匹配，则返回 TRUE；反之返回 FALSE。匹配使用的是 JDK 中的正则表达式接口实现的，因为正则 也依据其中的规则。例如，正则表达式必须和整个字符串 A 相匹 配，而不是只需与其字符串匹配。

2）案例实操

（1）查询出薪水等于 5000 的所有员工

hive (default)> select * from emp where sal =5000;

（2）查询工资在 500 到 1000 的员工信息

hive (default)> select * from emp where sal between 500 and 1000;

（3）查询 comm 为空的所有员工信息

hive (default)> select * from emp where comm is null;

（4）查询工资是 1500 或 5000 的员工信息

hive (default)> select * from emp where sal IN (1500, 5000);

6.2.1 Like 和 RLike

1）使用 LIKE 运算选择类似的值

2）选择条件可以包含字符或数字:

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

_ 代表一个字符。

3）RLIKE 子句是 Hive 中这个功能的一个扩展，其可以通过 Java 的正则表达式这个更 强大的语言来指定匹配条件。

4）案例实操

（1）查找以 2 开头薪水的员工信息

hive (default)> select * from emp where sal LIKE ‘2%’;

（2）查找第二个数值为 2 的薪水的员工信息

6.2.3 逻辑运算符（And/Or/Not）

操作符 含义

AND 逻辑并

OR 逻辑或

NOT 逻辑否

案例实操

（1）查询薪水大于 1000，部门是 30

hive (default)> select * from emp where sal>1000 and deptno=30;

（2）查询薪水大于 1000，或者部门是 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.3 分组
6.3.1 Group By 语句

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

案例实操：

（1）计算 emp 表每个部门的平均工资

hive (default)> select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;

（2）计算 emp 每个部门中每个岗位的最高薪水

hive (default)> select t.deptno, t.job, max(t.sal) max_sal from emp t group by

t.deptno, t.job;

6.3.2 Having 语句

1）having 与 where 不同点
（1）where 针对表中的列发挥作用，查询数据；having 针对查询结果中的列发挥作用， 筛选数据。

（2）where 后面不能写聚合函数，而 having 后面可以使用聚合函数。

（3）having 只用于 group by 分组统计语句。
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.4 Join 语句

6.4.1 等值 Join

Hive 支持通常的 SQL JOIN 语句，但是只支持等值连接，不支持非等值连接。 案例实操

1）根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称；

hive (default)> select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;

6.4.2 表的别名

1）好处
（1）使用别名可以简化查询。

（2）使用表名前缀可以提高执行效率。

2）案例实操
合并员工表和部门表
hive (default)> select e.empno, e.ename, d.deptno from emp e join
dept d on e.deptno = d.deptno;

6.4.3 内连接

内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;

6.4.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.4.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.4.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.4.7 多表连接

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

6.4.8 笛卡尔积

1）笛卡尔集会在下面条件下产生
（1）省略连接条件

（2）连接条件无效

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

6.5 排序

6.5.1 全局排序（Order By）

Order By：全局排序，一个 Reducer

1）使用 ORDER BY 子句排序
ASC（ascend）: 升序（默认）

DESC（descend）: 降序

2）ORDER BY 子句在 SELECT 语句的结尾

6.5.2 按照别名排序

按照员工薪水的 2 倍排序

hive (default)> select ename, sal*2 twosal from emp order by twosal;

6.5.3 多个列排序

按照部门和工资升序排序

hive (default)> select ename, deptno, sal from emp order by deptno, sal ;

6.5.4 每个 MapReduce 内部排序（Sort By）

Sort By：每个 Reducer 内部进行排序，对全局结果集来说不是排序。

1）设置 reduce 个数
hive (default)> set mapreduce.job.reduces=3;

2）查看设置 reduce 个数
hive (default)> set mapreduce.job.reduces;

3）根据部门编号降序查看员工信息
hive (default)> select * from emp sort by empno desc;

4）将查询结果导入到文件中（按照部门编号降序排序）
hive (default)> insert overwrite local directory '/opt/module/datas/sortby-result’select * from emp sort by deptno desc;