Hive（三）：join操作

join

join操作分六种：

1.内连接inner join…on…（可简写为join）

查询A、B两表共有的，即交集

select * from t_1 t1 join t_2 t2 on t1.id=t2.id;

2.左(外)连接left (outer) join…on…

左连接是显示左边的表的所有数据，如果有右边表的数据与之对应，则显示；否则显示null
select * from t_1 t1 left join t_2 t2 on t1.id=t2.id;

3.右(外)连接right (outer) join…on…

与左连接类似,右连接是显示右边的表的所有数据，如果有左边表的数据与之对应，则显示；否则显示null
select * from t_1 t1 right join t_2 t2 on t1.id=t2.id;

4.全连接full outer join…on…

左右两表的数据都显示，没有对应的数据，则显示null

select * from t_1 t1 full outer join t_2 t2 on t1.id=t2.id;

5.左半连接：semi join

semi join仅会显示t_1的数据，即左边表的数据。效率比左连接快，因为它会先拿到t_1的数据，然后在t_2中查找，只要查找到结果立马就返回t_1的数据。
select * from t_1 t1 left semi join t_2 t2 on t1.id=t2.id;

6.笛卡尔积

笛卡尔积是一种连接，表示左边表的行数乘以右边表的行数等于笛卡尔积结果集的大小。也就说如果左表表有4行数据，右边有3行数据，那么产生的数据将是4*3行数据。因此我们使用笛卡尔积会产生大量数据，他与其他类型不同，笛卡尔积不是并行执行的，而且使用mapreduce计算框架的话，任何方式都无法进行优化。如果使用错误的连接(join)语法可能会导致产生一个执行时间长，运行缓慢的笛卡尔积。
笛卡尔积在一些情况下还是有用的，例如:假如有一个表示用户偏好，另一个表表示新闻文章，同时有一个算法会推测出用户可能会喜欢读那些文章，这个时候就需要笛卡尔积生成所有用户和所有网页的对应关系集合。
左表

右表

select a.*，b.* from employee a  CROSS  join office b where a.oid=b.oid ;

7.注意事项

（1）join支持多个表连接，尽量把表从左到右，从小到大放置。

当对3个或者更多个表进行join连接时，如果每个on子句使用相同的连接键的话，那么只会产生一个 mapreduce job

hive 同时假定查询中最后一个表是最大的那个表，在对每行记录进行连接操作时，他会尝试将其他表缓存起来，然后扫描最后那个表进行计算，因此，用户要保证连续查询中的表的大小要从左到右依次增加的，对于大数据集会是很大的提速。

（2）多条件进行join时，一定要用括号：

tb_a join tb_b on (tb_a.a = tb_b.a and tb_a.b = tb_b.b )

（3）Hive怎样决定reducer个数？

Hadoop MapReduce程序中，reducer个数的设定极大影响执行效率，这使得Hive怎样决定reducer个数成为一个关键问题。遗憾的是Hive的估计机制很弱，不指定reducer个数的情况下，Hive会猜测确定一个reducer个数，基于以下两个设定：

1. hive.exec.reducers.bytes.per.reducer（默认为1000^3）

2. hive.exec.reducers.max（默认为999）

计算reducer数的公式很简单：

N=min(参数2，总输入数据量/参数1)
通常情况下，有必要手动指定reducer个数。考虑到map阶段的输出数据量通常会比输入有大幅减少，因此即使不设定reducer个数，重设参数2还是必要的。依据Hadoop的经验，可以将参数2设定为0.95*(集群中TaskTracker个数)。

参考链接

Hive学习：Hive连接JOIN用例详解
hive的join使用
【Hive】多条件join
HIVE中join、semi join、outer join
SQL join，left join ，right join ， inner join ， outer join用法解析及HIVE join 优化