Databricks Runtime での ANSI 準拠

適用対象: Databricks Runtime

この記事では、Databricks Runtime での ANSI 準拠について説明します。 Databricks SQL の ANSI モードについては、「ANSI_MODE」を参照してください。

Spark SQL には、ANSI SQL 標準への準拠をサポートするために、spark.sql.ansi.enabled と spark.sql.storeAssignmentPolicy の 2 つのオプションがあります。

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 つの種類があり、この記事ではこれらを 1 つずつ説明します。

算術演算

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

-- `spark.sql.ansi.enabled=true`
> SELECT 2147483647 + 1;
 error: integer overflow

-- `spark.sql.ansi.enabled=false`
> SELECT 2147483647 + 1;
  -2147483648

キャスト

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 のキャスト仕様に関する説明で示されている構文規則に従います。ただし、ANSI 標準では許可されていない、次の単純な型変換は許可されている点を除きます。

• 数値型 <=> ブール型
• StringType <=> BinaryType

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

-- Examples of explicit casting

-- `spark.sql.ansi.enabled=true`
> SELECT CAST('a' AS INT);
  ERROR: [CAST_INVALID_INPUT] The value 'a' of the type "STRING" cannot be cast to "INT" because it is malformed.

> SELECT CAST(2147483648L AS INT);
  ERROR: [CAST_OVERFLOW] The value 2147483648L of the type "BIGINT" cannot be cast to "INT" due to an overflow.

> SELECT CAST(DATE'2020-01-01' AS INT)
  ERROR: [DATATYPE_MISMATCH.CAST_WITH_FUNC_SUGGESTION] Cannot resolve "CAST(DATE '2020-01-01' AS INT)" due to data type mismatch: cannot cast "DATE" to "INT".

-- `spark.sql.ansi.enabled=false` (This is a default behavior)
> SELECT cast('a' AS INT);
  null

> SELECT CAST(2147483648L AS INT);
  -2147483648

> SELECT CAST(DATE'2020-01-01' AS INT);
  null

ストアの割り当て

spark.sql.storeAssignmentPolicy 設定の既定値は ANSI です。 この設定では、ソース値のデータ型がターゲット列の型と一致しない場合、Spark SQL は自動的に ANSI CAST 句を INSERT ステートメントに追加します。 このポリシーに基づくテーブルの挿入中に、Spark は無効なキャストをチェックして拒否し、データ品質を確保するために例外をスローします。 つまり、型の不一致が原因で挿入が失敗した場合、データがテーブルに部分的に書き込まれることはありません。

例 :

-- spark.sql.storeAssignmentPolicy=ANSI
> CREATE TABLE test(i INT);
> INSERT INTO test VALUES (2147483648L);
  ERROR: [CAST_OVERFLOW_IN_TABLE_INSERT] Fail to insert a value of "BIGINT" type into the "INT" type column `i` due to an overflow.

> INSERT INTO test VALUES ('a');
  ERROR: [CAST_INVALID_INPUT ERROR] The value 'a' of the type "STRING" cannot be cast to "INT" because it is malformed

次の例は、Spark SQL が互換性のないデータの挿入を防ぎ、データの整合性を維持することを示しています。

spark.sql.storeAssignmentPolicy が LEGACY に設定されている場合、Spark SQL は Spark 2.x までの一般的な動作に戻ります。 このモードでは、ANSI CAST を使用する代わりに、従来の CAST 操作が適用されます。 このポリシーでは、テーブルの挿入中に無効なキャストを実行すると、例外がスローされるのではなく、NULL 値または正しくない値が挿入されます。 例 :

-- spark.sql.storeAssignmentPolicy=LEGACY
> CREATE TABLE test(i INT);
> INSERT INTO test VALUES (2147483648L);
> INSERT INTO test VALUES ('a');
> SELECT * FROM test;

-- Results
--  -2147483648 (incorrect value due to overflow)
--  null (cannot cast 'a' to INT)

強制型変換

型の上位変換と優先順位

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

• 最も一般的でない型解決の場合、精度の損失を回避するために浮動小数点はスキップされます。

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

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

ここでは、優先順位リストを有向木として図で示しています:

最も一般的でない型解決

一連の型の最も一般的でない型は、一連の型のすべての要素によって優先順位リストから到達可能な最も狭い型です。

最も一般的でない型解決は、次の場合に使用します。

• ある型のパラメーターを必要としている関数を、より狭い型の引数を使用して呼び出すことができるかどうかを決定します。
• 複数のパラメーター (結合、最小、最大など) の共有引数型を必要とする関数の引数型を派生させます。
• 算術演算や比較などの演算子のオペランド型を派生させます。
• 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

-- 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: この関数は、ANSI モードでの null 入力に対して null を返します。
• element_at:
  • 無効なインデックスを使用している場合、この関数は ArrayIndexOutOfBoundsException をスローします。
  • キーがマップに存在しない場合、この関数は NoSuchElementException をスローします。
• elt: 無効なインデックスを使用している場合、この関数は ArrayIndexOutOfBoundsException をスローします。
• make_date: 結果の日付が無効な場合、この関数は例外で失敗します。
• make_timestamp: 結果のタイムスタンプが無効な場合、この関数は例外で失敗します。
• make_interval: 結果の間隔が無効な場合、この関数は例外で失敗します。
• next_day: 入力が有効な週の日付でない場合、この関数は IllegalArgumentException をスローします。
• parse_url: 入力文字列が有効な URL でない場合、この関数は IllegalArgumentException をスローします。
• to_date: 入力文字列を解析できない場合、またはパターン文字列が無効な場合、この関数は例外で失敗します。
• to_timestamp: 入力文字列を解析できない場合、またはパターン文字列が無効な場合、この関数は例外で失敗します。
• to_unix_timestamp: 入力文字列を解析できない場合、またはパターン文字列が無効な場合、この関数は例外で失敗します。
• unix_timestamp: 入力文字列を解析できない場合、またはパターン文字列が無効な場合、この関数は例外で失敗します。

SQL 演算子

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

• array_col[index]: 無効なインデックスを使用している場合、この演算子は ArrayIndexOutOfBoundsException をスローします。
• map_col[key]: キーがマップに存在しない場合、この演算子は NoSuchElementException をスローします。
• CAST(string_col AS TIMESTAMP): 入力文字列を解析できない場合、この演算子は例外で失敗します。
• CAST(string_col AS DATE): 入力文字列を解析できない場合、この演算子は例外で失敗します。

ANSI モードの便利な関数

ANSI モードがオンの場合、無効な操作に対して例外がスローされます。 次の SQL 関数を使用して、このような例外を抑制できます。

• try_cast: ランタイム エラーで例外をスローする代わりに CAST の結果を返す点を除き、NULL と同じです。
• try_add: 整数値オーバーフローで例外をスローする代わりに + の結果を返す点を除き、加算演算子 NULL と同じです。
• try_divide: 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 のすべてのキーワードの一覧を次に示します。