Hive Joins

Join Syntax

Hive 支持以下用于联接表的语法：

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_table

table_factor:
    tbl_name [alias]
  | table_subquery alias
  | ( table_references )

join_condition:
    ON expression

有关此连接语法的上下文，请参见Select Syntax。

join_condition:     ON equality_expression ( AND equality_expression )*

equality_expression:     expression = expression

Examples

编写联接查询时要考虑的一些要点如下：

• 允许使用复杂的连接表达式，例如

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)

是有效的联接。

• 同一查询中可以连接 2 个以上的表，例如

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

是有效的联接。

• 如果对于每个表在联接子句中使用相同的列，则 Hive 将多个表上的联接转换为单个 map/reduce 作业。

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 列，因此被转换为单个 map/reduce 作业。另一方面

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

被转换为两个 map/reduce 作业，因为在第一个联接条件中使用了 b 中的 key1 列，而在第二个连接条件中使用了 b 中的 key2 列。第一个 map/reduce 作业将 a 与 b 联接，然后将结果与 c 联接到第二个 map/reduce 作业中。

• 在联接的每个 map/reduce 阶段中，序列中的最后一个表将通过化简器流式传输，并在其中缓冲其他表。因此，通过组织表以使最大的表出现在序列的最后，它有助于减少在 reducer 中为连接键的特定值缓冲行所需的内存。例如在

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

所有这三个表都在一个 Map/归约作业中联接，并且表 a 和 b 的键的特定值的值被缓存在化简器的内存中。然后，对于从 c 中检索的每一行，将使用缓冲的行来计算联接。同样的

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

计算联接涉及两个 Map/归约作业。其中的第一个将 a 与 b 连接起来，并缓冲 a 的值，同时在减速器中流式传输 b 的值。这些作业中的第二个将缓冲第一个连接的结果，同时将 c 的值通过简化器流式传输。

• 在联接的每个 map/reduce 阶段中，可以通过提示指定要流式传输的表。例如在

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)

所有这三个表都在一个 Map/归约作业中联接，并且表 b 和 c 的键的特定值的值被缓冲在化简器的内存中。然后，对于从 a 检索的每一行，将使用缓冲的行来计算联接。如果省略 STREAMTABLE 提示，则 Hive 将流化 Connecting 最右边的表。

• 存在 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 的 LEFT，因此保留了 a 中的所有行；右外连接将保留 b 中的所有行，而全外连接将保留 a 中的所有行和 b 中的所有行。 OUTER JOIN 语义应符合标准 SQL 规范。

• 联接在出现子句之前发生。因此，如果您想限制联接的输出，则需求应在 WHERE 子句中，否则应在 JOIN 子句中。这个问题的一大困惑是分区表：

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'

将在 b 上加入 a，生成 a.val 和 b.val 的列表。但是，WHERE 子句也可以引用联接输出中的 a 和 b 的其他列，然后将其过滤掉。但是，只要 JOIN 中的一行找到 a 的键而 b 的键都没有，则 b 的所有列都将为 NULL， 包括 ds 列 。这就是说，您将过滤掉没有有效 b.key 的所有联接输出行，因此，您超出了 LEFT OUTER 的要求。换句话说，如果您在 WHERE 子句中引用 b 的任何列，则联接的 LEFT OUTER 部分无关紧要。相反，在进行外部联接时，请使用以下语法：

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

..结果是对联接的输出进行了预过滤，对于具有有效 a.key 但不匹配 b.key 的行，您将不会遇到后过滤麻烦。相同的逻辑适用于 RIGHT 和 FULL 连接。

• 连接不是可交换的！联接是左关联的，无论它们是 LEFT 联接还是 RIGHT 联接。

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

...首先在 b 上加入 a，将 a 或 b 中所有没有其他键的东西都扔掉。然后将精简表连接到 c。如果 a 和 c 中都存在一个键但 b 中不存在键，这将提供不直观的结果：整个行(包括 a.val1，a.val2 和 a.key)都被删除在“ a JOIN b”步骤中，因为不在 b。结果中没有 a.key，因此当它与 c 一起左移时，由于没有 c.key 与 a.key 匹配，因此 c.val 不会加入(因为 a 中的行已删除) )。同样，如果这是一个 RIGHT OUTER JOIN(而不是 LEFT)，我们最终会得到一个更奇怪的效果：NULL，NULL，NULL，c.val，因为即使我们将 a.key = c.key 指定为联接键，我们删除了所有与第一个 JOIN 不匹配的行。
为了获得更直观的效果，我们应该改为 FROM FROM c 左外连接 a ON(c.key = a.key)左外连接 b ON(c.key = b.key)。

