ANSI 準拠

Spark 3.0 から、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 のため、これは別の構成です。一方で構成 spark.sql.ansi.enabled はデフォルトで無効です。

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

Arithmetic オペレーション

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

-- `spark.sql.ansi.enabled=true`
SELECT 2147483647 + 1;
java.lang.ArithmeticException: integer overflow

SELECT abs(-2147483648);
java.lang.ArithmeticException: integer overflow

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

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

Cast

spark.sql.ansi.enabled が true に設定されている場合、CAST 構文による明示的なキャストは、標準で定義されたキャストパターンの違反のため実行時例外を投げます。例えば、文字列から数値。

Spark ANSIモードのCAST句は、ISO/IEC 9075-2:2011 Information technology — Database languages - SQL — Part 2: Foundation (SQL/Foundation)のセクション6.13 “cast specification”の構文規則に従います。ただし、ANSI規格では許可されていない次の単純な型変換を特別に許可します:

• NumericType <=> BooleanType
• StringType <=> BinaryType
• ArrayType => String
• MapType => String
• StructType => String

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

上記の表では、ランタイム例外を引き起こす可能性のある全てのCASTが赤のYでマークされています:

• CAST(Numeric AS Numeric): 値がターゲットデータ型の範囲外の場合、オーバーフロー例外を発生させます。
• CAST(String AS (Numeric/Date/Timestamp/Interval/Boolean)): 値がターゲットデータ型として解析できない場合、実行時例外を発生させます。
• CAST(Array AS Array): 要素の変換に例外がある場合例外を発生させます。
• CAST(Map AS Map): キーと値の変換に例外がある場合、例外を発生させます。
• CAST(Struct AS Struct): 構造体フィールドの変換に例外がある場合、例外を発生させます。

-- 明示的なキャストの例

-- `spark.sql.ansi.enabled=true`
SELECT CAST('a' AS INT);
java.lang.NumberFormatException: invalid input syntax for type numeric: a

SELECT CAST(2147483648L AS INT);
java.lang.ArithmeticException: Casting 2147483648 to int causes overflow

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: Cannot write incompatible data to table '`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|
+---+

ストアの割り当て

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

* Sparkはinterval型テーブルカラムをサポートしません。

** 配列/マップ/構造体型の場合、データ型チェックルールはそのコンポーネント要素に再帰的に適用されます。

テーブルの挿入中に、Sparkは数値のオーバーフロー時に例外を投げます。

CREATE TABLE test(i INT);

INSERT INTO test VALUES (2147483648L);
java.lang.ArithmeticException: Casting 2147483648 to int causes overflow

型強制

タイプの昇格と優先順位

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

* For least common type resolution float is skipped to avoid loss of precision.

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

文字列リテラルと型指定されていないNULLには、特別な規則が適用されます。NULLは他の型に昇格できますが、文字列リテラルは任意の単純なデータ型に昇格できます。

これは優先順位リストを有向ツリーとしてグラフィカルに表現したものです:

最も一般的ではない型の解決

型のセットの中で最も一般的ではない型は、型のセットの全ての要素が優先順位リストから到達できる最も狭い型です。

もっとも一般的ではない型の解決は、以下の目的で使われます:

• 型のパラメータを期待する関数が、より狭い型の引数を使って呼び出せるかどうかを決定します。
• 合体、最小、最大などの複数のパラメータの共有される引数型を期待する関数の引数型を導出します。
• 算術演算や比較などの演算子のオペランド型を導出します。
• ケース式などの式の結果型を導出します。
• 配列およびマップコンストラクタの要素、キー、または値の型を導出します。もっとも一般的ではない型がFLOATに解決される場合、特別なルールが適用されます。float型の値では、型のいずれかがINT, BIGINT, DECIMALの場合、桁が失われる可能性を回避するとともに、最も一般的ではない型がDOUBLEにプッシュされます。

-- coalesce関数は、最も一般的ではない型を共有している限り、引数型の任意のセットを受け入れます。
-- 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

-- The substring function expects arguments of type INT for the start and length parameters.
> SELECT substring('hello', 1Y, 2);
he
> SELECT substring('hello', '1', 2);
he
> SELECT substring('hello', 1L, 2);
Error: Argument 2 requires an INT type.
> SELECT substring('hello', str, 2) FROM VALUES(CAST('1' AS STRING)) AS T(str);
Error: Argument 2 requires an INT type.

SQL 関数

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

• size: この関数は、null入力に対してnullを返します。
• element_at:
  • 無効なインデックスを使っている場合、この関数はArrayIndexOutOfBoundsExceptionを投げます。
  • キーがマップに存在しない場合、NoSuchElementException
• 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を投げます。
• map_col[key]: キーがマップに存在しない場合、このオペレータはNoSuchElementExceptionを投げます。

ANSIモードで役立つ機能

ANSIモードがオンの場合、無効な操作に対して例外が投げられます。以下のSQLの機能を使って、そのような例外を抑制することができます。

• try_cast: CASTと同じですが、実行時エラー時に例外を投げる代わりにNULLの結果を返します。
• try_add: add 演算子+と同じですが、整数値のオーバーフローで例外を投げる代わりにNULLの結果を返します。
• try_divide: division演算子 /と同じですが、0の除算で例外を投げる代わりに、NULLを返します。

SQL キーワード

spark.sql.ansi.enabled が true の場合、Spark SQL は ANSI モードパーサを使います。このモードでは、Spark SQL には2種類のキーワードがあります:

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

ANSI モードが無効の場合、Spark SQL には2種類のキーワードがあります:

• 予約されていないキーワード: ANSI モードが有効な場合と同じ定義。
• 厳密な予約されていないキーワード: 予約されていないキーワードの厳格なバージョン。テーブルのエイリアスとして使えません。

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

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