ANSI合规性

在Spark SQL中，有两个选项符合SQL标准： spark.sql.ansi.enabled 和 spark.sql.storeAssignmentPolicy （见下表以获取详细信息）。

当 spark.sql.ansi.enabled 设置为 true 时，Spark SQL 使用 ANSI 兼容的方言，而不是 Hive 兼容的。 例如，如果 SQL 操作符/函数的输入无效，Spark 将在运行时抛出异常，而不是返回 null 结果。 一些 ANSI 方言特性可能并不是直接来自 ANSI SQL 标准，但它们的行为与 ANSI SQL 的风格一致。

此外，Spark SQL 有一个独立的选项来控制在向表中插入行时的隐式类型转换行为。
类型转换行为在标准中被定义为存储分配规则。

当 spark.sql.storeAssignmentPolicy 被设置为 ANSI 时，Spark SQL 遵循 ANSI 存储分配规则。这是一个单独的配置，因为它的默认值是 ANSI ，而配置 spark.sql.ansi.enabled 默认是禁用的。

以下小节介绍了在启用ANSI模式时，算术运算、类型转换和SQL解析中的行为变化。对于Spark SQL中的类型转换，它们分为三种类型，本文将逐一介绍：转换、存储赋值和类型强制转换。

算术运算

在Spark SQL中，对数值类型（小数类型除外）进行的算术操作默认不检查溢出。 这意味着如果某个操作导致溢出，结果与Java/Scala程序中相应操作的结果相同（例如，如果两个整数的和超过可表示的最大值，结果是一个负数）。 另一方面，Spark SQL对小数溢出的返回值是null。 当 spark.sql.ansi.enabled 设置为 true 并且在数值和区间算术操作中发生溢出时，它会在运行时抛出算术异常。

-- `spark.sql.ansi.enabled=true`
SELECT 2147483647 + 1;
org.apache.spark.SparkArithmeticException: [ARITHMETIC_OVERFLOW] integer 溢出. 使用 'try_add' 来 容忍 溢出 并且 返回 NULL 代替. 如果 需要 设置 spark.sql.ansi.enabled 为 "false" 以 绕过 此 错误.
== SQL(line 1, position 8) ==
SELECT 2147483647 + 1
^^^^^^^^^^^^^^
SELECT abs(-2147483648);
org.apache.spark.SparkArithmeticException: [ARITHMETIC_OVERFLOW] integer 溢出. 如果 需要 设置 spark.sql.ansi.enabled 为 "false" 以 绕过 此 错误.
-- `spark.sql.ansi.enabled=false`
SELECT 2147483647 + 1;
+----------------+
|(2147483647 + 1)|
+----------------+
| -2147483648|
+----------------+
SELECT abs(-2147483648);
+----------------+
|abs(-2147483648)|
+----------------+
| -2147483648|
+----------------+

演员表

当 spark.sql.ansi.enabled 设置为 true 时，通过 CAST 语法进行的显式转换会对标准中定义的非法转换模式抛出运行时异常，例如从字符串转换为整数。

此外，ANSI SQL模式不允许在关闭ANSI模式时允许的以下类型转换：

• 数字 <=> 二进制
• 日期 <=> 布尔值
• 时间戳 <=> 布尔值
• 日期 => 数字

在 CAST 表达式中，源数据类型和目标数据类型的有效组合如下表所示。“Y”表示该组合在语法上是有效的，没有限制，而“N”表示该组合是无效的。

在上表中，所有带有新语法的 CAST 都标记为红色 Y :

