Spark SQL----ANSI Compliance

在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程序中的相应操作相同(例如，如果两个integers的总和大于可表示的最大值，则结果为负数)。另一方面，Spark SQL对于小数溢出返回null。当spark.sql.ansi.enabled设置为true并且在数字和间隔(interval)算术运算中发生溢出时，它会抛出一个算术异常。

-- `spark.sql.ansi.enabled=true`
SELECT 2147483647 + 1;
org.apache.spark.SparkArithmeticException: [ARITHMETIC_OVERFLOW] integer overflow. Use 'try_add' to tolerate overflow and return NULL instead. If necessary set spark.sql.ansi.enabled to "false" to bypass this error.
== SQL(line 1, position 8) ==
SELECT 2147483647 + 1
       ^^^^^^^^^^^^^^

SELECT abs(-2147483648);
org.apache.spark.SparkArithmeticException: [ARITHMETIC_OVERFLOW] integer overflow. If necessary set spark.sql.ansi.enabled to "false" to bypass this error.

-- `spark.sql.ansi.enabled=false`
SELECT 2147483647 + 1;
+----------------+
|(2147483647 + 1)|
+----------------+
|     -2147483648|
+----------------+

SELECT abs(-2147483648);
+----------------+
|abs(-2147483648)|
+----------------+
|     -2147483648|
+----------------+

二、Cast

当spark.sql.ansi.enabled设置为true时，CAST语法的显式强制转换将为标准中定义的非法强制转换patterns抛出运行时异常，例如从string强制转换为integer。此外，ANSI SQL模式不允许以下类型转换，它们在ANSI模式关闭时是允许的:

• Numeric <=> Binary
• Date <=> Boolean
• Timestamp <=> Boolean
• Date => Numeric

下表给出了CAST表达式中源数据类型和目标数据类型的有效组合。“Y”表示该组合在语法上不受限制地有效，“N”表示该组合无效。
此表格见这里。
在上表中，所有具有新语法的CAST都标记为红色Y：
CAST（Numeric AS Numeric）：如果值超出目标数据类型的范围，则引发溢出异常。
CAST（String AS (Numeric/Date/Timestamp/Timestamp_NTZ/Interval/Boolean)）：如果无法将值解析为目标数据类型，则引发运行时异常。
CAST（Timestamp AS Numeric）：如果自epoch以来的秒数超出目标数据类型的范围，则引发溢出异常。
CAST（Numeric AS Timestamp）：如果numeric值乘以1000000（微秒/秒）超出Long类型的范围，则引发溢出异常。
CAST（Array AS Array）：如果元素的转换中有异常，则引发异常。
CAST（Map AS Map）：如果在key和value的转换上有任何异常，则引发异常。
CAST（Struct AS Struct）：如果在struct字段的转换上存在异常，则引发异常。
CAST（Numeric AS String）：将十进制值强制转换为字符串时，始终使用纯字符串表示法，而不是在需要指数时使用科学表示法
CAST（Interval AS Numeric）：如果day-time interval的微秒数或year-month interval的月数超出目标数据类型的范围，则引发溢出异常。
CAST（Numeric AS Interval）：如果目标interval的结束单位的numeric时间超出Int类型（year-month intervals）或Long类型（day-time intervals）的范围，则引发溢出异常。

-- Examples of explicit casting

-- `spark.sql.ansi.enabled=true`
SELECT CAST('a' AS INT);
org.apache.spark.SparkNumberFormatException: [CAST_INVALID_INPUT] The value 'a' of the type "STRING" cannot be cast to "INT" because it is malformed. Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.
== SQL(line 1, position 8) ==
SELECT CAST('a' AS INT)
       ^^^^^^^^^^^^^^^^

SELECT CAST(2147483648L AS INT);
org.apache.spark.SparkArithmeticException: [CAST_OVERFLOW] The value 2147483648L of the type "BIGINT" cannot be cast to "INT" due to an overflow. Use `try_cast` to tolerate overflow and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.

