Classement

S’applique à : Databricks SQL Databricks Runtime 16.1 et versions ultérieures

Un classement est un ensemble de règles qui détermine la façon dont les comparaisons de chaînes sont effectuées. Les classements prennent en charge les comparaisons sans respect de la casse, d’accentuation et d’espace de fin, ainsi que l’ordre des chaînes prenant en charge la langue.

Les chaînes de Azure Databricks sont représentées sous la forme UTF-8 encodées Unicode des caractères. Par défaut, Azure Databricks compare les chaînes par leur représentation UTF-8 binaire, appelée UTF8_BINARY classement. UTF8_BINARY les comparaisons sont rapides et appropriées dans de nombreux cas, mais elles peuvent ne pas convenir aux applications qui nécessitent un tri ou des comparaisons prenant en charge la langue.

Au-delà des comparaisons binaires, un cas d’usage courant ne respecte pas la casse. Le UTF8_LCASE classement est conçu à cet effet. Il convertit les chaînes en minuscules avant de les comparer à l’aide UTF8_BINARYde .

Pour les comparaisons linguistiques, Azure Databricks utilise les technologies suivantes :

• Bibliothèque International Components for Unicode (ICU) pour calculer le classement.
• Tables CLDR (Common Locale Data Repository) pour le classement prenant en charge les paramètres régionaux.
• Unicode Locale Data Markup Language (LDML) pour encoder les classements en interne.

Ces technologies sont encapsulées dans un ensemble de classements nommés disponibles pour une utilisation dans les instructions SQL.

Noms de classement

Azure Databricks fournit des classements système nommés pour simplifier l’identification. Les spécifications LDML peuvent être complexes à lire et à utiliser directement.

Syntaxe

{ UTF8_BINARY |
  UTF8_LCASE |
  { UNICODE | locale } [ _ modifier [...] ] }

locale
  language_code [ _ script_code ] [ _ country_code ]

modifier
  { CS | CI | AS | AI | RTRIM }