• CAST(Numeric AS Numeric)：如果值超出目标数据类型的范围，则引发溢出异常。
• CAST(String AS (Numeric/Date/Timestamp/Timestamp_NTZ/Interval/Boolean))：如果值无法解析为目标数据类型，则引发运行时异常。
• CAST(Timestamp AS Numeric)：如果自纪元以来的秒数超出目标数据类型的范围，则引发溢出异常。
• CAST(Numeric AS Timestamp)：如果数值乘以1000000（每秒的微秒数）超出Long类型的范围，则引发溢出异常。
• CAST(Array AS Array)：如果在转换元素时出现任何异常，则引发异常。
• CAST(Map AS Map)：如果在转换键和值时出现任何异常，则引发异常。
• CAST(Struct AS Struct)：如果在转换结构字段时出现任何异常，则引发异常。
• CAST(Numeric AS String)：在将十进制值转换为字符串时，始终使用普通字符串表示法，而不是在需要指数时使用科学计数法。
• CAST(Interval AS Numeric)：如果日间间隔的微秒数或年-月间隔的月数超出目标数据类型的范围，则引发溢出异常。
• CAST(Numeric AS Interval)：如果数值乘以目标间隔的结束单位超出年-月间隔的Int类型或日间间隔的Long类型的范围，则引发溢出异常。

-- 显式转换的示例
-- `spark.sql.ansi.enabled=true`
SELECT CAST('a' AS INT);
org.apache.spark.SparkNumberFormatException: [CAST_INVALID_INPUT] 值 'a' 的 类型 "STRING" 不能 被 转换 为 "INT" 因为 它 是 格式不正确. 请修正 值 根据 语法, 或者 更改 其 目标 类型. 使用 `try_cast` 来 容忍 格式不正确 的 输入 并 返回 NULL 作为. 如果 必要 则设置 "spark.sql.ansi.enabled" 为 "false" 以 绕过 此 错误.
== SQL(行 1, 位置 8) ==
SELECT CAST('a' AS INT)
^^^^^^^^^^^^^^^^
SELECT CAST(2147483648L AS INT);
org.apache.spark.SparkArithmeticException: [CAST_OVERFLOW] 值 2147483648L 的 类型 "BIGINT" 不能 被 转换 为 "INT" 因为 溢出. 使用 `try_cast` 来 容忍 溢出 并 返回 NULL 作为. 如果 必要 则设置 "spark.sql.ansi.enabled" 为 "false" 以 绕过 此 错误.
SELECT CAST(DATE'2020-01-01' AS INT);
org.apache.spark.sql.AnalysisException: 无法 解析 'CAST(DATE '2020-01-01' AS INT)' 由于 数据 类型 不匹配: 无法 转换 date 为 int.
要 将 值 从 date 转换为 int, 你 可以 使用 函数 UNIX_DATE 作为.
-- `spark.sql.ansi.enabled=false` (这是默认行为)
SELECT CAST('a' AS INT);
+--------------+
|CAST(a AS INT)|
+--------------+
| null|
+--------------+
SELECT CAST(2147483648L AS INT);
+-----------------------+
|CAST(2147483648 AS INT)|
+-----------------------+
| -2147483648|
+-----------------------+
SELECT CAST(DATE'2020-01-01' AS INT)
+------------------------------+
|CAST(DATE '2020-01-01' AS INT)|
+------------------------------+
| null|
+------------------------------+
-- 存储赋值规则的示例
CREATE TABLE t (v INT);
-- `spark.sql.storeAssignmentPolicy=ANSI`
INSERT INTO t VALUES ('1');
org.apache.spark.sql.AnalysisException: [INCOMPATIBLE_DATA_FOR_TABLE.CANNOT_SAFELY_CAST] 无法 写入 不兼容的 数据 到 表 `spark_catalog`.`default`.`t`: 无法安全地 转换 `v`: "STRING" 到 "INT".
-- `spark.sql.storeAssignmentPolicy=LEGACY` (这是直到Spark 2.x的遗留行为)
INSERT INTO t VALUES ('1');
SELECT * FROM t;
+---+
| v|
+---+
| 1|
+---+

取整在铸造中

当将带有小数的十进制数转换为以SECOND作为结束单位的时间间隔类型（如INTERVAL HOUR TO SECOND）时，Spark将小数部分四舍五入到“最近的邻居”，除非两个邻居距离相等，在这种情况下向上舍入。

店铺分配

如开头所述，当 spark.sql.storeAssignmentPolicy 被设置为 ANSI (这是默认值)时，Spark SQL 遵循 ANSI 存储分配规则进行表插入。表插入中源数据类型和目标数据类型的有效组合在下表中给出。