SELECT CAST(DATE'2020-01-01' AS INT);
org.apache.spark.sql.AnalysisException: cannot resolve 'CAST(DATE '2020-01-01' AS INT)' due to data type mismatch: cannot cast date to int.
To convert values from date to int, you can use function UNIX_DATE instead.

-- `spark.sql.ansi.enabled=false` (This is a default behaviour)
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|
+------------------------------+

-- Examples of store assignment rules
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] Cannot write incompatible data for table `spark_catalog`.`default`.`t`: Cannot safely cast `v`: "STRING" to "INT".

-- `spark.sql.storeAssignmentPolicy=LEGACY` (This is a legacy behaviour until Spark 2.x)
INSERT INTO t VALUES ('1');
SELECT * FROM t;
+---+
|  v|
+---+
|  1|
+---+

2.1 Rounding in cast

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

三、存储分配

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

• Spark不支持interval类型的表列。
• 对于Array/Map/Struct类型，数据类型检查规则递归地应用于其组件元素。

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

CREATE TABLE test(i INT);

INSERT INTO test VALUES (2147483648L);
org.apache.spark.SparkArithmeticException: [CAST_OVERFLOW_IN_TABLE_INSERT] Fail to insert a value of "BIGINT" type into the "INT" type column `i` due to an overflow. Use `try_cast` on the input value to tolerate overflow and return NULL instead.

四、Type coercion

4.1 类型提升和优先级

当spark.sql.ansi.enabled设置为true时，Spark SQL使用一些规则来管理如何解决数据类型之间的冲突。这种冲突解决的核心是类型优先级列表，它定义了给定数据类型的值是否可以隐式地提升到另一个数据类型。

• 对于最不常见的类型解析，float被跳过以避免精度损失。
• 字符串可以提升为多种数据类型。请注意，Byte/Short/Int/Decimal/Float不在此precedent列表中。Byte/Short/Int和String之间最不常见的类型是Long, Decimal和Float之间最不常见的类型是Double。
• 对于复杂类型，优先级规则递归地应用于其组成元素。特殊规则适用于未类型化的NULL。NULL可以被提升为任何其他类型。这是优先级列表作为有向树的图形描述:
  

4.2 最不常见类型解析

一组类型中最不常见的类型是该组类型的所有元素从优先级列表中可访问的最窄（narrowest）类型。
最不常见的类型解析用于：

• 派生函数的参数类型，这些函数需要多个参数的共享参数类型，例如coalesce、最小或最大。
• 派生算术运算或比较等运算符的操作数类型。
• 派生表达式（如大小写表达式）的结果类型。
• 派生数组和映射构造函数的元素、键或值类型。如果最不常见的类型解析为FLOAT，则应用特殊规则。对于浮点类型值，如果任何类型是INT、BIGINT或DECIMAL，则最不常见的类型将被推到DOUBLE，以避免潜在的数字丢失。

