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

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 的语法区别

（1）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）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;

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

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

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

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

SELECT  * FROM (SELECT * FROM t);

（4）关于窗口函数。

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 用于进一步指定窗口的大小和位置。

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

参考文献

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