Collatie

Van toepassing op: Databricks SQL Databricks Runtime 16.1 en hoger

Een collatie is een set regels die bepaalt hoe tekenreeksvergelijkingen worden uitgevoerd. Sorteringen ondersteunen hoofdlettergevoelige, accentgevoelige en volgruimtegevoelige vergelijkingen, evenals taalbewuste tekenreeksvolgorde.

Tekenreeksen in Azure Databricks worden weergegeven als UTF-8 gecodeerde Unicode-tekens. Standaard vergelijkt Azure Databricks tekenreeksen met hun binaire UTF-8-weergave, ook wel UTF8_BINARY sortering genoemd. UTF8_BINARY vergelijkingen zijn in veel gevallen snel en geschikt, maar zijn mogelijk niet geschikt voor toepassingen waarvoor taalbewuste sortering of vergelijkingen nodig zijn.

Naast binaire vergelijkingen is een veelvoorkomende use-case niet hoofdlettergevoelig. De UTF8_LCASE sortering is hiervoor ontworpen. Hiermee worden tekenreeksen geconverteerd naar kleine letters voordat ze worden vergeleken met behulp van UTF8_BINARY.

Voor taalbewuste vergelijkingen gebruikt Azure Databricks de volgende technologieën:

• International Components for Unicode (ICU) library to compute sortation.
• Common Locale Data Repository -tabellen (CLDR) voor landinstellingenbewuste sortering.
• Unicode Locale Data Markup Language (LDML) om sorteringen intern te coderen.

Deze technologieën worden ingekapseld in een set benoemde sorteringen die beschikbaar zijn voor gebruik in SQL-instructies.

Collatienamen

Azure Databricks biedt benoemde systeemsorteringen om de identificatie te vereenvoudigen. LDML-specificaties kunnen complex zijn om rechtstreeks te lezen en te gebruiken.

Syntaxis

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

locale
  language_code [ _ script_code ] [ _ country_code ]

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

• UTF8_BINARY

  Een binaire sortering waarmee tekenreeksen byte byte worden vergeleken op basis van de UTF-8-weergave. UTF8_BINARY is de standaard en meest lichtgewicht sortering in Azure Databricks.

  In deze sortering: 'A' (x'65') <'B' (x'66') < ... < 'Z' (x'90'). 'Z' (x'90') <'a' (x'97'), en 'A' (x'65') <>'a' (x'97'). Tekens zoals 'Ä' (x'C384') zijn groter dan beide 'Z' en 'z'.

• UTF8_LCASE

  Een lichtgewicht, niet hoofdlettergevoelige sortering die tekenreeksen converteert naar kleine letters voordat u ze vergelijkt met behulp van UTF8_BINARY.

  UTF8_LCASE is de sortering die wordt gebruikt voor Identifiers in Azure Databricks.

  Bijvoorbeeld:

  ORDER BY col COLLATE UTF8_LCASE
  

  is gelijk aan:

  ORDER BY LOWER(col) COLLATE UTF8_BINARY
  

• UNICODE

  De landinstelling van de ICU-hoofdmap, bekend in CLDR als landinstelling root (LDML-specificatie: und-u). Met deze sortering wordt een taalagnostische volgorde toegepast die vergelijkbare tekens groepeert. Bijvoorbeeld: 'a''Ä'<'b''A'<<. Deze sortering is standaard hoofdlettergevoelig en accentgevoelig.

• locale

  Een landinstellingsbewuste sortering op basis van CLDR-tabellen. De landinstelling wordt opgegeven als een taalcode, een optionele scriptcode en een optionele landcode. Landinstellingen zijn niet hoofdlettergevoelig.

  • language_code: Een ISO 639-1-taalcode van twee letters.
  • script_code: Een ISO 15924-scriptcode van vier letters.
  • country_code: Een ISO 3166-1 alfa-3-landcode van drie letters.

• modifier

  Bepaalt hoofdlettergevoeligheid, accentgevoeligheid en volgruimtegedrag. Modifiers zijn hoofdlettergevoelig en kunnen in elke volgorde worden opgegeven.

  • CS:Hoofdlettergevoelig. Het standaardgedrag.
  • CI: Niet hoofdlettergevoelig.
  • AS: Accentgevoelig. Het standaardgedrag.
  • AI: Accentgevoelig.

  Van toepassing op: Databricks SQL Databricks Runtime 16.2 en hoger

  • RTRIM: volgruimte ongevoelig. Knipt volgspaties (u0020) af voordat de vergelijking wordt vergeleken.

  Van toepassing op: Databricks SQL Databricks Runtime 16.2 en hoger

  U kunt RTRIM, maximaal één van CS of CI, en ten hoogste één van AS of AI.

Wanneer Azure Databricks een sorteringsnaam verwerkt, wordt de naam genormaliseerd door standaardwaarden te verwijderen. Bijvoorbeeld normaliseert SR_CYR_SRN_CS_AS naar SR.

Zie Ondersteunde sorteringenvoor een lijst met ondersteunde sorteringen.

Voorbeelden

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

Vergelijken UTF8_LCASE en UNICODE-sorteringen

