Hive07_多表查询

HIVE多表查询

1 Join 语句

1) 等值 Join

Hive 支持通常的 SQL JOIN 语句。

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

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

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;

3) 内连接

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

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

4) 左外连接

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

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

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) 满外连接

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

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

7) 多表连接

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

数据准备
1700 Beijing
1800 London
1900 Tokyo

1）创建位置表

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

2）导入数据

hive (default)> load data local inpath '/usr/soft/datas/location.txt' 
into table location;

3）多表连接查询

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。

8) 笛卡尔积

1）笛卡尔集会在下面条件下产生
（1）省略连接条件
（2）连接条件无效
（3）所有表中的所有行互相连接

2）案例实操

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

2 排序

1) 全局排序（Order By）

Order By：全局排序，只有一个 Reducer 1）使用 ORDER BY 子句排序
ASC（ascend）: 升序（默认）
DESC（descend）: 降序

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

3）案例实操

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

hive (default)> select * from emp order by sal;

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

hive (default)> select * from emp order by sal desc;

2) 按照别名排序

按照员工薪水的 2 倍排序

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

3) 多个列排序

按照部门和工资升序排序

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

4) 每个 Reduce 内部排序（Sort By）

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

Sort by 为每个 reducer 产生一个排序文件。每个 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 deptno desc;

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

hive (default)> insert overwrite local directory 
'/usr/soft/data/sortby-result'
select * from emp sort by deptno desc;

5) 分区（Distribute By）

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

1）案例实操：

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

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

注意：
? distribute by 的分区规则是根据分区字段的 hash 码与 reduce 的个数进行模除后，
余数相同的分到一个区。 ? Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前。

6) Cluster By

当 distribute by 和 sorts 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;

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