HIve SQL中的七种join
原始数据:
dept表:
10 ACCOUNTING 1700
20 RESEARCH 1800
30 SALES 1900
40 OPERATIONS 1700
emp表:
7369 MITH CLERK 7902 1980-12-17 800.0 NULL 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.0 300.0 30
7521 WARD SALESMAN 7698 1981-2-22 1250.0 500.0 30
7566 JONES MANAGER 7839 1981-4-2 2975.0 NULL 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.0 1400.0 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.0 NULL 30
7782 CLARK MANAGER 7839 1981-6-9 2450.0 NULL 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.0 NULL 20
7839 KING PRESIDENT NULL 1981-11-17 5000.0 NULL 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.0 NULL 30
7876 ADAMS CLERK 7788 1987-5-23 1100.0 NULL 20
7900 JAMES CLERK 7698 1981-12-3 950.0 NULL 30
7902 FORD ANALYST 7566 1981-12-3 3000.0 NULL 40
7934 MILLER CLERK 7782 1982-1-23 1300.0 NULL 50
创建部门表:
create table if not exists dept( deptno int,
dname string, loc int
)
row format delimited fields terminated by '\t';
创建员工表:
create table if not exists dept( deptno int,
dname string, loc 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;
Join语句:
使用表的别名的好处:
(1)可以简化查询
(2)使用表名前缀可以提高执行效率
1.内连接
//内连接:只有进行连接的两个表中都存在与连接条件相匹配的数据才会保留下来
hive (default)> select e.empno, e.ename, d.deptno, d.dname
from emp e
join dept d
on e.deptno = d.deptno;
注意:在这里面,如果select后面的字段是两张表都有的,则要加上别名,例如:别名.字段名(e.ename);如果字段只有一个表里面单独有的字段,则可以不用加别名,例如:ename字段是emp单独有的字段,可以不用加别名。但是在实际的开发过程中,每个字段都尽量加上别名,因为写上别名的查询效率要高,写了别名就到指定表里面查找,不写多张表都去查找。
2.左外连接
//左外连接：(以左表为主)JOIN 操作符左边表中符合 WHERE 子句的所有记录将会被返回
hive (default)> select e.empno, e.ename, d.deptno
from emp e
left join dept d
on e.deptno = d.deptno;
3.右外连接
//右外连接：(以右表为主)JOIN 操作符右边表中符合 WHERE 子句的所有记录将会被返回。
hive (default)> select e.empno, e.ename, d.deptno
from emp e
right join dept d
on e.deptno = d.deptno;
4.满外连接
满外连接：将会返回所有表中符合 WHERE 语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL 值替代。
//所有的数据
hive (default)> select e.empno, e.ename, d.deptno
from emp e
full join dept d
on e.deptno = d.deptno;
注意:满外连接的时候,是要把所有的字段都显示,但是在select的时候遇到公共字段,而我们取了别名,只能从取了别名的表中取数据,就导致另一个表里面的数据就缺少了.例如:A表和B表都有ID字段,A的id:10,20,30.B的id:10,20,40,这里面在select的时候选A.id或者B.id都不好,因为总会导致30或者40这个数据缺失.这里介绍了一个函数叫做,NVL
5. 取左表独有部分
hive (default)>select e.empno, e.ename, d.deptno
from emp e
where e.deptno not in
(select deptno
from dept
);
上面这种方法是通过子查询,还有另外一种写法:
hive (default)>select e.empno, e.ename, d.deptno
from emp e
left join dept d
on e.deptno=d.deptno
where d.deptno=null;
6. 取右表独有部分
hive (default)>select d.deptno,d.dnamefrom emp eright join dept don e.deptno=d.deptnowhere e.deptno is null;
7.查询两张表独有的数据
hive (default)>select e.empno,e.ename,nvl(e.deptno,d.deptno),d.dnamefrom emp efull join dept d on e.deptno = d.deptnowhere e.deptno is nulll or d.deptno is null;
笛卡尔积
hive中还有一个额外的连接方式,叫做笛卡尔积。
笛卡尔集会在下面条件下产生
（1）省略连接条件
（2）连接条件无效
（3）所有表中的所有行互相连接
举个例子来描述笛卡尔积,A表中有3条数据.B表中有4条数据,采用笛卡尔积来计算,则一共有3*4=12条数据。
hive (default)> select empno, dname from emp, dept;
穿插一个多表连接的知识点:
create table if not exists location( loc int,
loc_name string
)
row format delimited fields terminated by '\t';
表的数据:
1700 Beijing
1800 London
1900 Tokyo
//查询员工姓名,部门名称以及部门所在城市名称
select e.ename,d.dname,l.loc_name
from emp e
join dept d
on e.deptno=d.deptno
join location l
on d.loc=l.loc;
大多数情况下，Hive会对每对JOIN连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表e和表d进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表l;进行连接操作。
注意：为什么不是表d和表l先进行连接操作呢？这是因为Hive总是按照从左到右的顺序执行的。
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！