《Hive权威指南》第六章：查询

最新推荐文章于 2022-08-09 08:34:23 发布

csyigy

最新推荐文章于 2022-08-09 08:34:23 发布

阅读量336

点赞数

分类专栏： Hive权威指南文章标签： mapreduce hive hadoop 大数据

本文链接：https://blog.csdn.net/csyigy/article/details/105199099

版权

Hive权威指南专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1.SELECT … FROM 语句

SELECT是SQL中的射影算子。FROM子句标识了从哪个表、视图或嵌套查询中选择记录，SELECT指定了要保存的列以及输出函数需要调用的一个或多个列（例如，COUNT(*) 这样的聚合函数）。

employees表：

CREATE TABLE employees(
    name            STRING,
    salary          FLOAT,
    subordiantes    ARRAY<STRING>,
    deductions      MAP<STRING, FLOAT>,
    address         STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>
)
PARTITIONED BY (country STRING, state STRING);

当用户选择的列是集合(ARRAY<T>)类型时，Hive会使用JSON（Java脚本对象表示法）语法应用于输出。

同样，MAP与STRUCT类型的列，输出也是使用JSON格式来表示，其中格式上有些细微的差别，相信你一眼也能看懂。

接下来，来看看如何引用集合数据类型中的元素。

数组索引是基于0，这个和Java是一样的。这里选择subordinates数组中的第一个元素查询：

hive> SELECT name, subordinates[0] FROM employees;
John Doe	Mary Smith
Mary Smith	Bill King
Todd Jones	NULL
Bill King	NULL

引用一个不存在的元素会返回NULL。同时，提取出STRING类型的值不再加引号

为了引用一个MAP元素，用户还可以使用ARRAY[…]语法，但是使用的是键值而不是整数索引：

hive> SELECT name, deductions["State Taxes"] FROM employees
John Doe	0.05
Mary Smith	0.05
Todd Jones	0.03
Bill King	0.03

最后，为了引用STRUCT中的一个元素，用户可以使用“点”符号，类似于前面提到的“表的别名.列名”这样的用法：

hive> SELECT name, address.city FROM employees;
John Doe	Chicago
Mary Smith	Chicago
Todd Jones	Oak Park
Bill King	Obscuria

WHERE子句中同样可以使用这些引用方式

1.1 使用正则表达式来指定列

从stocks中选择symbol列和所有列名以price作为前缀的列：

hive> SELECT symbol, `price.*` FROM stocks;

前提是先设置属性：set hive.support.quoted.identifiers=None;

否则会报错。

在这里插入图片描述
当然也可以使用“LIKE”和“RLIKE”关键字，后面再讨论。

1.2 使用列值进行计算

hive> SELECT upper(name), salary, deductions["Federal Taxes"],
    > round(salary * (1 - deductions["Federal Taxes"])) FROM employees;
JOHN DOE	100000.0	0.2		80000
MARY SMITH	80000.0		0.2		64000
TODD JONES	70000.0		0.15	59500
BILL KING	60000.0		0.15	51000

1.3 算数运算符

Hive中支持所有典型的算数运算符。

算数运算符：

运算符	类型	描述
A+B	数值	A和B相加
A-B	数值	A减去B
A*B	数值	A和B相乘
A/B	数值	A除以B。如果能整除，那么返回商数
A%B	数值	A除以B的余数
A&B	数值	A和B按位取与
A\|B	数值	A和B按位取或
A^B	数值	A和B按位取亦或
~A	数值	A按位取反

算数运算符接受任意的数值类型。不过，如果数据类型不同，那么两种类型中值范围较小的那个数据类型将转换为其他范围更广的数据类型。（例如INT和BIGINT两种类型进行运算，INT会将类型转换提升为BIGINT）

1.4 使用函数

Hive v0.8.0版本中所提供的，而且是用于处理单个列的数据。

1.4.1 数学函数

1.4.2 聚合函数

1.4.3 表生成函数

1.4.4 其他内置函数

1.5 LIMIT语句

典型的查询会返回多行数据。LIMIT子句用于限制返回行数

hive> SELECT upper(name), salary, deductions["Federal Taxes"],
    > round(salary * (1 - deductions["Federal Taxes"])) FROM employees
    > LIMIT 2;
