当前位置：首页 > news >正文

Hive 中书写SQL注意的地方

news 来源：原创 2025/8/25 20:31:18

1.1 关于 DDL

（1）创建带有主键约束的 Hive 表时报错。

Hive 目前还没有严格支持“主键约束”，创建带有主键约束的 HIVE 表时报了如下错误：

SemanticException [Error 10326]: Invalid Constraint: syntax ENABLE/ENFORCED feature not supported yet.Please use DISABLE/NOT ENFORCED instead.

2）DROP TABLE 时，使用反引号无法引用表名带有点号的表。

Hive 表名无意间加入点号，即使使用反引号将表名扩住，DROP TABLE 时仍报错。

DROP TABLE IF EXISTS test.`test.my_hive_tbl`;

这个应该是 Hive SQL 的不完善的地方。

解决办法是先切换数据库，然后不带库名单独指定表名再 DROP。

USE test;
DROP TABLE IF EXISTS `test.my_hive_tbl`;

2.2 关于 DML
（1）使用 INSERT 向数据表插入时，Hive 必须制定 TABLE 关键字。

在 Hive 中，使用 INSERT INTO 或 INSERT OVERWRITE 向表插入数据时，不指定 TABLE 关键字将会报错。

可见 Hive SQL 和 SQL 还是有不少区别之处。比如 MySQL 是不需要 TABLE 关键字的。

（2）Hive 通过 SELECT 进行 INSERT INTO TABLE 时，无法指定待插入的列。

这是因为 Hive SQL 中的 INSERT 子句是按照列的位置顺序进行插入的，而不是按照列名进行插入的，并且要插入全部字段。

这与 SQL 标准语法有所差异。比如 MySQL 在 INSERT 时可以指定目标表的列。

解决办法是严格保证 INSERT 语句中的字段和建表语句中的字段的顺序一致，如果没有则显示指定缺省值。

比如源表 source_table 列如下：

CREATE TABLE source_table (
a STRING,
b STRING,
c STRING
);

目标表 target_table 定义如下：

CREATE TABLE target_table (
a STRING,
b STRING,
c STRING
);

如果从 source_table 查询 a 和 c 插入到 target_table，插入时指定列名将报错：

INSERT OVERWRITE TABLE target_table(a, c)
SELECT a, c FROM source_table;

应该是

INSERT OVERWRITE TABLE target_table
SELECT a, NULL, c FROM source_table;

2.3 关于 DQL
（1）Hive 中不能将列别名用于 GROUP BY 子句。

在 Hive 中，不能在 GROUP BY 子句中使用列别名，只能使用原始列名。这是因为在查询执行顺序中，GROUP BY 子句是在列别名之后执行的，因此无法识别别名。

假设有一个表 my_table 包含两个列 col1 和 col2，其中 col1 的值为 ‘A’ 或 ‘B’，col2 的值为整数。现在需要按照 col1 列的值进行分组，并计算每组中 col2 的平均值。以下是一个错误的查询语句：

SELECT col1 AS my_col, AVG(col2) as avg_col
FROM my_table
GROUP BY my_col;

正确的
SELECT col1, AVG(col2) as avg_col
FROM my_table
GROUP BY col1;

SQL 标准GROUP BY 子句中不允许使用列别名，只能使用原始列名。这与 HiveSQL 中的规则相同。

MySQL 在某些情况下支持使用列别名，但是这并不是标准行为。具体而言，如果使用的是 MySQL 5.7.5 或更高版本，并且在 SELECT 子句中使用了列别名，则可以在 GROUP BY 子句和 ORDER BY 子句中使用相同的别名。例如：

SELECT col1 + col2 AS my_col, COUNT(*)
FROM my_table
GROUP BY my_col
ORDER BY my_col DESC;

2）Hive 子查询一定要设置别名。

Hive 即使不需要对子查询进行引用，也要设置别名。

在 SQL 标准中，并没有强制规定子查询一定要设置别名。不同的数据库实现可能会有不同的规定。但是，大多数数据库实现都要求子查询设置别名，比如 MySQL、SQL Server 和 PostgreSQL。

而在 Oracle 中是可以不加别名。

SELECT * FROM (SELECT * FROM t);

（3）关于窗口函数。

Hive 对窗口函数的支持比较早，自 Hive 0.11 版本便开始支持窗口函数。

而 MySQL 从 8.0 版本才开始支持窗口函数。

窗口函数是一种基于窗口（Window）的计算方式，可以对数据集中的一个子集（称为窗口）进行聚合、排序、排名等操作，同时不影响原始数据集中的数据。

通常的窗口函数的语法如下：

function_name(expression) OVER (
[PARTITION BY partition_expression, ... ]
[ORDER BY sort_expression [ASC | DESC], ... ]
[ROWS | RANGE frame_specification]
)

其中，function_name(expression) 表示要进行计算的窗口函数和参数；PARTITION BY 子句用于对数据进行分组，确定窗口的大小；ORDER BY 子句用于对数据进行排序，确定窗口的位置；ROWS 或者 RANGE 用于指定窗口的类型；frame_specification 用于进一步指定窗口的大小和位置。

需要注意的是，不同的数据库实现对于窗口函数语法的支持和细节可能会有所不同，实际使用中需要查看所使用的数据库实现的文档，以了解其具体的语法和使用方式。

（4）关于全连接。
Hive 支持全连接，即 FULL JOIN 或 FULL OUTER JOIN。

全连接将两个表中的所有行连接起来，并返回包含左表、右表和笛卡尔积的结果。如果左表或右表中没有与另一个表中的某一行匹配的行，则返回 NULL 值。

对于 MySQL 来说，则不支持。但是我们可以对左连接和右连接的结果做 UNION 操作（会去除重复行）来实现。

2.4 关于 Functions
（1）日期时间函数 FROM_UNIXTIME 将毫秒级时间戳转换为日期报错。

Hive SQL 中使用 FROM_UNIXTIME(timestamp/1000, ‘yyyy-MM-dd’) 转为日期时，其中 timestamp 为 bigint 类型的毫秒时间戳，除以 1000 后报了如下错误：

UDFFromUnixTime with (double). Possible choices:_FUNC_(bigint) _FUNC_(bigint, string) _FUNC_(int) _FUNC_(int, string)

这里将 timestamp/1000 结果由 bigint 变为 double，需要使用使用 CAST() 函数将 double 类型转换为 bigint 类型。

CAST(timestamp/1000 AS BIGINT)

另外，日期格式也与 MySQL 存在区别，MySQL 中日期格式为%Y-%m-%d。

1.1 关于 DDL

相关文章：