Hive查询操作详解

Hive

数据准备：


Tips：
（1）SQL 语言大小写不敏感。
（2）SQL 可以写在一行或者多行。
（3）关键字不能被缩写也不能分行。
（4）各子句一般要分行写。
（5）使用缩进提高语句的可读性。

1.基本查询（select…from）

1.全表和特定列查询

- 全表查询

hive (default)> select * from emp;
1

- 特定列查询

select empno, ename from emp;
1

2.列别名

• 重命名一个列
• 便于计算
• 紧跟列名，也可以在列名和别名之间加入关键字‘AS’

 hive (default)> 
select 
    ename AS name, 
    deptno dn 
from emp;
1
2
3
4
5

3.Limit语句

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

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

hive (default)> select * from emp limit 2,3; -- 表示从第2行开始，向下抓取3行
1

4.Where语句

• 使用where子句，将不满足条件的行过滤掉
• where子句紧随from子句

查询出薪水大于1000的所有员工。
hive (default)> select * from emp where sal > 1000;
1
2

注意：where子句中不能使用字段别名。

5.关系运算函数

select ename,sal from where sal between 1000 and 2000;
1

select ename,job from emp where job is  null;
1

select ename from emp where ename like '小%';
1

select ename from emp where ename like '小_';
1

---

6.逻辑运算函数

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

hive (default)> 
select 
    * 
from emp 
where sal > 1000 and deptno = 30;
1
2
3
4
5

（2）查询薪水大于1000，或者部门是30

hive (default)> 
select 
    * 
from emp 
where sal>1000 or deptno=30;
1
2
3
4
5

（3）查询除了20部门和30部门以外的员工信息

hive (default)> 
select 
    * 
from emp 
where deptno not in(30, 20);
1
2
3
4
5

7.聚合函数

• count(*)，表示统计所有行数，包含null值；
• count(某列)，表示该列一共有多少行，不包含null值；
• max()，求最大值，不包含null，除非所有值都是null；
• min()，求最小值，不包含null，除非所有值都是null；
• sum()，求和，不包含null。
• avg()，求平均值，不包含null。

（1）求总行数（count）

hive (default)> select count(*) cnt from emp;
1

（2）求工资的最大值（max）

hive (default)> select max(sal) max_sal from emp;
1

（3）求工资的最小值（min）

hive (default)> select min(sal) min_sal from emp;
1

（4）求工资的总和（sum）

hive (default)> select sum(sal) sum_sal from emp; 
1

（5）求工资的平均值（avg）

hive (default)> select avg(sal) avg_sal from emp;
1

---

2.分组查询（group by）

having与where不同点

• where后面不能写分组聚合函数，而having后面可以使用分组聚合函数。
• having只用于group by分组统计语句。
• where分组前过滤，having分组后过滤。

（1）求每个部门的平均薪水

hive (default)> 
select 
    deptno, 
    avg(sal) 
from emp 
group by deptno;
1
2
3
4
5
6

（2）求平均薪水大于2000的部门。

hive (default)>
select 
    deptno, 
    avg(sal) avg_sal 
from emp 
group by deptno  
having avg_sal > 2000;
1
2
3
4
5
6
7

---

3.联合查询（Join）

Hive支持通常的sql join语句，支持等值连接，也支持非等值连接。

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

hive (default)> 
select 
    e.empno, 
    e.ename, 
    d.dname 
from emp e --重命名
join dept d 
on e.deptno = d.deptno; --员工表和部门表中的部门编号相等
1
2
3
4
5
6
7
8

表的别名

• 使用别名可以简化查询。
• 区分字段的来源

（2）合并员工表和部门表。

hive (default)> 
select 
    e.*,
    d.* 
from emp e 
join dept d 
on e.deptno = d.deptno;
1
2
3
4
5
6
7

---

内连接

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

hive (default)> 
select 
    e.empno, 
    e.ename, 
    d.deptno 
from emp e 
join dept d 
on e.deptno = d.deptno;
1
2
3
4
5
6
7
8

---

左外连接

join操作符左边表中符合where子句的所有记录将会被返回。

hive (default)> 
select 
    e.empno, 
    e.ename, 
    d.deptno 
