Conformità ANSI in Databricks Runtime

Si applica a: Databricks Runtime

Questo articolo descrive la conformità ANSI in Databricks Runtime. Per la modalità ANSI in Databricks SQL, vedere ANSI_MODE.

Spark SQL offre due opzioni per supportare la conformità con lo standard SQL ANSI: spark.sql.ansi.enabled e spark.sql.storeAssignmentPolicy.

Quando spark.sql.ansi.enabled è impostato su true, Spark SQL usa un dialetto conforme anSI anziché essere conforme a Hive. Ad esempio, Spark genererà un'eccezione in fase di esecuzione anziché restituire risultati Null se gli input a un operatore/funzione SQL non sono validi. Alcune caratteristiche del dialetto ANSI potrebbero non essere direttamente dallo standard SQL ANSI, ma i relativi comportamenti sono allineati allo stile di ANSI SQL.

Spark SQL offre inoltre un'opzione indipendente per controllare i comportamenti di cast impliciti durante l'archiviazione di righe in una tabella. I comportamenti di cast vengono definiti come regole di assegnazione dell'archivio nello standard.

Quando spark.sql.storeAssignmentPolicy è impostato su ANSI, Spark SQL è conforme alle regole di assegnazione dell'archivio ANSI. Si tratta di una configurazione separata perché il valore predefinito è ANSI, mentre la configurazione spark.sql.ansi.enabled è disabilitata per impostazione predefinita.

La tabella seguente riepiloga il comportamento:

Le sottosezioni seguenti presentano modifiche al comportamento nelle operazioni aritmetiche, nelle conversioni dei tipi e nell'analisi SQL quando è abilitata la modalità ANSI. Per le conversioni dei tipi in Spark SQL, sono disponibili tre tipi e questo articolo li introduce uno alla sola: cast, assegnazione di archivi e coercizione dei tipi.

Operazioni aritmetiche

In Spark SQL le operazioni aritmetiche eseguite sui tipi numerici (ad eccezione del decimale) non vengono controllate per impostazione predefinita per gli overflow. Ciò significa che nel caso in cui un'operazione causi overflow, il risultato è lo stesso con l'operazione corrispondente in un programma Java o Scala (ad esempio, se la somma di 2 numeri interi è superiore al valore massimo rappresentabile, il risultato è un numero negativo). Spark SQL restituisce invece null per gli overflow decimali. Quando spark.sql.ansi.enabled è impostato su true e si verifica un overflow in operazioni aritmetiche numeriche e di intervallo, genera un'eccezione aritmetica in fase di esecuzione.

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

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

Cast

Quando spark.sql.ansi.enabled è impostato su true, il cast esplicito tramite CAST sintassi genera un'eccezione di runtime per i modelli di cast non validi definiti nello standard, ad esempio i cast da una stringa a un numero intero.

La CAST clausola della modalità SPARK ANSI segue le regole di sintassi della sezione 6.13 "specifica cast" in ISO/IEC 9075-2:2011 Information Technology - Linguaggi di database - SQL - Parte 2: Foundation (SQL/Foundation), tranne che consente in particolare le conversioni di tipi semplici seguenti che non sono consentite in base allo standard ANSI:

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

Le combinazioni valide del tipo di dati di origine e di destinazione in un'espressione CAST vengono fornite dalla tabella seguente. "Y" indica che la combinazione è sintatticamente valida senza restrizione e "N" indica che la combinazione non è valida.

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

Assegnazione dello Store

L'impostazione spark.sql.storeAssignmentPolicy predefinita è ANSI. Con questa impostazione, quando i tipi di dati dei valori di origine non corrispondono ai tipi di colonna di destinazione, Spark SQL aggiunge automaticamente clausole CAST ANSI all'istruzione INSERT. Durante l'inserimento di tabelle in questo criterio, Spark verifica e rifiuta cast non validi, generando un'eccezione per garantire la qualità dei dati. Ciò significa che se un tentativo di inserimento non riesce a causa di una mancata corrispondenza del tipo, non comporterà la scrittura parziale dei dati nella tabella.

Esempi:

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

Questi esempi mostrano Spark SQL che impedisce l'inserimento di dati incompatibili, mantenendo così l'integrità dei dati.

spark.sql.storeAssignmentPolicy Quando è impostato su LEGACY, Spark SQL ripristina il comportamento prevalente fino a Spark 2.x. In questa modalità, invece di usare ANSI CAST, applica le operazioni CAST legacy. In questo criterio, i cast non validi durante gli inserimenti di tabelle comportano l'inserimento di valori NULL o valori non corretti, anziché generare un'eccezione. Esempi:

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

Coercizione dei tipi

Promozione e precedenza dei tipi

Quando spark.sql.ansi.enabled è impostato su true, Spark SQL usa diverse regole che regolano il modo in cui vengono risolti i conflitti tra i tipi di dati. Al centro di questa risoluzione dei conflitti è l'elenco di precedenza dei tipi che definisce se i valori di un determinato tipo di dati possono essere promossi in modo implicito a un altro tipo di dati.

• Per evitare la perdita di precisione, viene ignorata la risoluzione dei tipi meno comune.

** Per un tipo complesso, la regola di precedenza si applica in modo ricorsivo ai relativi elementi componenti.

