ANSI-Konformität in Databricks Runtime

Gilt für: Databricks Runtime

In diesem Artikel wird die ANSI-Konformität in Databricks Runtime beschrieben. Informationen zum ANSI-Modus in Databricks SQL finden Sie unter ANSI_MODE.

Spark SQL bietet zwei Optionen zur Unterstützung der Kompatibilität mit dem ANSI-SQL-Standard: spark.sql.ansi.enabled und spark.sql.storeAssignmentPolicy.

Wenn spark.sql.ansi.enabled auf true festgelegt ist, verwendet Spark SQL einen ANSI-kompatiblen Dialekt, statt Hive-kompatibel zu sein. Spark löst z. B. zur Laufzeit eine Ausnahme aus, statt NULL-Ergebnisse zurückzugeben, wenn die Eingaben für einen SQL-Operator/eine SQL-Funktion ungültig sind. Einige ANSI-Dialektfunktionen stammen möglicherweise nicht direkt aus dem ANSI-SQL-Standard, aber ihr Verhalten entspricht dem Stil von ANSI SQL.

Darüber hinaus verfügt Spark SQL über eine unabhängige Option zum Steuern des impliziten Umwandlungsverhaltens beim Speichern von Zeilen in eine Tabelle. Das Umwandlungsverhalten ist im Standard als Speicherzuweisungsregeln definiert.

Wenn spark.sql.storeAssignmentPolicy auf ANSI festgelegt ist, entspricht Spark SQL den ANSI-Speicherzuweisungsregeln. Dies ist eine separate Konfiguration, da der Standardwert ANSI ist, während die Konfiguration spark.sql.ansi.enabled standardmäßig deaktiviert ist.

In der folgenden Tabelle wird das Verhalten zusammengefasst:

In den folgenden Unterabschnitten werden die Verhaltensänderungen bei arithmetischen Operationen, Typkonvertierungen und der SQL-Analyse bei aktiviertem ANSI-Modus aufgezeigt. In Bezug auf Typkonvertierungen in Spark SQL gibt es drei Arten, die in diesem Artikel einzeln vorgestellt werden: Umwandlung, Speicherzuweisung und Typkoersion.

Arithmetische Operationen

In Spark SQL werden arithmetische Operationen für numerische Typen (mit Ausnahme von „Decimal“) standardmäßig nicht auf Überläufe überprüft. Das heißt, wenn eine Operation Überläufe verursacht, ist das Ergebnis dasselbe wie bei der entsprechenden Operation in einem Java- oder Scala-Programm (wenn z. B. die Summe von zwei ganzen Zahlen höher ist als der maximal darstellbare Wert, ist das Ergebnis eine negative Zahl). Andererseits gibt Spark SQL bei Dezimalüberläufen NULL zurück. Wenn spark.sql.ansi.enabled auf true festgelegt ist und ein Überlauf bei numerischen und Intervallarithmetikoperationen auftritt, wird zur Laufzeit eine arithmetische Ausnahme ausgelöst.

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

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

Umwandeln

Wenn spark.sql.ansi.enabled auf true festgelegt ist, löst die explizite Umwandlung durch die CAST-Syntax eine Laufzeitausnahme für ungültige Umwandlungsmuster aus, die im Standard definiert sind, z. B. Umwandlungen von einer Zeichenfolge in eine ganze Zahl.

Die CAST-Klausel des Spark-ANSI-Modus folgt den Syntaxregeln von Abschnitt 6.13 „Umwandlungsspezifikation“ in ISO/IEC 9075-2:2011 Information technology — Database languages — SQL — Part 2: Foundation (SQL/Foundation), mit der Ausnahme, dass sie speziell die folgenden einfachen Typkonvertierungen zulässt, die gemäß dem ANSI-Standard nicht zulässig sind:

• NumericType <=> BooleanType
• StringType <=> BinaryType

Die gültigen Kombinationen aus Quell- und Zieldatentyp in einem CAST-Ausdruck sind in der folgenden Tabelle aufgeführt. „J“ gibt an, dass die Kombination syntaktisch ohne Einschränkung gültig ist, und „N“ gibt an, dass die Kombination ungültig ist.

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

Speicherzuweisung