from emp e 
left join dept d 
on e.deptno = d.deptno;
1
2
3
4
5
6
7
8

右外连接

join操作符右边表中符合where子句的所有记录将会被返回。

hive (default)> 
select 
    e.empno, 
    e.ename, 
    d.deptno 
from emp e 
right join dept d 
on e.deptno = d.deptno;
1
2
3
4
5
6
7
8

---

满外连接

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

hive (default)> 
select 
    e.empno, 
    e.ename, 
    d.deptno 
from emp e 
full join dept d 
on e.deptno = d.deptno;
1
2
3
4
5
6
7
8

多表连接

• 连接n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。
  
  多表连接查询：

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;
1
2
3
4
5
6
7
8
9
10

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

---

笛卡尔积

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

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

hive (default)> 
select 
    empno, 
    dname 
from emp, dept;
1
2
3
4
5

联合（union & union all）

• union和union all都是上下拼接sql的结果，这点是和join有区别的，join是左右关联，union和union all是上下拼接。
• union去重，union all不去重。
• union和union all在上下拼接sql结果时有两个要求：
  （1）两个sql的结果，列的个数必须相同
  （2）两个sql的结果，上下所对应列的类型必须一致

将员工表30部门的员工信息和40部门的员工信息，利用union进行拼接显示。

hive (default)> 
select 
    *
from emp
where deptno=30
union
select 
    *
from emp
where deptno=40;
1
2
3
4
5
6
7
8
9
10

---

4.排序

全局排序（Order By）

Order By：全局排序，只有一个Reduce。

• 使用Order By子句排序
  asc（ascend）：升序（默认）
  desc（descend）：降序
• Order By子句在select语句的结尾

（1）查询员工信息按工资升序排列

hive (default)> 
select 
    * 
from emp 
order by sal;
1
2
3
4
5

（2）查询员工信息按工资降序排列

hive (default)> 
select 
    * 
from emp 
order by sal desc;
1
2
3
4
5

（3）按照别名排序

• 按照员工薪水的2倍排序。

hive (default)> 
select 
    ename, 
    sal * 2 twosal 
from emp 
order by twosal;
1
2
3
4
5
6

（4）多个列排序案例

按照部门和工资升序排序。

hive (default)> 
select 
    ename, 
    deptno, 
    sal 
from emp 
order by deptno, sal;
1
2
3
4
5
6
7

---

每个Reduce内部排序（Sort By）

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

• Sort by为每个reduce产生一个排序文件。

• 每个Reduce内部进行排序，对全局结果集来说不是排序。

1）设置reduce个数

hive (default)> set mapreduce.job.reduces=3;
1

2）查看设置reduce个数

hive (default)> set mapreduce.job.reduces;
1

3）根据部门编号降序查看员工信息

hive (default)> 
select 
    * 
from emp 
sort by deptno desc;
1
2
3
4
5

每个Reduce内部进行排序，对全局结果集来说不是排序（局部有序）。


4）将查询结果导入到文件中（按照部门编号降序排序）

hive (default)> insert overwrite local directory '/opt/module/hive/datas/sortby'
 select * from emp sort by deptno desc;
1
2

每个reduce内有序。

分区（Distribute By）

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

（1）先按照部门编号分区，再按照员工薪资排序

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

hive (default)> set mapreduce.job.reduces=3;
1

select 
    * 
from emp 
distribute by deptno 
sort by sal desc;
1
2
3
4
5

• distribute by的分区规则是根据分区字段的hash码与reduce的个数进行相除后，余数相同的分到一个区。
• Hive要求distribute by语句要写在sort by语句之前。
• 注意：按照部门编号分区，不一定就是固定死的数值，可以是20号和30号部门分到一个分区里面去。
  

分区排序（Cluster By）

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

（1）以下两种写法等价

hive (default)> 
select 
    * 
from emp 
cluster by deptno;

hive (default)> 
select 
    * 
from emp 
distribute by deptno 
sort by deptno;
1
2
3
4
5
6
7
8
9
10
11
12

注意：按照部门编号分区，不一定就是固定死的数值，可以是20号和30号部门分到一个分区里面去。