++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
_++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
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
;