Die Einstellung spark.sql.storeAssignmentPolicy standardmäßig auf ANSI festgelegt. Wenn die Datentypen von Quellwerten durch diese Einstellung nicht mit den Zielspaltentypen übereinstimmen, fügt Spark SQL automatisch ANSI CAST-Klauseln zur INSERT-Anweisung hinzu. Während der Tabelleneinfügung prüft und lehnt Spark durch diese Richtlinie ungültige Umwandlungen ab und löst eine Ausnahme aus, um die Datenqualität sicherzustellen. Wenn das Einfügen aufgrund eines Typkonflikts fehlschlägt, werden somit keine Daten teilweise in die Tabelle geschrieben.

Beispiele:

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

Diese Beispiele zeigen, dass Spark SQL verhindert, dass inkompatible Daten eingefügt werden, wodurch die Datenintegrität sichergestellt wird.

Wenn spark.sql.storeAssignmentPolicy auf LEGACY festgelegt ist, greift Spark SQL auf das Verhalten zurück, das bis zu Spark 2.x bestand. In diesem Fall verwendet es anstelle von ANSI CAST ältere CAST-Vorgänge. Mit dieser Richtlinie führen ungültige Umwandlungen während der Tabelleneinfügung dazu, dass entweder NULL-Werte oder falsche Werte eingefügt werden, anstatt eine Ausnahme zu auslösen. Beispiele:

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

Typumwandlung

Typerweiterung und Rangfolge von Typen

Wenn spark.sql.ansi.enabled auf true festgelegt ist, verwendet Spark SQL mehrere Regeln, die steuern, wie Konflikte zwischen Datentypen gelöst werden. Das Kernstück dieser Konfliktlösung ist die Typenrangfolgeliste, die definiert, ob Werte eines bestimmten Datentyps implizit auf einen anderen Datentyp heraufgestuft/erweitert werden können.

• Bei der Auflösung des kleinsten gemeinsamen Typs wird „Float“ übersprungen, um Genauigkeitsverluste zu vermeiden.

** Bei einem komplexen Typ wird die Rangfolgeregel rekursiv auf die zugehörigen Komponentenelemente angewendet.

Spezielle Regeln gelten für den Typ „String“ und den nicht typisierten NULL-Wert. Ein NULL-Wert kann auf jeden anderen Typ heraufgestuft werden, und „String“ kann auf jeden einfachen Datentyp heraufgestuft werden.

Hier sehen Sie eine grafische Darstellung der Rangfolgeliste in Form eines gerichteten Baums:

Auflösung des kleinsten gemeinsamen Typs

Der kleinste gemeinsame Typ aus einer Reihe von Typen ist der niedrigste Typ, der aus der Rangfolgeliste von allen Elementen der Reihe von Typen erreicht werden kann.

Die Auflösung des kleinsten gemeinsamen Typs wird für Folgendes verwendet:

• Entscheiden, ob eine Funktion, die einen Parameter eines Typs erwartet, mit einem Argument eines niedrigeren Typs aufgerufen werden kann.
• Ableiten des Argumenttyps für Funktionen, die einen gemeinsamen Argumenttyp für mehrere Parameter erwarten, z. B. COALESCE, LEAST oder GREATEST.
• Ableiten der Operandentypen für Operatoren wie z. B. arithmetische Operationen oder Vergleiche.
• Ableiten des Ergebnistyps für Ausdrücke wie den CASE-Ausdruck.
• Ableiten der Element-, Schlüssel- oder Werttypen für Array- und Map-Konstruktoren.

Spezielle Regeln werden angewendet, wenn der kleinste gemeinsame Typ in FLOAT aufgelöst wird. Wenn bei Werten vom Typ „Float“ einer der Typen INT, BIGINT oder DECIMAL ist, wird der kleinste gemeinsame Typ zu DOUBLE erweitert, um einen potenziellen Verlust von Ziffern zu vermeiden.

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

Das Verhalten einiger SQL-Funktionen kann im ANSI-Modus (spark.sql.ansi.enabled=true) anders ausfallen.

• size: Diese Funktion gibt bei Null-Eingaben im ANSI-Modus NULL zurück.
• element_at:
  • Diese Funktion löst ArrayIndexOutOfBoundsException aus, wenn ungültige Indizes verwendet werden.
  • Diese Funktion löst NoSuchElementException aus, wenn der Schlüssel in der Zuordnung nicht vorhanden ist.
