join

Join 语法

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 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)

是一个有效的连接。

• 如果每个表在连接子句中使用了相同的列，则Hive将多个表上的连接转换为单个映射/缩减作业

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

被转换成一个map / reduce作业，因为只有b的key1列参与了连接。另一方面

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作业与b连接，然后在第二个map / reduce作业中将结果与c连接。

• 在连接的每个映射/缩减阶段中，序列中的最后一个表都通过reducer进行流式传输，与其他缓冲区一样。因此，通过组织这些表，可以减少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 / reduce作业中，表a和b的键的特定值的值被缓存在reducer的内存中。然后，对于从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 / reduce参与计算连接。其中的第一个和b一起加入一个缓冲区，并缓存一个值，同时在reducers中传送b的值。其中一个作业的第二个缓冲了第一个连接的结果，同时通过reducer传送了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 / reduce作业中，并且表b和c的键的特定值的值被缓存在reducer中的内存中。然后，对于从a中检索的每一行，都会使用缓冲的行计算连接。如果省略了STREAMTABLE提示，则Hive会将最右边的表格加入连接。

• 存在LEFT，RIGHT和FULL OUTER连接，以提供更多的ON子句的控制权。例如，这个查询：

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

将返回一行中的每一行。当有一个等于a.key的b.key时，这个输出行将是a.val，b.val，当没有相应的b.key时，输出行将是a.val，NULL。从没有对应的a.key的行将被删除。语法“FROM LEFT OUTER JOIN b”必须写在一行上，以便理解它是如何工作的 - a是在这个查询中b的左边，所以a中的所有行都保留; RIGHT OUTER JOIN将保留来自b的所有行，并且FULL OUTER JOIN将保留来自b的所有行的所有行。OUTER JOIN语义应该符合标准的SQL规范。

• 连接发生在条款之前。因此，如果要限制连接的OUTPUT，则需要在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.val和b.val的列表。但是，WHERE子句也可以引用连接输出中的a和b的其他列，然后将其过滤掉。但是，每当JOIN的某行为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行的后过滤的麻烦。相同的逻辑适用于右和全连接。

• 连接不可交换！连接是左关联的，无论它们是左或右连接。

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或b中的所有内容。缩小的表然后在c上结合。这提供了不直观的结果，如果在a和c中都存在一个键，但不是b：整个行（包括a.val1，a.val2和a.key）在“a JOIN b”步骤中被删除，因为它不在b。结果没有a.key，所以当它与L是外部连接的时候，c.val没有进入，因为没有与a.key相匹配的c.key（因为a的那一行被删除了）。同样，如果这是一个RIGHT OUTER JOIN（而不是LEFT），我们最终会得到一个更奇怪的效果：NULL，NULL，NULL，c.val，因为即使我们指定了a.key = c.key作为连接键，我们删除了与第一个JOIN不匹配的所有行。
为了达到更直观的效果，我们应该做一个ON（c.key = a.key）LEFT OUTER JOIN b ON（c.key = b.key）。

• LEFT SEMI JOIN以有效的方式实现不相关的IN / EXISTS子查询语义。从Hive 0.13开始，使用子查询支持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)

• 如果除了一个连接的表格之外的所有表格都很小，则连接可以作为仅地图工作来执行。查询

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

不需要reduce。对于A的每个映射器，B都被完全读取。限制是不能执行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

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

set hive.optimize.bucketmapjoin = true

• 如果要连接的表在连接列上进行排序和压缩，并且具有相同数量的存储桶，则可以执行排序合并连接。相应的桶在映射器上相互连接。如果A和B都有4个桶，

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

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

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

排序

摘录译自：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy

Order, Sort, Cluster, and Distribute By

这描述了SELECT子句的ORDER BY，SORT BY，CLUSTER BY和DISTRIBUTE BY的语法。

Order By的语法

Hive QL中的ORDER BY语法类似于SQL语言中ORDER BY的语法。

colOrder: ( ASC | DESC )
colNullOrder: (NULLS FIRST | NULLS LAST)           -- (Note: Available in Hive 2.1.0 and later)
orderBy: ORDER BY colName colOrder? colNullOrder? (',' colName colOrder? colNullOrder?)*
query: SELECT expression (',' expression)* FROM src orderBy

