Hive中你知道的Join小技巧

你知道的越多，你不知道的就越多

多表连接的mr个数

        如果我们Hive底层用的是mr引擎的话，那么我们进行表连接也算是一个mr。

        这里有两张表，表A和表B。

select a.* from a join b on a.id = b.id

        通过id进行连接，会产生一个mr job。

        可能大家会想，如果有三个表呢？A,B,C三个表，如果三个表进行表连接

select a.* from a join b on a.id = b.id join c on a.id = c.id

        当对3个或者更多个表进行Join连接时，如果每个On子句都是用相同的连接键的话，那么只会产生一个mapreduce任务。

小表Join大表

        Hive查询中，如果查询中最后一个表是最大的表的时候。会将小表缓存起来，然后扫描大表。为了使得开启mapjoin，那么表的大小从小到大，从左到右是依次增加的。

注意，Hive中右外连接和全外连接不支持。

 

左外和右外连接

        左外连接和右外连接。

        左外连接会返回左边表符合where语句的记录，左表作为主表，右表中匹配不上的字段值用Null代替。

        右外连接会返回右表中符合where语句的记录，右表作为主表，坐标中匹配不上的字段值用null代替。

        左外连接 主表就在left join的左边

        右外连接 主表就在right join的右边

左半开连接

        左半开连接会返回左边表的记录，前提是其记录对于右边表满足on语句中的判定条件。

        为了知道是怎么回事，用下面一个例子来解释

select s.ymd,s.symbol,s.price from 
stock s 
where s.ymd,s.smybol in (select d.ymd,d.symbol from div d)

             这条语句，很明显。查找S表中的ymd和price和symbol。前提是ymd,symbol要在D表中。如果使用mysql是一定可以跑出来的，但是在Hive里面不行。就像group by 别名，在hive里面是不行的，但是在mysql中是可以的。因为mysql中对于层级关联要求的不严格。

        既然在Hive中，我们无法使用这个语句，我们使用LEFT SEMI-Join来实现这条语句的功能。

前面说的，返回左表的记录，前提是右表满足On的条件

      

select s.ymd,s.symbol,s.price from 
stocks s 
LEFT SEMI JOIN div d 
on s.ymd = d.ymd AND s.symbol = d.symbol

        不过需要注意的是，SELECT和Where条件中，不能出现右表的字段。

        LEFT SEMI JOIN 比JOIN更高效，对于左表中一条指定的记录，在右表中一旦找到记录，就会停止查找。

笛卡尔积Join

        可能会有人疑惑，笛卡尔积join和普通的JOIN有什么区别呢？

        笛卡尔积join表示左边表的行数乘以右边表的行数等于笛卡尔积表的行数

        左表：M行 右表:N行 笛卡尔积表：M X N 行

Inner Join：只有进行连接的两个表都存在并且与连接标准相匹配的数据才会被保留下来

同时 Hive目前还不支持on中使用OR。