Hive 与 SQL 标准和主流 SQL DB 的语法区别_hive语言和sql

1.Hive 简介

Hive是一种基于Hadoop的数据仓库软件，可以将结构化数据文件映射为一张数据库表，并提供了类SQL查询接口，使得用户可以使用SQL类语言来查询数据。Hive可以处理包括文本、CSV、JSON、ORC和Parquet等格式的数据文件，支持数据的导入、导出、转换等操作。Hive可以在Hadoop集群上运行，利用Hadoop的分布式计算能力，可以处理大规模的数据集。

Hive基于Hadoop MapReduce进行计算，并提供了用于数据处理和分析的一系列工具和库，例如HiveQL（类SQL查询语言）、UDF（用户自定义函数）、HiveServer、Hive Metastore等。HiveQL类SQL查询语言支持大多数标准SQL操作，如SELECT、JOIN、GROUP BY、HAVING、ORDER BY等，同时还支持自定义函数和自定义聚合函数。

Hive还支持数据仓库中常见的分区、分桶、索引等特性，可以提高查询性能和数据管理效率。Hive可以与其他大数据工具和框架进行集成，例如Hadoop、HBase、Spark、Pig等，可以方便地进行数据处理和分析。

总之，Hive是一个强大的数据仓库工具，提供了方便的SQL查询接口和大规模数据处理能力，可以帮助用户快速构建和管理数据仓库，进行数据分析和挖掘。

2.Hive 与 SQL 标准和主流 SQL DB 的语法区别

2.1 关于 DDL

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

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

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

原因是 从 Hive 2.1.0 开始，Hive 支持不验证的主键和外键约束，可以使用 DISABLE NOVALIDATE 关键字修饰主键约束。

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

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

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

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

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

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

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

目标表 target_table 定义如下：

CREATE TABLE target_table (
  a STRING,
  b STRING,
  c STRING
);
1
2
3
4
5

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

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

应该是：

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

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

正确的语句应该是：

SELECT col1, AVG(col2) as avg_col
FROM my_table
GROUP BY col1;
1
2
3

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

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

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

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

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

SELECT  * FROM (SELECT * FROM t);
1

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

其中，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)
1

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

CAST(timestamp/1000 AS BIGINT)
1

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

---

参考文献

LanguageManual - Apache Hive
MySQL 8.0 Reference Manual :: 14.20 Window Functions