Hive 基本教程

Hive SQL

Hive 笔记

original icon
版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://www.knowledgedict.com/tutorial/hive-sql-dml.html

Hive SQL 数据操作(DML)详解


Hive SQL 数据操作主要是针对表的增删改查,和标准 SQL 类似,其中最常用的是查询语句,即 SELECT 相关语句。

Hive 查询

Hive 查询语句是 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]

最简单的查询语句,查询 t1 表的所有字段,代码如下:

SELECT * FROM t1

WHERE 子句

WHERE 子句是 boolean 表达式,例如,查找年龄大于 25 岁,性别为女的记录:

SELECT * FROM t1 WHERE age > 25 AND sex = 'female'

ALL 和 DISTINCT 子句

ALL 和 DISTINCT 选项指定是否要返回重复的行。如果没有给出该选项,则表示使用默认值 ALL(即返回所有匹配的行);DISTINCT 选项为指定从结果集中删除重复的行。

值得注意的是,DISTINCT 选项从 1.1.0 版本开始支持

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

更多 DISTINCT 相关内容:

基于分区查询

当指定表有分区定义(即 PARTITIONED BY 子句),查询可以在指定的分区范围内进行,否则会扫描整个表;此外,要想让分区内查询,必须在 WHERE 或 JOIN ... ON ... 子句上明确指定分区字段信息。

假设表的分区信息是日期字段 dt,那分区查询示例如下:

SELECT * FROM t1
WHERE dt >= '2020-09-15' AND dt <= '2020-10-08'

如果表 t1 和 t2 通过 JOIN 关联时,若要分区起作用,需要在 ON 上指定分区信息,示例如下:

SELECT t1.*
FROM t1 JOIN t2
  ON (t1.user_id = t2.id AND t1.dt >= '2020-09-15' AND t1.dt <= '2020-10-08')

GROUP BY 子句

Hive 提供 GROUP BY 子句做聚合操作。

SELECT col1 FROM t1 GROUP BY col1

Having 子句

聚合语句 GROUP BY 常和 HAVING 子句结合使用,用于指定聚合条件。

Hive 在 0.7.0 版本开始支持 HAVING,之前老版本需要通过其它 SELECT 子句才能做到,示例如下:

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 条记录,示例如下:

SELECT * FROM t1 LIMIT 5

返回第 3 个到第 7 个的记录,代码如下:

SELECT * FROM t1 LIMIT 2, 5

UNION 子句

hive 的查询中 UNION 子句将两个结构相同的表合并起来进行操作,UNION 可以单独使用,也可以 UNION ALL 搭配使用,两者区别如下:

  • UNION ALL 将两个表进行不去重合并,同时也没任何排序,由于只是简单地将两个列相同的表合并,所以效率比较快。
  • 通过 UNION 合并的表,会对结果集进行去重,同时进行默认规则的排序,作用等价于先 DISTINCT 去重、再 UNION ALL 表合并、再 ORDER BY 排序,效率方面它是 UNION ALL 的3倍,所以,数据量大时,不建议直接用 UNION。
select_statement UNION [ALL | DISTINCT] select_statement UNION [ALL | DISTINCT] select_statement ...

更多相关信息参考 Hive UNION 和 UNION ALL 用法详解

LETERAL VIEW

LETERAL VIEW 要与 UDTF 函数一起使用,比如 EXPLODE() 函数。LETERAL VIEW 首先将 UDTF 函数应用到每一行上,这时每一行经 UDTF 处理后得到多行输出,这些输出将会组建成一张虚拟表,然后这张虚拟表会跟当前表进行 JOIN 操作,JOIN 完成之后会得出一张结果虚拟表,这张结果表里就有了 UDTF 生成的列,当然原表的列除了 UDTF 消耗的列之外肯定也在都里面。

语法如下:

LATERAL VIEW udtf_function( expression [, ...] ) [ table_identifier ] AS column_identifier [, ...]

更多信息,请参见 LETERAL VIEW 的详细用法

MySQL 的 DML 遵守 SQL 的 DML 规范,数据操作语言(Data Manipulation Language),它主要用于添加 ...
HiveQL 是 Hive 的查询语言。和普遍使用的所有 SQL 方言一样,它不完全遵守任何一种 ANSI SQL 标准的修订版。HiveQ ...
Hive 提供了取模操作的函数 pmod,也叫做取余函数,它可以处理 int 或 double 类型参数,返回取余整数或浮点数。 ...
Hive SQL 条件函数 IF 是用于处理单个列的判断查询结果,形式为 if(条件表达式, 结果1, 结果2),相当于 java 中的三目 ...
Hive SQL 内置支持绝大多数 SQL 的内置函数,其中主要包括数学函数,类型转换函数,条件函数,字符函数,聚合函数,表生成函数等等。 ...