Prise en charge du classement

Le classement vous permet de spécifier des règles alternatives pour la comparaison de chaînes, qui peuvent être utilisées pour comparer et trier des données selon une langue particulière ou d’autres règles spécifiées par l’utilisateur.

Vue d’ensemble

Les sections suivantes expliquent ce qu’est le classement et comment l’utiliser pour comparer des chaînes :

Comprendre le classement

Les chaînes de texte dans Snowflake sont stockées à l’aide du jeu de caractères UTF-8 et, par défaut, les chaînes sont comparées selon les codes Unicode représentant les caractères de la chaîne.

Toutefois, la comparaison de chaînes en fonction de leurs représentations de caractères UTF-8 peut ne pas fournir le comportement souhaité/attendu. Par exemple :

  • Si les caractères spéciaux d’une langue donnée ne sont pas triés conformément aux normes de classement de cette langue, le tri peut générer des résultats inattendus.

  • Vous souhaiterez peut-être que les chaînes soient ordonnées par d’autres règles, telles que le fait d’ignorer si les caractères sont en majuscules ou en minuscules.

Le classement vous permet de spécifier explicitement les règles à utiliser pour comparer des chaînes, en fonction des éléments suivants :

  • Différents paramètres régionaux (c.-à-d. différents jeux de caractères pour différentes langues).

  • Sensibilité à la casse (c’est-à-dire s’il faut utiliser des comparaisons de chaînes sensibles à la casse ou insensibles à la casse sans appeler explicitement les fonctions UPPER ou LOWER pour convertir les chaînes).

  • Sensibilité aux accents (par exemple, si Z, Ź et Ż sont considérés comme la même lettre ou des lettres différentes).

  • Sensibilité à la ponctuation (c’est-à-dire si les comparaisons utilisent uniquement des lettres ou incluent tous les caractères). Par exemple, si une comparaison est insensible à la ponctuation, A-B-C et ABC sont alors considérés comme équivalents.

  • Des options supplémentaires, telles que les préférences de tri en fonction de la première lettre d’une chaîne et le découpage des espaces vides de début et de fin.

Utilisations pour le classement

Le classement peut être utilisé dans une grande variété d’opérations, y compris (mais sans s’y limiter) :

Utilisation

Exemple

Lien

Comparaison simple

... WHERE column1 = column2 ...

WHERE

Jointures

... ON table1.column1 = table2.column2 ...

JOIN

Tri

... ORDER BY column1 ...

ORDER BY

Tri Top-K

... ORDER BY column1 LIMIT N ...

LIMIT / FETCH

Agrégation

... GROUP BY ...

GROUP BY

Clauses de fenêtre

... PARTITION BY ... ORDER BY ...

Fonctions de la fenêtre

Fonctions scalaires

... LEAST(column1, column2, column3) ...

Fonctions scalaires

Fonctions d’agrégation

... MIN(column1), MAX(column1) ...

Fonctions d’agrégation

Clustering de données

... CLUSTER BY (column1) ...

Clés de clustering et tables en cluster

Contrôle de classement

Le contrôle des classements est granulaire. Vous pouvez spécifier explicitement le classement à utiliser pour :

  • Un compte, en utilisant le paramètre au niveau du compte DEFAULT_DDL_COLLATION.

  • Toutes les colonnes de toutes les tables ajoutées à une base de données, à l’aide de la commande ALTER DATABASE.

  • Toutes les colonnes de toutes les tables ajoutées à un schéma, à l’aide de la commande ALTER SCHEMA.

  • Toutes les colonnes ajoutées à une table, à l’aide de la commande ALTER TABLE.

  • Colonnes individuelles dans une table, à l’aide de la commande CREATE TABLE.

  • Comparaison spécifique dans une instruction SQL (par exemple, WHERE col1 = col2). Si plusieurs classements sont appliqués à une instruction, Snowflake détermine le classement à utiliser en fonction de la priorité. Pour plus de détails sur la préséance, voir Ordre de priorité dans les opérations sur plusieurs chaînes (dans ce chapitre).

Constructions de classements en SQL

Vous pouvez utiliser les constructions SQL suivantes pour le classement :

Clause COLLATE (pour les définitions de colonne de table)

L’ajout de la clause facultative COLLATE à la définition d’une colonne de table indique que le classement spécifié est utilisé pour les comparaisons et autres opérations connexes effectuées sur les données de la colonne :

CREATE TABLE <table_name> ( <col_name> <col_type> COLLATE '<collation_specification>'
                            [ , <col_name> <col_type> COLLATE '<collation_specification>' ... ]
                            [ , ... ]
                          )
Copy

Si aucune clause COLLATE n’est spécifiée pour une colonne, Snowflake utilise la valeur par défaut, qui compare les chaînes en fonction de leurs représentations de caractères UTF-8.

En outre, Snowflake prend en charge la spécification d’une chaîne vide pour la spécification de classement (par exemple, COLLATE ''), ce qui revient à ne spécifier aucun classement pour la colonne.

Cependant, notez qu’en raison de la priorité, la spécification de COLLATE '' pour une colonne n’a pas le même effet que la spécification explicite de COLLATE 'utf8'. Pour plus de détails, voir Ordre de priorité dans les opérations sur plusieurs chaînes (dans ce chapitre).

Pour savoir si un classement a été spécifié pour les colonnes d’une table, utilisez DESCRIBE TABLE (ou utilisez la fonction COLLATION pour afficher le classement éventuel d’une colonne spécifique).