JONE DOE	100000.0	0.2    80000
MARY SMITH	 80000.0	0.2	   64000

1.6 列别名

hive> SELECT upper(name), salary, deductions["Federal Taxes"] as fed_taxes,
    > round(salary * (1 - deductions["Federal Taxes"])) as salary_minus_fed_taxes
    > FROM employees LIMIT 2;
JONE DOE	100000.0	0.2    80000
MARY SMITH	 80000.0	0.2	   64000

1.7 嵌套SELECT语句

hive> FROM(
    >	SELECT upper(name), salary, deductions["Federal Taxes"] as fed_taxes,
    >	round(salary * (1 - deductions["Federal Taxes"])) as salary_minus_fed_taxes
    >	FROM employees
    > ) e
    > SELECT e.name, e.salary_minus_fed_taxes
    > WHERE e.salary_minus_fed_taxes > 70000;
JOHN DOE	100000.0	0.2	   80000

从这个嵌套查询语句中可以看到，我们将前面的结果集起了别名，称之为e，在这个语句外嵌套查询了name 和salary_minus_fed_taxes两个字段，同时约束后者的值要大于70000。

1.8 CASE … WHEN … THEN 句式

CASE … WHEN … THEN 语句和if条件语句类似，用于处理单个列的查询结果。

例如：

hive> SELECT name, salary,
	> CASE 
	>	WHEN salary < 50000.0 THEN 'low'
	>	WHEN salary >= 50000.0 AND salary < 70000.0 THEN 'middle'
	>	WHEN salary >= 70000.0 AND salary < 100000.0 THEN 'high'
	>	ELSE 'very high'
	> END AS bracket FROM employees;
John Doe			100000.0    very high
Mary Smith			 80000.0	high
Bill King			 60000.0	middle
Boss Man			200000.0	very high
...

1.9 什么情况下Hive可以避免进行MapReduce

Hive中对某些情况的查询可以不必使用MapReduce，也就是所谓的本地模式，例如：

SELECT * FROM employees;

在这种情况下，Hive可以简单地读取employees对应的存储目录下的文件，然后输出格式化后的内容到控制台。

对于WHERE语句中过滤条件知识分区字段这种情况（无论是否使用LIMIT语句限制输出记录条数），也是无需MapReduce过程的。

SELECT * FROM employees
WHERE country='US' AND state='CA'
LIMIT 100;

此外，如果属性 hive.exec.model.local.auto 的值设置为true的话，Hive还会尝试使用本地模式执行其他的操作：

set hive.exec.mode.local.auto=true;

否则，Hive使用MapReduce来执行其他所有的查询。

提示：

最好可以将set hive.exec.mode.local.auto=true；这个设置增加到你的 $HOME/.hiverc配置文件中

2 WHERE语句

WHERE语句使用谓词表达式，有几种谓词表达式可以使用AND和OR相连接。当谓词表达式计算结果为true时，相应的行将被保留并输出。

注意，不能在WHERE语句中使用列别名。不过可以使用户子查询，然后使用子查询的结果集点上列名取到相应的列（例如:(select col_name as col from test) t1 然后可以通过t1.col 取到该列）。

2.1 谓词操作符

这些操作符同样可以用于JOIN … ON 和 HAVING 语句中

操作符	支持的数据类型	描述
A=B	基本数据类型
A<=>B	基本数据类型	如果A和B都为NULL则返回TRUE，其他的和等号（=）操作符的结果一致，如果任一为NULL则结果为NULL （Hive v0.9.0版本新增）
A==B	没有	这个是错误语法！SQL使用=，而不是==
A<>B，A!=B	基本数据类型	A或者B为NULL，则返回NULL；如果A不等于B则返回TRUE，反之返回FALSE
A<B	基本数据类型
A<=B	基本数据类型
A>B	基本数据类型
A>=B	基本数据类型
A[NOT] BETWEEN B AND C	基本数据类型	如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于或等于B而且小于或等于C，则结果为TRUE，反之为FALSE。如果使用NOT关键字反之。（Hive v0.9.0 版本中新增）
A IS NULL	所有数据类型
A IS NOT NULL	所有数据类型
A [NOT] LIKE B	STRING 类型	B是一个简单的正则表达式，如果A与其匹配返回TRUE，反之返回FALSE。
A RLIKE B, A REGEXP B	STRING 类型	B是一个正则表达式，如果A与其相匹配，则返回TRUE；反之返回FALSE。

2.2 关于浮点数的比较

例如：

hive> SELECT name, salary, deductions['Federal Taxes']
	> FROM employees WHERE deductions['Federal Taxes'] > 0.2;
John Doe	 100000.0	0.2
Mary Smith	  80000.0	0.2
Boss Man	 200000.0	0.3
Fred Finance 150000.0	0.3

为什么deductions[‘Federal Taxes’]=0.2的记录也被输出了呢？

实际上这并非是仅存于Hive中或Java中的问题。而是所有使用IEEE标准进行浮点数编码的系统中普遍存在的一问题。

因为deductions这个map值的类型时FLOAT，而比较的数值0.2，Hive默认0.2为DOUBLE类型的，在IEEE编码标准编码中0.2的最近似的精确值应该略大于0.2，也就是0.2后面的若干个0后存在非零的数值。

这里为了方便理解，简化表示为：FLOAT类型的0.2 实际为0.2000001，而对于DOUBLE类型是0.200000000001。这是因为一个8个字节的DOUBL值具有更多的小数位。当表中的FLOAT值通过Hive转换为DOUBLE值时，其产生的DOUBLE类型的值是0.200000100000，这个值实际要比0.200000000001大。这就是为什么这个查询结果像是使用了>= 而不是 > 了。

在Hive中有两种方法解决这个问题：

如果是从TEXTFILE文本文件中读取数据的话，也就是目前为止我们所嘉定使用的存储格式，那么Hive会从数据文件中读取字符串“0.2”然后将其转换为一个数字。我们可以在表模式中定义对应的字段类型为DOUBLE而不是FLOAT。这样在进行浮点型数值比较的时候会比较准确。
相信大家会想到，显式的支出0.2为FLOAT类型的。Java中有一个很好的方式能够达到这个目的：只需要在数值末尾加上字母F或f（例如：0.2f）。不幸的是，Hive并不支持这种语法，这里必须使用cast操作符
```
hive> SELECT name, salary, deductions['Federal Taxes'] FROM employees
	> WHERE deductions['Federal Taxes'] > cast(0.2 AS FLOAT);
Ross Man	     20000.0	0.3
Fred Finance     150000.0	0.3
```
注意：cast操作符内部语法：数值 AS FLOAT

实际上，还有第三种解决方案，即：和钱相关的都避免使用浮点数。

警告：对浮点数进行比较时，需要保持极端谨慎的态度。要避免任何从窄类型隐式转换到更广泛类型的操作。

2.3 LIKE 和 RLIKE

描述了LIKE和RLIKE谓词操作符。用户可能在之前已经见过LIKE的使用了，这里就不多解释了。

RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。

hive> SELECT name, address.street
	> FROM employees WHERE address.street RLIKE '.*(Chicago|Ontario).*';
Mary Smith	100	Ontario St.
Todd Jones	200	Chicago Ave.

有关正则的使用可以参考网上一些其他的案例。

3 GROUP BY 语句

GROUP BY语句通常会和聚合函数一起使用，按照一个或多个列对结果进行分组，然后对每个组的执行聚合操作。

HAVING 语句

HIVING子句允许用户通过一个简单的语法完成原本需要通过子查询才能对GROUP BY 语句产生的分组进行条件过滤的任务。如下是对平均收盘价要大于$50.0：

hive> SELECT year(ymd), avg(price_close) FROM stocks
	> WHERE exchange = 'NASDAQ' AND symbol = 'AAPL'
	> GROUP BY year(ymd)
	> HAVING avg(price_close) > 50.0;

如果没有哦HAVING子句，那么这个查询需要使用一个嵌套的SELECT子查询：\

hive> SELECT s2.year, s2.avg FROM
	> (SELECT year(ymd) AS year, avg(price_close) AS avg FROM stocks
    > WHERE exchange = 'NASDAQ' AND symbol = 'AAPL'
    > GROUP BY year(ymd)) s2
    > WHERE s2.avg > 50.0;

4 JOIN语句

Hive支持通常的SQL JOIN语句，但是只支持等值连接。

4.1 INNER JOIN

内连接（INNER JOIN）只有进行连接的两个表中都存与连接标准相匹配的数据才会被保留下来。

SELECT  a.ymd, a.price_close, b.price_close
FROM stocks a JOIN stocks b
ON a.ymd <= b.ymd
WHERE a.symbol = 'AAPL' AND b.symbol = 'IBM';

上面语句在Hive中是非法的，主要原因是通过MapReduce很难实现这中类型的连接。（连接条件为小于等于）

同时Hive目前还不支持在ON字句中的谓词间使用OR。

Hive的JOIN都是从左往右的，多个表JOIN的时候，先将左边的表连接起来启一个MapReduce job，然后将MapReduce job的输出和后面的那个表进行连接操作，以此类推。

大多数情况下，Hive会对每个JOIN连接对象启动一个MapReduce任务

4.2 JOIN优化

当对3个或者更多表进行JOIN连接时，如果每个ON子句都使用相同的连接键的话，那么只会产生一个MapReduce job。

Hive同时假定查询中最后一个表是最大的表。在对每行记录进行连接操作时，它会尝试将其他表缓存起来，然后扫描最后那个表进行计算。因此，用户需要保证连续查询中的表的大小从左到右是依次增大的。

幸运的是Hive提供了一个“标记”机制来显示地告之查询优化器哪张表是大表，使用方式如下：

SELECT /*+STREAMTABLE(s)*/ s.ymd, s.symbol, s.price_close, d.dividend FROM stocks s JOIN dividends d ON s.ymd = d.ymd AND s.symbol = d.symbol WHERE s.symbol = 'AAPL';

现在Hive将会尝试将表 stocks作为驱动表，即使其在查询中不是位于最后。

还有一种类似的非常重要的优化叫做map-side JOIN，可以参考4.9节的内容。

4.3 LEFT OUTER JOIN

左外连接通过关键字LEFT OUTER进行标识：

hive> SELECT s.ymd, s.symbol, s.price_close, d.dividend
	> FROM stocks s LEFT OUTER JOIN dividends d ON s.ymd=d.ymd AND s.symbol=d.symbol
	> WHERE s.symbol = 'AAPL';
...
1987-05-01	AAPL	80.0	NULL
1987-05-04	AAPL	19.75	NULL
1987-05-05	AAPL	80.25	NULL
1987-05-06	AAPL	80.0	NULL
1987-05-07	AAPL	80.25	NULL
1987-05-11	AAPL	77.0	0.015
1987-05-12	AAPL	75.0	NULL
...

在这种JOIN连接操作中，JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。JOIN操作符右边表中如果没有符号ON后面连接条件的记录时，那么从右边表指定选择的列的值将会是NULL。

4.4 OUTER JOIN

嵌套SELECT 语句会按照要求执行“下推”过程，在数据进行连接操作之前会先进行分区过滤。

提示：

WHERE语句在连接操作执行后才会执行，因此WHERE语句应该只用于那些非NULL值的列值。同时，和Hive的文档说明中相反的是，ON语句中的分区过滤条件外连接（OUTER JOIN）中是无效的，不过在内连接（INNER JOIN）中是有效的。

4.5 RIGHT OUTER ON

右外连接（RIGHT OUTER JOIN）会返回右边表所有符合WHERE语句的记录。左表中匹配不上的字段值用NULL代替。

4.6 FULL OUTER JOIN

完全外连接（FULL OUTER JOIN）将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。

4.7 LEFT SEMI-JOIN

左半开连接（LEFT SEMI-JOIN）会返回左边表的记录，前提是其记录对于右边表满足ON语句中的判定条件。

大多数的SQL方言会通过IN…EXISTS结构来处理这种情况。例如下面的示例：

SELECT s.ymd, s.symbol, s.price_close FROM stocks s
WHERE s.ymd, s.symbol IN
(SELECT d.ymd, d.symbol FROM dividends d);

不过，用户可以使用如下LEFT SEMI JOIN 语法达到同样的目的：

hive> SELECT s.ymd, s.symbol, s.price_close
	> FROM stocks s LEFT SEMI JOIN dividends d ON s.ymd = d.ymd AND s.symbol=d.symbol;

注意：SELECT和WHERE语句中不能引用到右表中的表

警告：Hive不支持右半开连接（RIGHT SEMI-JOIN）

SEMI-JOIN比通常INNER JOIN要更高效，原因是：对于左表中一条指定的记录，在右表中一旦找到匹配的记录，Hive就会立即停止扫描。从这点来看，左表中选择的列是可以预测的。

4.8 笛卡尔积JOIN

笛卡尔积是一种连接，表示左边表的行数乘以右边表的行数等于笛卡尔积结果集的大小。

SELECT * FROM stocks JOIN dividends;

上面这条语句，事实上很难找到合适的理由来执行这类连接，上述语句会产生 stock表数据条数乘以dividends表中数据条数的结果。它和其他连接类型不同，笛卡尔积不是并行执行的，而且使用MapReduce计算架构的话，任何方式都无法进行优化。

这里大家可以注意，如果使用了错误的连接（JOIN）语法可能会导致产生一个执行的时间长、运行缓慢的笛卡尔积查询。例如，下面这个查询在很多数据库中会被优化成内连接（INNER JOIN），但是在Hive中没有此优化：

hive> SELECT * FROM stocks JOIN dividends
	> WHERE stock.symbol = dividends.symbol and stock.symbol='APPL';

在Hive中，这个查询在应用WHERE语句中的谓词条件前会先进行笛卡尔积计算。这个过程将会消耗很长的时间。如果设置属性 hive.mapred.mode值为strict的话，Hive 会阻止用户执行笛卡尔积查询。

提示：当然笛卡尔积有时候也是很有用的

例如，假设一个表存放用户偏好，另一个表存放新闻文章，同时有一个算法会推测出用户可能会喜欢读那些文章。这个时候就需要使用笛卡尔积生成所有用户和所有网页的对应关系的集合

4.9 map-side JOIN

如果所以表中只有一张表是小表，那么可以在最大的表通过mapper的时候将小表完全放到内存中。Hive可以在map端执行连接过程（称为map-side JOIN），这是因为可以和内存中的小表进行逐一匹配，从而省掉常规连接操作所需要的reduce过程。即使对于很小的数据集，这个优化也明显要快于常规的连接操作。其不仅减少了reduce过程，而且有时还可以同时减少map过程的执行步骤。

在Hive v0.7之前的版本中需要增加一个标记来触发这个优化，但是从Hive v0.7版本开始，Hive废弃了这种标记方式，用户可以通过设置属性 hive.auto.convert.JOIN的值为true，这样Hive才会在必要的时候启动这个优化。默认情况下这个属性的值是false。

同时，用户也可以配置能够使用这个优化的小表的大小。如果是这个属性的默认值（单位：字节）：
hive.mapjoin.smalltable.filesize=25000000

如果用户期望Hive在必要的时候自动启动这个优化的话，可以将这个属性设置在***$HOME/.hiverc***文件中。

Hive对于右外连接（RIGHT OUTER JOIN）和全外连接（FULL OUTER JOIN）不支持这个优化。

如果所有的数据都是分桶的，那么对于大表，在特定的情况下同样可以使用这个优化。（关于分桶，后面我会详细说明）这里简单介绍一下，就是表中的数据必须是按照ON语句中的键进行分桶的，而且其中一张分桶的个数必须是另一张表分桶个数的若干倍。当满足这些条件时，那么Hive可以在map阶段按照分桶数据进行连接。因此这种情况下，不需要先获取到表中所有的内容，之后才去和另一张表中每个分桶进行匹配连接。

不过这个优化默认也是没有开启的。需要设置参数hive.optimize.bucketmapJOIN为true才可以开启此优化：
set hive.optimize.bucketmapJOIN=true

如果所涉及的分桶表都具有相同的分桶数，而且数据是按照连接键或桶的键进行排序的，那么这时Hive可以执行一个更快的分类-合并连接（sort-merge JOIN）。同样地，这个优化需要设置如下属性才能开启：

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

5 ORDER BY 和 SORT BY

和其他SQL一样会对查询的结果进行一个全局排序，也就是说会所有的数据最后会通过一个reducer进行处理，对于大数据集容易出现OOM，同时这个过程也需要花费很长时间。

