ANSI 準拠

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

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

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

spark.sql.storeAssignmentPolicy が ANSI に設定されている場合、Spark SQL は ANSI ストア割り当てルールに準拠します。この設定のデフォルト値は ANSI であり、設定 spark.sql.ansi.enabled はデフォルトで無効になっているため、これは別の設定です。

以下のサブセクションでは、ANSI モードが有効になっている場合の算術演算、型変換、および SQL 解析の動作変更について説明します。Spark SQL の型変換には、キャスト、ストア割り当て、型の強制の 3 つの種類があり、この記事ではそれらを 1 つずつ紹介します。

算術演算

Spark SQL では、数値型 (decimal を除く) で実行される算術演算は、デフォルトではオーバーフローがチェックされません。これは、演算がオーバーフローを引き起こす場合、結果は Java/Scala プログラムでの対応する演算と同じになることを意味します (たとえば、2 つの整数の合計が表現可能な最大値よりも大きい場合、結果は負の数になります)。一方、Spark SQL は decimal のオーバーフローに対して null を返します。spark.sql.ansi.enabled が true に設定されていて、数値および間隔の算術演算でオーバーフローが発生した場合、実行時に算術例外をスローします。

-- `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 (数値/日付/タイムスタンプ/タイムスタンプ_NTZ/間隔/ブール)): 値をターゲットデータ型として解析できない場合は、実行時例外を発生させます。
• CAST(タイムスタンプ AS 数値): エポックからの秒数がターゲットデータ型の範囲外にある場合は、オーバーフロー例外を発生させます。
• CAST(数値 AS タイムスタンプ): 数値に 1000000 (1 秒あたりのマイクロ秒数) を掛けた値が Long 型の範囲外にある場合は、オーバーフロー例外を発生させます。
• CAST(配列 AS 配列): 要素の変換に問題がある場合は例外を発生させます。
• CAST(マップ AS マップ): キーと値の変換に問題がある場合は例外を発生させます。
• CAST(構造体 AS 構造体): 構造体フィールドの変換に問題がある場合は例外を発生させます。
• CAST(数値 AS 文字列): 10 進数値を文字列にキャストする場合、指数が必要な場合でも、科学的記数法を使用せずに、常にプレーンな文字列表現を使用します。
• CAST(間隔 AS 数値): 日時間隔のマイクロ秒数または年月間隔の月数がターゲットデータ型の範囲外にある場合は、オーバーフロー例外を発生させます。
• CAST(数値 AS 間隔): 数値にターゲット間隔の終了単位を掛けた値が、年月間隔の場合は Int 型の範囲外である場合、または日時間隔の場合は Long 型の範囲外である場合は、オーバーフロー例外を発生させます。

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

キャストでの丸め

小数部を持つ 10 進数を、INTERVAL HOUR TO SECOND のように終了単位として 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] 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 はスキップされます。

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

*** 複合型の場合、優先順位ルールは、その構成要素に対して再帰的に適用されます。

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

これは、優先順位リストを有向木としてグラフで表したものです:

最小共通型解決

型の集合における最小共通型は、型の集合のすべての要素によって、優先順位リストから到達可能な最も狭い型です。

最小共通型解決は、以下の場合に使用されます。

• coalesce、least、greatestなどの複数のパラメータに共有される引数型を期待する関数の引数型を導出します。
• 算術演算や比較などの演算子のオペランド型を導出します。
• case式などの式の戻り型を導出します。
• 配列およびマップコンストラクターの要素型、キー型、または値型を導出します。最小共通型がFLOATに解決される場合は、特別なルールが適用されます。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関数

関数呼び出し

ANSIモード（spark.sql.ansi.enabled=true）では、Spark SQLの関数呼び出しは

• 一般に、SQL関数の宣言されたパラメータ型として入力値を格納する際のStore assignmentルールに従います。
• 型なし 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_sum：関数sumと同じですが、整数/10進数/間隔値のオーバーフローで例外をスローする代わりにNULLの結果を返します。
• try_avg：関数avgと同じですが、10進数/間隔値のオーバーフローで例外をスローする代わりに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には2種類のキーワードがあります。

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

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

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

デフォルトでは、spark.sql.ansi.enabledとspark.sql.ansi.enforceReservedKeywordsの両方がfalseです。

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