Fonction COLLATE

Cette fonction utilise le classement spécifié dans l’expression de chaîne d’entrée :

COLLATE( <expression> , '[<collation_specification>]' )
Copy

Cette fonction peut aussi être appelée en utilisant la notation infix :

<expression> COLLATE '[<collation_specification>]'
Copy

Cette fonction est particulièrement utile pour spécifier explicitement un classement particulier pour une opération particulière (par exemple, le tri), mais elle peut également être utilisée pour :

  • Autoriser le classement dans la clause SELECT d’une sous-requête, en faisant en sorte que toutes les opérations effectuées sur la colonne spécifiée dans la requête externe utilisent le classement.

  • Créer une table en utilisant CTAS avec un classement spécifié.

Par exemple :

-- Evaluates using "English case-insensitive" collation:
SELECT * FROM t1 WHERE COLLATE(col1 , 'en-ci') = 'Tango';

-- Sorts the results using German (Deutsch) collation.
SELECT * FROM t1 ORDER BY COLLATE(col1 , 'de');

-- Creates a table with a column using French collation.
CREATE TABLE t2 AS SELECT COLLATE(col1, 'fr') AS col1 FROM t1;

-- Creates a table with a column using French collation.
CREATE TABLE t2 AS SELECT col1 COLLATE 'fr' AS col1 FROM t1;
Copy

Fonction COLLATION

Cette fonction renvoie la spécification de classement utilisée par une expression, y compris une colonne de table :

COLLATION( <expression> )
Copy

Si aucun classement n’a été spécifié pour l’expression, la fonction renvoie NULL.

Généralement, si vous utilisez ceci sur un nom de colonne, vous devriez utiliser DISTINCT pour éviter d’obtenir une ligne de sortie pour chaque ligne de la table. Par exemple :

SELECT DISTINCT COLLATION(column1) FROM table1;
Copy

Note

Cette fonction renvoie uniquement la spécification de classement, pas son niveau de priorité. Pour plus de détails sur la préséance, voir Ordre de priorité dans les opérations sur plusieurs chaînes (dans ce chapitre).

Spécifications de classement

Lorsque vous utilisez une clause COLLATE (pour une colonne de table) ou la fonction COLLATE (pour une expression), vous devez inclure une spécification de classement, qui détermine la logique de comparaison utilisée pour la colonne/expression.

Une spécification de classement consiste en une chaîne d’un ou plusieurs spécificateurs séparés par un trait d’union (-), sous la forme suivante :

'<specifier>[-<specifier> ...]'

Les spécificateurs suivants sont pris en charge (pour plus de détails, voir Spécificateurs pris en charge dans cette rubrique) :

  • Paramètre régional.

  • Sensibilité à la casse.

  • Sensibilité aux accents.

  • Sensibilité à la ponctuation.

  • Préférence de première lettre.

  • Conversion de casse.

  • Coupe d’espace.

Les spécificateurs ne font pas la distinction entre les majuscules et les minuscules et peuvent être dans n’importe quel ordre, à l’exception des paramètres régionaux, qui doivent toujours figurer en premier, s’ils sont utilisés.

Les sections suivantes fournissent plus de détails sur les spécifications de classement :

Exemples de spécification

Voici quelques exemples de chaînes de spécification de classement :

  • 'de' : langue allemande (néerlandais).

  • 'de-ci-pi' : paramètres régionaux allemands, avec des comparaisons insensibles à la casse et à la ponctuation.

  • 'fr_CA-ai' : région canadienne française, avec des comparaisons insensibles à l’accent.

  • 'en_US-trim' : paramètres régionaux US anglais, avec des espaces de début et de fin ajustés avant la comparaison.

Vous pouvez également spécifier une chaîne vide pour une spécification de classement (par exemple, COLLATE '' ou COLLATE(col1, '')), ce qui indique de ne pas utiliser de classement.

Spécificateurs pris en charge

Paramètre régional:

Spécifie les règles spécifiques à la langue et au pays à appliquer.

Prend en charge les chaînes de paramètres régionaux valides, composées d’un code de langue (obligatoire) et d’un code de pays (facultatif) sous la forme language_country. Quelques exemples de paramètres régionaux :

  • en - Anglais.

  • en_US - Anglais américain.

  • fr - Français.

  • fr_CA - Français canadien.

De plus, le pseudo-paramètre régional utf8 spécifie l’utilisation du classement Unicode, qui est la valeur par défaut. Pour plus de détails, voir Tri à l’aide du classement UTF-8 ou par des paramètres régionaux (dans ce chapitre).

Le spécificateur de paramètres régionaux est facultatif, mais, s’il est utilisé, il doit être le premier spécificateur dans la chaîne.

Sensibilité à la casse:

Détermine si la casse doit être prise en compte lors de la comparaison de valeurs. Valeurs possibles :

  • cs - Sensible à la casse (par défaut).

  • ci - Insensible à la casse.

Par exemple :

Classement Spécification

Valeur

Résultat

'en-ci'

Abc = abc

Vrai

'en-cs' / en

Abc = abc

Faux

Sensibilité aux accents:

Détermine si les caractères accentués doivent être considérés comme égaux ou différents de leurs caractères de base. Valeurs possibles :

  • as - Sensible aux accents (par défaut).

  • ai - Insensible aux accents.

Par exemple :

Classement Spécification

Valeur

Résultat

Remarques

'fr-ai'

E = É

Vrai

'fr-as' / 'fr'

E = É

Faux

'en-ai'

a = ą

Vrai

En anglais, ces lettres sont considérées comme n’ayant que des différences d’accent. Par conséquent, si vous indiquez une insensibilité aux accents, les valeurs comparées sont égales.

'pl-ai'

a = ą

Faux

En polonais, ces lettres sont traitées comme des lettres de base distinctes. Elles se comparent donc toujours comme étant inégales, que l’insensibilité ou non soit spécifiée ou non.

'pl-as' / 'pl'

a = ą

Faux

Notez que les règles de sensibilité aux accents et de classement varient d’une langue à l’autre. Par exemple, dans certaines langues, le classement est toujours sensible aux accents et vous ne pouvez pas le désactiver, même en spécifiant un classement sans accent.

Sensibilité à la ponctuation:

Détermine si les caractères qui ne sont pas des lettres comptent. Valeurs possibles :

  • ps - Sensible à la ponctuation.

  • pi - Insensible à la ponctuation.

Notez que la valeur par défaut est spécifique au paramètre régional (autrement dit, si aucune sensibilité à la ponctuation n’est spécifiée, des règles spécifiques au paramètre régional sont utilisées). Dans la plupart des cas, les règles sont équivalentes à ps.

Par exemple :

Classement Spécification

Valeur

Résultat

Remarques

'en-pi'

A-B-C = ABC

Vrai

'en-ps'

A-B-C = ABC

Faux

Préférence de première lettre:

Détermine si, lors du tri, les lettres majuscules ou minuscules doivent être les premières. Valeurs possibles :

  • fl - Les lettres minuscules sont triées en premier.

  • fu - Les lettres majuscules sont triées en premier.

Notez que la valeur par défaut est spécifique au paramètre régional (autrement dit, si aucune valeur n’est spécifiée, un ordre spécifique au paramètre régional est utilisé). Dans la plupart des cas, l’ordre correspond à fl.

De plus, ce spécificateur n’a aucun impact sur les comparaisons d’égalité.

Conversion de casse:

Il en résulte que les chaînes sont converties en minuscules ou en majuscules avant les comparaisons. Dans certaines situations, cela est plus rapide qu’un classement complet spécifique au paramètre régional. Valeurs possibles :

  • upper - Convertir la chaîne en majuscules avant les comparaisons.

  • lower - Convertir la chaîne en minuscules avant les comparaisons.

Notez que ce spécificateur n’a pas de valeur par défaut (c’est-à-dire que si aucune valeur n’est spécifiée, aucune des conversions n’a lieu).

Coupe d’espace:

Supprime les espaces de début / fin des chaînes avant les comparaisons. Cette fonctionnalité peut être utile pour effectuer des comparaisons équivalentes en sémantique (sauf dans des cas extrêmement rares) au type de données SQL CHAR.

Valeurs possibles :

  • trim - Supprimer les espaces de début et de fin avant les comparaisons.

  • ltrim - Supprimer uniquement les espaces avant les comparaisons.

  • rtrim - Ne supprimer que les espaces de fin avant les comparaisons.

Notez que ce spécificateur n’a pas de valeur par défaut (c’est-à-dire que si aucune valeur n’est spécifiée, le découpage n’est pas effectué).

Par exemple :

Classement Spécification

Valeur

Résultat

Remarques

'en-trim'

__ABC_ = ABC

Vrai

Pour les besoins de ces exemples, les caractères de soulignement représentent des espaces.

'en-ltrim'

__ABC_ = ABC

Faux

'en-rtrim'

__ABC_ = ABC

Faux

'en'

__ABC_ = ABC

Faux

Détails d’implémentation de classement

Les sections suivantes fournissent plus de détails sur la prise en charge du classement :

Comparaison insensible à la casse

Comparaison des chaînes en majuscules et comparaison des chaînes d’origine

Dans certaines langues, deux caractères minuscules ont le même caractère majuscule correspondant. Par exemple, certaines langues prennent en charge les formes avec et sans point des I minuscules (i vs. ı). Le fait de forcer les chaînes de caractères à être en majuscules affecte les comparaisons.

L’exemple suivant illustre cette différence :

Créer la table :

create or replace table test_table (col1 varchar, col2 varchar);
insert into test_table values ('ı', 'i');
Copy

Interroger les données :

select col1 = col2,
       COLLATE(col1, 'lower') = COLLATE(col2, 'lower'),
       COLLATE(col1, 'upper') = COLLATE(col2, 'upper')
    from test_table;
+-------------+-------------------------------------------------+-------------------------------------------------+
| COL1 = COL2 | COLLATE(COL1, 'LOWER') = COLLATE(COL2, 'LOWER') | COLLATE(COL1, 'UPPER') = COLLATE(COL2, 'UPPER') |
|-------------+-------------------------------------------------+-------------------------------------------------|
| False       | False                                           | True                                            |
+-------------+-------------------------------------------------+-------------------------------------------------+
Copy

Poids des caractères

Snowflake prend en charge les spécifications de classification suivantes.

  • ICU (International Components for Unicode).

  • Spécifications de collation spécifiques à Snowflake (par exemple, upper et lower).

Pour les opérations de comparaison insensibles à la casse définies par l’ICU, Snowflake suit l’Unicode Collation Algorithm (UCA) et ne prend en compte que les poids principaux et secondaires (et non les poids tertiaires) des caractères Unicode. Les caractères qui ne diffèrent que par leur poids tertiaires sont traités comme identiques. Par exemple, en utilisant la spécification de classification en-ci , un espace et un espace insécable sont considérés comme identiques.

