ANSI-efterlevnad i "Databricks Runtime"

Gäller för: Databricks Runtime

I den här artikeln beskrivs ANSI-efterlevnad i Databricks Runtime. För ANSI-läge i Databricks SQL, se ANSI_MODE.

Spark SQL har två alternativ för att stödja efterlevnad med ANSI SQL-standarden: spark.sql.ansi.enabled och spark.sql.storeAssignmentPolicy.

När spark.sql.ansi.enabled är inställt på trueanvänder Spark SQL en ANSI-kompatibel dialekt i stället för att vara Hive-kompatibel. Spark utlöser till exempel ett undantag vid körning i stället för att returnera nullresultat om indata till en SQL-operator/-funktion är ogiltiga. Vissa ANSI-dialektfunktioner kanske inte direkt kommer från ANSI SQL-standarden, men deras beteenden överensstämmer med ANSI SQL:s formatmall.

Dessutom har Spark SQL ett oberoende alternativ för att styra implicita gjutningsbeteenden vid lagring av rader i en tabell. Kastbeteenden definieras som regler för lagringstilldelning i standarden.

När spark.sql.storeAssignmentPolicy är inställt på ANSIföljer Spark SQL ansi-lagringstilldelningsreglerna. Det här är en separat konfiguration eftersom standardvärdet är ANSI, medan konfigurationen spark.sql.ansi.enabled är inaktiverad som standard.

I följande tabell sammanfattas beteendet:

Följande underavsnitt visar beteendeändringar i aritmetiska åtgärder, typkonverteringar och SQL-parsning när ANSI-läget är aktiverat. För typkonverteringar i Spark SQL finns det tre typer av dem och den här artikeln introducerar dem en i taget: cast, store assignment och type coercion.

Aritmetiska åtgärder

I Spark SQL kontrolleras aritmetiska åtgärder som utförs på numeriska typer (med undantag för decimaltecken) inte för spill som standard. Det innebär att om en åtgärd orsakar spill blir resultatet detsamma med motsvarande åtgärd i ett Java- eller Scala-program (om summan av två heltal är högre än det maximala värde som kan representeras blir resultatet ett negativt tal). Å andra sidan returnerar Spark SQL null för decimalöverskridningar. När spark.sql.ansi.enabled är inställt på true och en översvämning inträffar i numeriska och intervallaritmetiska operationer, utlöser det ett aritmetiskt undantag vid körning.

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

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

Rollbesättning

När spark.sql.ansi.enabled är inställt på trueutlöser explicit gjutning av CAST syntax ett körningsundantag för ogiltiga gjutningsmönster som definierats i standarden, till exempel gjutningar från en sträng till ett heltal.

CAST Satsen i Spark ANSI-läge följer syntaxreglerna i avsnitt 6.13 "cast specification" i ISO/IEC 9075-2:2011 Information technology — Database language - SQL – Part 2: Foundation (SQL/Foundation), förutom att den särskilt tillåter följande enkla typkonverteringar som inte tillåts enligt ANSI-standarden:

• NumericType <=> BooleanType
• Strängtyp <=> Binärtyp

De giltiga kombinationerna av käll- och måldatatypen i ett CAST uttryck anges av följande tabell. "Y" anger att kombinationen är syntaktiskt giltig utan begränsning och "N" anger att kombinationen inte är giltig.

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

Butiksuppdrag

Inställningen spark.sql.storeAssignmentPolicy är som standard ANSI. Med den här inställningen, när datatyperna för källvärden inte matchar målkolumntyperna, lägger Spark SQL automatiskt till ANSI CAST-satser i -instruktionen INSERT . Under tabellinfogning under den här principen söker Spark efter och avvisar ogiltiga avgjutningar, vilket utlöser ett undantag för att säkerställa datakvaliteten. Det innebär att om ett infogningsförsök misslyckas på grund av ett typmatchningsfel resulterar det inte i att några data delvis skrivs till tabellen.

Exempel:

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

De här exemplen visar att Spark SQL förhindrar att inkompatibla data infogas, vilket upprätthåller dataintegriteten.

När spark.sql.storeAssignmentPolicy är inställt på LEGACYåtergår Spark SQL till det beteende som är vanligt upp till Spark 2.x. I det här läget tillämpas äldre CAST-åtgärder i stället för ANSI CAST. Under den här principen resulterar ogiltiga avgjutningar under tabellinfogningar i antingen NULL-värden eller felaktiga värden som infogas, i stället för att utlösa ett undantag. Exempel:

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

Typtvång

Typhöjning och prioritet

När spark.sql.ansi.enabled är inställt på trueanvänder Spark SQL flera regler som styr hur konflikter mellan datatyper löses. Kärnan i den här konfliktlösningen är typprioritetens lista som definierar om värden för en viss datatyp kan höjas upp till en annan datatyp implicit.

• För typbestämning av den minst vanliga typen hoppar man över typen float för att undvika förlust av precision.

** För en komplex typ gäller prioritetsregeln rekursivt för dess komponentelement.

