[Hive] 07 - HQL：数据检索-查询（select）

女爷i 2022-10-02 07:51 286阅读 0赞

**环境**

*  宿主机：Windows 10 64\_bit
 *  虚拟机：VMware pro 12
    
     *  CentOS 7.5 64\_bit（3台：1个master、2个slave）
     *  Hadoop-2.6.5
     *  MariaDB-5.5.60
     *  Hive 1.2.2
 *  ssh工具：SecureCRT 7.3

**目录**

0、Select语法

\----0.0 group by

\----0.1 Order/Sort/Distribute/Cluster By

\----0.2 转换和Map-Reduce 脚本

\----0.3 操作符和用户自定义函数（UDFs）

\----0.4 XPath UDF

\----0.5 Join-联接

\----0.6 Join优化

\----0.7 Union

\----0.8 Lateral View（侧视图）

1、子查询Sub Queries

2、抽样（samping）

3、虚拟列

4、窗口和分析函数

5、增强的聚合（aggregation）、多维数据集(cube）、分组（group）和汇总(roll up）

6、程序语言：hive hpl/sql

7、explain语法

**正文**

# 0、Select语法 #

**预热**  
首先认识`select ...from`语法，是SQL中的射影算子，`from`子句标识了从哪个表、视图、或嵌套查询中选择记录。更多参考【[Hive-Select语法][Hive-Select]】。

语法：

[with CommonTableExpression (, CommomnTableExpression)*] --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 0.13`之前的行为和限制，即列名为字母数字和下划线字符，可以设置配置属性`hive.support.quoted.identifiers`为`none`。在这个配置，由反引号包围的名字被解释为正则表达式。
 *  取得当前数据库（`Hive 0.13.0`起），可以使用`current_database()`函数。

hive> select current_database();

*  若要指定一个数据库，可以用数据库名去限定表名（`Hive 0.7`起，`db_name.table_name`），或在查询语句之前使用`use`语句（`Hive 0.6`起）。`db_name.table_name`允许查询访问不同数据库中的表。

`use`可设置数据库，用于所有后续的Hive QL语句。使用`default`关键字可以回到默认数据库：

use database_name;
    select query_specification;
    use default;

**where子句**  
`where`条件是一个布尔表达式，用于过滤条件，即查找到符合过滤条件的记录。例如，下方查询只返回来自`US`地区数量大于10的销售记录。在`where`子句中，Hive支持多个操作符和UDF：

select * from sales where amout > 10 and region='US';

**all和distinct子句**

*  `all`和`distinct`选项指定是否返回重复的行；
 *  假如这两个选项都没给，默认是`all`（所有匹配的行都会返回）；
 *  `distinct`指定会从结果集中删除重复的行；
 *  `Hive 1.1.0`支持`select distinct *`。

创建表
    hive> create table t1(col1 int, col2 int);
    
    往表中插入数据
    hive> insert into table t1 values(1,20),(1,18),(1,66),(2,24),(3,30);
    
    显示t1表中两个列
    hive> select col1,col2 from t1;
    OK
    1       20
    1       18
    1       66
    2       24
    3       30
    
    查询不重复的col1列
    hive> select distinct col1 from t1;
    OK
    1
    2
    3
    
    hive> select distinct col1,col2 from t1;
    OK
    1       18
    1       20
    1       66
    2       24
    3       30

`all`和`distinct`还可用在`union`子句中。[Union 语法][Union]

**基于分区的查询**  
通常，一个`select`查询会扫描整个表（取样除外（[sampling][]））。  
假如一个表是用`partition by`子句创建的，查询可以执行分区修剪和只扫描与查询指定的分区相关的表的一小部分。假如在`where`子句中指定了分区或在`join`中的`on`子句，则Hive目前能执行分区修剪。例如，在`page_view`表中有`date`列分区，下方的查询语句只会检索日期在`2008-03-01` 和`2008-03-31`之间的行。

select page_view.* 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子句**  
`having`允许用户通过一个简单的语法完成原本需要通过子查询才能对`group by`子句产生的分组进行条件过滤的任务。

`Hive 0.7.0`中，Hive添加了对`havin`子句的支持。在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子句**

*  `limit`子句用于限制（限定）由`select`语句返回的行数。
 *  `limit`可以带一个或两个数字参数，且必须是非负整数常量。
    
     *  第一个参数指定指定要返回的第一行的偏移量（`Hive 2.0.0`)）；
     *  第二个参数指定要返回的最大行数；
     *  当只给一个参数时，它代表最大行数，默认偏移量为`0`。

下方查询将返回5个任意的customer：

select * from customers limit 5;

下方查询将返回以`create_date`的前5个customer：

select * from customers order by create_date limit 5;

下方将返回以`create_date`的第3个到第7个customer：

select * from customers order by create_date limit 2,5;

**limit子句**

*  在`Hive 0.13.0`之前，`select`语句可以采用基于正则的列规范；
 *  在`Hive 0.13.0`之后，可以设置属性`hive.support.quoted.identifiers`为`none`【各种属性的设置可参考[Hive Configuration Properties][]】

可以使用Java的正则语法。下方将查询`sales`表中除了`ds`和`hr`的所有列：

select `(ds|hr)?+.+` from sales;

## 0.0 group by ##

`group by`子句通常会和聚合函数一起使用，按照一个或多个列对结果进行分组，然后对每个分组进行聚合操作。

`groupByClause`子句：`group by groupByExpression (, groupByExpression)*`  
`groupByExpression`：`expression`  
`group by`查询：`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`。

示例1：统计表的行数。在`Hive 0.6.0`需要用`count(1)`代替`count(*)`。其他版本两个都可以用。

select count(*) from table2;

示例2：按性别（`gender`）统计不同的`user`。

insert overwrite table pv_gender_sum
    select pv_users.gender, count (distinct pv_users.userid)
    from pv_users
    group by pv_users.gender;

同时，还可以做多聚合（Multiple aggregations），不过，没有两个聚合能够有不同的`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;

不过，下方查询是不允许的。不允许在同一个查询中使用多个`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;

**`select`语句和`group by`子句**  
当使用`group by`子句时，**`select`语句只能包含已在`group by`子句包含了的列。**当然，在`select`语句中可以有多个聚合函数（如`count()`）。例如：

create table t1(a integer, b integer);

对于上面这个表，一个`group by`查询可以像这样：

select a, sum(b) from t1
    group by a;

上面这个查询能够执行，是因为`select`语句包含了`group by key`，和一个聚合函数（`sum(b)`）。  
不过，**下方这种查询**是不会执行的：因为`select`语句有一个额外的列（`b`），它没有包含在`group by`子句中，它也不是一个聚合函数。

select a,b from t1
    group by a;

比如这个表像这样：

a   b
    ------
    100 1
    100 2
    100 3

由于分组（`group by`）只作用在`a`，那么对于组`a=100`，Hive应该显示给`b`什么值呢？有人认为，它应该是第一个值或最低值，但也会认为有多个选项。Hive通过使SQL（准确地说是HQL）在`select`语句中具有不包含在`group by`子句中的列无效 来消除这种猜测。

**`group by`子句高级特性**  
**多group by插入**  
聚合或简单`select`的输出可以可以进一步发送到多个表中、甚至到hadoop dfs文件中（这个可以使用hdfs工具进行操作）。例如，假如对性别（`gender`）进行细分，需要通过年龄（`age`）查找唯一的page view的细分，可以通过语句完成：

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;

**对`group by`进行map-side聚合**  
`hive.map.aggr`控制了怎么做聚合，默认是`false`。假如设置为`true`，Hive将直接在map task中执行第一级聚合。这通常能提供更高的效率，但可能需要更多的memory才能运行成功。

set hive.map.aggr=true;
    select count(*) from t2;

更多可参考[LanguageManual GroupBy][]。

## 0.1 Order/Sort/Distribute/Cluster By ##

### 0.1.0 `order by` ###

Hive QL的`order by`语法跟SQL的`order by`语法是相似的。其会对查询结果执行一个全局排序，即 会有一个所有的数据都通过一个reducer进行处理的过程（对于大数据集，该过程将消耗一些时间来执行）。语法：

colOrder：( asc | desc) --默认排序为升序（asc）
    colNullOrder：( nulls first | null last) -- Hive 2.1.0+支持
    orderBy：order by colName colOrder? colNullOrder? (',' colName colOrder? colNullOrder?)*
    
    查询语句：
    select expression (',' expression)* from src orderBy;

在`order by`子句中有一些限制。在严格模式下（`hive.mapred.mode=strict`），其后面必须跟着`limit`；但是，在非严格模式下，就不必了。原因是为了把所有结果的顺序加在一起，这必须有一个reducer来最终输出排序。假如在输出中行数太大了，单个reducer可能花费很长的时间去完成。

切记的是 通过列名来指定，而不是位置编号。不过，在`Hive 0.11.0+`，当配置了下面的属性时，列是可以通过位置来指定的：

*  `Hive 0.11.0`到`Hive 2.1.x`，设置`hive.groupby.orderby.position.alias=true`，默认是`false`；
 *  `Hive 2.2.0+`，`hive.orderby.position.alias=true`，默认就为`true`。

`Hive 2.1.0+`，在`order by`子句中，支持对每列指定`null`的排序。对于升序排序（`asc`），默认`null`排序是`nulls first`，而对于降序排序（`desc`）默认的`null`排序是`nulls last`。  
`Hive 3.0.0+`，在子查询中`order by`没有限制（`limit`）了，优化器将删除视图。禁用它可以去设置`hive.remove.orderby.in.subquery=false`。

### 0.1.1 `sort by` ###

`sort by`也和SQL中的`order by`语法相似。

colOrder：( asc | desc)
    sortBy：sort by colName colOrder? (',' colName colOrder?)*
    
    查询语句：
    select expression (',' expression)* from src sortBy;

在将行送进reducer之前，Hive会在`sort by`中使用列来对行进行排序。`sort`排序依赖于列的类型，举例：

*  假如列是数字类型，那么`sort`是会按照数字排序的；
 *  假如列是字符串类型，那么`sort`排序是按照字典排序。

`Hive 3.0.0+`，在子查询中`sort by`没有限制（`limit`）了，优化器将删除视图。禁用它可以去设置`hive.remove.orderby.in.subquery=false`。

**小结：**`sort by`和`order by`的不同  
Hive支持`sort by`，它会对每个reducer上数据进行排序。`sort by`和`order by`的不同是：

*  `sort by`只确保在一个reducer内的行的排序。假如有多个reducer，`sort by`可能给出部分排序的最终结果。
 *  `order by`确保在输出中总得排序。

一般情况下，根据用户指定的顺序，数据将在每一个reducer中被排序。示例：

select key, value from src sort by key asc, value desc

查询会有两个reducer，每一个的输出结果是：

0   5
    0   3
    3   6
    9   1

0   4
    0   3
    1   1
    2   5

**对`sort by`设置类型**  
在转换后，变量的类型通常被认为是字符串，意味着数字数据将被以字典排序。为了避免这个，在使用`sort by`之前，可以使用带有强制转换（`cast`）的`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

### 0.1.2 `cluster by`和`distribute by` ###

`cluster by`和`distribute by`主要跟[Transform/Map-Reduce脚本][Transform_Map-Reduce]一起使用。但是，假如有一个需要去做分区和为了后续查询对一个子查询的输出进行排序，有时，`cluster by`和`distribute by`是非常有用的。

`cluster by`是对`distribute by`和`sort by`的一个捷径。

在`distribute by`中，Hive使用列在reducer之间去分布（distribute，分配）行。所有按`distribute by`的列分布的行都将转到同一个reducer。然而，`distribute by`不能确保在分布的键上聚类或排序属性。例如：通过`distribute by x`将下方5行分布到2个reducer中去：

x1
    x2
    x4
    x3
    x1

reducer1 得到：

x1
    x2
    x1

reducer2得到：

x4
    x3

注意，所有行中具有相同key（x1）被确保分布到了同一个reducer上（上述例中是reducer1），但是不能确保它们聚集在相邻的位置。

相反，假如用`cluster by x`，这2个reducer将进一步以`x`对行进行排序，那么：  
reducer1 得到：

x1
    x1
    x2

reducer2得到：

x3
    x4

不是通过`cluster by`，用户可以指定`distribute by`和`sort 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;

## 0.2 转换和Map-Reduce 脚本 ##

通过使用Hive语言支持的本身特性，用户还可以往数据流中插入（plug）自己自定义的mapper和reducer。例如，为了运行一个自定义的mapper脚本（map\_script）和一个自定义的reducer脚本（reducer\_script），用户可以发布使用了`transform`子句的命令嵌入到mapper和reducer脚本中。

默认情况下，在发送到用户脚本之前，列将被转换为字符串、并以tab键分割；同样地，为了区分`null`值、空字符串，所有`null`值将被转换为字面字符串`\N`。用户脚本的标准输出将被对待为以tab分割的字符串列，任何只含有`\N`的单元将被重新解释一个`null`，然后，生成的字符串列将按常规方式转换为表声明中指定的数据类型。用户脚本可以将调试信息输出到标准错误，该错误将显示在Hadoop的任务详细信息页上。可以用`row format`覆盖这些默认值。

更多可参考[LanguageManual Transform][Transform_Map-Reduce]。

**稍后更新其他内容**

## 0.3 操作符和用户自定义函数（UDFs） ##

参考博客【[\[Hive\] 08 - 内建操作符、函数（UDF）][Hive_ 08 - _UDF]】

## 0.4 XPath UDF ##

XPath，即XML路径语言（XML Path Language，可扩展标记语言路径语言），它是一种用来确定XML文档中某部分位置的语言。

UDF  
`xpath, xpath_short, xpath_int, xpath_long, xpath_float, xpath_double, xpath_number, xpath_string`

*  使用XPath表达式解析XML数据的函数
 *  `Hive 0.6.0+`

UDF的xpath家族是通过JDK提供的Java XPath library javax.xml.xpath的包装器。这个library是基于XPath 1.0规范。有关[Java XPath library][]

参考：[LanguageManual XPathUDF][]

## 0.5 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] --Hive 0.10+
    
    table_reference：
      table_factor
      join_table
    
    table_factor：
      tbl_name [alias]
      table_subquery alias
      ( table_references )
    
    join_condition：
      on expression

对于联接（`join`）语法的上下文可参考上方的`select`语法。  
`Hive 0.13.0+`：隐式`join`表示。允许`from`子句去联接一个以逗号分隔的表列表，省略`join`关键字。例如：

select *
    from table1 t1, table2 t2, table3 t3
    where t1.id=t2.id and t2.id=t3.id and t1.zipcode='02535';

`Hive 0.13.0+`：不合格的列引用。在`join`条件中支持非限定列引用。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=k1;

`Hive 2.2.0+`：在`on`子句中的复杂表达式。在此之前，Hive不支持非相等条件的`join`条件。尤其是，`join`条件的语法受到如下限制:

join_condition:
      on equality_expression ( and equality_expression )*
    
    equality_expression:
      expression=expression

**示例**  
编写`join`查询时，要考虑一些重点：

*  允许使用复杂的`join`表达式，如：下方都是有效的联接（`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);

*  在同一个查询中，可以`join`两个以上的表，如：下方是一个有效的联接（`join`）

select a.val, b.val, c.val from a join b on (a.key=b.key1) join c on (c.key=b.key2);

*  假如对于每个表，`join`子句中使用相同的列，Hive将多个表上的`join`转换为单个map/reduce job。如：

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 job，只有b的key1列参与了联接（`join`）。另一方面

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 job，因为b的key1列用在了第一个联接（`join`）条件，以及b的key2列用在了第二个上。第一个map/reduce job 联接了a和b，它的结果然后在第二个map/reduce job中联接c。

*  在联接（`join`）的每一个map/reduce阶段，在顺序上的最后一个表通过reducers进行流式传输，其他表被缓冲了。因此，通过组织表使得最大的表出现在顺序的最后，帮助减少了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 job上被联接了，并且对于表a和b的键的特定值都缓冲reducer内存中。然后对于从c中检索的每一行，联接（`join`）是随着缓冲的行来计算的。相似于

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 job参与了。第一个联接（`join`）a与b，缓冲了a的值，在reducer中流式缓冲了b的值。第二个联接（`join`）在通过reducer流式缓冲了c的值时，这些job缓冲了第一个联接（`join`）的结果。

*  在`join`的每一个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);