Tri à l’aide du classement UTF-8 ou par des paramètres régionaux

Les chaînes sont toujours stockées en interne dans Snowflake dans UTF-8 et peuvent représenter n’importe quel caractère dans n’importe quelle langue prise en charge par UTF-8. Par conséquent, le classement par défaut est UTF-8 (c.-à-d. 'utf8').

Le classement UTF-8 est basé sur la représentation numérique du caractère, par opposition à l’ordre alphabétique du caractère.

Ceci est comparable au tri en fonction de la valeur ordinale de chaque caractère ASCII, ce qui est important à noter car les lettres majuscules ont des valeurs ordinales inférieures aux lettres minuscules :

A = 65
B = 66
...
a = 97
b = 98
...

En conséquence :

  • Si vous triez dans l’ordre UTF-8, toutes les lettres majuscules sont renvoyées avant toutes les lettres minuscules :

    A , B , … , Y , Z , … , a , b , … , y , z

  • En revanche, la spécification de classement 'en' trie dans l’ordre alphabétique (au lieu d’utiliser la représentation interne UTF-8), ce qui a pour résultat que A et a sont renvoyés avant B et b :

    a , A , b , B , …

De plus, les différences entre les spécificateurs de sensibilité à la casse cs et ci ont une incidence sur le tri :

  • cs (sensible à la casse) renvoie toujours la version minuscule d’une lettre avant la version majuscule de la même lettre. Par exemple, en utilisant 'en-cs' :

    a , A , b , B , …

    Notez que la casse est la valeur par défaut et que, par conséquent, 'en-cs' et 'en' sont équivalents.

  • ci (insensible à la casse) renvoie les versions majuscules et minuscules des lettres de façon aléatoire les unes par rapport aux autres, mais toujours avant les versions majuscules et minuscules des lettres ultérieures. Par exemple, en utilisant 'en-ci' :

    A , a , b , B , …

Certains caractères non alphabétiques peuvent également être triés différemment selon le paramètre de classement. L’exemple de code suivant montre que le caractère plus (+) et le caractère moins (-) sont triés différemment selon les paramètres de classement :

Créer la table :

create or replace table demo (
    no_explicit_collation VARCHAR,
    en_ci VARCHAR COLLATE 'en-ci',
    en VARCHAR COLLATE 'en',
    utf_8 VARCHAR collate 'utf8');
insert into demo (no_explicit_collation) values
    ('-'),
    ('+');
update demo SET
    en_ci = no_explicit_collation,
    en = no_explicit_collation,
    utf_8 = no_explicit_collation;
Copy

Interroger les données :

select max(no_explicit_collation), max(en_ci), max(en), max(utf_8)
    from demo;
+----------------------------+------------+---------+------------+
| MAX(NO_EXPLICIT_COLLATION) | MAX(EN_CI) | MAX(EN) | MAX(UTF_8) |
|----------------------------+------------+---------+------------|
| -                          | +          | +       | -          |
+----------------------------+------------+---------+------------+
Copy

Ordre de priorité dans les opérations sur plusieurs chaînes

Lors de l’exécution d’une opération sur deux chaînes (ou plus), des classements différents peuvent être spécifiés pour des chaînes différentes. La détermination du classement à appliquer dépend de la manière dont le classement a été spécifié pour chaque entrée et de la priorité de chaque élément spécifique.

Il y a 3 niveaux de priorité (du plus élevé au plus bas) :

Fonction:

Le classement est spécifié à l’aide de la fonction Fonction COLLATE dans une instruction SQL.

Colonne:

Le classement a été spécifié dans la définition de colonne.

Aucun:

Aucun classement n’est/n’a été spécifié pour une expression/colonne donnée, ou un classement avec une spécification vide est/a été utilisé (par exemple COLLATE(col1, '') ou col1 STRING COLLATE '').

Lors de la détermination du classement à utiliser, la spécification du classement avec la priorité la plus élevée est utilisée. Si plusieurs classements sont spécifiés et qu’ils ont le même niveau de priorité, leurs valeurs sont comparées et, si elles ne sont pas égales, une erreur est renvoyée.

Par exemple, considérons une table avec les spécifications de classement au niveau de la colonne suivantes :

CREATE OR REPLACE TABLE collation_precedence_example(
  col1    VARCHAR,               -- equivalent to COLLATE ''
  col2_fr VARCHAR COLLATE 'fr',  -- French locale
  col3_de VARCHAR COLLATE 'de'   -- German locale
);
Copy

Si la table est utilisée dans une instruction comparant deux chaînes, le classement est appliqué comme suit :

-- Uses the 'fr' collation because the precedence for col2_fr is higher than
-- the precendence for col1.
... WHERE col1 = col2_fr ...

-- Uses the 'en' collation, because it is explicitly specified in the statement,
-- which takes precedence over the collation for col2_fr.
... WHERE col1 COLLATE 'en' = col2_fr ...

-- Returns an error because the expressions have different collations at the same
-- precedence level.
... WHERE col2_fr = col3_de ...

-- Uses the 'de' collation because collation for col2_fr has been removed.
... WHERE col2_fr COLLATE '' = col3_de ...

-- Returns an error because the expressions have different collations at the same
-- precedence level.
... WHERE col2_fr COLLATE 'en' = col3_de COLLATE 'de' ...
Copy

Note

Bien que le classement par défaut de Snowflake soit 'utf8', la spécification d’une chaîne vide (ou l’absence de classement) est différente de la spécification explicite de 'utf8', car le classement explicite a une priorité supérieure à l’absence de classement. Les deux dernières instructions dans les exemples de code ci-dessous montrent cette différence :

CREATE OR REPLACE TABLE collation_precedence_example2(
  s1 STRING COLLATE '',
  s2 STRING COLLATE 'utf8',
  s3 STRING COLLATE 'fr'
);

-- Uses 'utf8' because s1 has no collation and 'utf8' is the default.
SELECT * FROM collation_precedence_example2 WHERE s1 = 'a';

-- Uses 'utf8' because s1 has no collation and s2 has explicit 'utf8' collation.
SELECT * FROM collation_precedence_example2 WHERE s1 = s2;
Copy

Cet exemple s’exécute sans erreur car s1 n’a pas de classement et s3 a un classement explicite fr, donc le classement explicite a priorité :

SELECT * FROM collation_precedence_example2 WHERE s1 = s3;
+----+----+----+
| S1 | S2 | S3 |
|----+----+----|
+----+----+----+
Copy

Cet exemple provoque une erreur car s2 et s3 ont des classements différents spécifiés au même niveau de priorité :

SELECT * FROM collation_precedence_example2 WHERE s2 = s3;
Copy

Sortie :

002322 (42846): SQL compilation error: Incompatible collations: 'fr' and 'utf8'
Copy

Prise en charge limitée du classement dans les fonctions intégrées

Le classement n’est pris en charge que dans un sous-ensemble de fonctions de chaîne. Les fonctions qui pourraient raisonnablement être attendues pour implémenter le classement, mais qui ne prennent pas encore en charge le classement, renvoient une erreur lorsqu’elles sont utilisées avec le classement. Ces messages d’erreur sont affichés non seulement lors de l’appel de la fonction COLLATE, mais également lors de l’appel d’une fonction de chaîne sur une colonne définie comme étant assemblée dans l’instruction CREATE TABLE ou ALTER TABLE ayant créé cette colonne.

Actuellement, le classement n’influence que les opérations de comparaison simples.

Par exemple, POSITION('abc' in COLLATE('ABC', 'en-ci')) ne trouve pas abc dans ABC, même si un classement ne respectant pas la casse est spécifié.

Fonctions prenant en charge le classement

Ces fonctions prennent en charge le classement :

Certaines de ces fonctions présentent des limites d’utilisation avec le classement. Pour plus de détails, voir la documentation de chaque fonction spécifique.

Cette liste pourrait s’étendre avec le temps.

Prudence

Certains opérateurs et prédicats SQL, tels que || (concaténation) et LIKE, sont implémentés en tant que fonctions (et sont disponibles en tant que fonctions, par exemple LIKE() et CONCAT()). Si un prédicat ou un opérateur est implémenté en tant que fonction et que la fonction ne prend pas en charge le classement, le prédicat ou l’opérateur ne prend pas en charge le classement.

Voir aussi Limites de classement.

Conséquences sur les performances de l’utilisation du classement

L’utilisation du classement peut affecter les performances de diverses opérations de base de données :

  • Les opérations impliquant des comparaisons peuvent être plus lentes.

    Cela peut avoir un impact sur les clauses WHERE simples, ainsi que sur les jointures, les tris, les opérations GROUP BY, etc.

  • Utilisée avec certaines fonctions dans les prédicats WHERE, le nettoyage des micro-partitions pourrait être moins efficace.

  • L’utilisation d’un classement dans un prédicat WHERE différent du classement spécifié pour la colonne peut entraîner une diminution de l’efficacité du nettoyage ou une élimination complète du nettoyage.

Considérations supplémentaires sur l’utilisation du classement

  • N’oubliez pas que, malgré des appellations similaires, les fonctions de classement suivantes donnent des résultats différents :

    • COLLATE spécifie explicitement le classement à utiliser.

    • COLLATION indique quel classement est utilisé si aucun n’est spécifié explicitement.

  • Une colonne avec une spécification de classement peut utiliser des caractères ne provenant pas du paramètre régional du classement, ce qui peut avoir une incidence sur le tri.

    Par exemple, si une colonne est créée avec une clause COLLATE 'en', les données de la colonne peuvent contenir le caractère non anglais É. Dans cette situation, le caractère É est trié de façon proche de E.

  • Vous pouvez spécifier des opérations de classement qui ne sont pas nécessairement significatives.

    Par exemple, vous pouvez spécifier que les données polonaises sont comparées aux données françaises à l’aide d’un classement allemand :

    SELECT ... WHERE COLLATE(French_column, 'de') = Polish_column;
    
    Copy

    Cependant, Snowflake ne recommande pas d’utiliser cette fonctionnalité de cette manière, car elle pourrait générer des résultats inattendus ou non souhaités.

  • Une fois qu’une colonne de table est définie, vous ne pouvez pas modifier le classement de la colonne. En d’autres termes, une fois qu’une colonne a été créée avec un classement particulier à l’aide d’une instruction CREATE TABLE, vous ne pouvez pas utiliser ALTER TABLE pour modifier le classement.

    Toutefois, vous pouvez spécifier un classement différent dans une instruction DML, telle qu’une instruction SELECT, faisant référence à la colonne.

Différences entre ci et upper / lower