• elt: Diese Funktion löst ArrayIndexOutOfBoundsException aus, wenn ungültige Indizes verwendet werden.
• make_date: Diese Funktion schlägt mit einer Ausnahme fehl, wenn das resultierende Datum ungültig ist.
• make_timestamp: Diese Funktion schlägt mit einer Ausnahme fehl, wenn der resultierende Zeitstempel ungültig ist.
• make_interval: Diese Funktion schlägt mit einer Ausnahme fehl, wenn das resultierende Intervall ungültig ist.
• next_day: Diese Funktion löst IllegalArgumentException aus, wenn die Eingabe kein gültiger Wochentag ist.
• parse_url: Diese Funktion löst IllegalArgumentException aus, wenn die Eingabezeichenfolge keine gültige URL ist.
• to_date: Diese Funktion schlägt mit einer Ausnahme fehl, wenn die Eingabezeichenfolge nicht analysiert werden kann oder die Musterzeichenfolge ungültig ist.
• to_timestamp: Diese Funktion schlägt mit einer Ausnahme fehl, wenn die Eingabezeichenfolge nicht analysiert werden kann oder die Musterzeichenfolge ungültig ist.
• to_unix_timestamp: Diese Funktion schlägt mit einer Ausnahme fehl, wenn die Eingabezeichenfolge nicht analysiert werden kann oder die Musterzeichenfolge ungültig ist.
• unix_timestamp: Diese Funktion schlägt mit einer Ausnahme fehl, wenn die Eingabezeichenfolge nicht analysiert werden kann oder die Musterzeichenfolge ungültig ist.

SQL-Operatoren

Das Verhalten einiger SQL-Operatoren kann im ANSI-Modus (spark.sql.ansi.enabled=true) anders ausfallen.

• array_col[index]: Dieser Operator löst ArrayIndexOutOfBoundsException aus, wenn ungültige Indizes verwendet werden.
• map_col[key]: Dieser Operator löst NoSuchElementException aus, wenn der Schlüssel in der Zuordnung nicht vorhanden ist.
• CAST(string_col AS TIMESTAMP): Dieser Operator schlägt mit einer Ausnahme fehl, wenn die Eingabezeichenfolge nicht analysiert werden kann.
• CAST(string_col AS DATE): Dieser Operator schlägt mit einer Ausnahme fehl, wenn die Eingabezeichenfolge nicht analysiert werden kann.

Nützliche Funktionen für den ANSI-Modus

Wenn der ANSI-Modus aktiviert ist, werden Ausnahmen für ungültige Operationen ausgelöst. Sie können die folgenden SQL-Funktionen verwenden, um solche Ausnahmen zu unterdrücken.

• try_cast: Diese Funktion ist mit CAST identisch, außer dass sie das Ergebnis NULL zurückgibt, statt bei Laufzeitfehlern eine Ausnahme auszulösen.
• try_add: Diese Funktion ist mit dem Additionsoperator (+) identisch, außer dass sie das Ergebnis NULL zurückgibt, statt bei einem Ganzzahlüberlauf eine Ausnahme auszulösen.
• try_divide: Diese Funktion ist mit dem Divisionsoperator (/) identisch, außer dass sie das Ergebnis NULL zurückgibt, statt bei einer Division durch Null (0) eine Ausnahme auszulösen.

SQL-Schlüsselwörter

Wenn spark.sql.ansi.enabled auf „true“ festgelegt ist, verwendet Spark SQL den Parser im ANSI-Modus. In diesem Modus verfügt Spark SQL über zwei Arten von Schlüsselwörtern:

• Reservierte Schlüsselwörter: Schlüsselwörter, die reserviert sind und nicht als Bezeichner für Tabellen, Ansichten, Spalten, Funktionen, Aliase usw. verwendet werden können.
• Nicht reservierte Schlüsselwörter: Schlüsselwörter, die nur in bestimmten Kontexten eine besondere Bedeutung haben und in anderen Kontexten als Bezeichner verwendet werden können. Beispielsweise ist EXPLAIN SELECT ... ein Befehl, aber EXPLAIN kann an anderen Stellen als Bezeichner verwendet werden.

Wenn der ANSI-Modus deaktiviert ist, verfügt Spark SQL über zwei Arten von Schlüsselwörtern:

• Nicht reservierte Schlüsselwörter: Es gilt dieselbe Definition wie bei aktiviertem ANSI-Modus.
• Nicht reservierte Schlüsselwörter im strikten Modus: Eine strikte Version nicht reservierter Schlüsselwörter, die nicht als Tabellenalias verwendet werden können.

Standardmäßig ist spark.sql.ansi.enabled auf „false“ festgelegt.

Im Folgenden finden Sie eine Liste aller Schlüsselwörter in Spark SQL.