【大数据】HiveQL的查询操作

G皮T

已于 2023-08-26 10:50:53 修改

阅读量865

点赞数 6

分类专栏： # Hive 文章标签： big data hive 大数据数据仓库 HiveQL

于 2022-06-02 22:47:04 首次发布

本文链接：https://blog.csdn.net/be_racle/article/details/125107940

版权

Hive 专栏收录该内容

10 篇文章 1 订阅

订阅专栏

HiveQL的查询操作

1. SELECT … FROM …

我们首先创建一个 employees 表，以供后续查询需要。

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

以下是一些基础的查询语句。

SELECT name, salary FROM employees;

SELECT name, subordinates[0] FROM employees;

SELECT name, deductions["State Taxes"] FROM employees;

SELECT name, address.city FROM employees;

（1）使用正则表达式来指定列

SELECT symbol, 'price.*' FROM stocks;

（2）使用列值进行计算

SELECT upper(name), salary, deductions["Federal Taxes"],
       round(salary * (1 - deductions["Federal Taxes"])) 
FROM employees;

（3）常用的函数

函数类型	举例
数学函数	`round()`、`exp()`、`pow()`、`sqrt()`、`abs()` 等等
聚合函数	`count()`、`sum()`、`avg()`、`max()`、`min()` 等等
表生成函数	`explode()`、`parse_url_tuple()` 等等
其他内置函数	`concat()`、`reverse()`、`substr()`、`datediff()` 等等

（4）LIMIT 语句

限制返回的行数。

SELECT upper(name), salary, deductions["Federal Taxes"],
       round(salary * (1 - deductions["Federal Taxes"])) 
FROM employees LIMIT 2;

（5）设置列别名

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;

（6）嵌套 SELECT 语句

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;

（7）CASE … WHEN … THEN …

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;

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

本地模式的查询不必使用 MapReduce。

SELECT * FROM employees;

对于 WHERE 语句中过滤条件只是分区字段，这种情况也是无需 MapReduce 过程的。

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

如果属性 hive.exec,mode.local.auto 的值设置为 true 的话，Hive 还会尝试使用本地模式执行其他的操作。否则，Hive 使用 MapReduce 来执行其他所有的查询。

2. WHERE 语句

不能在 WHERE 语句中使用列别名，但是可以使用一个嵌套的 SELECT 语句。注意，下面这种写法是错误的。

SELECT name, salary, deductions["Federal Taxes"],
       salary * (1 - deductions["Federal Taxes"])
FROM employees
WHERE round(salary * (1 - deductions["Federal Taxes"])) > 70000;

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

SELECT name, address.street
FROM employees WHERE address.street RLIKE '.*(Chicago|Ontario).*';

字符串中的点号 (.) 表示和任意的字符匹配，星号 (*) 表示重复 “左边的字符串” （在上面这个例子中为点号）0 次到无数次。表达式 (x|y) 表示和 x 或者 y 匹配。

SELECT name, address FROM employees
WHERE address.street LIKE '%Chicago%' OR address.street LIKE '%Ontario%';

3. GROUP BY 语句

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

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

4. JOIN 语句

（1）INNER JOIN

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

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 进行连接操作。

（2）JOIN 优化

在上面的例子中，每个 ON 子句都使用到了 a.ymd 作为其中一个 JOIN 连接键。在这种情况下，Hive 通过一个优化可以在同一个 MapReduce job 中连接 3 张表。同样，如果 b.ymd 也用于 ON 子句的话，那么也会应用到这个优化。

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';

（3）LEFT OUTER JOIN

在这种连接操作中，JOIN 操作符左边表中符合 WHERE 子句的所有记录将会被返回。

（4）OUTER JOIN

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

（5）RIGHT OUTER JOIN

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

（6）FULL OUTER JOIN

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

（7）LEFT SEMI JOIN

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

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

Hive 不支持右半开连接（RIGHT SEMI JOIN）。

（8）笛卡尔积 JOIN

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

笛卡尔积会产生大量的数据，和其他连接类型不同，笛卡尔积不是并行执行的，而且使用 MapReduce 计算架构的话，任何方式都无法进行优化。如果使用了错误的 JOIN 语法，可能会导致产生一个执行时间长、运行缓慢的笛卡尔积查询。

（9）map side JOIN

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

在 Hive 0.7 之前的版本，如果想使用这个优化，需要在查询语句中增加一个标记来进行触发。

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';

从 Hive 0.7 版本开始，废弃了这种标记的方式，不过如果增加了这个标记同样是有效的。如果不加上这个标记，，那么这时用户需要设置属性 hive.auto.convert.JOIN 的值为 true。默认情况下，这个属性的值是 false。

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

5. ORDER BY 和 SORT BY

Hive 中 ORDER BY 语句和其他 SQL 语言定义是一样的。其会对查询结果集执行一个全局排序，也就是说会有一个所有的数据都通过一个 reducer 进行处理的过程。对于大数据集这个过程可能会耗费很多的时间。

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

SELECT s.ymd, s.symbol, s.price_close
FROM stocks s
ORDER BY s.ymd ASC, s.symbol DESC;

SELECT s.ymd, s.symbol, s.price_close
FROM stocks s
SORT BY s.ymd ASC, s.symbol DESC;

6. 含有 SORT BY 的 DISTRIBUTE BY

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

默认情况下，MapReduce 计算框架会依据 map 输入的键计算相应的哈希值，然后按照得到的哈希值将键-值对均匀分发到多个 reducer 中去。但不幸的是，这也就意味着当我们使用 SORT BY 时，不同 reducer 的输出内容会有明显的重叠，至少对于排列顺序而言是这样的，即使每个 reducer 的输出的数据都是有序的。

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

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 个句子的一个简写方式。

如下面的例子所示，我们将前面的查询语句中 SORT BY 后面的 s.ymd 字段去掉，而只对 s.symbol 字段使用 CLUSTER BY 语句。

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

使用 DISTRIBUTE BY … SORT BY 语句或其简化版的 CLUSTER BY 语句会剥夺 SORT BY 的并行性，然而这样可以实现输出文件的数据是全局排序的。

8. 类型转换

Hive 会在适当的时候，对数值型数据类型进行隐式类型转换。不过，用户也可以使用 cast() 函数对指定的值进行显式的类型转换。
转换语法：cast(value AS TYPE)

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

9. 抽样查询

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

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

SELECT * from numbers TABLESAMPLE(BUCKET 3 OUT OF 10 ON number) s;

SELECT * from numbers TABLESAMPLE(BUCKET 1 OUT OF 2 ON number) s;

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

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

SELECT * FROM numbersflat TABLESAMPLE(0.1 PERCENT) s;

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

10. UNION ALL

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

SELECT log.ymd, log.level, log.message
  FROM (
    SELECT l1.ymd, l1.level, l1.message, 'Log1' AS source
    FROM log1 l1
  UNION ALL
    SELECT l2.ymd, l2.level, l2.message, 'Log2' AS source
    FROM log1 l2
  ) log
SORT BY log.ymd ASC;