* Spark不支持间隔类型的表列。

** 对于数组/映射/结构类型，数据类型检查规则递归适用于其组成元素。

在插入表时，Spark会因数值溢出而抛出异常。

CREATE TABLE test(i INT);
INSERT INTO test VALUES (2147483648L);
org.apache.spark.SparkArithmeticException: [CAST_OVERFLOW_IN_TABLE_INSERT] 失败 插入 一个 值 类型为 "BIGINT" 到 的 "INT" 类型 列 `i` 由于 溢出. 使用 `try_cast` 处理 输入 的 值 以 容忍 溢出 并且 返回 NULL 作为.

类型转换

类型提升与优先级

当 spark.sql.ansi.enabled 设置为 true 时，Spark SQL 使用几条规则来处理数据类型之间的冲突。解决冲突的核心是类型优先级列表，该列表定义给定数据类型的值是否可以隐式提高到另一种数据类型。

* 为了避免精度损失，最小公共类型解析中跳过浮点数。

** 字符串可以提升为多种数据类型。请注意，Byte/Short/Int/Decimal/Float不在此优先级列表中。Byte/Short/Int和字符串之间的最少共同类型是Long，而Decimal/Float之间的最少共同类型是Double。

*** 对于复杂类型，优先级规则递归适用于其组成元素。

未类型化的NULL适用特殊规则。NULL可以提升为任何其他类型。

这是优先级列表作为有向树的图形表示：

最小公共类型解析

一组类型中最不常见的类型是从优先级列表中通过该组类型的所有元素可到达的最狭窄类型。

最小公共类型解析用于：

• 推导期望共享参数类型的函数的参数类型，例如 coalesce、least 或 greatest。
• 推导操作符的操作数类型，例如算术运算或比较。
• 推导表达式的结果类型，例如 case 表达式。
• 推导数组和映射构造函数的元素、键或值类型。如果最小公共类型解析为 FLOAT，则应用特殊规则。对于 float 类型值，如果任何类型是 INT、BIGINT 或 DECIMAL，则最小公共类型提升为 DOUBLE，以避免潜在的数字丢失。

-- coalesce 函数接受任何类型的参数，只要它们具有最小公有类型。
-- 结果类型是参数的最小公有类型。
> SET spark.sql.ansi.enabled=true;
> SELECT typeof(coalesce(1Y, 1L, NULL));
BIGINT
> SELECT typeof(coalesce(1, DATE'2020-01-01'));
错误: 不兼容 类型 [INT, DATE]
> SELECT typeof(coalesce(ARRAY(1Y), ARRAY(1L)));
ARRAY<BIGINT>
> SELECT typeof(coalesce(1, 1F));
DOUBLE
> SELECT typeof(coalesce(1L, 1F));
DOUBLE
> SELECT (typeof(coalesce(1BD, 1F)));
DOUBLE
> SELECT typeof(coalesce(1, '2147483648'))
BIGINT
> SELECT typeof(coalesce(1.0, '2147483648'))
DOUBLE
> SELECT typeof(coalesce(DATE'2021-01-01', '2022-01-01'))
DATE

SQL 函数

函数调用

在ANSI模式下(spark.sql.ansi.enabled=true)，Spark SQL的函数调用：

• 一般来说，它遵循 Store assignment 规则，将输入值存储为SQL函数的声明参数类型
• 对于未类型化的NULL，有特殊规则。NULL可以提升为任何其他类型。

> SET spark.sql.ansi.enabled=true;
-- 隐式将 Int 转换为字符串类型
> SELECT concat('total number: ', 1);
total number: 1
-- 隐式将时间戳转换为日期类型
> select datediff(now(), current_date);
0
-- 隐式将字符串转换为双精度类型
> SELECT ceil('0.1');
1
-- 特殊规则：隐式将 NULL 转换为日期类型
> SELECT year(null);
NULL
> CREATE TABLE t(s string);
-- 不能将字符串列存储为数字类型。
> SELECT ceil(s) from t;
Error in query: cannot resolve 'CEIL(spark_catalog.default.t.s)' due to data type mismatch
-- 不能将字符串列存储为日期类型。
> select year(s) from t;
Error in query: cannot resolve 'year(spark_catalog.default.t.s)' due to data type mismatch