-- The coalesce function accepts any set of argument types as long as they share a least common type. 
-- The result type is the least common type of the arguments.
> SET spark.sql.ansi.enabled=true;
> SELECT typeof(coalesce(1Y, 1L, NULL));
BIGINT
> SELECT typeof(coalesce(1, DATE'2020-01-01'));
Error: Incompatible types [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函数

5.1 函数调用

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

• 通常，它遵循存储分配规则，将输入值存储为SQL函数的声明参数类型
• 特殊规则适用于非类型化的NULL。NULL可以升级为任何其他类型。

> SET spark.sql.ansi.enabled=true;
-- implicitly cast Int to String type
> SELECT concat('total number: ', 1);
total number: 1
-- implicitly cast Timestamp to Date type
> select datediff(now(), current_date);
0

-- implicitly cast String to Double type
> SELECT ceil('0.1');
1
-- special rule: implicitly cast NULL to Date type
> SELECT year(null);
NULL

> CREATE TABLE t(s string);
-- Can't store String column as Numeric types.
> SELECT ceil(s) from t;
Error in query: cannot resolve 'CEIL(spark_catalog.default.t.s)' due to data type mismatch
-- Can't store String column as Date type.
> select year(s) from t;
Error in query: cannot resolve 'year(spark_catalog.default.t.s)' due to data type mismatch

5.2 具有不同行为的函数

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

• size：输入为null，则此函数返回null。
• element_at：如果使用无效索引，此函数将引发ArrayIndexOutOfBoundsException。
• elt:如果使用无效索引，此函数将引发ArrayIndexOutOfBoundsException。
• parse_url:如果输入字符串不是有效的url，此函数将抛出IllegalArgumentException。
• to_date：如果输入字符串无法解析，或者pattern字符串无效，则此函数将失败并抛出异常。
• to_timestamp：如果输入字符串无法解析，或者pattern字符串无效，则此函数将失败并抛出异常。
• unix_timestamp：如果输入字符串无法解析，或者pattern字符串无效，则此函数将失败并抛出异常。
• to_unix_timestamp：如果输入字符串无法解析，或者pattern字符串无效，则此函数将失败并抛出异常。
• make_date：如果结果日期无效，则此函数应失败并抛出异常。
• make_timestamp：如果结果时间戳无效，此函数将失败并抛出异常。
• make_interval：如果结果间隔无效，此函数将失败并抛出异常。
• next_day：如果输入不是有效的星期几，则此函数引发IllegalArgumentException。

六、SQL操作符

在ANSI模式下，某些SQL操作符的行为可能会有所不同(spark.sql.ansi.enabled=true)。

• array_col[index]:如果使用无效的索引，将抛出ArrayIndexOutOfBoundsException。

七、ANSI模式的有用函数

当ANSI模式打开时，它会为无效操作抛出异常。你可以使用以下SQL函数来抑制此类异常。

• try_cast：与cast相同，只是它在运行出错时返回NULL结果，而不是抛出异常。
• try_add：与add运算符+相同，只是它在整数值溢出时返回NULL结果，而不是抛出异常。
• try_subtract：与subtract运算符-相同，只是它在整数值溢出时返回NULL结果，而不是抛出异常。
• try_multiply：与multiply运算符*相同，只是它在整数值溢出时返回NULL结果，而不是抛出异常。
• try_divide：与除法运算符/相同，只是它在除0时返回NULL结果，而不是抛出异常。
• try_sum：与函数sum相同，只是它在integral/decimal/interval值溢出时返回NULL结果，而不是抛出异常。
• try_avg：与函数avg相同，只是它在decimal/interval值溢出时返回NULL结果，而不是抛出异常。
• try_element_at：与函数element_at相同，只是它在数组的索引越界时返回NULL结果，而不是抛出异常。
• try_to_timestamp：与函数to_timestamp相同，只是它在字符串解析错误时返回NULL结果，而不是抛出异常。

八、SQL关键字(可选，默认禁用)

当spark.sql.ansi.enabled和spark.sql.ansi.enforceReservedKeywords都为true时，Spark SQL将使用ANSI模式解析器。
使用ANSI模式解析器，Spark SQL有两种关键字：

• 非保留关键字：仅在特定上下文中具有特殊含义，并可在其他上下文中用作标识符的关键字。例如，EXPLAIN SELECT …是一个命令，但EXPLAIN可以在其他地方用作标识符。
• 保留关键字：保留的关键字不能用作表、视图、列、函数、别名等的标识符。
  使用默认的解析器，Spark SQL有两种关键字：
• 非保留关键字：与启用ANSI模式时的定义相同。
• 严格非保留关键字：比非保留关键字的更严格，不能用作表别名。
  默认情况下，spark.sql.ansi.enabled和spark.sql.ansi.enforceReservedKeywords均为false。
  后面的链接是Spark SQL中所有关键字的列表。