Les spécifications de classement upper et lower peuvent offrir de meilleures performances que la spécification de classement ci lors de la comparaison et du tri de chaînes. Cependant, upper et lower ont des effets légèrement différents de ci, comme expliqué dans les sections suivantes :

Différences dans le traitement des points de code ignorables

L’algorithme de classement Unicode spécifie que les éléments de classement (points de code) peuvent être ignorables, ce qui signifie qu’un point de code n’est pas pris en compte lors de la comparaison et du tri des chaînes.

  • Avec la spécification de classement ci, ces points de code sont ignorés. Cela peut rendre difficile la recherche ou le remplacement de points de code ignorables.

  • Avec les spécifications de classement upper et lower, ces points de code ne sont pas ignorés.

Par exemple, le point de code U+0001 est ignorable. Si vous comparez ce point de code à une chaîne vide avec la spécification de classement en-ci, le résultat est TRUE car U+0001 est ignoré :

SELECT '\u0001' = '' COLLATE 'en-ci';
Copy
+-------------------------------+
| '\U0001' = '' COLLATE 'EN-CI' |
|-------------------------------|
| True                          |
+-------------------------------+

En revanche, si vous utilisez les spécifications de classement upper ou lower, le résultat est FALSE car U+0001 n’est pas ignoré :

SELECT '\u0001' = '' COLLATE 'upper';
Copy
+-------------------------------+
| '\U0001' = '' COLLATE 'UPPER' |
|-------------------------------|
| False                         |
+-------------------------------+

De même, supposons que vous appeliez la fonction REPLACE pour supprimer ce point de code d’une chaîne. Si vous utilisez la spécification de classement en-ci, la fonction ne supprime pas le point de code car U+0001 est ignoré.

Comme le montre l’exemple ci-dessous, la chaîne renvoyée par la fonction REPLACE a la même longueur que la chaîne passée dans la fonction, car celle-ci ne supprime pas le caractère U+0001.

SELECT
  LEN('abc\u0001') AS original_length,
  LEN(REPLACE('abc\u0001' COLLATE 'en-ci', '\u0001')) AS length_after_replacement;
Copy
+-----------------+--------------------------+
| ORIGINAL_LENGTH | LENGTH_AFTER_REPLACEMENT |
|-----------------+--------------------------|
|               4 |                        4 |
+-----------------+--------------------------+

En revanche, si vous utilisez la spécification de classement upper ou lower, la fonction supprime le point de code de la chaîne et renvoie une chaîne plus courte.

SELECT
  LEN('abc\u0001') AS original_length,
  LEN(REPLACE('abc\u0001' COLLATE 'upper', '\u0001')) AS length_after_replacement;
Copy
+-----------------+--------------------------+
| ORIGINAL_LENGTH | LENGTH_AFTER_REPLACEMENT |
|-----------------+--------------------------|
|               4 |                        3 |
+-----------------+--------------------------+

Différences lorsque les caractères sont représentés par des points de code différents

Dans Unicode, différentes séquences de points de code peuvent représenter le même caractère. Par exemple, la petite lettre grecque Iota avec Dialytika et Tonos peut être représentée par le caractère précomposé avec le point de code U+0390 ou par la séquence de points de code U+03b9 U+0308 U+0301 pour les caractères décomposés.

Si vous utilisez la spécification de classement ci, les différentes séquences de points de code pour un caractère sont traitées comme le même caractère. Par exemple, le point de code U+0390 et la séquence de points de code U+03b9 U+0308 U+0301 sont considérés comme équivalents :

SELECT '\u03b9\u0308\u0301' = '\u0390' COLLATE 'en-ci';
Copy
+-------------------------------------------------+
| '\U03B9\U0308\U0301' = '\U0390' COLLATE 'EN-CI' |
|-------------------------------------------------|
| True                                            |
+-------------------------------------------------+

Afin d’améliorer les performances pour les spécifications de classement upper et lower, les séquences ne sont pas traitées de la même manière. Deux séquences de points de code ne sont considérées comme équivalentes que si elles aboutissent à la même représentation binaire après avoir été converties en majuscules ou en minuscules.

Par exemple, l’utilisation de la spécification upper avec le point de code U+0390 et la séquence de points de code U+03b9 U+0308 U+0301 donne des caractères qui sont traités comme égaux :

SELECT '\u03b9\u0308\u0301' = '\u0390' COLLATE 'upper';
Copy
+-------------------------------------------------+
| '\U03B9\U0308\U0301' = '\U0390' COLLATE 'UPPER' |
|-------------------------------------------------|
| True                                            |
+-------------------------------------------------+

L’utilisation de la spécification lower produit des caractères qui ne sont pas égaux :

SELECT '\u03b9\u0308\u0301' = '\u0390' COLLATE 'lower';
Copy
+-------------------------------------------------+
| '\U03B9\U0308\U0301' = '\U0390' COLLATE 'LOWER' |
|-------------------------------------------------|
| False                                           |
+-------------------------------------------------+

Ces différences sont moins susceptibles de se produire lors de l’utilisation de upper (plutôt que lower) car il n’y a qu’un seul point de code majuscule composite (U+0130), contre plus de 100 points de code minuscules composites.

Différences avec les séquences de points de code représentant un seul caractère

Dans les cas où une séquence de points de code représente un caractère unique, la spécification de classement ci reconnaît que la séquence représente un caractère unique et ne fait pas correspondre les points de code individuels de la séquence.

