Conformité ANSI dans Databricks Runtime

S’applique à : Databricks Runtime

Cet article décrit la conformité ANSI dans Databricks Runtime. Pour le mode ANSI dans Databricks SQL, consultez ANSI_MODE.

Spark SQL dispose de deux options pour prendre en charge la conformité avec ANSI SQL : spark.sql.ansi.enabled et spark.sql.storeAssignmentPolicy.

Quand spark.sql.ansi.enabled est défini sur true, Spark SQL utilise un dialecte conforme à ANSI plutôt qu’à Hive. Par exemple, Spark lèvera une exception au moment de l’exécution au lieu de retourner des résultats null si les entrées d’un opérateur ou d’une fonction SQL ne sont pas valides. Certaines caractéristiques du dialecte ANSI ne sont pas directement issues de la norme ANSI SQL, mais leurs comportements s’alignent sur le style d’ANSI SQL.

De plus, Spark SQL offre une option indépendante pour contrôler les comportements de conversion implicite lors du stockage de lignes dans une table. Les comportements de conversion sont définis comme des règles d’affectation de magasin dans la norme.

Quand spark.sql.storeAssignmentPolicy est défini sur ANSI, Spark SQL est conforme aux règles d’affectation de magasin ANSI. Il s’agit d’une configuration distincte, car sa valeur par défaut est ANSI, tandis que la configuration spark.sql.ansi.enabled est désactivée par défaut.

Le tableau suivant récapitule ce comportement :

Les sous-sections suivantes présentent des changements de comportement dans les opérations arithmétiques, les conversions de types et l’analyse des SQL lorsque le mode ANSI est activé. En ce qui concerne les conversions de types dans Spark SQL, il en existe de trois sortes que cet article présente : cast, affectation de magasin et forçage de type.

Opérations arithmétiques

Dans Spark SQL, les opérations arithmétiques effectuées sur des types numériques (à l’exception du type décimal) ne sont pas vérifiées par défaut sur le plan des dépassements. Cela signifie que, si une opération provoque des dépassements, le résultat est le même avec l’opération correspondante dans un programme Java ou Scala (par exemple, si la somme de 2 entiers est supérieure à la valeur maximale pouvant être représentée, le résultat est un nombre négatif). Par ailleurs, Spark SQL retourne la valeur null pour les dépassements décimaux. Lorsque spark.sql.ansi.enabled est défini sur true et qu’un dépassement se produit dans des opérations arithmétiques numériques et d’intervalle, celui-ci lève une exception arithmétique au moment de l’exécution.

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

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

Caster

Lorsque spark.sql.ansi.enabled est défini sur true, un cast explicite à l’aide de la syntaxe CAST lève une exception de runtime pour les modèles de cast non autorisés définis dans la norme, tels des casts de chaîne en entier.

La clause CAST du mode ANSI Spark suit les règles de syntaxe de la section 6.13 « cast specification » de la spécification ISO/IEC 9075-2:2011 Information technology — Database languages - SQL — Part 2: Foundation (SQL/Foundation), sauf qu’elle autorise spécialement les conversions de types directes suivantes, que la norme ANSI n’autorise pas :

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

Les combinaisons valides de types de données source et cible dans une expression CAST sont présentées dans le tableau suivant. « O » indique que la combinaison est syntaxiquement valide sans restriction, et « N » qu’elle ne l’est pas.

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

Affectation de magasin

Le paramètre spark.sql.storeAssignmentPolicy par défaut est ANSI. Avec ce paramètre, lorsque les types de données des valeurs sources ne correspondent pas aux types de colonnes cibles, Spark SQL ajoute automatiquement des clauses ANSI CAST à l’instruction INSERT. Lors de l’insertion d’une table dans le cadre de cette stratégie, Spark vérifie et rejette les casts invalides, en levant une exception pour garantir la qualité des données. Cela signifie que si une tentative d’insertion échoue en raison d’une incompatibilité de type, aucune donnée n’est partiellement écrite dans la table.

Exemples :

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

Ces exemples montrent que Spark SQL empêche l’insertion de données incompatibles, ce qui maintient l’intégrité des données.

Lorsque le spark.sql.storeAssignmentPolicy est défini sur LEGACY, Spark SQL rétablit le comportement répandu jusqu’à Spark 2.x. Dans ce mode, au lieu d’utiliser ANSI CAST, il applique des opérations CAST héritées. Dans cette stratégie, les casts non valides pendant les insertions de table entraînent l’insertion de valeurs NULL ou de valeurs incorrectes au lieu de lever une exception. Exemples :

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

Forçage de type

Promotion et priorité de type

Lorsque spark.sql.ansi.enabled est défini sur true, Spark SQL utilise plusieurs règles qui régissent le mode de résolution des conflits entre les types de données. Au cœur de cette résolution des conflits se trouve la liste des priorités des types qui définit si les valeurs d’un type de données peuvent être promues de manière implicite vers un autre type de données.

• Pour la résolution du type le moins courant, le type float est ignoré pour éviter la perte de précision.

** Pour un type complexe, la règle de priorité s’applique de manière récursive à ses éléments constitutifs.

Des règles spéciales s’appliquent au type chaîne (String) et aux valeurs NULL non typées. Une valeur NULL peut être promue vers n’importe quel autre type, tandis qu’une chaîne peut être promue vers tout type de données simple.