Le regole speciali si applicano al tipo String e ai valori NULL non tipizzato. Un valore NULL può essere alzato di livello a qualsiasi altro tipo, mentre un valore String può essere promosso a qualsiasi tipo di dati semplice.

Si tratta di una rappresentazione grafica dell'elenco di precedenza come albero diretto:

Risoluzione dei tipi meno comuni

Il tipo meno comune di un set di tipi è il tipo più stretto raggiungibile dall'elenco di precedenza da tutti gli elementi del set di tipi.

La risoluzione dei tipi meno comune viene usata per:

• Decidere se una funzione che prevede un parametro di un tipo può essere richiamata usando un argomento di un tipo più stretto.
• Derivare il tipo di argomento per le funzioni che prevedono un tipo di argomento condiviso per più parametri, ad esempio coalesce, least o greatest.
• Derivare i tipi di operando per operatori quali operazioni aritmetiche o confronti.
• Derivare il tipo di risultato per le espressioni, ad esempio l'espressione case.
• Derivare i tipi di elemento, chiave o valore per i costruttori di matrice e mapping.

Le regole speciali vengono applicate se il tipo meno comune viene risolto in FLOAT. Con i valori di tipo float, se uno dei tipi è INT, BIGINT o DECIMAL, viene eseguito il push del tipo meno comune in DOUBLE per evitare la potenziale perdita di cifre.

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

Funzioni SQL

Il comportamento di alcune funzioni SQL può essere diverso in modalità ANSI (spark.sql.ansi.enabled=true).

• size: questa funzione restituisce null per l'input Null in modalità ANSI.
• element_at:
  • Questa funzione genera ArrayIndexOutOfBoundsException un'eccezione se si usano indici non validi.
  • Questa funzione genera NoSuchElementException un'eccezione se la chiave non esiste nella mappa.
• elt: questa funzione genera ArrayIndexOutOfBoundsException un'eccezione se si usano indici non validi.
• make_date: questa funzione ha esito negativo con un'eccezione se la data del risultato non è valida.
• make_timestamp: questa funzione ha esito negativo con un'eccezione se il timestamp del risultato non è valido.
• make_interval: questa funzione ha esito negativo con un'eccezione se l'intervallo di risultati non è valido.
• next_day: questa funzione genera IllegalArgumentException se l'input non è un giorno valido della settimana.
• parse_url: questa funzione genera IllegalArgumentException un'eccezione se una stringa di input non è un URL valido.
• to_date: questa funzione ha esito negativo con un'eccezione se la stringa di input non può essere analizzata o la stringa del criterio non è valida.
• to_timestamp: questa funzione ha esito negativo con un'eccezione se la stringa di input non può essere analizzata o la stringa del criterio non è valida.
• to_unix_timestamp: questa funzione ha esito negativo con un'eccezione se la stringa di input non può essere analizzata o la stringa del criterio non è valida.
• unix_timestamp: questa funzione ha esito negativo con un'eccezione se la stringa di input non può essere analizzata o la stringa del criterio non è valida.

Operatori SQL

Il comportamento di alcuni operatori SQL può essere diverso in modalità ANSI (spark.sql.ansi.enabled=true).

• array_col[index]: questo operatore genera un'eccezione se si ArrayIndexOutOfBoundsException usano indici non validi.
• map_col[key]: questo operatore genera un'eccezione NoSuchElementException se la chiave non esiste nella mappa.
• CAST(string_col AS TIMESTAMP): questo operatore non riesce con un'eccezione se non è possibile analizzare la stringa di input.
• CAST(string_col AS DATE): questo operatore non riesce con un'eccezione se non è possibile analizzare la stringa di input.

Funzioni utili per la modalità ANSI

Quando la modalità ANSI è attivata, genera eccezioni per le operazioni non valide. È possibile usare le funzioni SQL seguenti per eliminare tali eccezioni.

• try_cast: identico a CAST, ad eccezione del fatto che restituisce NULL il risultato anziché generare un'eccezione in caso di errore di runtime.
• try_add: identico all'operatore +add , ad eccezione del fatto che restituisce NULL il risultato anziché generare un'eccezione sull'overflow del valore integrale.
• try_divide: identico all'operatore /di divisione , ad eccezione del fatto che restituisce NULL il risultato anziché generare un'eccezione per la divisione di 0.

Parole chiave SQL

Se spark.sql.ansi.enabled è true, Spark SQL userà il parser in modalità ANSI. In questa modalità Spark SQL include due tipi di parole chiave:

• Parole chiave riservate: parole chiave riservate e che non possono essere usate come identificatori per tabella, vista, colonna, funzione, alias e così via.
• Parole chiave non riservate: parole chiave che hanno un significato speciale solo in contesti specifici e possono essere usate come identificatori in altri contesti. Ad esempio, EXPLAIN SELECT ... è un comando, ma EXPLAIN può essere usato come identificatori in altre posizioni.

Quando la modalità ANSI è disabilitata, Spark SQL ha due tipi di parole chiave:

• Parole chiave non riservate: stessa definizione di quella abilitata per la modalità ANSI.
• Parole chiave strict-non riservate: una versione rigorosa di parole chiave non riservate, che non può essere usata come alias di tabella.

Per impostazione predefinita spark.sql.ansi.enabled , false.

Di seguito è riportato un elenco di tutte le parole chiave in Spark SQL.