hive 连接（join）查询

1、内连接

复制代码hive> select b.*,a.name from userinfo2 b,userinfo a where a.userid=b.userid;hive> select b.*,a.name from userinfo2 b join userinfo a on a.userid=b.userid;

1
2
hive> select b.*,a.name from userinfo2 b,userinfo a
where a.userid=b.userid;

hive> select b.*,a.name from userinfo2 b join userinfo a on a.userid=b.userid;

2、外连接

复制代码#左联select b.*,a.name from userinfo2 b left join userinfo a on a.userid=b.userid; #右联 hive> select a.*,b.name from userinfo b right join userinfo2 a on a.userid=b.userid;

1
2
3
#左联
select b.*,a.name from userinfo2 b left join userinfo a on a.userid=b.userid;
#右联
hive> select a.*,b.name from userinfo b right join userinfo2 a on a.userid=b.userid;

全连接，两边都全显示

复制代码hive> select a.*,b.name from userinfo b right join userinfo2 a on a.userid=b.userid;

1
hive> select a.*,b.name from userinfo b right join userinfo2 a on a.userid=b.userid;

3、半连接

复制代码hive> select * from userinfo2 a where a.userid in (select userid from userinfo); #left semi join必须遵守一个规则：右表（userinfo）只能在on子句里出现，也不能在where子句以及select中出现 hive> select * from userinfo2 a left semi join userinfo b on (a.userid=b.userid);

1
2
3
hive> select * from userinfo2 a where a.userid in (select userid from userinfo);
#left semi join必须遵守一个规则：右表（userinfo）只能在on子句里出现，也不能在where子句以及select中出现
hive> select * from userinfo2 a left semi join userinfo b on (a.userid=b.userid);

4、map连接

复制代码在之前的内联查询中： select b.*,a.name from userinfo2 b join userinfo a on a.userid=b.userid; 如果有一个连接表小到可以放入内存，例如userinfo表，hive就可以较小的表放入每个mapper的内存中来执行连接,这就是map连接。 执行这个查询不使用reducer，因此这个查询对right和full join无效，因为只有在对所有输入上进行聚集的步骤（即reduce）才能检测到哪个数据行无法匹配。 map连接可以利用分桶表，需要设置hive.optimize.bucketmapjoin=true

1
2
3
4
5
在之前的内联查询中：
select b.*,a.name from userinfo2 b join userinfo a on a.userid=b.userid;
如果有一个连接表小到可以放入内存，例如userinfo表，hive就可以较小的表放入每个mapper的内存中来执行连接,这就是map连接。
执行这个查询不使用reducer，因此这个查询对right和full join无效，因为只有在对所有输入上进行聚集的步骤（即reduce）才能检测到哪个数据行无法匹配。
map连接可以利用分桶表，需要设置hive.optimize.bucketmapjoin=true

 5、子查询，Hive只支持在FROM子句中使用子查询，子查询必须有名字，并且列必须唯一：SELECT ... FROM(subquery) name ...

某些情况子查询可以出现在where子句中，本文中第三点半连接就是子查询，子查询可以出现在where的in或者是exists中。

复制代码#子查询的列名必须有唯一的列名。hive> select year,avg(max_data) from (select year,max(data) as max_data from weather_data group by year) mt group by year;

1
#子查询的列名必须有唯一的列名。
hive> select year,avg(max_data) from (select year,max(data) as max_data from weather_data group by year) mt group by year;