具有不同行为的函数

某些SQL函数的行为在ANSI模式下可能会有所不同 ( spark.sql.ansi.enabled=true ).

• size : 此函数对空输入返回空值。
• element_at :
  • 如果使用无效的索引，此函数将抛出 ArrayIndexOutOfBoundsException 。
• elt : 如果使用无效的索引，此函数将抛出 ArrayIndexOutOfBoundsException 。
• parse_url : 如果输入字符串不是有效的 URL，此函数将抛出 IllegalArgumentException 。
• to_date : 如果输入字符串无法解析或模式字符串无效，此函数应抛出异常。
• to_timestamp : 如果输入字符串无法解析或模式字符串无效，此函数应抛出异常。
• unix_timestamp : 如果输入字符串无法解析或模式字符串无效，此函数应抛出异常。
• to_unix_timestamp : 如果输入字符串无法解析或模式字符串无效，此函数应抛出异常。
• make_date : 如果结果日期无效，此函数应抛出异常。
• make_timestamp : 如果结果时间戳无效，此函数应抛出异常。
• make_interval : 如果结果时间间隔无效，此函数应抛出异常。
• next_day : 如果输入不是有效的星期几，此函数将抛出 IllegalArgumentException 。

SQL 操作符

某些SQL运算符的行为在ANSI模式下可能有所不同 ( spark.sql.ansi.enabled=true ).

• array_col[index] : 如果使用无效的索引，则此操作符会抛出 ArrayIndexOutOfBoundsException 。

ANSI模式下的有用函数

当ANSI模式开启时，它会对无效操作抛出异常。您可以使用以下SQL函数来抑制这些异常。

• try_cast ：与 CAST 相同， except that it returns NULL 结果，而不是在运行时错误上抛出异常。
• try_add ：与加法运算符 + 相同， except that it returns NULL 结果，而不是在整数值溢出时抛出异常。
• try_subtract ：与加法运算符 - 相同， except that it returns NULL 结果，而不是在整数值溢出时抛出异常。
• try_multiply ：与加法运算符 * 相同， except that it returns NULL 结果，而不是在整数值溢出时抛出异常。
• try_divide ：与除法运算符 / 相同， except that it returns NULL 结果，而不是在除以0时抛出异常。
• try_sum ：与函数 sum 相同， except that it returns NULL 结果，而不是在整数/小数/时间间隔值溢出时抛出异常。
• try_avg ：与函数 avg 相同， except that it returns NULL 结果，而不是在小数/时间间隔值溢出时抛出异常。
• try_element_at ：与函数 element_at 相同， except that it returns NULL 结果，而不是在数组索引越界时抛出异常。
• try_to_timestamp ：与函数 to_timestamp 相同， except that it returns NULL 结果，而不是在字符串解析错误时抛出异常。

SQL关键字（可选，默认禁用）

当 spark.sql.ansi.enabled 和 spark.sql.ansi.enforceReservedKeywords 都为真时，Spark SQL 将使用 ANSI 模式解析器。

使用ANSI模式解析器，Spark SQL有两种关键字：

• 非保留关键词：在特定上下文中具有特殊含义的关键词，可以在其他上下文中用作标识符。例如， EXPLAIN SELECT ... 是一个命令，但 EXPLAIN 可以在其他地方作为标识符使用。
• 保留关键词：被保留的关键词，不能用作表、视图、列、函数、别名等的标识符。

使用默认解析器，Spark SQL 有两种关键字：

• 非保留关键字：与启用ANSI模式时的定义相同。
• 严格非保留关键字：非保留关键字的严格版本，不能用作表别名。

默认情况下， spark.sql.ansi.enabled 和 spark.sql.ansi.enforceReservedKeywords 都是 false。

以下是Spark SQL中所有关键字的列表。