ANSI 準拠

Spark SQL では、SQL 標準に準拠するための 2 つのオプションがあります。それは spark.sql.ansi.enabled と spark.sql.storeAssignmentPolicy です（詳細は以下の表を参照）。

デフォルトでは、spark.sql.ansi.enabled は true で、Spark SQL は Hive 準拠ではなく、ANSI 準拠の構文を使用します。たとえば、SQL 演算子/関数への入力が無効な場合、Spark は null 結果を返すのではなく、実行時に例外をスローします。一部の ANSI 構文機能は ANSI SQL 標準から直接ではないかもしれませんが、その動作は ANSI SQL のスタイルに沿っています。

さらに、Spark SQL には、テーブルに行を挿入する際の暗黙的なキャスト動作を制御する独立したオプションがあります。キャスト動作は、標準ではストア代入ルールとして定義されています。

デフォルトでは、spark.sql.storeAssignmentPolicy は ANSI で、Spark SQL は ANSI ストア代入ルールに準拠します。

次のサブセクションでは、ANSI モードが有効な場合の算術演算、型変換、SQL 解析における動作の変更について説明します。Spark SQL での型変換には 3 種類あり、この記事で順に紹介します：キャスト、ストア代入、型変換。

算術演算

Spark SQL では、デフォルトで、間隔型と数値型のオーバーフローの両方に対して実行時に算術例外がスローされます。spark.sql.ansi.enabled が false の場合、decimal 型は null 値を生成し、その他の数値型は Java/Scala プログラムの対応する操作と同じように動作します（例：2 つの整数の合計が表現可能な最大値を超える場合、結果は負の数になります）。これは Spark 3 以前の動作です。

-- `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|
+----------------+

キャスト

spark.sql.ansi.enabled が true に設定されている場合、CAST 構文による明示的なキャストは、標準で定義されている不正なキャストパターン（例：文字列から整数へのキャスト）に対して実行時例外をスローします。

さらに、ANSI SQL モードは、ANSI モードがオフの場合に許可されている次の型変換を禁止します。

• 数値型 <=> バイナリ型
• 日付型 <=> ブール型
• タイムスタンプ型 <=> ブール型
• 日付型 => 数値型

CAST 式で有効なソースとターゲットのデータ型の組み合わせは、次の表で示されます。「Y」は制限なしで構文的に有効な組み合わせを示し、「N」は無効な組み合わせを示します。

上記の表では、新しい構文のすべての CAST は赤色の Y でマークされています。

• CAST(数値型 AS 数値型): 値がターゲットデータ型の範囲外の場合、オーバーフロー例外をスローします。
• CAST(文字列型 AS (数値型/日付型/タイムスタンプ型/Timestamp_NTZ/間隔型/ブール型)): 値がターゲットデータ型として解析できない場合、実行時例外をスローします。
• CAST(タイムスタンプ型 AS 数値型): エポックからの秒数がターゲットデータ型の範囲外の場合、オーバーフロー例外をスローします。
• CAST(数値型 AS タイムスタンプ型): 数値に 1000000（マイクロ秒/秒）を掛けた値が Long 型の範囲外の場合、オーバーフロー例外をスローします。
• CAST(配列型 AS 配列型): 要素の変換にエラーがある場合、例外をスローします。
• CAST(マップ型 AS マップ型): キーと値の変換にエラーがある場合、例外をスローします。
• CAST(構造体型 AS 構造体型): 構造体フィールドの変換にエラーがある場合、例外をスローします。
• CAST(数値型 AS 文字列型): 指数が必要な場合、科学表記法ではなく、decimal 値を文字列にキャストする際に常にプレーンな文字列表現を使用します。
• CAST(間隔型 AS 数値型): 日時間隔のマイクロ秒数または年月間隔の月数がターゲットデータ型の範囲外の場合、オーバーフロー例外をスローします。
• CAST(数値型 AS 間隔型): 数値にターゲット間隔の終了単位を掛けた値が、年月間隔の場合は Int 型、日時間隔の場合は Long 型の範囲外の場合、オーバーフロー例外をスローします。

-- Examples of explicit casting

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

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`
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|
+---+

キャストでの丸め

小数点以下を持つ decimal を INTERVAL HOUR TO SECOND のような SECOND を終了単位とする間隔型にキャストする場合、Spark は小数点以下の部分を「最近傍」に向かって丸めます。ただし、両方の隣接する値が等距離にある場合は、切り上げます。

ストア代入

最初にも述べたように、spark.sql.storeAssignmentPolicy が ANSI（デフォルト値）に設定されている場合、Spark SQL はテーブル挿入時に ANSI ストア代入ルールに準拠します。テーブル挿入における有効なソースとターゲットのデータ型の組み合わせは、次の表で示されます。

* Spark は間隔型テーブル列をサポートしていません。

** 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.

型変換

型昇格と優先順位

spark.sql.ansi.enabled が true に設定されている場合、Spark SQL はデータ型間の競合を解決するためのいくつかのルールを使用します。この競合解決の中心となるのは、指定されたデータ型の値が暗黙的に別のデータ型に昇格できるかどうかを定義する型優先順位リストです。

* 共通の最小型解決では、精度損失を避けるために float はスキップされます。

** String は複数の種類のデータ型に昇格できます。Byte/Short/Int/Decimal/Float はこの優先順位リストにないことに注意してください。Byte/Short/Int と String の間の共通の最小型は Long であり、Decimal/Float の間の共通の最小型は Double です。