Voici une représentation graphique de la liste des priorités sous la forme d’une arborescence dirigée:

Résolution du type le moins courant

Le type le moins courant d’un ensemble de types est le type le plus étroit accessible à partir de la liste des priorités par tous les éléments de l’ensemble de types.

La résolution du type le moins courant est utilisée pour :

• Décider si une fonction qui attend un paramètre d’un type donné peut être appelée à l’aide d’un argument d’un type plus étroit.
• Déduire le type d’argument pour des fonctions qui attendent un type d’argument partagé pour plusieurs paramètres, tels que coalesce, least ou greatest.
• Déduire les types d’opérandes pour des opérateurs tels que des opérations arithmétiques ou des comparaisons.
• Déterminer le type de résultat pour des expressions telles que l’expression case.
• Déduire les types d’élément, de clé ou de valeur pour des constructeurs de tableau et de mappage.

Des règles spéciales sont appliquées si le type le moins courant se résout en FLOAT. Avec des valeurs de type float, si l’un des types est INT, BIGINT ou DECIMAL, le type le moins courant est poussé vers le type DOUBLE pour éviter toute perte potentielle de chiffres.

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

Fonctions SQL

Le comportement de certaines fonctions SQL peut différer en mode ANSI (spark.sql.ansi.enabled=true).

• size : cette fonction retourne null pour une entrée null en mode ANSI.
• element_at:
  • Cette fonction lève une exception ArrayIndexOutOfBoundsException en cas d’utilisation d’index non valides.
  • Cette fonction lève une exception NoSuchElementException si la clé n’existe pas dans le mappage.
• elt : cette fonction lève une exception ArrayIndexOutOfBoundsException en cas d’utilisation d’index non valides.
• make_date : cette fonction échoue avec une exception si la date du résultat n’est pas valide.
• make_timestamp : cette fonction échoue avec une exception si le timestamp du résultat n’est pas valide.
• make_interval : cette fonction échoue avec une exception si l’intervalle du résultat n’est pas valide.
• next_day : cette fonction lève une exception IllegalArgumentException si l’entrée n’est pas un jour de la semaine valide.
• parse_url : cette fonction lève une exception IllegalArgumentException si une chaîne d’entrée n’est pas une URL valide.
• to_date : cette fonction échoue avec une exception si la chaîne d’entrée ne peut pas être analysée ou si la chaîne de modèle n’est pas valide.
• to_timestamp : cette fonction échoue avec une exception si la chaîne d’entrée ne peut pas être analysée ou si la chaîne de modèle n’est pas valide.
• to_unix_timestamp : cette fonction échoue avec une exception si la chaîne d’entrée ne peut pas être analysée ou si la chaîne de modèle n’est pas valide.
• unix_timestamp : cette fonction échoue avec une exception si la chaîne d’entrée ne peut pas être analysée ou si la chaîne de modèle n’est pas valide.

Opérateurs SQL

Le comportement de certains opérateurs SQL peut différer en mode ANSI (spark.sql.ansi.enabled=true).

• array_col[index] : cet opérateur lève une exception ArrayIndexOutOfBoundsException en cas d’utilisation d’index non valides.
• map_col[key] : cet opérateur lève une exception NoSuchElementException si la clé n’existe pas dans le mappage.
• CAST(string_col AS TIMESTAMP) : cet opérateur échoue avec une exception si la chaîne d’entrée ne peut pas être analysée.
• CAST(string_col AS DATE) : cet opérateur échoue avec une exception si la chaîne d’entrée ne peut pas être analysée.

Fonctions utiles pour le mode ANSI

Lorsque le mode ANSI est activé, il lève des exceptions pour les opérations non valides. Vous pouvez utiliser les fonctions SQL suivantes pour supprimer de telles exceptions.

• try_cast : fonction identique à CAST, sauf qu’elle retourne le résultat NULL au lieu de lever une exception sur une erreur de runtime.
• try_add : fonction identique à l’opérateur d’ajout +, sauf qu’elle retourne le résultat NULL au lieu de lever une exception sur un dépassement de valeur intégrale.
• try_divide : fonction identique à l’opérateur de division /, sauf qu’elle retourne le résultat NULL au lieu de lever une exception sur une division de 0.

Mots clés SQL

Lorsque spark.sql.ansi.enabled a la valeur true, Spark SQL utilise l’analyseur en mode ANSI. Dans ce mode, Spark SQL possède deux types de mots clés :

• Mots clés réservés : ne peuvent pas être utilisés en tant qu’identificateurs pour une table, un affichage, une colonne, une fonction, un alias, etc.
• Mots clés non réservés : ont une signification spéciale uniquement dans des contextes particuliers, et peuvent être utilisés comme identificateurs dans d’autres contextes. Par exemple, EXPLAIN SELECT ... est une commande, mais EXPLAIN peut être utilisé comme identificateur dans d’autres emplacements.

Lorsque le mode ANSI est désactivé, Spark SQL possède deux types de mots clés :

• Mots clés non réservés : même définition que lorsque le mode ANSI est activé.
• Mots clés non réservés stricts : version stricte des mots clés non réservés, qui ne peut pas être utilisée en tant qu’alias de table.

Par défaut, spark.sql.ansi.enabled a la valeur false.

Vous trouverez ci-dessous une liste de tous les mots clés dans Spark SQL.