Hive 基本教程

Hive SQL

Hive 笔记

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