• UTF8_BINARY

  Classement binaire qui compare les chaînes d’octets byte en fonction de leur représentation UTF-8. UTF8_BINARY est le classement par défaut et le plus léger dans Azure Databricks.

  Dans ce classement : 'A' (x'65') <'B' (x'66') < ... < 'Z' (x'90'). Toutefois, 'Z' (x'90') <'a' (x'97') et 'A' (x'65') <>'a' (x'97'). Les caractères tels que 'Ä' (x’C384') sont supérieurs aux deux 'Z' et 'z'.

• UTF8_LCASE

  Classement léger sans respect de la casse qui convertit les chaînes en minuscules avant de les comparer à l’aide UTF8_BINARYde .

  UTF8_LCASE est le classement utilisé pour Identifiers dans Azure Databricks.

  Exemple :

  ORDER BY col COLLATE UTF8_LCASE
  

  équivaut à :

  ORDER BY LOWER(col) COLLATE UTF8_BINARY
  

• UNICODE

  Paramètres régionaux racine de l’ICU, connus dans CLDR comme root paramètres régionaux (spécification LDML : und-u). Ce classement applique un ordre indépendant du langage qui regroupe des caractères similaires. Par exemple : 'a''b'<<'A'<'Ä'. Ce classement respecte la casse et respecte les accents par défaut.

• locale

  Classement prenant en charge les paramètres régionaux basé sur les tables CLDR. Les paramètres régionaux sont spécifiés sous la forme d’un code de langue, d’un code de script facultatif et d’un code de pays facultatif. Les valeurs de paramètres régionaux ne respectent pas la casse.

  • language_code: code de langue ISO 639-1 à deux lettres.
  • script_code: Code de script ISO 15924 à quatre lettres.
  • country_code: code de pays ISO 3166-1 alpha-3 à trois lettres.

• modifier

  Contrôle la sensibilité de la casse, la sensibilité des accents et le comportement d’espace de fin. Les modificateurs ne respectent pas la casse et peuvent être spécifiés dans n’importe quel ordre.

  • CS: respectant la casse. Comportement par défaut.
  • CI: ne respectant pas la casse.
  • AS: respect des accents. Comportement par défaut.
  • AI: sans respect des accents.

  S’applique à : Databricks SQL Databricks Runtime 16.2 et versions ultérieures

  • RTRIM: insensible à l’espace de fin. Supprime les espaces de fin (u0020) avant la comparaison.

  S’applique à : Databricks SQL Databricks Runtime 16.2 et versions ultérieures

  Vous pouvez spécifier RTRIM, au plus un ou CSCI, et au plus un ou ASAI.

Lorsque Azure Databricks traite un nom de classement, il normalise le nom en supprimant les valeurs par défaut. Par exemple, SR_CYR_SRN_CS_AS normalise à SR.

Pour obtenir la liste des interclassements pris en charge, consultez Classements pris en charge.

Exemples

-- Fully qualified collation names are supported; case doesn't matter.
system.builtin.unicode

-- All collations are system-defined and do not require qualification.
unicode

-- Two-letter language code for German collation.
DE

-- Two-letter language code and three-letter country code for French Canadian collation.
fr_CAN

-- Two-letter language code, four-letter script code, and three-letter country code
-- for Traditional Chinese in Macao.
zh_Hant_MAC

-- German collation with case-insensitive and accent-insensitive modifiers.
-- 'Ä', 'A', and 'a' are all considered equal.
de_CI_AI

-- Backticks are allowed but not required for built-in collations.
`UTF8_BINARY`

Comparer UTF8_LCASE et unicode

Les UTF8_LCASE classements basés sur UNICODE, tels que UNICODE_CI les UNICODE_CI_AI comparaisons qui ne respectent pas la casse et prennent en charge les comparaisons qui ne respectent pas la casse, mais elles diffèrent dans la façon dont elles gèrent les caractères accentués et les règles spécifiques aux paramètres régionaux.

Différences de comportement

UTF8_LCASE convertit les chaînes en minuscules et les compare en octets à l’aide UTF8_BINARYde . Il est léger et rapide, mais traite les caractères accentués comme distincts de leurs formes nonaccentées.

Les classements unicode utilisent la bibliothèque ICU et les données de paramètres régionaux CLDR. Les CI modificateurs AI étendent ceci pour traiter les caractères comme équivalents même lorsqu’ils diffèrent en cas, accentuation ou les deux. Ces classements sont plus approfondis, mais ont un coût de calcul plus élevé.

Quand utiliser chaque classement

• Utilisez cette option UTF8_LCASE pour les comparaisons rapides qui ne respectent pas la casse lorsque les caractères accentués doivent rester distincts. Ce classement convient parfaitement aux migrations d’entrepôts de données à partir de systèmes qui utilisent une correspondance simple sans respect de la casse.

• Utilisez UNICODE_CI les comparaisons qui ne respectent pas la casse qui suivent les règles Unicode, en particulier lorsque les données incluent des caractères provenant de plusieurs langages où la casse simple est insuffisante.

• Utilisez UNICODE_CI_AI quand les différences de cas et d’accentuation doivent être ignorées. Par exemple, si une recherche "resume" doit correspondre "résumé". Cela est courant dans les applications de recherche et multilingues accessibles par l’utilisateur.

Exemples

-- UTF8_LCASE lowercases then compares bytes.
-- Accented and unaccented characters are not equivalent.
> SELECT 'Cafe' = 'café' COLLATE UTF8_LCASE;
  false

> SELECT 'Café' = 'café' COLLATE UTF8_LCASE;
  true

-- UNICODE_CI is case-insensitive but accent-sensitive.
-- 'Café' equals 'café' (case differs) but not 'Cafe' (accent differs).
> SELECT 'Café' = 'cafe' COLLATE UNICODE_CI;
  false

> SELECT 'Café' = 'café' COLLATE UNICODE_CI;
  true

-- UNICODE_CI_AI is case-insensitive and accent-insensitive.
-- 'Café' matches 'cafe' because both case and accent differences are ignored.
> SELECT 'Cafe' = 'café' COLLATE UNICODE_CI_AI;
  true

> SELECT 'resume' = 'résumé' COLLATE UNICODE_CI_AI;
  true

-- UTF8_LCASE sorts by lowercase byte order.
-- UNICODE collations sort by linguistic similarity.
> SELECT col FROM VALUES('Banana'), ('apple'), ('Ångström'), ('äpfel') AS t(col)
    ORDER BY col COLLATE UTF8_LCASE;
  apple
  Banana
  Ångström
  äpfel

> SELECT col FROM VALUES('Banana'), ('apple'), ('Ångström'), ('äpfel') AS t(col)
    ORDER BY col COLLATE UNICODE_CI;
  apple
  Ångström
  äpfel
  Banana

Collation par défaut

Le classement par défaut s’applique aux STRING littéraux, aux marqueurs de paramètres, aux fonctions sans STRING paramètres qui produisent des chaînes et des définitions de type de champ ou de variable sans COLLATE clause.

Le classement par défaut est dérivé comme suit :

• Pour les instructions DDL telles que ALTER TABLE, , CREATE VIEWCREATE TABLE, et CREATE FUNCTION:

  • Le classement par défaut est le classement par défaut de l’objet en cours de création ou de modification.
  • Si aucune clause n’est DEFAULT COLLATION spécifiée, le classement par défaut est UTF8_BINARY.

• Pour les instructions DML (UPDATE, , DELETE FROM, INSERTMERGE INTO) et Query, le classement par défaut est UTF8_BINARY.

Priorité du classement

Azure Databricks applique des règles de priorité de classement pour déterminer quel classement utiliser pour une chaîne donnée. Quatre niveaux de précédence sont définis :

1. Explicite

   Le classement est explicitement attribué à l’aide d’unecollate expression.

   -- Force binary collation to check whether a VIN matches a Ferrari.
   vin COLLATE UTF8_BINARY LIKE 'ZFF%'
   
   -- Force German collation to order German first names.
   ORDER BY vorname COLLATE DE
   

2. Implicite

   Le classement est implicitement attribué par un nom de colonne, un nom de champ, un alias de colonne, un nom de variable ou une référence de nom de paramètre , y compris le résultat d’une sous-requête où le classement n’est pas None.

   -- Use the collation of the column as defined.
   employee.name LIKE 'Mc%'
   
   -- Use the collation of the variable as defined.
   translate(session.tempvar, 'Z', ',')
   

3. Par défaut

   Un STRING littéral, nommé ou un marqueur de paramètre non nommé ou STRING produit par une fonction d’un autre type.

   -- A literal string has the default collation.
   'Hello'
   
   -- :parm1 is a parameter marker using the session default collation.
   EXECUTE IMMEDIATE 'SELECT :parm1' USING 'Hello' AS parm1;
   
   -- ? is a parameter marker using the session default collation.
   EXECUTE IMMEDIATE 'SELECT ?' USING 'Hello';
   
   -- The result of casting a non-STRING to STRING uses the default collation.
   CAST(5 AS STRING)
   
   -- The date is converted to a string using the default collation.
   to_char(DATE'2016-04-08', 'y')
   
   -- session_user() returns a STRING with the default collation.
   session_user()
   

   Le classement attribué est le classement par défaut.

4. Aucun

   Résultat STRING d’une fonction, d’un opérateur ou d’une opération de jeu, telle que UNION celle qui accepte plusieurs STRING arguments avec différents classements implicites.

   -- Concatenating two strings with different explicit collations results in no collation.
   SELECT fr || de AS freutsch FROM VALUES('Voulez vous ' COLLATE FR, 'Kartoffelsupp...' COLLATE DE) AS T(fr, de)
   
   -- A union of two strings with different explicit collations results in no collation.
   SELECT 'Voulez vous ' COLLATE FR UNION ALL SELECT 'Kartoffelsupp...' COLLATE DE
   

Dérivation de classement

Lors de la dérivation du classement pour un STRING résultat, les règles de précédence s’appliquent comme suit :

1. Si l’expression correspond à l’une des définitions ci-dessus, le classement et la priorité sont définis.

2. Si l’expression est une fonction ou un opérateur avec un paramètre unique STRING retournant un STRING, le classement et la priorité sont ceux du STRING paramètre.

3. Si l’expression est une fonction ou un opérateur avec deux paramètres ou plus STRING :

   1. Si tous les paramètres ont le même classement et priorité, le résultat utilise ce classement et cette priorité.

   2. Si les paramètres ont des classements ou des précédences différents, laissez C1 et C2 soyez des classements distincts et D être le classement par défaut. Le résultat est déterminé par le tableau suivant :

Exemples

> SELECT 'hello' = 'hello   ' COLLATE UNICODE_RTRIM;
  true

> CREATE TABLE words(fr STRING COLLATE FR, de STRING COLLATE DE, en STRING COLLATE EN);
> INSERT INTO words VALUES ('Salut', 'Hallo', 'Hello');

-- A literal string has the default collation.
> SELECT collation('Ciao');
  UTF8_BINARY

-- A function producing a STRING has the default collation.
> SELECT collation(user());
  UTF8_BINARY

-- A function that modifies a STRING passes the collation through.
> SELECT collation(upper('Ciao'));
  UTF8_BINARY

-- Implicit collation (French) wins over default collation.
> SELECT collation(fr || 'Ciao') FROM words;
  FR

-- Explicit collation (French) wins over implicit collation (German).
> SELECT collation('Salut' COLLATE FR || de) FROM words;
  FR

-- Implicit German collides with implicit French; the result has no collation.
> SELECT collation(de || fr) FROM words;
  null

-- Explicit collation (French) wins over default collation (Italian).
> SELECT collation('Salut' COLLATE FR || 'Ciao');
  FR

-- Explicit collation (French) collides with explicit collation (German).
> SELECT collation('Salut' COLLATE FR || 'Hallo' COLLATE DE);
  COLLATION_MISMATCH.EXPLICIT

-- Explicit collation wins over no collation.
> SELECT collation('Ciao' COLLATE IT || (fr || de)) FROM words;
  IT

-- Implicit collation (English) does not win over None.
> SELECT collation(en || (fr || de)) FROM words;
  null

-- Explicit collation (English) wins over implicit collation anywhere in the expression.
> SELECT collation((fr || ltrim('H' COLLATE EN, fr)) || fr) FROM words;
  EN

Limites

De nombreuses fonctions de correspondance de modèle et d’expression régulière prennent en charge uniquement UTF8_BINARY et UTF8_LCASE les classements. Azure Databricks génère une erreur lorsque ces fonctions sont utilisées avec d’autres classements tels que UNICODE ou des classements spécifiques aux paramètres régionaux tels que DE ou FR_CI_AI.

Les fonctions affectées incluent LIKE, ILIKE, RLIKEet la famille de regexp_* fonctions. Pour la correspondance de chaînes sur les colonnes avec d’autres classements, utilisez contains la fonction, startswith la fonction ou endswith la fonction.

Connexe

• Expression collate
• Fonction collation
• Fonction de table collations