*** 複合型の場合、優先順位ルールはコンポーネント要素に再帰的に適用されます。

型指定のない NULL には特別なルールが適用されます。NULL は任意の他の型に昇格できます。

これは、優先順位リストの有向木としてのグラフィカルな表現です：

共通の最小型解決

型のセットの共通の最小型とは、セットのすべての要素から優先順位リストで到達可能な最も狭い型です。

共通の最小型解決は、次の目的で使用されます。

• 複数のパラメータに対して共通の引数型を期待する関数（coalesce、least、greatest など）の引数型を導出します。
• 算術演算や比較などの演算子のオペランド型を導出します。
• CASE 式などの式の結果型を導出します。
• 配列およびマップコンストラクタの要素型、キー型、または値型を導出します。共通の最小型が FLOAT に解決される場合は特別なルールが適用されます。float 型の値の場合、INT、BIGINT、または DECIMAL のいずれかの型がある場合、桁落ちの可能性を避けるために共通の最小型は DOUBLE にプッシュされます。

Decimal 型は、精度とスケールというパラメータを持つため、ここでは少し複雑です。decimal(precision, scale) は、値が整数部分で最大 precision - scale 桁、小数部分で scale 桁を持つことができることを意味します。decimal 型間の共通の最小型は、すべての値を表現するのに十分な整数桁と小数桁を持つ必要があります。より正確には、decimal(p1, s1) と decimal(p2, s2) の間の共通の最小型は、スケールが max(s1, s2)、精度が max(s1, s2) + max(p1 - s1, p2 - s2) になります。しかし、Spark の decimal 型には最大精度 38 があります。最終的な decimal 型がより高い精度を必要とする場合、切り捨てを行う必要があります。整数部分の桁はより重要であるため、Spark はまず小数部分の桁を切り捨てます。たとえば、decimal(48, 20) は decimal(38, 10) に削減されます。

注：算術演算では、decimal 入力用の共通の最小型を計算するために特別なルールがあります。

切り捨てルールも算術演算では異なります。小数部分には少なくとも 6 桁が保持されるため、scale は最大 6 までしか削減できません。この場合、オーバーフローが発生する可能性があります。

-- 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 関数

関数呼び出し

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

挙動が異なる関数

一部の SQL 関数の挙動は、ANSI モード（spark.sql.ansi.enabled=true）では異なる場合があります。

• size: この関数は、null 入力に対して null を返します。
• 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 と同じですが、実行時エラーで例外をスローする代わりに NULL 結果を返します。
• try_add: 加算演算子 + と同じですが、整数値のオーバーフローで例外をスローする代わりに NULL 結果を返します。
• try_subtract: 加算演算子 - と同じですが、整数値のオーバーフローで例外をスローする代わりに NULL 結果を返します。
• try_multiply: 加算演算子 * と同じですが、整数値のオーバーフローで例外をスローする代わりに NULL 結果を返します。
• try_divide: 除算演算子 / と同じですが、0 で割る場合に例外をスローする代わりに NULL 結果を返します。
• try_mod: 剰余演算子 % と同じですが、0 で割る場合に例外をスローする代わりに NULL 結果を返します。
• try_sum: sum 関数と同じですが、整数/decimal/間隔値のオーバーフローで例外をスローする代わりに NULL 結果を返します。
• try_avg: avg 関数と同じですが、decimal/間隔値のオーバーフローで例外をスローする代わりに NULL 結果を返します。
• try_element_at: element_at 関数と同じですが、配列のインデックス範囲外で例外をスローする代わりに NULL 結果を返します。
• try_to_timestamp: to_timestamp 関数と同じですが、文字列解析エラーで例外をスローする代わりに NULL 結果を返します。
• try_parse_url: parse_url 関数と同じですが、URL 解析エラーで例外をスローする代わりに NULL 結果を返します。
• try_make_timestamp: make_timestamp 関数と同じですが、エラーで例外をスローする代わりに NULL 結果を返します。
• try_make_timestamp_ltz: make_timestamp_ltz 関数と同じですが、エラーで例外をスローする代わりに NULL 結果を返します。
• try_make_timestamp_ntz: make_timestamp_ntz 関数と同じですが、エラーで例外をスローする代わりに NULL 結果を返します。
• try_make_interval: make_interval 関数と同じですが、無効な間隔で例外をスローする代わりに NULL 結果を返します。

SQL キーワード（オプション、デフォルトで無効）

spark.sql.ansi.enabled と spark.sql.ansi.enforceReservedKeywords の両方が true の場合、Spark SQL は ANSI モードパーサーを使用します。

ANSI モードパーサーでは、Spark SQL には 2 種類のキーワードがあります。

• 非予約キーワード: 特定のコンテキストでのみ特殊な意味を持ち、他のコンテキストでは識別子として使用できるキーワード。例：EXPLAIN SELECT ... はコマンドですが、EXPLAIN は他の場所で識別子として使用できます。
• 予約キーワード: 予約されており、テーブル、ビュー、列、関数、エイリアスなどの識別子として使用できないキーワード。

デフォルトパーサーでは、Spark SQL には 2 種類のキーワードがあります。

• 非予約キーワード: ANSI モードが有効な場合と同じ定義。
• 厳格非予約キーワード: 非予約キーワードの厳格なバージョンであり、テーブルエイリアスとして使用できません。

デフォルトでは、spark.sql.ansi.enforceReservedKeywords は false です。

以下は、Spark SQL のすべてのキーワードのリストです。