三个表都联接（`join`）在一个单独的map/reduce job里，并且对于表b和c的键的一个特定值都缓存了reducer内存中。然后从表a检索的每一行，`join`都会随着缓存的行来计算。假如忽略了streamtable提示，在联接（`join`）中，Hive将流式传输最右边那个表。

*  为了提供在`on`子句上有更多的控制，存在`left`、`right`、`full outer`联接（`join`），而没有匹配的。例如，这个查询：

select a.val, b.val from a left outer join b on (a.key=b.key);

将对a中每一行返回一行。当有一个b.key等于a.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的所有行。`outer join`应该符合SQL标准规范。

*  `join`发生在`before where clauses`。所以，假如想限制一个联接的输出，那么在`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上联接（`join`）a，产生一个a.val和b.val的列。然而，`where`子句也可以引用`join`输出的a和b的其他列，然后过滤掉它们。然而，无论何时，来自`join`的一行

更多的示例可参考[Joins-Examples][]

**MapJoin的限制**  
假如但是所`join`的表中只有一个是小表，`join`可以被执行作为只有一个job的map。下方查询：

select /*+ mapjoin(b) */ a.key, a.value
    from a join b on a.key=b.key;

上述查询不需要一个reducer。对于a的每一个mapper，b是被完全读取的。  
更多可参考[Join][Joins-Examples]

## 0.6 Join优化 ##

`Hive 0.11.0+`。Hive查询执行计划的优化，以提高Join的效率并减少对用户提示的需要。  
**Hive优化器的改进**

**星形join优化**

参考[JoinOptimization][]

## 0.7 Union ##

Union语法

select_statement union [all | distinct] select_statement unoin [all | distinct] select_statement ...

union用于将来自多个 `select`语句的结果组合为一个结果集。

*  `Hive 1.2.0`之前的版本，只支持`union all`（bag union），不会去掉重复的行；
 *  `Hive 1.2.0+`，`union`的默认行为是从结果中删除重复的行。可选的`distinct`关键字除了默认值之外没有其他效果，因为它还指定了重复行删除。对于`all`关键字，不会删除重复的行，结果会包括所有`select`语句中的所有匹配的行。

参考[Union][Union 1]

## 0.8 Lateral View（侧视图） ##

**lateral view语法**

lateralView：lateral view udft(expression) tableAlias as columnAlias(',' columnAlias)*
    
    fromClause：from baseTable (lateralView)*

**描述**  
laterial view 跟用户定义的表生成函数（比如`explode()`）一起使用。

**示例**

**多lateral view**

**外lateral view**

**以后用上再继续补充。**

# 1、子查询Sub Queries #

*  在`from`子句中的子查询

select ... from (subquery) name ...
    
    select ... from (subquery) as name ...-- Hive 0.13.0起

在Hive 0.12.0中，Hive只在`from`子句中支持子查询。这个子查询必须给一个名称，因为在一个`from`子句中的每一个表都必须有一个名称。在子查询`select`中的列必须有一个唯一的名称。就像一个表中的列一样，在子查询`select`中的列在外部查询中可用。子查询和`union`也是一个查询表达式。  
在`Hive 0.13.0+`中，在子查询名词之前，可用包含可选的`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.0+`，在`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`子句才支持父查询的引用。

# 2、抽样（samping） #

抽样语法：

*  取样桶表

table_sample：tablesample (bucket x out of y [on colname])

`tablesample`子句允许用户为数据的样本而不是整个表编写查询。在`from`子句中，任何表都可以添加`tablesample`子句。

*  块取样

`Hive 0.8+`

更多参考[Sampling][sampling]

# 3、虚拟列 #

`Hive 0.8.0`支持两个虚拟列：

*  `input_file_name`，是mapper task的输入文件名称；
 *  `block_offset_inside_file`，是当前全局文件的位置。

对于块压缩文件，它是当前块文件的偏移，是当前块的第一个字节的文件偏移量。`Hive 0.8.0+`，添加了下面的虚拟列：

*  `row_offset_inside_block`
 *  `raw_data_size`
 *  `row_id`
 *  `grouping_id`

不过这些列出的所有虚拟列都不能用于任何其他目的。  
示例：

select input_file_name, key, block_offset_inside_file from src;
    
    select key, count(input_file_name) from src group by key order by key;
    
    select * from src where block_offset_inside_file > 12000 order by key;

# 4、窗口和分析函数 #

**对Hive QL的增强**  
`Hive 0.11+`，本节介绍窗口、分析函数对Hive QL的增强。所有窗口和分析函数都按照SQL标准运行。当前版本支持下方的窗口、分析函数：

*  窗口函数
    
     *  `lead`，可以选择指定要引导的行数（未指定时，默认是1行）。如果当前行的前导超出窗口末端，则返回`null`。
     *  `lag`，设置滞后的行数
     *  `first_value`
     *  `last_value`
 *  `over`子句
    
     *  跟标准聚合一起使用：
        
         *  `count`
         *  `sum`
         *  `min`
         *  `max`
         *  `avg`
     *  跟任何基本数据类型的一个或多个分区列的`partition by`语句一起使用
     *  `partition by` 和`order by`一起使用
 *  分析函数
    
     *  `rank`
     *  `row_number`
     *  `dense_rank`
     *  `cume_dist`
     *  `percent_rank`
     *  `ntile`
 *  `Hive 2.1.0+`支持`distinct`
 *  `Hive 2.1.0+`支持在`over`子句里使用聚合函数

**示例**  
在`select`语句中使用Hive QL 窗口和分析函数。

[参考][Link 1]

# 5、增强的聚合（aggregation）、多维数据集(cube）、分组（group）和汇总(roll up） #

主要是针对`select`语句中的`group by`子句的聚合功能的增强。

参考[Enhanced Aggregation, Cube, Grouping and Rollup][Enhanced Aggregation_ Cube_ Grouping and Rollup]

# 6、程序语言：hive hpl/sql #

`Hive 2.0.0+`

参考[Hive HPL/SQL][Hive HPL_SQL]

# 7、explain语法 #

Hive提供了一个`explain`语法，它显示查询的执行计划。语法：

*  `Hive 0.14.0+`，支持`authorization`；
 *  `Hive 2.3.0+`，支持`vectorization`；
 *  `Hive 3.2.0+`，支持`locks`；

示例：

explain
    from src insert overwrite table dest_g1 select src.key, sum(substr(src.value,4)) group by src.key;

参考[LanguageManual Explain][]

[官方手册目录][Link 2]

[Hive-Select]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select
[Union]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Union#LanguageManualUnion-UnionSyntax
[sampling]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Sampling
[Hive Configuration Properties]: https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties#ConfigurationProperties-hive.support.quoted.identifiers
[LanguageManual GroupBy]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+GroupBy
[Transform_Map-Reduce]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Transform
[Hive_ 08 - _UDF]: https://blog.csdn.net/weixin_38256474/article/details/92651687
[Java XPath library]: http://java.sun.com/javase/6/docs/api/javax/xml/xpath/package-summary.html
[LanguageManual XPathUDF]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+XPathUDF
[Joins-Examples]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins#LanguageManualJoins-Examples
[JoinOptimization]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+JoinOptimization
[Union 1]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Union
[Link 1]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics
[Enhanced Aggregation_ Cube_ Grouping and Rollup]: https://cwiki.apache.org/confluence/display/Hive/Enhanced+Aggregation%2C+Cube%2C+Grouping+and+Rollup
[Hive HPL_SQL]: https://cwiki.apache.org/confluence/pages/viewpage.action?pageId=59690156
[LanguageManual Explain]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Explain
[Link 2]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual