第6章：HiveQL：查询

SELECT…FROM语句

表结构:

1
2
3
4
5
6
7
8
CREATE TABLE employees (
    name             STRING,
    salary           FLOAT,
    subordinates     ARRAY<STRING>,
    deductions       MAP<STRING,FLOAT>,
    address          STRUCT<street:STRING,city:STRING,zip:INT>
)
PARTITION BY (country STRING, state STRING);

hive> SELECT name, subordinates, deductions address FROM employees;

John Doe [“Mary Smith”,”Todd Jones”] {“Federal Taxes”:0.2,”State Taxes”:0.05} {“street”:”1 Michigan Ave.”,”city”:”Chicago”,”state”:”IL”,”zip:60600}

Mary Smith [“Bill King”] {“Federal Taxes”:0.2,”State Taxes”:0.05} {“street”:”100 Ontario St.”,”city”:”Chicago”,”state”:”IL”,”zip:60601}

Todd Jones [] {“Federal Taxes”:0.15,”State Taxes”:0.03} {“street”:”200 Chicago Ave.”,”city”:”Oak Park”,”state”:”IL”,”zip:60700}

Bill King [] {“Federal Taxes”:0.15,”State Taxes”:0.03} {“street”:”300 Obscure Dr.”,”city”:”Obscuria”,”state”:”IL”,”zip:60100}

引用集合数据类型中的元素：

hive> SELECT name, subordinates[0] FROM employees;

John Doe Mary Smith

Mary Smith Bill King

Todd Jones NULL

Bill King NULL

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

引用MAP元素：

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

使用正则表达式来指定列

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

AAPL 195.69 197.88 194.0

AAPL 192.63 196.0 190.85

AAPL 196.73 198.37 191.57

AAPL 195.17 200.2 194.42

…

使用列值进行计算

hive> SELECT upper(name), salary, deductions[“Federal Taxes”], round(salary * (1 - deductions[“Federal Taxes”])) FROM employees;

算术运算符

算术运算符

如果数据类型不同，那么两种类型中值范围较小的那个数据类型将转换为其他范围更广的数据类型。

例如，对于INT和BIGINT运算，INT会将类型转换提升为BIGINT。对于INT和FLOAT运算，INT将提升为FLOAT。

当进行算术运算时，需要注意数据溢出或者数据下溢问题。Hive遵循的是底层Java中数据类型的规则，因此当溢出或者数据下溢发生时计算结果不会自动转换为更广泛的数据类型。 乘法和除法最有可能引发这个问题。

如果用户比较担心溢出和下溢，可以考虑在表模式中定义使用范围更广的数据类型。不过这样做的缺点是每个数据值会占用更多额外的内存。

使用函数

数学函数

数学函数

聚合函数

聚合函数

可以通过设置属性hive.map.aggr值位true来提高聚合性能，不过这个设置将需要更多地内存！

hive> SET hive.map.aggr=true;

表生成函数

当时用表生成函数时，Hive要求使用列别名。

表生成函数

其他内置函数

其他内置函数

LIMIT语句

同mysql

LIMIT子句用于限制返回的行数。

列别名

as …

CASE … WHEN … THEN 句式

同mysql

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

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

例如： SELECT * FROM employees;

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

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

SELECT * FROM employees

WHERE country=’US’ AND state=’CA’

LIMIT 100;

此外，

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

Hive还会尝试用用本地模式执行其他的操作，最好将这个设置增加到$HOME/.hiverc配置文件中。

WHERE 语句

谓词操作符

谓词操作符

关于浮点数比较

例如：

1
2
3
4
5
6
7
8
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

实际上0.2对于FLOAT类型是0.2000001，而对于DOUBLE类型是0.200000000001。这是因为一个8字节的DOUBLE值具有更多的小数位，当表中的FLOAT值通过Hive转换为DOUBLE值时，其产生的DOUBLE值时0.200000100000，这个值实际要比0.200000000001大。

规避方法：

1.如果我们从TEXTFILE文本文件中读取数据的话，Hive会从数据文件中读取字符串”0.2”，然后将其转换为一个真实的数字。可以在表模式中定义对应的字段类型为DOUBLE而不是FLOAT。这样我们就可以deductions[‘Federal Taxes’]这个DOUBLE值和0.2这个DOUBLE值进行比较。不过这种变化会增加我们查询时所需的内存消耗。

2.显式地指出0.2为FLOAT类型的。

1
2
3
SELECT name, salary, deductions['Federal Taxes'] 
FROM employees
WHERE deductions['Federal Taxes'] > cast(0.2 AS FLOAT);

3.和钱相关的都避免使用浮点数

LIKE和RLIKE

LIKE略过。。。

RLIKE：

1
2
3
4
5
6
7
SELECT name, address.street
FROM employees
WHERE address.street 
RLIKE '.*(Chicago|Ontario).*';

Mary Smith  100 Ontario St.
Todd Jones  200 Chicago Ave.

含义：

字符串中的点号.表示和任意的字符匹配

星号*表示重复“左边的字符串”零次到无数次

表达式(x|y)表示和x或者y匹配

JOIN语句

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

INNER JOIN

Hive中不支持的查询语句：

1
2
3
4
5
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目前还不支持在ON子句中的谓词间使用OR。

对于如下语句

1
2
3
4
5
6
7
SELECT a.ymd, a.price_close, b.price_close, c.price_close,  
FROM stocks a 
JOIN stocks b
ON a.ymd = b.ymd
JOIN stocks c
ON a.ymd = c.ymd
WHERE a.symbol = 'AAPL' AND b.symbol = 'IBM' AND c.symbol = 'GE';

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

本例中会首先启动一个MapReduce job对表a和表b进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表c进行连接操作。

Hive总是按照从左到右顺序执行的。

JOIN优化

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

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

    Hive提供了一个“标记”机制来显式地告之查询优化器哪张表是大表

    1
    2
    3
    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';

LEFT SIMI-JOIN

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

Hive不支持如下查询：

1
2
3
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语法达到同样的目的：

1
2
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语句中不能引用到右边表中的字段。

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

笛卡尔积JOIN

JOIN查询在很多数据库中会被优化成INNER JOIN，但是在Hive中没有此优化

map-side JOIN

如果所有表中只有一张表是小表，那么可以在最大的表通过mapper的时候将小表完全放到内存中。Hive可以在map端执行连接过程，这是因为Hive可以和内存中的小表进行逐一匹配，从而省略掉常规连接操作所需要的reduce过程。

1
2
3
SELECT /*+ MAPJOIN(d) */ 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';

通过设置set hive.auto.convert.join=true;可以使Hive在必要的时候启动这个优化。

也可以配置能够使用这个优化的小表的大小。

set hive.mapjoin.smalltable.filesize=25000000

ORDER BY 和 SORT BY

ORDER BY： 全局排序。所有的数据都通过一个reduce进行处理。比较耗时

SORT BY： 局部排序。只会在每个reducer中对数据进行排序，保证每个reducer的输出数据都是有序的。可以提高后面进行的全局排序的效率。

在sort by之前我们还要配置属性:set mapreduce.job.reduces=2;
配置两个ruducer，默认一个，不然sort by是没有用处的。

含有SORT BY的DISTRIBUTE BY

DISTRIBUTE BY采用hash算法，在map端将查询的结果中hash值相同的结果分发到对应的reduce文件中。

假设我们希望具有相同股票交易码的数据在一起处理。我们可以使用DISTRIBUTE BY来保证具有相同股票交易码的记录会分发到同一个reducer中进行处理，然后使用SORT BY来按照我们的期望对数据进行排序。

1
2
3
4
SELECT s.ymd, s.symbol, s.price_close
FROM stocks s
DISTRIBUTE BY s.symbol
SORT BY s.symbol ASC, s.ymd ASC;

需要注意的是，Hive要求DISTRIBUTE BY语句要写在SORT BY之前。

CLUSTER BY

CLUSTER BY 除了具有DISTRIBUTE BY的功能外，还会对该字段进行排序。

cluster by id = distribute by id +sort by id

注意：

1）cluster by 和sort by 不可以同时使用

2）当分组字段和排序字段是同一个字段的时候 cluster by id = distribute by id +sort by id

不是同一个字段的时候 请不要使用 cluster by id

四种排序的区别：

https://www.cnblogs.com/dcx-1993/p/11041228.html

类型转换

1
2
3
SELECT name,salary 
FROM employees
WHERE cast(salary AS FLOAT) < 10000.0;

如果salary字段的值不是合法的浮点数字符串的话，会返回NULL.

需要注意，将浮点数转换成整数的推荐方式是使用round()或者floor()

类型转换BINARY值

Hive v0.8.0版本中新引入的BINARY类型只支持将BINARY类型转换为STRING类型。

如果其值是数值的话，可以通过如下方式进行转换：

1
2
3
SELECT (2.0*cast(cast(b as STRING) as DOUBLE)) FROM src;

b为BINARY类型

抽样查询

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

假设numbers表只有number字段，其值是1到10

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

使用rand()函数(返回一个0到1范围内的随机数) 进行抽样

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;

无结果

数据块抽样

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

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

这种方式不一定适用于所有的文件格式。而且这种抽样的最小抽样单元是一个HDFS数据块。如果表的数据大小小于普通的块大小128MB的话，那么将会返回所有行。

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

1
2
3
4
5
<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>

分桶表的输入裁剪

对于大多数类型的表，如下查询与TABLESAMPLE操作相同

SELECT * FROM numbersflat WHERE number % 2 = 0;

0

2

4

6

8

10

不过，如果TABLESAMPLE语句中指定的列和CLUSTERED BY 语句中指定的列相同，那么TABLESAMPLE查询就只会扫描涉及到的表的hash分区下的数据：

hive> CREATE TABLE numbers_bucketed (number int) CLUSTERED BY (number) INTO 3 BUCKETS;

hive> INSERT OVERWRITE TABLE numbers_bucketed SELECT number FROM numbers;

hive> dfs -ls /user/hive/warehouse/mydb.db/numbers_bucketed;

/user/hive/warehouse/mydb.db/numbers_bucketed/000000_0

/user/hive/warehouse/mydb.db/numbers_bucketed/000001_0

/user/hive/warehouse/mydb.db/numbers_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

UNION ALL

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