Hive join优化（mapjoin，streamtable）

一、 Join语法

Hive支持的join语法：

join_table:    table_reference [INNER] JOIN table_factor [join_condition]  | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition  | table_reference LEFT SEMI JOIN table_reference join_condition  | table_reference CROSS JOIN table_reference [join_condition] (as of Hive 0.10)table_reference:    table_factor  | join_tabletable_factor:    tbl_name [alias]  | table_subquery alias  | ( table_references )join_condition:    ON expression

在Hive2.2.0以前，hive仅支持等值连接，即expression1 = expression2。现在Hive支持复杂的join查询

SELECT a.* FROM a JOIN b ON (a.id = b.id)SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department)SELECT a.* FROM a LEFT OUTER JOIN b ON (a.id <> b.id) --非等值查询SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2) --多表查询

二、 注意点

• 多表查询

 SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1) --语句1SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)  --语句2

上述HQL语句1和语句2仅在与表c关联时使用的key不同，但执行计划却会有很大不同。语句1因为两个JOIN使用的条件都b.key1，只会产生1个map/reduce job，而语句2会产生2个job。

• on和where

  join操作发生在where语句之前，如果是想对join的结果做过滤则把条件加在where后，否则应该加载on处。分析如下语句3:

SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)WHERE a.ds='2009-07-07' AND b.ds='2009-07-07' --语句3

语句3想实现表a左连接表b，然后根据a.ds和b.ds进行过滤，但实际上却达不到这样的效果。因为join操作发生在where之前，a LEFT JOIN b导致表b中如果没有与a相等的key值，所有列都会被填充为NULL，包括ds列，所以再在b.ds进行过滤是无效的，因为这部分的b.ds已经被填充为NULL,会多过滤掉一部分数据。

• mapjoin
  如果在进行join操作时，有一个表很小，则可以将join操作调整到map阶段执行。这就是典型的极大表和极小表关联问题。有两种解决方式：1.增加/*+ MAPJOIN(b) */标示；2.设置参数hive.optimize.bucketmapjoin = true，在

SELECT /*+ MAPJOIN(b) */ a.key, a.valueFROM a JOIN b ON a.key = b.key

• streamtable
  在多表级联时，一般大表都写在最后，因为写在最后的表默认使用stream形式加载，其他表的结果缓存在内存中。
  可以使用/*+ STREAMTABLE(a) */ 来标示具体哪个表使用stream形式。在表关联时，使用该标识来指出大表，能避免数据表过大导致占用内存过多而产生的问题。

SELECT /*+ STREAMTABLE(a) */ a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

三、参考资料

1.HIVE wiki