Hive 中文查询文档

查询语法

翻译版

原文：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select

选择语法

[WITH CommonTableExpression (, CommonTableExpression)*]    (Note: Only available starting with Hive 0.13.0)
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
  [ORDER BY col_list]
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT [offset,] rows]

• SELECT语句可以是联合查询或另一个查询的子查询的一部分。
• table_reference指示查询的输入。它可以是常规表，视图，联接构造或子查询。
• 表名和列名不区分大小写。
  • 在Hive 0.12和更早版本中，表和列名称中仅允许使用字母数字和下划线字符。
  • 在Hive 0.13和更高版本中，列名称可以包含任何 Unicode 字符（请参阅 HIVE-6013）。反引号（```）中指定的任何列名均按字面意义处理。在反引号字符串中，使用双反引号（````）表示反引号字符。
  • 要恢复到0.13.0之前的行为并将列名称限制为字母数字和下划线字符，请将配置属性设置 hive.support.quoted.identifiers 为 none。在此配置中，带反引号的名称被解释为正则表达式。有关详细信息，请参阅 列名中支持带引号的标识符 （附加到 HIVE-6013）。另请参见下面的REGEX列规范。
• 简单查询。例如，以下查询从表t1中检索所有列和所有行。

SELECT * FROM t1 

注意

从Hive 0.13.0开始，FROM是可选的（例如SELECT 1+1）。

要获取当前数据库（从Hive 0.13.0开始），请使用current_database（）函数：

SELECT current_database()

要指定数据库，请使用数据库名称来限定表名（db_name.table_name从Hive 0.7开始的“ ” ），或者在查询语句之前发出USE语句（从Hive 0.6开始 ）。

“ db_name.table_name”允许查询访问不同数据库中的表。

USE为所有后续的HiveQL语句设置数据库。用关键字“ default”重新发出，以重置为默认数据库。

USE database_name;
SELECT query_specifications;
USE default;

条件查询

WHERE条件是一个布尔表达式。例如，以下查询仅返回美国区域中金额大于10的那些销售记录。Hive 在WHERE子句中支持许多运算符和UDF：

SELECT * FROM sales WHERE amount > 10 AND region = "US"

从Hive 0.13开始，WHERE子句支持某些类型的子查询。

ALL和DISTINCT子句

ALL和DISTINCT选项指定是否应返回重复的行。如果没有给出这些选项，则默认值为ALL（返回所有匹配的行）。DISTINCT指定从结果集中删除重复的行。注意，Hive从版本1.1.0（HIVE-9194）开始支持SELECT DISTINCT * 。

hive> SELECT col1, col2 FROM t1
    1 3
    1 3
    1 4
    2 5
hive> SELECT DISTINCT col1, col2 FROM t1
    1 3
    1 4
    2 5
hive> SELECT DISTINCT col1 FROM t1
    1
    2

ALL和DISTINCT也可以在UNION子句中使用- 有关更多信息，请参见联合语法。

基于分区的查询

通常，SELECT查询会扫描整个表（不用于采样）。如果使用PARTITIONED BY子句创建的表，则查询可以进行分区修剪，并且仅扫描与查询指定的分区相关的表的一部分。如果在WHERE子句或JOIN的ON子句中指定了分区谓词，则Hive当前会进行分区修剪。例如，如果表page_views是按列日期进行分区的，则以下查询将检索2008-03-01至2008-03-31之间仅几天的行。

SELECT page_views.*
FROM page_views
WHERE page_views.date >= '2008-03-01' AND page_views.date <= '2008-03-31'

如果一个表page_views与另一个表dim_users连接在一起，则可以在ON子句中指定分区范围，如下所示：

SELECT page_views.*
FROM page_views JOIN dim_users
  ON (page_views.user_id = dim_users.id AND page_views.date >= '2008-03-01' AND page_views.date <= '2008-03-31')

• 另请参阅 分区过滤器语法。
• 另请参阅分组依据。
• 另请参见排序依据/群集依据/分配依据/排序依据。

分组条件 HAVING

Hive在0.7.0版中添加了对HAVING子句的支持。在旧版本的Hive中，可以通过使用子查询来实现相同的效果，例如：

SELECT col1 FROM t1 GROUP BY col1 HAVING SUM(col2) > 10

也可以表示为

SELECT col1 FROM (SELECT col1, SUM(col2) AS col2sum FROM t1 GROUP BY col1) t2 WHERE t2.col2sum > 10

限制条件

LIMIT子句可用于约束SELECT语句返回的行数。

LIMIT接受一个或两个数字参数，这两个参数都必须是非负整数常量。

第一个参数指定要返回的第一行的偏移量（从 Hive 2.0.0开始），第二个参数指定要返回的最大行数。

当给出单个参数时，它代表最大行数，偏移量默认为0。

以下查询返回5个任意客户

SELECT * FROM customers LIMIT 5

以下查询返回要创建的前5个客户

SELECT * FROM customers ORDER BY create_date LIMIT 5

以下查询将要创建的第3个客户返回第7个客户

SELECT * FROM customers ORDER BY create_date LIMIT 2,5

REGEX查询

如果配置属性hive.support.quoted.identifiers 设置为 ，则SELECT语句可以在Hive低于0.13.0的版本中或在0.13.0及更高版本中采用基于正则表达式的列规范 none。

• 我们使用Java regex语法。尝试http://www.fileformat.info/tool/regex.htm进行测试。
• 以下查询选择ds和hr以外的所有列。

SELECT `(ds|hr)?+.+` FROM sales

更多选择语法

有关SELECT语句的其他语法和功能，请参见以下文档：

• 通过...分组
• 排序/顺序/群集/分发者
• 加入
  • 蜂巢加入
  • 加盟优化
  • 外连接行为
• 联盟
• 表样本
• 子查询
• 虚拟列
• 运算符和UDF
• 侧视图
• 窗口化，覆盖和分析
• 常用表表达式

分组查询

原文地址：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+GroupBy

分组查询

groupByClause: GROUP BY groupByExpression (, groupByExpression)*
 
groupByExpression: expression
 
groupByQuery: SELECT expression (, expression)* FROM src groupByClause?

在 groupByExpression列中，是通过名称而不是位置编号指定的。但是，在Hive 0.11.0和更高版本中，按以下方式配置时可以按位置指定列：

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

简单的例子

为了计算表中的行数：

SELECT COUNT(*) FROM table2;

请注意，对于不包含HIVE-287的Hive版本，您需要使用COUNT（1）代替COUNT（*）。

为了按性别计算不同用户的数量，可以编写以下查询：

INSERT OVERWRITE TABLE pv_gender_sum
SELECT pv_users.gender, count (DISTINCT pv_users.userid)
FROM pv_users
GROUP BY pv_users.gender;

可以同时完成多个聚合，但是任何两个聚合都不能具有不同的DISTINCT列。例如，以下操作是可能的，因为count（DISTINCT）和sum（DISTINCT）指定同一列：

INSERT OVERWRITE TABLE pv_gender_agg
SELECT pv_users.gender, count(DISTINCT pv_users.userid), count(*), sum(DISTINCT pv_users.userid)
FROM pv_users
GROUP BY pv_users.gender;

请注意，对于不包含HIVE-287的Hive版本，您需要使用COUNT（1）代替COUNT（*）。

但是，不允许以下查询。我们不允许在同一查询中使用多个DISTINCT表达式。

INSERT OVERWRITE TABLE pv_gender_agg
SELECT pv_users.gender, count(DISTINCT pv_users.userid), count(DISTINCT pv_users.ip)
FROM pv_users
GROUP BY pv_users.gender;

选择语句并按子句分组

使用group by子句时，select语句只能包含group by子句中包含的列。当然，您也可以count在select语句中具有尽可能多的聚合函数（例如）。
让我们举一个简单的例子

CREATE TABLE t1(a INTEGER, b INTGER);

上表中的分组查询可能类似于：

SELECT
   a,
   sum(b)
FROM
   t1
GROUP BY
   a;

上面的查询之所以有效，是因为select子句包含a（group by键）和一个聚合函数（sum(b)）。

但是，下面的查询不工作：

SELECT
   a,
   b
FROM
   t1
GROUP BY
   a;

由于仅进行分组a，b因此Hive应该为该组显示什么值a=100？有人可能会说它应该是第一个值或最低值，但我们所有人都同意有多种可能的选择。Hive通过使select子句中不包含group by子句中的列变为无效SQL（准确地说是HQL）来消除这种猜测。

高级功能

多组插入

聚合或简单选择的输出可以进一步发送到多个表中，甚至发送到hadoop dfs文件中（然后可以使用hdfs实用程序对其进行操作）。例如，如果与性别细分一起，需要按年龄查找唯一页面浏览的细分，则可以通过以下查询来实现：

FROM pv_users
INSERT OVERWRITE TABLE pv_gender_sum
  SELECT pv_users.gender, count(DISTINCT pv_users.userid)
  GROUP BY pv_users.gender
INSERT OVERWRITE DIRECTORY '/user/facebook/tmp/pv_age_sum'
  SELECT pv_users.age, count(DISTINCT pv_users.userid)
  GROUP BY pv_users.age;

分组依据的地图端聚合

hive.map.aggr控制我们如何进行聚合。默认为false。如果将其设置为true，则Hive将直接在map任务中进行一级聚合。
通常，这可以提高效率，但可能需要更多内存才能成功运行。

set hive.map.aggr=true;
SELECT COUNT(*) FROM table2;

请注意，对于不包含HIVE-287的Hive版本，您需要使用COUNT（1）代替COUNT（*）。

分组集，多维数据集，汇总和GROUPING__ID函数

版

在Hive版本0.10.0中添加了分组集，CUBE和ROLLUP运算符以及GROUPING__ID函数。

有关这些聚合运算符的信息，请参见增强的聚合，多维数据集，分组和汇总。

另请参阅JIRA：

• HIVE-2397支持，并提供汇总选项
• HIVE-3433在Hive中实施CUBE和ROLLUP运算符
• HIVE-3471在Hive中实施分组集
• HIVE-3613实现grouping_id函数

Hive版本0.11.0中的新功能：

• HIVE-3552 HIVE-3552高效的方式，用于为大量分组设置键执行多维数据集/汇总/分组设置

排序，排序，聚类和分配

翻译版

英文地址：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy

这描述了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子句。原因是为了强加所有结果的总顺序，必须有一个减速器才能对最终输出进行排序。如果输出中的行数太大，则单个减速器可能需要很长时间才能完成。

请注意，列是通过名称而不是位置编号指定的。但是，在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。

排序依据的语法

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

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

Hive使用SORT BY中的列对行进行排序，然后再将行提供给reducer。排序顺序将取决于列类型。如果该列是数字类型，则排序顺序也是数字顺序。如果列是字符串类型，则排序顺序将是字典顺序。

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

排序依据和排序依据之间的区别

Hive支持SORT BY，可对每个reducer的数据进行排序。“ order by”和“ sort by”之间的区别在于，前者保证输出中的总顺序，而后者仅保证精简器中行的排序。如果存在多个减速器，则“排序依据”可能会给出部分排序的最终结果。

注意：关于单个列的单独SORT BY与CLUSTER BY之间的区别可能会造成混淆。区别在于，如果有多个reducer分区，则CLUSTER BY按字段划分，而SORT BY则是随机划分，以便将数据（和负载）均匀地分布在reducer上。

基本上，每个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之前使用第二个带有强制类型转换的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和Distribute By主要与Transform / Map-Reduce脚本一起使用。但是，如果需要对查询的输出进行分区和排序以用于后续查询，有时在SELECT语句中很有用。

Cluster By是Distribute By和Sort By的捷径。

蜂巢使用在列分发由该行减速器之间分配。具有相同“ 分配依据”列的所有行将转到相同的reducer。但是，“ 分发依据”不保证分布式键上的聚类或排序属性。

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

x1
x2
x4
x3
x1

减速机1

x1
x2
x1

减速机2

x4
x3

请注意，具有相同键x1的所有行都可以保证分配给同一归约器（在这种情况下为归约器1），但不能保证它们聚集在相邻位置。

相反，如果我们使用Cluster By x，则两个化简器将进一步对x上的行进行排序：

减速机1

x1
x1
x2

减速机2

x3
x4

请注意，具有相同键x1的所有行都可以保证分配给同一归约器（在这种情况下为归约器1），但不能保证它们聚集在相邻位置。

相反，如果我们使用Cluster By x，则两个化简器将进一步对x上的行进行排序：

减速机1

x1
x1
x2

减速机2

x3
x4

用户可以指定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;

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

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

0.13.0+版：隐式联接表示法

从Hive 0.13.0开始，支持隐式联接表示法（请参见HIVE-5558）。这允许FROM子句连接以逗号分隔的表列表，而省略JOIN关键字。例如：

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

版本0.13.0+：不合格的列引用

从Hive 0.13.0开始，在连接条件中支持不合格的列引用（请参阅HIVE-6393）。Hive尝试根据Join的输入来解决这些问题。如果不合格的列引用解析为多个表，则Hive会将其标记为歧义引用。

例如：

CREATE TABLE a (k1 string, v1 string);`
`CREATE TABLE b (k2 string, v2 string);
SELECT k1, v1, k2, v2`
`FROM a JOIN b ON k1 = k2; 

版本2.2.0+：ON子句中的复杂表达式

从Hive 2.2.0开始，支持ON子句中的复杂表达式（请参阅HIVE-15211和HIVE-15251）。在此之前，Hive不支持不是相等条件的联接条件。

特别是，连接条件的语法受到如下限制：

join_condition:
    ON equality_expression ( AND equality_expression )*
equality_expression:
    expression = 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)

是有效的联接。

• 同一查询中可以连接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将多个表上的联接转换为单个映射/减少作业。

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)

所有这三个表都在一个映射/归约作业中联接，并且表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)

计算联接涉及两个映射/归约作业。其中的第一个将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)

所有这三个表都在一个映射/归约作业中联接，并且表b和c的键的特定值的值被缓冲在化简器的内存中。然后，对于从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中的每一行返回一行。当存在等于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部分无关紧要。而是在进行OUTER JOINing时，使用以下语法：

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开始，使用子查询支持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
  

  不需要减速器。对于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
  

  只能在映射器上完成。并非完全为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
  

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

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

MapJoin限制

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

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

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

• 不支持以下内容。

  • 联盟后跟一个MapJoin
  • 横向视图后跟一个MapJoin
  • 减少接收器（分组依据/加入/排序依据/集群依据/分发依据），其次是MapJoin
  • MapJoin之后是联盟
  • MapJoin，然后加入
  • MapJoin之后为MapJoin

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

  • 如果对所有输入进行了存储分区或排序，则应将联接转换为存储分区的地图端联接或存储分区的排序合并联接。

• 考虑在不同的键上使用多个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作业执行。如果用户事先知道输入足够小以适合内存，则可以使用以下可配置参数来确保查询在单个map-reduce作业中执行。

• hive.auto.convert.join.noconditionaltask-Hive是否启用基于输入文件大小的关于将普通联接转换为mapjoin的优化。如果启用此参数，并且n向联接的表/分区的n-1个大小的总和小于指定的大小，则该联接将直接转换为mapjoin（没有条件任务）。
• hive.auto.convert.join.noconditionaltask.size-如果关闭了hive.auto.convert.join.noconditionaltask，则此参数无效。但是，如果启用了该连接，并且n向联接的表/分区的n-1个大小的总和小于此大小，则该联接将直接转换为mapjoin（没有条件任务）。默认值为10MB。

JOIN优化

外部联接中的谓词下推

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

Hive版本0.11中的增强功能

有关在Hive版本0.11.0中引入的对连接优化的增强功能的信息，请参见连接优化。在增强的优化（HIVE-3784和相关的JIRA）中，不再强调使用提示。

Union 语法

联合语法

select_statement UNION [ALL | DISTINCT] select_statement UNION [ALL | DISTINCT] select_statement ...

UNION用于将来自多个SELECT语句的结果合并为一个结果集。

• 1.2.0之前的Hive版本仅支持UNION ALL（袋联合），在这种情况下不会消除重复的行。
• 在Hive 1.2.0 和更高版本中，UNION的默认行为 是从结果中删除重复的行。可选的 DISTINCT 关键字除了默认关键字外没有其他作用，因为它还指定了重复行删除。使用可选的 ALL 关键字时，不会发生重复行删除，并且结果包括所有SELECT语句中的所有匹配行。

您可以在同一查询中混合使用UNION ALL和UNION DISTINCT。处理混合的UNION类型，以使DISTINCT联合覆盖其左侧的所有ALL联合。可以通过使用UNION DISTINCT显式地生成DISTINCT联合，也可以通过使用UNION隐式地生成，而没有后面的DISTINCT或ALL关键字。

每个select_statement返回的列的数量和名称必须相同。否则，将引发架构错误。

FROM子句中的UNION

如果必须对UNION的结果进行一些其他处理，则可以将整个语句表达式嵌入到FROM子句中，如下所示：

SELECT *
FROM (
  select_statement
  UNION ALL
  select_statement
) unionResult

例如，如果我们假设有两个不同的表来跟踪哪个用户发布了视频并且哪个用户发布了评论，则以下查询将UNION ALL的结果与用户表结合在一起，以为所有用户创建一个带注释的流视频发布和评论发布事件：

SELECT u.id, actions.date
FROM (
    SELECT av.uid AS uid
    FROM action_video av
    WHERE av.date = '2008-06-03'
    UNION ALL
    SELECT ac.uid AS uid
    FROM action_comment ac
    WHERE ac.date = '2008-06-03'
 ) actions JOIN users u ON (u.id = actions.uid)

DDL中的并集和插入语句

可以在视图，插入和CTAS（将表创建为select）语句中使用并集。一个查询可以包含多个UNION子句，如上面的语法所示。

适用条款

要将ORDER BY，SORT BY，CLUSTER BY，DISTRIBUTE BY或LIMIT应用于单个SELECT，请将子句放在括住SELECT的括号内：

SELECT key FROM (SELECT key FROM src ORDER BY key LIMIT 10)subq1
UNION
SELECT key FROM (SELECT key FROM src1 ORDER BY key LIMIT 10)subq2

要将ORDER BY，SORT BY，CLUSTER BY，DISTRIBUTE BY或LIMIT子句应用于整个UNION结果，请将ORDER BY，SORT BY，CLUSTER BY，DISTRIBUTE BY或LIMIT放在最后一个之后。下面的示例同时使用ORDER BY和LIMIT子句：

SELECT key FROM src
UNION
SELECT key FROM src1 
ORDER BY key LIMIT 10

模式匹配的列别名

UNION在表达式列表的两边都希望有相同的架构。结果，以下查询可能会失败，并显示一条错误消息，例如“ FAILED：SemanticException 4:47联合双方的模式都应匹配”。

INSERT OVERWRITE TABLE target_table
  SELECT name, id, category FROM source_table_1
  UNION ALL
  SELECT name, id, "Category159" FROM source_table_2

在这种情况下，列别名可用于强制使用相等的架构：

INSERT OVERWRITE TABLE target_table
  SELECT name, id, category FROM source_table_1
  UNION ALL
  SELECT name, id, "Category159" as category FROM source_table_2

列类型转换

在 2.2.0版HIVE-14251之前 ，Hive尝试在Hive类型组之间执行隐式转换。随着HIVE-14251的更改， Hive将仅在每个类型组（包括字符串组，数字组或日期组）内执行隐式转换，而不跨组执行隐式转换。为了合并来自不同组的类型，例如字符串类型和日期类型，在查询中需要从字符串到日期或从日期到字符串的显式转换。

SELECT name, id, cast('2001-01-01' as date) d FROM source_table_1
UNION ALL
SELECT name, id, hiredate as d FROM source_table_2

版本信息

在Hive 0.12.0和更早版本中，联合只能在诸如“ SELECT * FROM（select_statement UNION ALL select_statement UNION ALL ...）unionResult “ 之类的子查询中使用。

从Hive 0.13.0开始，联合还可以用于顶级查询：“ select_statement UNION ALL select_statement UNION ALL ...”。（请参阅HIVE-6189。）

在Hive 1.2.0之前，仅支持UNION ALL（袋子联合）。自Hive 1.2.0起支持UNION（或UNION DISTINCT）。（请参阅HIVE-9039。）

Sampling Syntax

采样桶式表

table_sample: TABLESAMPLE (BUCKET x OUT OF y [ON colname])

TABLESAMPLE子句允许用户为数据样本而不是整个表编写查询。TABLESAMPLE子句可以添加到FROM子句中的任何表中。桶的编号从1开始。colname表示要对表中的每一行进行采样的列。colname可以是表中的非分区列之一，也可以是rand（），它指示整行而不是单个列的采样。表格中的行在“同名”中随机存储到y个存储桶中，编号从1到y。返回属于存储桶x的行。

在以下示例中，表源的32个存储桶中的第三个存储桶。“ s”是表别名。

SELECT *
FROM source TABLESAMPLE(BUCKET 3 OUT OF 32 ON rand()) s;

输入修剪：通常，TABLESAMPLE将扫描整个表并获取样本。但是，这不是很有效。相反，可以使用CLUSTERED BY子句创建该表，该子句指示对该表进行哈希分区/集群的列集。如果TABLESAMPLE子句中指定的列与CLUSTERED BY子句中的列匹配，则TABLESAMPLE仅扫描表的所需哈希分区。

例：

因此，在上述示例中，如果表'source'是使用'CLUSTERED BY id INTO 32 BUCKETS'创建的

TABLESAMPLE(BUCKET 3 OUT OF 16 ON id)

将选择第3和第19个群集，因为每个存储桶将由（32/16）= 2个群集组成。

另一方面，tablesample子句

TABLESAMPLE(BUCKET 3 OUT OF 64 ON id)

会挑选出第三个群集的一半，因为每个存储桶都将由（32/64）= 1/2群集组成。

有关使用CLUSTERED BY子句创建存储桶表的信息，请参见创建表（尤其是存储桶排序表）和存储桶表。

块采样

从Hive 0.8开始提供块采样。在JIRA下寻址-https: //issues.apache.org/jira/browse/HIVE-2121

block_sample: TABLESAMPLE (n PERCENT)

这将允许Hive至少获取n％的数据大小（注意，它不一定表示行数）作为输入。仅支持CombineHiveInputFormat，并且不处理某些特殊的压缩格式。如果我们无法对其进行采样，则MapReduce作业的输入将是整个表/分区。我们在HDFS块级别执行此操作，以便采样粒度为块大小。例如，如果块大小为256MB，那么即使输入大小的n％仅100MB，您也将获得256MB的数据。

在下面的示例中，输入大小为0.1％或更大将用于查询。

SELECT *
FROM source TABLESAMPLE(0.1 PERCENT) s;

有时您想使用不同的块对相同的数据进行采样，则可以更改此种子编号：

set hive.sample.seednumber=<INTEGER>;

或者，用户可以指定要读取的总长度，但是它与PERCENT采样有相同的限制。（由于蜂房0.10.0的- https://issues.apache.org/jira/browse/HIVE-3401）

block_sample: TABLESAMPLE (ByteLengthLiteral)
 
ByteLengthLiteral : (Digit)+ ('b' | 'B' | 'k' | 'K' | 'm' | 'M' | 'g' | 'G')

在以下示例中，输入大小为100M或更大将用于查询。

SELECT *
FROM source TABLESAMPLE(100M) s;

Hive还支持按行数限制输入，但对于以上两种，其行为不同。首先，它不需要CombineHiveInputFormat，这意味着它可以与非本地表一起使用。其次，将用户给定的行数应用于每个拆分。因此，总行数可以根据输入拆分的数量而变化。（由于蜂房0.10.0的- https://issues.apache.org/jira/browse/HIVE-3401）

block_sample: TABLESAMPLE (n ROWS)

例如，以下查询将从每个输入拆分中获取前10行。

SELECT * FROM source TABLESAMPLE(10 ROWS);

FROM子句中的子查询

SELECT ... FROM (subquery) name ...
SELECT ... FROM (subquery) AS name ...   (Note: Only valid starting with Hive 0.13.0)

Hive仅在FROM子句中支持子查询（通过Hive 0.12）。必须为子查询指定一个名称，因为FROM子句中的每个表都必须具有一个名称。子查询选择列表中的列必须具有唯一的名称。子查询选择列表中的列在外部查询中可用，就像表的列一样。子查询也可以是带有UNION的查询表达式。Hive支持任意级别的子查询。

Hive 0.13.0和更高版本（HIVE-6519）中的子查询名称之前可以包含可选关键字“ AS” 。

简单子查询的示例：

SELECT col
FROM (
  SELECT a+b AS col
  FROM t1
) t2

包含UNION ALL的子查询的示例：

SELECT t3.col
FROM (
  SELECT a+b AS col
  FROM t1
  UNION ALL
  SELECT c+d AS col
  FROM t2
) t3

WHERE子句中的子查询

从Hive 0.13开始，WHERE子句支持某些类型的子查询。这些查询可以将查询结果视为IN和NOT IN语句的常量（称为不相关子查询，因为该子查询未引用父查询中的列）：

SELECT *
FROM A
WHERE A.a IN (SELECT foo FROM B);

其他受支持的类型是EXISTS和NOT EXISTS子查询：

SELECT A
FROM T1
WHERE EXISTS (SELECT B FROM T2 WHERE T1.X = T2.Y)

有一些限制：

• 仅在表达式的右侧支持这些子查询。
• IN / NOT IN子查询只能选择一个列。
• EXISTS / NOT EXISTS必须具有一个或多个相关谓词。
• 仅在子查询的WHERE子句中支持对父查询的引用。

运算符和用户定义的函数（UDF）

这里不做介绍了。