Hive笔记-7

6.4 Join 语句

6.4.1 等值 Join

 Hive支持通常的sql join语句，但是只支持等值连接，不支持非等值连接

Join是对两个表或多个表进行横向的拼接:

1) 案例实操

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

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

Join之前(emp):

Join之前(dept):

Join之后:

可以看到把两张表横向的拼接在了一起

得到每一个location的人数:

 hive sql执行过程：

等值连接&不等值连接:

等值连接:

不等值连接:

Hive是在2.X版本之前不支持非等值连接,但在2.X之后支持非等值连接

6.4.2 表的别名

1) 好处

(1) 使用别名可以简化查询。

(2) 区分字段的来源。

2) 案例实操

合并员工表和部门表。

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

emp e 和 dept d

是使用了别名,在字段名后面可以写出他的别名便于查询.

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 0
    e.em pno,
    e.ename,
    d.deptno
from emp e
left join dept d
on e.deptno = d.deptno;

返回join左表的所有的行,

返回a,b两表的所有列

如果b表里没有a表所需要的值,则为null

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;

相当于并集

使用满外连接,需要使用full join 或者 full outer join

返回a表和b表的所有行,如何每列没有所需要的值则返回null

6.4.7 多表连接

多表关联:

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

 数据准备，在/opt/module/hive/datas/下：vim location.txt

部门位置id 部门位置

[atguigu@hadoop102 datas]$ vim location.txt

1700 北京
1800 上海
1900 深圳

关联两个表:

得到结果:

添加子查询:

查看结果：

关联三张表:

a,b两表先关联,之后再跟c表关联,关联完再跟d表关联,这就形成了多表连接.

1) 创建位置表

hive (default)>
create table if not exists location(
    loc int,           -- 部门位置id
    loc_name string   -- 部门位置
)
row format delimited fields terminated by '\t';

2) 导入数据

hive (default)> load data local inpath '/opt/module/hive/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总是按照从左到右的顺序执行的。