Hive增加了一个可供选择的方式，也就是SORT BY，其只会在每个reducer中对数据进行排序，这可以保证每个reducer的输出数据都是有序的（但并非全局有序）。这样可以提高后面进行的全局排序的效率。

同时用户可以使ASC关键字（默认的）表示升序，或者加上DESC关键字，表示降序，来对结果集进行控制。

6 含有SORT BY 的DISTRIBUTE BY

DISTRIBUTE BY 控制map的输出在reducer中是如何划分的。MapReduce job这个传输的所有数据都是按照键-值对的方式进行组织的，因此Hive在将用户的查询语句转成MapReduce job时，其必须在内部使用这个功能。

通常用户不需要担心这个特性。不过对于使用了Streaming特性（参考后面14章的内容）以及一些状态为UDAF（用户自定义聚合函数）的查询是个例外。

默认情况下，MapReduce计算框架会依据map输入的键计算相应的哈希值，然后按照得到的哈希值将键-值对均匀分发到多个reducer中去。不过不幸的是，这也就意味着当我们使用SORT BY时，不同reducer的输出内容会有明显的重叠。（也正是因为如此会发生数据倾斜的情况）

假如我们希望具有相同股票交易码的数据在一起处理。那么我们可以使用DISTRIBUTE BY来保证具有相同股票交易码的记录会分发到同一个reducer中进行处理，然后使用SORT BY来按照我们的期望对数据进行排序。如下这个例子就演示了这种用法：

hive> SELECT s.ymd, s.symbol, s.price_close
	> FROM stocks s
	> DISTRIBUTE BY s.symbol
	> SORT BY s.symbol ASC, s.ymd ASC;

DISTRIBUTE BY 和 GROUP BY在其控制着reducer是如何接受一行行数据进行处理这方面是类似的，而SORT BY则控制着reducer内的数据是如何进行排序的。

需要注意的是，Hive要求DISTRIBUTE BY 语句要在 SORT BY 语句之前（即先分区在排序）。

7 CLUSTER BY

在前面的例子中，s.symbol列被用在了DISTRIBUTE BY 语句中，而s.symbol列和s.ymd位于 SORT BY 语句中。如果这2个语句中涉及到的列完全相同，而且采用的是升序排序方式（也就是默认的排序方式），那么在这种情况下，CLUSTER BY 就等价于前面的2个语句，相当于前面2个句子的一个简写。

如下例：

hive> SELECT s.ymd, s.symbol, s.price_close
	> FROM stocks s
	> CLUSTER BY s.symbol;

如果把第6小节中 SORT BY 后面的s.ymd ASC去掉，就可以使用CLUSTER BY替代 DISTRIBUTE BY … SORT BY。

8 类型装换

语法：cast(value AS TYPE)

例如：

SELECT name,salary FROM employees
WHERE cast(salary AS FLOAT) < 100000.0;

如果上例中的salary是一个不合法的浮点数字字符串的话，Hive会返回NULL。

将浮点数转换成整数的推荐方式是使用round()或者floor()函数，而不是使用类型转换操作符cast。

类型转换BINARY值

Hive v0.8.0版本中新引入的BINARY类型只支持BINARY类型转换成STRING类型。不过如果用户知道其值是数值的话，那么可以通过嵌套cast() 的方式对其进行类型转换，如下面例子，其中b字段类型是BINARY：

SELECT (2*cast(cast(b as string) as double)) from src;

用户同样可以将STRING类型转化为BINARY类型。

9 抽样查询

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive可以通过对表进行分桶抽样来满足这个需求。

我们可以使用rand()函数进行抽样，这个函数会返回一个随机值。前两个查询都返回了不相等的值，而第3个查询语句无返回结果：

hive> SELECT * from numbers TABLESAMPLE(BUCKET 3 OUT OF 10 ON rand()) s;
2
4
hive> SELECT * from numbers TABLESAMPLE(BUCKET 3 OUT OF 10 ON rand()) s;
7
10
hive> SELECT * from numbers TABLESAMPLE(BUCKET 3 OUT OF 10 ON rand()) s;

如果我们是按照指定的列而非rand()函数进行分桶的话，那么同一语句多次执行的返回值是相同的：

