Hive学习之连接查询

       Hive支持连接查询，但有一些条件必须遵守，比如只支持相等查询，其它查询如不等式查询则不支持，还支持外连接，左半连接查询。另外Hive支持多于两个表以上的连接查询。下面为Hive连接查询的语法：

join_table:    table_reference 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_table table_factor:    tbl_name [alias]  | table_subquery alias  | ( table_references ) join_condition:    ON equality_expression ( AND equality_expression )* equality_expression:    expression = expression

      上面的语法仅列出了连接查询的连接部分，至于查询部分可以参考SELECT语法格式。或许很多人在这样的语法面前都失掉了一半耐心，其实该语法很简单，可以理解为四种连接查询，分别为相等连接查询、外连接查询、左半连接查询和交叉连接（笛卡尔积）查询。即使这样还是有很多人不喜欢这么复杂的语法（包括本人）而更喜欢隐式连接查询，即FROM关键字后面跟着用逗号分隔的表明而省略掉join关键字，幸好Hive-0.13.0提供了这样的支持，如：

SELECT * FROM table1 t1, table2 t2, table3 t3 WHERE t1.id = t2.id  AND  t2.id = t3.id  AND  t1.zipcode = '02535';

      下面通过一些列子对连接查询的某些特性进行学习。

• 首先是Hive中的连接查询只支持相等连接而不支持不等连接查询：

//有效的连接查询，相等连接查询SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department)//无效的连接查询，Hive不支持不等连接查询SELECT a.* FROM a JOIN b ON (a.id <> b.id) 

• 其次是Hive支持两个表以上的连接查询：

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

• 如果所有jion子句都使用了某个表的相同列，Hive将多个表的连接查询转换为一个map/reduce作业。如下所示：

//由于jion子句中使用了表b的key1列，该查询转换为一个作业SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)//由于表b的key1列用在第一个jion子句中，key2列用在第二个jion子句中，该查询被转换为两个作业，第一个作业执行表a和b的连接查询，第二个作业将第一个作业的结果与第二个jion子句进行连接查询SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

• 在每个join的map/reduce阶段，序列中的最后一个表是以流的方式通过reducers，而其它表则缓存在reducers的内存中。这样通过将最大的表放在序列的最后有助于减少reducers的内存需求。如：

//下面的查询中，从a和b中满足条件的行中提取a.val和b.val，并缓存在reducers的内存中，对于从c中提取的每行记录，与缓存中的行进行连接计算SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)//下面的查询包含两个作业，第一个作业缓存a的值，将b的值以流的方式通过reducers，第二个作业缓存结果，并将c的值以流的方式通过reducers。SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

• 在每个join的map/reduce阶段，可以通过提示指定要流处理的表：

//下面的查询中b.val和c.val缓存在reducers的内存中，对于从a中提取的每行记录，与缓存中的行进行连接计算。如果省略STREAMTABLE提示，jion中最右边的表被流处理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)

• LEFT，RIGHT和FULL OUTER，即左连接，右连接和全连接，为当ON从句不匹配时提供了更多的控制，如：

SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)

 该查询将返回a中的所有行，当a.key=b.key时返回a.val，b.val，没有对应的b.key时返回a.val，NULL，b中没有对应的a.key的行将会丢掉。"FROM a LEFT OUTER JOIN b"必须写在一行中为了理解该语句是如何工作的—a在b的左侧，a中的所有行被保留。RIGHT OUTER JOIN将保留b中所有的行，FULL OUTER JOIN将保留a中的所有行和b中的所有行。

• Join出现在WHERR子句之前。因此如果想限制连接查询的输出，限制条件应该出现在WHERE子句中，否则应该出现在JOIN子句中。当在分区表上执行连接查询时或许会有一些困惑：

//ds为分区列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'

当该左外连接在a中发现key而在b中没有发现key时，b中的列将为null，包括分区列ds，也就是将会过滤掉连接查询输出中没有有效b.key的列，或者说左外连接与WHERE子句中引用的b中的任何列无关。相反下面的语句将会提前根据条件过滤：

SELECT a.val, b.val FROM a LEFT OUTER JOIN bON (a.key=b.key AND b.ds='2009-07-07' AND a.ds='2009-07-07')

• Join连接是不可以交换的，无论是LEFT还是RIGHT连接都是左结合的。看下面的示例：

SELECT a.val1, a.val2, b.val, c.valFROM aJOIN b ON (a.key = b.key)LEFT OUTER JOIN c ON (a.key = c.key)

第一个连接a和b，丢掉所有不满足条件的记录，结果再与c进行左外连接。如果当key存在于a和c中但不在b中时，结果不是直观的。A中包含key的行丢弃掉，应为b中没有与key对应的行，这样结果将不包含key，再与c进行左外连接时将不包含c.val，该值将为null。如果是RIGHT OUTER JOIN的话，结果将为null，null，null，c.val，分析方法同分析左外连接一样。

• 左半连接以高效的方式实现了IN/EXISTS子查询。左半连接的限制是右侧的表只能出现在ON子句中，不能出现在WHERE或者SELECT子句中，如：

SELECT a.key, a.valueFROM aWHERE a.key in (SELECT b.key  FROM B);//两者是等价的SELECT a.key, a.valFROM a LEFT SEMI JOIN b on (a.key = b.key)

• 如果除了一个表以外的所有正在连接的表都比较小，连接操作可以只作为map作业执行，如：

SELECT /*+ MAPJOIN(b) */ a.key, a.valueFROM a join b on a.key = b.key

• 该查询不需要reducer任务，对于A的每个mapper，B被完全读取。但a FULL/RIGHT OUTER JOIN b不能被执行。如果正在连接查询的表在连接列上进行了分桶，并且一个表的桶数是另一个表的桶的倍数，桶可以彼此连接。如果表a有4个桶，b有4个桶，那么下面的连接查询可以仅适用mapper任务完成：

SELECT /*+ MAPJOIN(b) */ a.key, a.valueFROM a join b on a.key = b.key

与对于a的每个mapper任务都读取整个b不同，只读取被要求的桶。对于上面的查询，处理a的桶1的mapper任务只读取b的桶1，但这不是默认行为，可以使用下面的参数进行配置管理：

hive.optimize.bucketmapjoin = true     //默认值为false

•  如果表在排序和分桶的列上进行连接查询，且它们有相同的桶，那么合并查询可以被执行。对应的桶在mapper任务中彼此连接，该过程同上。但需要设置下面的参数：

hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;//默认为org.apache.hadoop.hive.ql.io.CombineHiveInputFormathive.optimize.bucketmapjoin = true; //默认值为falsehive.optimize.bucketmapjoin.sortedmerge = true;  //默认值为false