Par exemple, la séquence de points de code U+03b9 U+0308 U+0301 représente un seul caractère (la petite lettre grecque Iota avec Dialytika et Tonos). U+0308 et U+0301 représentent les accents appliqués à U+03b9.

Pour la spécification de classement ci, si vous utilisez la fonction CONTAINS pour déterminer si la séquence U+03b9 U+0308 contient U+03b9 ou U+0308, la fonction renvoie FALSE car la séquence U+03b9 U+0308 est traitée comme un seul caractère :

SELECT CONTAINS('\u03b9\u0308', '\u03b9' COLLATE 'en-ci');
Copy
+----------------------------------------------------+
| CONTAINS('\U03B9\U0308', '\U03B9' COLLATE 'EN-CI') |
|----------------------------------------------------|
| False                                              |
+----------------------------------------------------+
SELECT CONTAINS('\u03b9\u0308', '\u0308' COLLATE 'en-ci');
Copy
+----------------------------------------------------+
| CONTAINS('\U03B9\U0308', '\U0308' COLLATE 'EN-CI') |
|----------------------------------------------------|
| False                                              |
+----------------------------------------------------+

Pour améliorer les performances, les spécifications upper et lower ne traitent pas ces séquences comme un seul caractère. Dans l’exemple ci-dessus, la fonction CONTAINS renvoie TRUE car ces spécifications traitent la séquence de points de code comme des caractères distincts :

SELECT CONTAINS('\u03b9\u0308', '\u03b9' COLLATE 'upper');
Copy
+----------------------------------------------------+
| CONTAINS('\U03B9\U0308', '\U03B9' COLLATE 'UPPER') |
|----------------------------------------------------|
| True                                               |
+----------------------------------------------------+
SELECT CONTAINS('\u03b9\u0308', '\u0308' COLLATE 'upper');
Copy
+----------------------------------------------------+
| CONTAINS('\U03B9\U0308', '\U0308' COLLATE 'UPPER') |
|----------------------------------------------------|
| True                                               |
+----------------------------------------------------+

Différences lorsque des modifications apportées à un cas entraînent l’attribution de plusieurs points de code

Pour certains caractères composites, la version majuscule ou minuscule du caractère est représentée par une séquence de points de code. Par exemple, le caractère majuscule du caractère allemand ß est une séquence de deux caractères S (SS).

Même si ß et SS sont équivalents, lorsque vous utilisez la spécification de classement upper, les recherches de ß et SS renvoient des résultats différents. Les séquences produites par la conversion des cas correspondent soit intégralement, soit pas du tout.

SELECT CONTAINS('ß' , 's' COLLATE 'upper');
Copy
+--------------------------------------+
| CONTAINS('SS' , 'S' COLLATE 'UPPER') |
|--------------------------------------|
| False                                |
+--------------------------------------+
SELECT CONTAINS('ss', 's' COLLATE 'upper');
Copy
+-------------------------------------+
| CONTAINS('SS', 'S' COLLATE 'UPPER') |
|-------------------------------------|
| True                                |
+-------------------------------------+

Différences dans l’ordre de tri

Le tri pour les spécifications de classement upper et lower fonctionne différemment du tri pour la spécification ci :

  • Avec la spécification ci, les chaînes sont triées par clé de classement. En général, la clé de classement peut tenir compte de la sensibilité à la casse, de la sensibilité à l’accent, de la langue, etc.

  • Avec les spécifications upper et lower, les chaînes sont triées par point de code pour améliorer les performances.

Par exemple, certains caractères de la plage ASCII (tels que + et -) sont triés différemment :

SELECT '+' < '-' COLLATE 'en-ci';
Copy
+---------------------------+
| '+' < '-' COLLATE 'EN-CI '|
|---------------------------|
| False                     |
+---------------------------+
SELECT '+' < '-' COLLATE 'upper';
Copy
+---------------------------+
| '+' < '-' COLLATE 'UPPER' |
|---------------------------|
| True                      |
+---------------------------+

Autre exemple, les chaînes dont les points de code sont ignorés sont triées dans un ordre différent :

SELECT 'a\u0001b' < 'ab' COLLATE 'en-ci';
Copy
+-----------------------------------+
| 'A\U0001B' < 'AB' COLLATE 'EN-CI' |
|-----------------------------------|
| False                             |
+-----------------------------------+
SELECT 'a\u0001b' < 'ab' COLLATE 'upper';
Copy
+-----------------------------------+
| 'A\U0001B' < 'AB' COLLATE 'UPPER' |
|-----------------------------------|
| True                              |
+-----------------------------------+

En outre, les emojis sont triés différemment :

SELECT 'abc' < '❄' COLLATE 'en-ci';
Copy
+-----------------------------+
| 'ABC' < '❄' COLLATE 'EN-CI' |
|-----------------------------|
| False                       |
+-----------------------------+
SELECT 'abc' < '❄' COLLATE 'upper';
Copy
+-----------------------------+
| 'ABC' < '❄' COLLATE 'UPPER' |
|-----------------------------|
| True                        |
+-----------------------------+

Limites de classement

Le classement n’est pris en charge que pour les chaînes jusqu’à 8MB

Bien que le type de données VARCHAR de Snowflake prenne en charge les chaînes allant jusqu’à 16MB, Snowflake ne prend en charge le classement que lorsque la chaîne résultante est de 8MB ou moins. (Certaines opérations de classement peuvent allonger une chaîne).

Limitations sur le classement et les UDFs