hive> SELECT * from numbers TABLESAMPLE(BUCKET 3 OUT OF 10 ON number) s;
2
hive> SELECT * from numbers TABLESAMPLE(BUCKET 5 OUT OF 10 ON number) s;
4
hive> SELECT * from numbers TABLESAMPLE(BUCKET 3 OUT OF 10 ON number) s;
2

分桶语句中的分母表示的是数据将会被散列的桶的个数，而分子表示将会选择的桶的个数：

hive> SELECT * from numbers TABLESAMPLE(BUCKET 1 OUT OF 2 ON number) s;
2
4
6
8
10
hive> SELECT * from numbers TABLESAMPLE(BUCKET 2 OUT OF 2 ON number) s;
1
3
5
7
9

9.1 数据块抽样

Hive提供了另外一种按照抽样百分比进行抽样的方式，这种是基于行数的，按照输入路径下的数据块百分比进行的抽样：

hive> SELECT * FROM numbersflat TABLESAMPLE(0.1 PERCENT) s

这总抽样不一定适用于所有的文件格式。另外，这种抽样的最小抽样单元式一个HDFS数据块。因此，如果表的数据大小小于普通的块大小128M的话，那么将会返回所有行。

基于百分比的抽样方式提供了一个变量，用于控制基于数据块的调优的种子信息：

<property>
	<name>hive.sample.seednumber</name>
    <value>0</value>
    <description>A number used for percentage sampling. By changing this number, user will change the subsets of data sampled.</description>
</property>

9.2 分桶表的输入裁剪

从上面看TABLESAMPLE语句，如下查询和TABLESAMPLE操作相同：

hive> SELECT * FROM numbersflat WHERE number % 2 = 0;
2
4
6
8
10

对于大多数类型的表确实是这样。抽样会扫描表中所有的数据，然后在每N行中抽取一行数据。不过如果TIMESAMPLE语句中指定的列和CLUSTERED BY语句中指定列相同，那么TABLESAMPLE查询就只会扫描涉及到的表的哈斯分区下的数据：

hive> CREATE TABLE numbers_bucketed(number int) CLUSTERD BY (number) INTO 3 BUCKETS;
hive> SET hive.enforce.bucketing=true;
hive> INSERT OVERWRITE TABLE numbers_bucketed SELECT number FROM numbers;

hive> dfs -ls /user/hive/warehouse/mydb.db/number_bucketed;
/user/hive/warehouse/mydb.db/number_bucketed/000000_0
/user/hive/warehouse/mydb.db/number_bucketed/000001_0
/user/hive/warehouse/mydb.db/number_bucketed/000002_0

hive> dfs -cat /user/hive/warehouse/mydb.db/numbers_bucketed/000001_0;
1
7
10
4

因为这个表以及聚集了3个数据桶了，所以下面的查询可以高效的对其中一个数据桶进行抽样：

hive> SELECT * FROM numbers_bucketed TABLESAMPLE(BUCKET 2 OUT OF 3 ON NUMBER) s;
1
7
10
4

10 UNION ALL

UNION ALL可以将2个或多个表进行合并。每一个union子查询都必需具有相同的列，而且对应的每个字段的字段类型必须是一致的。

UNION ALL也可以用于同一个表的数据合并。（从逻辑上讲，可以使用一个SELECT和WHERE语句来获相同的结果。所以这种方法便于将一个复杂的WHERE语句分割成2个或多个UNION子查询）。不过除非源表建立了索引，否则这个查询会将对同一分源数据进行多次拷贝分发。

例如：

FROM(
	FROM src SELECT src.key, src.value WHERE src.key < 100
    UNION ALL
    FROM src SELECT src.* WHERE src.key > 110
) unioninput
INSERT OVERWRITE DIRECTORY '/tmp/union.out' SELECT unioninput.*

csyigy

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
《Hive权威指南》第六章：查询

1.SELECT … FROM 语句SELECT是SQL中的射影算子。FROM子句标识了从哪个表、视图或嵌套查询中选择记录，SELECT指定了要保存的列以及输出函数需要调用的一个或多个列（例如，COUNT(*) 这样的聚合函数）。employees表：CREATE TABLE employees( name STRING, salary ...
复制链接

扫一扫