Särskilda regler gäller för strängtypen och otypad NULL. En NULL kan höjas upp till vilken annan typ som helst, medan en sträng kan höjas upp till valfri enkel datatyp.

Det här är en grafisk skildring av prioritetslistan som ett riktat träd:

Minsta gemensamma typupplösning

Den minsta vanliga typen från en uppsättning typer är den smalaste typen som kan nås från prioritetslistan av alla element i uppsättningen av typer.

Den minsta vanliga typupplösningen används för att:

• Bestäm om en funktion som förväntar sig en parameter av en typ kan anropas med ett argument av en smalare typ.
• Härled argumenttypen för funktioner som förväntar sig en delad argumenttyp för flera parametrar, till exempel coalesce, least eller greatest.
• Härled operandtyperna för operatorer som aritmetiska åtgärder eller jämförelser.
• Härled resultattypen för uttryck som falluttrycket.
• Härled element-, nyckel- eller värdetyperna för matris- och kartkonstruktorer.

Särskilda regler tillämpas om den minst vanliga typen matchar FLOAT. Om någon av typerna är INT, BIGINT eller DECIMAL skickas den minst vanliga typen till DOUBLE för att undvika potentiell förlust av siffror.

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

Beteendet för vissa SQL-funktioner kan vara annorlunda i ANSI-läge (spark.sql.ansi.enabled=true).

• size: Den här funktionen returnerar null för null-indata i ANSI-läge.
• element_at:
  • Den här funktionen genererar ArrayIndexOutOfBoundsException om du använder ogiltiga index.
  • Den här funktionen genererar NoSuchElementException om nyckeln inte finns på kartan.
• elt: Den här funktionen genererar ArrayIndexOutOfBoundsException om du använder ogiltiga index.
• make_date: Den här funktionen misslyckas med ett undantag om resultatdatumet är ogiltigt.
• make_timestamp: Den här funktionen misslyckas med ett undantag om resultattidsstämpeln är ogiltig.
• make_interval: Den här funktionen misslyckas med ett undantag om resultatintervallet är ogiltigt.
• next_day: Den här funktionen genererar IllegalArgumentException om indata inte är en giltig veckodag.
• parse_url: Den här funktionen utlöser IllegalArgumentException om en indatasträng inte är en giltig URL.
• to_date: Den här funktionen misslyckas med ett undantag om indatasträngen inte kan parsas eller om mönstersträngen är ogiltig.
• to_timestamp: Den här funktionen misslyckas med ett undantag om indatasträngen inte kan parsas eller om mönstersträngen är ogiltig.
• to_unix_timestamp: Den här funktionen misslyckas med ett undantag om indatasträngen inte kan parsas eller om mönstersträngen är ogiltig.
• unix_timestamp: Den här funktionen misslyckas med ett undantag om indatasträngen inte kan parsas eller om mönstersträngen är ogiltig.

SQL-operatorer

Beteendet för vissa SQL-operatorer kan vara annorlunda i ANSI-läge (spark.sql.ansi.enabled=true).

• array_col[index]: Den här operatorn genererar ArrayIndexOutOfBoundsException om ogiltiga index används.
• map_col[key]: Den här operatorn genererar NoSuchElementException om nyckeln inte finns på kartan.
• CAST(string_col AS TIMESTAMP): Den här operatorn misslyckas med ett undantag om indatasträngen inte kan parsas.
• CAST(string_col AS DATE): Den här operatorn misslyckas med ett undantag om indatasträngen inte kan parsas.

Användbara funktioner för ANSI-läge

När ANSI-läget är aktiverat utlöser det undantag för ogiltiga åtgärder. Du kan använda följande SQL-funktioner för att förhindra sådana undantag.

• try_cast: identisk med CAST, förutom att det returnerar NULL resultat i stället för att utlösa ett undantag vid ett körningsfel.
• try_add: identisk med add-operatorn +, förutom att den returnerar NULL resultat i stället för att utlösa ett undantag vid spill av integralvärde.
• try_divide: identisk med divisionsoperatorn /, förutom att den returnerar NULL resultat i stället för att utlösa ett undantag vid delning av 0.

SQL-nyckelord

När spark.sql.ansi.enabled är sant använder Spark SQL ANSI-lägesparsern. I det här läget har Spark SQL två typer av nyckelord:

• Reserverade nyckelord: Nyckelord som är reserverade och inte kan användas som identifierare för tabell, vy, kolumn, funktion, alias osv.
• Icke-reserverade nyckelord: Nyckelord som endast har en särskild betydelse i vissa kontexter och som kan användas som identifierare i andra sammanhang. Till exempel är EXPLAIN SELECT ... ett kommando, men EXPLAIN kan användas som identifierare på andra platser.

När ANSI-läget är inaktiverat har Spark SQL två typer av nyckelord:

• Icke-reserverade nyckelord: Samma definition som när ANSI-läget aktiveras.
• Strikt icke-reserverade nyckelord: En strikt version av icke-reserverade nyckelord som inte kan användas som tabellalias.

Som standard är spark.sql.ansi.enabled falsk.

Nedan visas en lista över alla nyckelord i Spark SQL.