Snowflake ne prend pas en charge le classement avec des UDFs (fonctions définies par l’utilisateur) :

  • Vous ne pouvez pas renvoyer une valeur de chaîne assemblée à partir d’une UDF ; le serveur indique l’incompatibilité du type de retour réel avec le type de retour déclaré.

  • Si vous envoyez une valeur de chaîne assemblée à une UDF, les informations de classement ne sont pas transmises ; l’UDF voit la chaîne comme une chaîne non assemblée.

Classement non pris en charge pour les chaînes dans VARIANT, ARRAY ou OBJECT

Les chaînes stockées dans un VARIANT, OBJECT ou ARRAY n’incluent pas de spécification de classement. Donc :

  • La comparaison de ces valeurs utilise toujours le classement « utf8 ».

  • Lorsqu’une valeur VARCHAR avec une spécification de classement est utilisée pour construire une valeur ARRAY, OBJECT ou VARIANT, la spécification de classement n’est pas conservée.

  • Les utilisateurs peuvent toujours comparer une valeur stockée dans un ARRAY, OBJECT ou VARIANT en extrayant la valeur, en la convertissant en VARCHAR et en ajoutant une spécification de classement. Par exemple :

    COLLATE(VARIANT_COL:fld1::VARCHAR, 'en-ci') = VARIANT_COL:fld2::VARCHAR
    
    Copy

Exemples

L’instruction suivante crée une table qui utilise un classement différent pour chaque colonne :

CREATE TABLE collation_demo (
  uncollated_phrase VARCHAR, 
  utf8_phrase VARCHAR COLLATE 'utf8',
  english_phrase VARCHAR COLLATE 'en',
  spanish_phrase VARCHAR COLLATE 'sp'
  );

INSERT INTO collation_demo (uncollated_phrase, utf8_phrase, english_phrase, spanish_phrase) 
   VALUES ('pinata', 'pinata', 'pinata', 'piñata');
Copy

La requête suivante sur la table affiche les valeurs attendues :

SELECT * FROM collation_demo;
+-------------------+-------------+----------------+----------------+
| UNCOLLATED_PHRASE | UTF8_PHRASE | ENGLISH_PHRASE | SPANISH_PHRASE |
|-------------------+-------------+----------------+----------------|
| pinata            | pinata      | pinata         | piñata         |
+-------------------+-------------+----------------+----------------+
Copy

La requête suivante ne trouve pas de correspondance, car le caractère ñ ne correspond pas à n :

SELECT * FROM collation_demo WHERE spanish_phrase = uncollated_phrase;
+-------------------+-------------+----------------+----------------+
| UNCOLLATED_PHRASE | UTF8_PHRASE | ENGLISH_PHRASE | SPANISH_PHRASE |
|-------------------+-------------+----------------+----------------|
+-------------------+-------------+----------------+----------------+
Copy

Changer de classement ne force pas à traiter des caractères associés, mais inégaux (par exemple ñ et n) :

SELECT * FROM collation_demo 
    WHERE spanish_phrase = uncollated_phrase COLLATE 'sp';
+-------------------+-------------+----------------+----------------+
| UNCOLLATED_PHRASE | UTF8_PHRASE | ENGLISH_PHRASE | SPANISH_PHRASE |
|-------------------+-------------+----------------+----------------|
+-------------------+-------------+----------------+----------------+
Copy

Les exemples suivants illustrent l’effet du classement sur l’ordre de tri :

INSERT INTO collation_demo (spanish_phrase) VALUES
   ('piña colada'),
   ('Pinatubo (Mount)'),
   ('pint'),
   ('Pinta');
Copy
SELECT spanish_phrase FROM collation_demo 
  ORDER BY spanish_phrase;
+------------------+
| SPANISH_PHRASE   |
|------------------|
| piña colada      |
| piñata           |
| Pinatubo (Mount) |
| pint             |
| Pinta            |
+------------------+
Copy

La requête suivante inverse l’ordre de ñ et n en remplaçant le classement de “sp” (espagnol) par “utf8” :

SELECT spanish_phrase FROM collation_demo 
  ORDER BY COLLATE(spanish_phrase, 'utf8');
+------------------+
| SPANISH_PHRASE   |
|------------------|
| Pinatubo (Mount) |
| Pinta            |
| pint             |
| piña colada      |
| piñata           |
+------------------+
Copy

Cet exemple montre comment utiliser la fonction COLLATION pour afficher le classement d’une expression, telle qu’une colonne :

CREATE TABLE collation_demo2 (c1 VARCHAR COLLATE 'fr', c2 VARCHAR COLLATE '');
INSERT INTO collation_demo2 (c1, c2) VALUES
    ('a', 'a'),
    ('b', 'b');
Copy
SELECT DISTINCT COLLATION(c1), COLLATION(c2) FROM collation_demo2;
+---------------+---------------+
| COLLATION(C1) | COLLATION(C2) |
|---------------+---------------|
| fr            | NULL          |
+---------------+---------------+
Copy

Vous pouvez également utiliser DESCRIBE TABLE pour afficher des informations de classement sur les colonnes d’un tableau :

DESC TABLE collation_demo2;
+------+--------------------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+ 
| name | type                           | kind   | null? | default | primary key | unique key | check | expression | comment | policy name |
|------+--------------------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------|
| C1   | VARCHAR(16777216) COLLATE 'fr' | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
| C2   | VARCHAR(16777216)              | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
+------+--------------------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
Copy