“order by”子句有一些限制。在严格模式（即，hive.mapred.mode = strict）中，order by子句必须后跟一个“limit”子句。如果将hive.mapred.mode设置为nonstrict，则不需要limit子句。原因是为了强加所有结果的总顺序，必须有一个reducer来排序最终的输出。如果输出中的行数太大，单个减速器可能需要很长时间才能完成。
请注意，列是按名称指定的，而不是按位置编号指定的。但是，在Hive 0.11.0及更高版本中， 按以下方式进行配置时，可以按位置指定列：

• 对于Hive 0.11.0到2.1.x，将hive.groupby.orderby.position.alias设置 为true（默认值为false）。
• 对于Hive 2.2.0及更高版本， hive.orderby.position.alias 默认为true。

默认的排序顺序是升序（ASC）。
在Hive 2.1.0和更高版本中，支持在“order by”子句中为每个列指定空排序顺序。ASC顺序的缺省排序顺序为NULLS FIRST，而DESC顺序的缺省排序顺序为NULLS LAST。

在Hive 3.0.0及更高版本中，优化程序将删除子查询和视图中无限制的顺序。要禁用它，请将hive.remove.orderby.in.subquery设置 为false。

Sort By的语法

该排序语法类似于语法ORDER BY在SQL语言。

colOrder: ( ASC | DESC )
sortBy: SORT BY colName colOrder? (',' colName colOrder?)*
query: SELECT expression (',' expression)* FROM src sortBy

ive使用SORT BY中的列对行进行排序，然后将这些行传送给还原器。排序顺序将取决于列类型。如果该列是数字类型，那么排序顺序也是按数字顺序排列的。如果该列是字符串类型，那么排序顺序将是字典顺序。
在hive 3.0.0或更高版本，排序没有限制 的子查询 和 意见 将被优化去除。要禁用它，请将hive.remove.orderby.in.subquery设置 为false。

Sort By and Order By的区别

Hive支持对每个Reducer数据进行排序的SORT BY。“order by”和“sort by”之间的区别在于，前者保证输出中的总顺序，而后者只保证缩减器内的行的顺序。如果有多个还原剂，“排序”可能会给出部分有序的最终结果。

注意：可能会混淆SORT BY单独列和CLUSTER BY之间的区别。不同之处在于，CLUSTER BY按字段进行分区，如果有多个reducer分区，则为SORT BY，以便在整个还原器中统一分配数据（和加载）。

基本上，每个reducer中的数据将按照用户指定的顺序进行排序。以下示例显示

SELECT key, value FROM src SORT BY key ASC, value DESC

查询有2个reducer，每个的输出是：

0   5
0   3
3   6
9   1

0   4
0   3
1   1
2   5

Sort By设置方式

变换之后，变量类型通常被认为是字符串，这意味着数字数据将按字典顺序排序。为了克服这个问题，可以在使用SORT BY之前使用第二个带有强制转换的SELECT语句。

FROM (FROM (FROM src
            SELECT TRANSFORM(value)
            USING 'mapper'
            AS value, count) mapped
      SELECT cast(value as double) AS value, cast(count as int) AS count
      SORT BY value, count) sorted
SELECT TRANSFORM(value, count)
USING 'reducer'
AS whatever

Cluster By and Distribute By的语法

Cluster By和Distribution By主要用于Transform / Map-Reduce脚本。但是，如果需要对后续查询的查询输出进行分区和排序，则在SELECT语句中有时会很有用。

Cluster By是Distribute By和Sort By的缩写。

Hive使用Distribute By中的列在reducer中分配行。具有相同“ 分布依据”列的所有行将转到同一个还原器。但是，“ 分发依据”不保证对分布式密钥进行集群或排序属性。

例如，我们在以下5行中将x分配给2个reducer：

x1
x2
x4
x3
x1

reducer1得到

x1
x2
x1

reducer2得到

x4
x3

用户可以指定“ Distribute By” 和“ Sort By,” ，而不是指定“ Cluster By” ，因此分区列和排序列可以不同。通常情况下，分区列是排序列的前缀，但这不是必需的。

SELECT col1, col2 FROM t1 CLUSTER BY col1

SELECT col1, col2 FROM t1 DISTRIBUTE BY col1
SELECT col1, col2 FROM t1 DISTRIBUTE BY col1 SORT BY col1 ASC, col2 DESC

FROM (
  FROM pv_users
  MAP ( pv_users.userid, pv_users.date )
  USING 'map_script'
  AS c1, c2, c3
  DISTRIBUTE BY c2
  SORT BY c2, c1) map_output
INSERT OVERWRITE TABLE pv_users_reduced
  REDUCE ( map_output.c1, map_output.c2, map_output.c3 )
  USING 'reduce_script'
  AS date, count;