Hive 查询

Hive 查询

官网资料： 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]

一、基本查询

数据准备

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

创建表：

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';

导入数据：

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.1 全表和特定列查询

select * from emp;

select empno,ename from emp;

select empno as no,ename name from emp;

• 注意：
  • SQL语音大小写不敏感
  • SQL可以写在一行或者多行
  • 关键字不能被缩写也不能被分行
  • 各子句一般分行写
  • 使用缩进提高语句的可读性

1.2 算术运算符

算术运算符

1.3 常用函数

select count(*) cnt from emp;

select max(sal) max_sal from emp;

select min(sal) min_sal from emp;

select sum(sal) sum_sal from emp;

select avg(sal) avg_sal from emp;

1.4 Limit语句

Limit子句会限制返回的行数。

select * from emp limit 5;

1.5 Where语句

where子句紧随from子句。

select * from emp where sal > 1000;

1.6 比较运算符

操作符

例子：

select * from emp where sal = 5000;

select * from emp where sal between 500 and 1000;

select * from emp where comm is null;

select * from emp where sal in (1500, 5000);

1.7 Like和RLike

使用LIKE运算选择满足条件的值。

%：代表零个或多个字符（任意字符）。

_：代表一个字符。

RLike运算可通过Java正则表达式来指定匹配条件。

例子：

# 查找名字以A开头的员工信息
select * from emp where ename like 'A%';

# 查找名字中第二个字母为A的员工信息
select * from emp where ename like '_A%';

# 查找名字中带有A的员工信息
select * from emp where ename rlike '[A]';

1.8 逻辑运算符

逻辑操作符

# 查询薪水大于1000，部门是30的员工信息
select * from emp where sal > 1000 and deptno = 300;

# 查询薪水大于1000，或者部门是30的员工信息
select * from emp where sal > 1000 or deptno = 30;

# 查询除了20部门和30部门以外的员工信息
select * from emp where deptno not in (30,20);

二、分组

2.1 Group By 语句

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

例子：

select deptno, avg(sal) avg_sal from emp
group by deptno;

select deptno, job, max(sal) max_sal from emp
group by deptno, job;

2.2 Having 语句

1. having和where不同点

   1. where后面不能写分组函数，而having后面可以使用分组函数。
   2. having只用于group by分组统计语句。

2. 例子

   # 求每个部门的平均薪水大于2000的部门
   select deptno, avg(sal) avg_sal from emp
   group by deptno
   having avg_sal > 2000;
   

三、连接join

3.1 等值join

# 根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称
select e.empno, e.ename, d.deptno, d.dname from emp e
join dept d on e.deptno = d.deptno;

3.2 内连接

内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

select e.empno, e.ename, d.deptno from emp e 
join dept d on e.deptno = d.deptno;

3.3 左外连接

左外连接：JOIN 操作符左边表中符合 WHERE 子句的所有记录将会被返回。

select e.empno, e.ename, d.deptno from emp e 
left join dept d on e.deptno = d.deptno;

3.4 右外连接

右外连接：JOIN 操作符右边表中符合 WHERE 子句的所有记录将会被返回。

select e.empno, e.ename, d.deptno from emp e 
right join dept d on e.deptno = d.deptno;

3.5 满外连接

满外连接：将会返回所有表中符合 WHERE 语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用 NULL 值替代。

select e.empno, e.ename, d.deptno from emp e 
full join dept d on e.deptno = d.deptno;

大多数情况下，Hive 会对每对 JOIN 连接对象启动一个 MapReduce 任务。

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

3.6 笛卡尔积

• 笛卡尔积产生的条件
  1. 省略连接条件
  2. 连接条件无效
  3. 所有表中的所有行互相连接
• 例子

select empno,dname from emp, dept;

四、排序

4.1 全局排序

Order By：全局排序，只有一个Reduce。asc(ascend)：升序；desc(descend)：降序。

select * from emp order by sal;

select * from emp order by sal desc;

select ename, deptno, sal from emp order by deptno, sal desc;

select ename,sal*2 tsal from emp order by tsal;

4.2 每个reduce内部排序—sort by

对于大规模的数据集order by的效率非常低。在很多情况下，并不需要全局排序，此时可以使用sort by。

sort by为每个reduce产生一个排序文件。每个reduce内部进行排序，对全局结果集来说不是排序。

# 设置reduce个数
set mapreduce.job.reduces=3;

# 查看reduce个数
set mapreduce.job.reduces;

# 根据部门编号降序查看员工信息
select * from emp sort by deptno desc;

# 将查询结果导入到文件，按照部门标号降序排序
insert overwrite local directory '/opt/data/sortby-result'
select * from emp sort by deptno desc;

4.3 分区—distribute by

distribute by：在有些情况下，我们需要控制某个特定行应该到哪个 reducer，通常是为了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by 类似 MR 中 partition（自定义分区），进行分区，结合 sort by 使用。

对于 distribute by 进行测试，一定要分配多 reduce 进行处理，否则无法看到 distributeby 的效果。

例子：

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 语句之前。

4.4 cluster by

当 distribute by 和sorts by 字段相同时，可以使用 cluster by 方式。

cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是升序排序，不能指定排序规则为 ASC 或者 DESC。

select * from emp distribute by deptno sort by deptno;

select * from emp cluster by deptno;
# 两者等价