019 Hive的join相关查询 group by、sort by、order by distributeBy、clusterBy语句 Hive的union、union all语句

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
_++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

---

join相关

create table dept(
deptno int,
dname String,
loc String
)
row format delimited fields terminated by '\t'

load data local inpath ‘/home/olddata/dept’ into table dept;

create table emp(
empno int,
ename String,
job String,
mgr int,
hiredate String,
sal int,
comm int,
deptno int
)
row format delimited fields terminated by '\t'

;

mgr 上司的id

load data local inpath ‘/home/olddata/emp’ into table emp;

dept表

10	accounting	NEW YORK
20	research	Dallas
30	sales	Chicago
40	operations	Boston

emp表

7869	smith	clerk	7902	1980-12-17	800	200	20
7499	allen	salesman	7698	1981-02-20	1600 300 30 
7521	ward	salesman	7698	1981-02-22	1250	500	30
7566	jones	manager	7839	1981-04-02	2975	1000	20
7554	martin	salesman	7698	1981-09-28	1250	1400 30
7698	blake	manager	7839	1981-05-01	2850	1000	30
7782	clark	manager	7839	1981-06-09	2450	1000	10
7788	scott	analyst	7566	1987-04-19	3000	1000	20
7839	king	presidnent	1000	1981-11-17	5000	1000	10
7844	turner	salesman	7698	1981-09-08	1500	1000	30
7876	adams	clerk	7788	1987-05-23	1100	1000	20
7900	james	clerk	7698	1981-12-03	950	1000	30
7902	ford	analyst	7566	1981-12-02	300	1000	20
7934	miller	clerk	7782	1981-01-23	1300	1000	10

left join // left semi join // left outer join:
上述三者都是以左表为准 来匹配右表 右表匹配不上则用null来代替

select e1.ename
from emp e
left join emp e1
on e.mgr = e1.empno
where e.ename = “jones”;

这样两个表dept和tmp创建完成
然后加载数据
然后我们来查询某个人的上司

等待查询完成
结果应该是king
没有错误jones的上司是king 结果要挺长时间的 要MapReduce

这几个表是在olqf数据库里的（因为我学到这里关机了 第二天重新打开 想要重新上面的查询 不知道是在哪里的表 所以要看一下 在olqf里 所以要先 use olqf 然后再select…）

然后继续

######################################
right join // right outer join
hive不支持right semi join
上述二者都是以右表为准 来匹配左表 左表匹配不上则用null代替

然后再右连接查询试试 看看结果是不是和左连接一样

select e1.ename
from emp e
right join emp e1
on e.mgr = e1.empno
where e.ename = "jones"
;

同样结果是king

+++++++++++++++++++++++++++++++++++++++++
多表用 “,” 分开 // join // inner join:
上述三者都属于内连接

然后再内连接查询试试 看看是不是同样的结果

select e1.ename
from emp e, emp e1
where e.mgr = e1.empno and e.ename = "jones"
;

同样没有问题

然后

---

============================================================

hive对于join on的连接 只支持 等值连接 就是只能放等号 =
（and 也支持：就是on语句后面还可以加条件 join…on…and…）
不支持非等值连接：> < != >= <= <>

============================================================

hive提供对map端join的标识：

select
/*+MAPJOIN(d)*/
d.deptno
from dept d
left join emp e
on d.deptno = e.deptno
where e.deptno is null
;

hive 在0.7之前提供mapjoin()来标识为map-join来执行
0.7之后已经废弃 但是使用仍然生效
1.2之后默认开启map-side join

上面代码是查询emp中没有使用到dept表中的部门
即emp表后面是10 20 30 但是没有40这个部门 我们要把40查出来

看运行结果

结果40没有问题

同样我们把注释去掉 效果是一样的

同样是启动本地任务去运行map join
结果一样是40

================================================

group by:分组 通常和having搭配使用 通常和聚合函数搭配使用
带group by的语句 select后面的字段要么再group by后面出现过 要么在聚合函数中

比如 e.sal没有出现在group by之后 所以会出错

select e.deptno, e.sal
from emp e
group by e.deptno
;

这样就不出错

select e.deptno, sum(e.sal)
from emp e
group by e.deptno
;

我们看一下结果
第一行null先不管了 （emp数据表第二行1600 30 这个30没读出来）
那三个部门总工资分别如下

having：对分完组之后的结果集进行过滤

比如说我们再分完组后再进行过滤 去求平均工资大于三十

select e.deptno, avg(e.sal) as avgs
from emp e 
group by e.deptno
having avgs > 30
;

结果：

====================================================

sort by : 局部排序 指单个reducer结果集排序
order by ：全局排序 整个job的所有reducer中的数据都会排序（通常使用一个reducer）
当reducer数量为1时，两者都一样
通常和desc、asc搭配使用，默认是asc

演示一下
演示之前需要手动设置为3
默认是-1

set mapreduce.job.reduces=3;

设置完之后我们看看是不是排序的
按照员工编号倒排序

sort by 多个reducer时是局部排序

select e.empno, e.deptno
from emp e
sort by e.empno desc
;

结果 3个reducer倒排

order by

select e.empno, e.deptno
from emp e
order by e.empno desc
;

还是1个reducer

还是全局排序

====================================================================================

distribute by:类似于分区的功能

当distribute by 和 sort by同时存在时，distribute by在sort by前面。

select e.empno, e.deptno
from emp e
distribute by e.empno
sort by e.empno asc 
;

语句意思是
sort实现empno排序 升序 distribute实现以empno放到多个分区

（注意在运行之前我们已将默认 mapreduce.job.reduces=-1; 为-1的设置为3个分区 set mapreduce.job.reduces=3;）
果然是三个

cluster by ：兼有distribute by和sort by的功能，但是sort by需要是升序

select e.empno, e.deptno
from emp e
cluster by e.empno
;

同样也是3个分区

====================================================================================

union :
union all:
都是将多个结果集进行合并。

union ：去重并不排序
union all ：不去重排序，只是将两部分数据进行合并
单个union子句不能使用：orderBy clusterBy distributeBy sortBy limit

union 运行结果去重没有排序

select d.deptno, d.dname
from dept d
union
select e.deptno, e.ename
from emp e
;

union all ：不去重排序，只是将两部分数据进行合并

select e.empno, e.ename
from emp e
where e.empno > 7900
union all
select e.empno, e.ename
from emp e
;