Zowel UTF8_LCASE als op UNICODE gebaseerde sorteringen, zoals UNICODE_CI en UNICODE_CI_AI ondersteuning voor hoofdlettergevoelige vergelijkingen, maar ze verschillen in hoe ze accenttekens en landspecifieke regels verwerken.

Gedragsverschillen

UTF8_LCASE converteert tekenreeksen naar kleine letters en vergelijkt deze byte byte met behulp van UTF8_BINARY. Het is licht en snel, maar behandelt accenttekens als verschillend van hun niet-begeleide formulieren.

Sorteringen op basis van UNICODE maken gebruik van de ICU-bibliotheek en CLDR-landinstellingen. De CI en AI modifiers breiden dit uit om tekens als gelijkwaardig te behandelen, zelfs wanneer ze verschillen in het geval, accent of beide. Deze sorteringen zijn grondiger, maar hebben hogere rekenkosten.

Wanneer moet u elke sortering gebruiken

• Gebruik UTF8_LCASE deze indeling voor snelle hoofdlettergevoelige vergelijkingen wanneer accenttekens uniek moeten blijven. Deze sortering is geschikt voor datawarehouse-migraties van systemen die gebruikmaken van eenvoudige hoofdlettergevoelige tekenreekskoppelingen.

• Gebruik UNICODE_CI deze indeling voor niet-hoofdlettergevoelige vergelijkingen die voldoen aan Unicode-regels, met name wanneer gegevens tekens uit meerdere talen bevatten, waarbij eenvoudige kleine letters onvoldoende zijn.

• Gebruik UNICODE_CI_AI deze functie wanneer zowel hoofdletters als accentverschillen moeten worden genegeerd. Als een zoekopdracht "resume" bijvoorbeeld overeenkomt "résumé"met . Dit is gebruikelijk in gebruikersgerichte zoek- en meertalige toepassingen.

Voorbeelden

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

Standaardsortering

De standaardsortering is van toepassing op STRING letterlijke waarden, parametermarkeringen, functies zonder STRING parameters die tekenreeksen produceren, en kolom-, veld- of variabeletypedefinities zonder een COLLATE component.

De standaardsortering wordt als volgt afgeleid:

• Voor DDL-instructies zoals ALTER TABLE, CREATE VIEW, en CREATE TABLECREATE FUNCTION:

  • De standaardsortering is die van het object dat wordt gemaakt of gewijzigd.
  • Als er geen DEFAULT COLLATION component is opgegeven, wordt de standaardsortering UTF8_BINARY.

• Voor DML-instructies (UPDATE, DELETE FROM, , INSERT) MERGE INTOen Query is UTF8_BINARYde standaardsortering .

Sorteringsprioriteit

Azure Databricks past sorteringsprioriteitsregels toe om te bepalen welke sortering moet worden gebruikt voor een bepaalde tekenreeks. Er worden vier prioriteitsniveaus gedefinieerd:

1. Expliciet

   De sortering wordt expliciet toegewezen met behulp van collate expressies.

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

   De sortering wordt impliciet toegewezen door een kolomnaam, veldnaam, kolomalias, variabelenaam of parameternaam , inclusief het resultaat van een subquery waarbij de sortering niet Geen is.

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

   Een STRING letterlijke, benoemde of niet-benoemde parametermarkeringen of een STRING functie die wordt geproduceerd door een functie van een ander 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()
   

   De toegewezen sortering is de standaardsortering.

4. Geen

   Een STRING resultaat van een functie, operator of set-bewerking, zoals UNION die meerdere STRING argumenten met verschillende impliciete sorteringen accepteert.

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

Sorteringsderivatie

Bij het afleiden van de sortering voor een STRING resultaat gelden de prioriteitsregels als volgt:

1. Als de expressie overeenkomt met een van de bovenstaande definities, worden de sortering en prioriteit gedefinieerd.

2. Als de expressie een functie of operator is met één STRING parameter die een STRINGretourneert, zijn de sortering en prioriteit die van de STRING parameter.

3. Als de expressie een functie of operator is met twee of meer STRING parameters:

   1. Als alle parameters dezelfde sortering en prioriteit hebben, gebruikt het resultaat die sortering en prioriteit.

   2. Als parameters verschillende sorteringen of prioriteit hebben, kunt u afzonderlijke sorteringen D en C1C2 de standaardsortering zijn. Het resultaat wordt bepaald door de volgende tabel:

Voorbeelden

> 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

Beperkingen

Veel patroonkoppelings- en regex-functies ondersteunen alleen UTF8_BINARY en UTF8_LCASE sorteringen. Azure Databricks veroorzaakt een fout wanneer deze functies worden gebruikt met andere sorteringen, zoals UNICODE of landinstellingenspecifieke sorteringen, zoals DE of FR_CI_AI.

Betrokken functies omvatten LIKE, ILIKE, en RLIKEde regexp_* functiefamilie. Gebruik functie, functie, contains functie of functie voor startswithhet vergelijken van kolommen met andere sorteringenendswith.

Aanverwant

• collate uitdrukking
• collation Functie
• collations tabelfunctie