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

2024-05-12 08:25:47 浏览数 (2)

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 列如下:

代码语言:javascript复制
CREATE TABLE source_table (
  a STRING,
  b STRING,
  c STRING
);

目标表 target_table 定义如下:

代码语言:javascript复制
CREATE TABLE target_table (
  a STRING,
  b STRING,
  c STRING
);

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

代码语言:javascript复制
INSERT OVERWRITE TABLE target_table(a, c)
SELECT a, c FROM source_table;

应该是:

代码语言:javascript复制
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 的平均值。以下是一个错误的查询语句:

代码语言:javascript复制
SELECT col1 AS my_col, AVG(col2) as avg_col
FROM my_table
GROUP BY my_col;

正确的语句应该是:

代码语言:javascript复制
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 子句中使用相同的别名。例如:

代码语言:javascript复制
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 中是可以不加别名。

代码语言:javascript复制
SELECT  * FROM (SELECT * FROM t);

(4)关于窗口函数。

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

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

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

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

代码语言:javascript复制
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

0 人点赞