• LEFT SEMI JOIN 以有效的方式实现了不相关的 IN/EXISTS 子查询语义。从 Hive 0.13 开始，使用subqueries支持 IN/NOT IN/EXISTS/NOT EXISTS 运算符，因此这些 JOIN 中的大多数不再需要手动执行。使用 LEFT SEMI JOIN 的限制是只能在连接条件(ON 子句)中引用右侧表，而不能在 WHERE 或 SELECT 子句等中引用。

SELECT a.key, a.value
  FROM a
  WHERE a.key in
   (SELECT b.key
    FROM B);

可以重写为：

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

• 如果除一个要连接的表之外的所有表都很小，则可以将其作为仅 Map 作业执行。查询

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

不需要减速器。对于 A 的每个 Map 器，B 都会被完全读取。限制是不能执行 a FULL/RIGHT OUTER JOIN b 。

• 如果要联接的表在联接列上进行了存储桶化，并且一个表中的存储桶数是另一表中存储桶数的倍数，则这些存储桶可以彼此连接。如果表 A 有 4 个存储桶，表 B 有 4 个存储桶，则以下联接

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

只能在 Map 器上完成。并非完全为 A 的每个 Map 器获取 B，而是仅获取所需的存储桶。对于上面的查询，A 的 Map 器处理存储桶 1 将仅获取 B 的存储桶 1.这不是默认行为，由以下参数控制

set hive.optimize.bucketmapjoin = true

• 如果要联接的表在联接列上进行了排序和存储，并且它们具有相同数量的存储桶，则可以执行排序合并联接。对应的存储桶在 Map 器处相互连接。如果 A 和 B 都有 4 个存储桶，

SELECT /*+ MAPJOIN(b) */ a.key, a.value
  FROM A a JOIN B b ON a.key = b.key

只能在 Map 器上完成。 A 的存储桶的 Map 器将遍历 B 的对应存储桶。这不是默认行为，需要设置以下参数：

set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
  set hive.optimize.bucketmapjoin = true;
  set hive.optimize.bucketmapjoin.sortedmerge = true;

MapJoin Restrictions

• 如果除一个要连接的表之外的所有表都很小，则可以将其作为仅 Map 作业执行。查询

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

不需要减速器。对于 A 的每个 Map 器，B 都会被完全读取。

• 不支持以下内容。

• union 后跟一个 MapJoin

  • 横向视图后跟一个 MapJoin

  • 减少接收器(分组依据/加入/排序依据/集群依据/分发依据)，其次是 MapJoin

  • MapJoin 之后是 union

  • MapJoin，然后加入

  • MapJoin 其次是 MapJoin

• 配置变量 hive.auto.convert.join(如果设置为 true)会在运行时自动将联接转换为 mapjoins，应使用它代替 mapjoin 提示。 mapjoin 提示仅应用于以下查询。

• 如果对所有 Importing 进行了存储分区或排序，则该联接应转换为存储分区的 Map 端连接或存储分区的排序合并联接。

• 考虑在不同的键上使用多个 mapjoin 的可能性：

select /*+MAPJOIN(smallTableTwo)*/ idOne, idTwo, value FROM
  ( select /*+MAPJOIN(smallTableOne)*/ idOne, idTwo, value FROM
    bigTable JOIN smallTableOne on (bigTable.idOne = smallTableOne.idOne)                                                   
  ) firstjoin                                                             
  JOIN                                                                  
  smallTableTwo ON (firstjoin.idTwo = smallTableTwo.idTwo)

不支持上述查询。如果没有 mapjoin 提示，则以上查询将作为 2 个仅 map 作业执行。如果用户事先知道 Importing 足够小以适合内存，则可以使用以下可配置参数来确保查询在单个 map-reduce 作业中执行。

• hive.auto.convert.join.noconditionaltask-Hive 是否启用基于 Importing 文件大小的关于将普通联接转换为 mapjoin 的优化。如果启用此参数，并且 n 向联接的表/分区的 n-1 个大小的总和小于指定的大小，则该联接将直接转换为 mapjoin(没有条件任务)。

  • hive.auto.convert.join.noconditionaltask.size-如果关闭了 hive.auto.convert.join.noconditionaltask，则此参数不起作用。但是，如果启用了该连接，并且 n 向联接的表/分区的 n-1 个大小的总和小于此大小，则该联接将直接转换为 mapjoin(没有条件任务)。默认值为 10MB。

Join Optimization

外部联接中的谓词下推

有关外部联接中的谓词下推的信息，请参见Hive 外部联接行为。

Hive 版本 0.11 中的增强功能

请参阅Join Optimization，以获取有关 Hive 版本 0.11.0 中引入的加入优化的增强功能的信息。在增强的优化(HIVE-3784和相关的 JIRA)中不再强调使用提示。