Zestawianie

Dotyczy: Databricks SQL Databricks Runtime 16.1 lub nowszej

Sortowanie to zestaw reguł określający sposób wykonywania porównań ciągów. Sortowania obsługują porównania bez uwzględniania wielkości liter, bez uwzględniania akcentu i końcowego bez uwzględniania spacji, a także porządkowanie ciągów obsługujących język.

Ciągi w Azure Databricks są reprezentowane jako UTF-8 zakodowane Unicode znaków. Domyślnie Azure Databricks porównuje ciągi według ich binarnej reprezentacji UTF-8, znanej jako UTF8_BINARY sortowania. UTF8_BINARY porównania są szybkie i odpowiednie w wielu przypadkach, ale mogą nie być odpowiednie dla aplikacji, które wymagają sortowania lub porównywania obsługującego język.

Poza porównaniami binarnymi typowym przypadkiem użycia jest dopasowywanie bez uwzględniania wielkości liter. Sortowanie UTF8_LCASE jest przeznaczone do tego celu. Konwertuje ciągi na małe litery przed porównaniem ich przy użyciu metody UTF8_BINARY.

W przypadku porównań obsługujących język Azure Databricks używa następujących technologii:

• Biblioteka International Components for Unicode (ICU) do obliczania sortowania.
• Typowe tabele repozytorium danych ustawień regionalnych (CLDR) na potrzeby sortowania obsługującego ustawienia regionalne.
• Język LDML (Unicode Locale Data Markup Language) do wewnętrznego kodowania porządkowań.

Te technologie są hermetyzowane w zestawie nazwanych sortowania dostępnych do użycia w instrukcjach SQL.

Uwaga / Notatka

Aby uzyskać ograniczenia dotyczące używania sortowania z tabelami usługi Delta Lake, zobacz Ograniczenia.

Nazwy sortowania

Azure Databricks zapewnia nazwane sortowania systemu w celu uproszczenia identyfikacji. Specyfikacje LDML mogą być złożone do bezpośredniego odczytywania i używania.

Składnia

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

locale
  language_code [ _ script_code ] [ _ country_code ]

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

• UTF8_BINARY

  Sortowanie binarne, które porównuje ciągi bajtów bajtów na podstawie ich reprezentacji UTF-8. UTF8_BINARY jest domyślnym i najbardziej uproszczonym sortowaniem w Azure Databricks.

  W tym sortowaniu: 'A' (x'65') <'B' (x'66') < ... < 'Z' (x'90'). 'Z' Jednak (x'90') (x'97') <'a' i 'A' (x'65') <>'a' (x'97'). Znaki, takie jak 'Ä' (x'C384') są większe niż i 'z''Z' .

• UTF8_LCASE

  Uproszczone sortowanie bez uwzględniania wielkości liter, które konwertuje ciągi na małe litery przed ich porównaniem przy użyciu metody UTF8_BINARY.

  UTF8_LCASE to sortowanie używane do Identifiers w Azure Databricks.

  Na przykład:

  ORDER BY col COLLATE UTF8_LCASE
  

  jest odpowiednikiem:

  ORDER BY LOWER(col) COLLATE UTF8_BINARY
  

• UNICODE

  Ustawienia regionalne katalogu głównego ICU znane w clDR jako root ustawienia regionalne (specyfikacja LDML: und-u). To sortowanie stosuje niezależną od języka kolejność grupowania podobnych znaków. Na przykład: 'a''b'<<'A'<'Ä'. Sortowanie jest domyślnie wrażliwe na wielkość liter i akcent.

• locale

  Sortowanie obsługujące ustawienia regionalne oparte na tabelach CLDR. Ustawienia regionalne są określane jako kod języka, opcjonalny kod skryptu i opcjonalny kod kraju. Wartości ustawień regionalnych są niewrażliwe na wielkość liter.

  • language_code: dwuliterowy kod języka ISO 639-1 .
  • script_code: czteroliterowy kod skryptu ISO 15924 .
  • country_code: trzyliterowy kod kraju ISO 3166-1 alfa-3 .

• modifier

  Określa ważność wielkości liter, czułość akcentu i zachowanie spacji końcowej. Modyfikatory są bez uwzględniania wielkości liter i można je określić w dowolnej kolejności.

  • CS:Rozróżniana wielkość liter. Domyślne zachowanie.
  • CI:Bez uwzględniania wielkości liter.
  • AS: z uwzględnieniem akcentu. Domyślne zachowanie.
  • AI: bez uwzględniania akcentu.

  Dotyczy: Databricks SQL Databricks Runtime 16.2 lub nowsze

  • RTRIM: niewrażliwy na spację końcową. Przycina końcowe spacje (u0020) przed porównaniem.

  Dotyczy: Databricks SQL Databricks Runtime 16.2 lub nowsze

  Można określić RTRIM, co najwyżej jeden z CS elementów lub CI, i co najwyżej jeden z AS elementów lub AI.

Gdy Azure Databricks przetwarza nazwę sortowania, normalizuje nazwę, usuwając wartości domyślne. Na przykład SR_CYR_SRN_CS_AS normalizuje wartość SR.

Aby uzyskać listę obsługiwanych sortowania, zobacz Obsługiwane sortowania.

Przykłady

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

Porównywanie UTF8_LCASE i sortowanie UNICODE

Sortowania oparte zarówno UTF8_LCASE na formacie UNICODE, jak UNICODE_CI i UNICODE_CI_AI obsługują porównania bez uwzględniania wielkości liter, ale różnią się one sposobem obsługi znaków wyróżniających i reguł specyficznych dla ustawień regionalnych.

Różnice behawioralne

UTF8_LCASE Konwertuje ciągi na małe litery i porównuje je bajty bajtowe przy użyciu metody UTF8_BINARY. Jest lekki i szybki, ale traktuje akcentowane znaki w odróżnieniu od ich nieocentowanych form.

Sortowania oparte na standardzie UNICODE używają biblioteki ICU i danych regionalnych CLDR. Modyfikatory CI i AI rozszerzają je tak, aby traktować znaki jako równoważne, nawet jeśli różnią się wielkością liter, akcentem lub obydwoma. Te sortowania są bardziej dokładne, ale mają wyższy koszt obliczeniowy.

Zachowanie	UTF8_LCASE	UNICODE_CI	UNICODE_CI_AI
'A' = 'a'	true	true	true
'Café' = 'café'	true	true	true
'Cafe' = 'Café'	false	false	true
'ß' = 'ss' (Niemiecki ostry s)	false	false	false
'resume' = 'résumé'	false	false	true
Obsługuje LIKE i RLIKE	Yes	Nie.	Nie.
Obsługuje reguły specyficzne dla ustawień regionalnych	Nie.	Yes	Yes

Kiedy należy używać każdego sortowania

• Służy UTF8_LCASE do szybkiego porównywania bez uwzględniania wielkości liter, gdy znaki wyróżniające powinny pozostać odrębne. To sortowanie jest dobrze dostosowane do migracji magazynu danych z systemów, które używają prostego dopasowywania ciągów bez uwzględniania wielkości liter.

• W UNICODE_CI przypadku porównań bez uwzględniania wielkości liter, które są zgodne z regułami Unicode, szczególnie w przypadku, gdy dane zawierają znaki z wielu języków, w których proste małe litery są niewystarczające.

• Użyj UNICODE_CI_AI polecenia , gdy należy zignorować różnice wielkości liter i akcentów. Jeśli na przykład wyszukiwanie "resume" powinno być zgodne "résumé"z . Jest to często spotykane w aplikacjach wyszukiwania i wielojęzycznych.

Przykłady

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

Sortowanie domyślne

Sortowanie domyślne ma zastosowanie do STRING literałów, znaczników parametrów, funkcji bez parametrów, które generują ciągi, kolumny, pola lub definicje typu zmiennej COLLATE bez STRING klauzuli.

Sortowanie domyślne jest pochodne w następujący sposób:

• W przypadku instrukcji DDL, takich jak ALTER TABLE, CREATE VIEW, CREATE TABLEi CREATE FUNCTION:

  • Sortowanie domyślne to domyślne sortowanie tworzonego lub zmienianego obiektu.
  • Jeśli nie określono klauzuli DEFAULT COLLATION, sortowanie domyślne to UTF8_BINARY.

• W przypadku instrukcji DML (UPDATE, DELETE FROM, , INSERTMERGE INTO) i Query domyślne sortowanie to UTF8_BINARY.

Pierwszeństwo sortowania

Azure Databricks stosuje reguły pierwszeństwa sortowania w celu określenia sortowania używanego dla danego ciągu. Zdefiniowano cztery poziomy pierwszeństwa:

1. Wyraźny

   Sortowanie jest jawnie przypisywane przy użyciu collate wyrażenia.

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

   Sortowanie jest niejawnie przypisywane przez nazwę kolumny, nazwę pola, alias kolumny, nazwę zmiennej lub odwołanie do nazwy parametru , w tym wynik podzapytania, w którym sortowanie nie ma wartości Brak.

   -- 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. Domyślny

   STRING Literał, nazwany lub nienazwany znaczniki parametru lub STRING wygenerowany przez funkcję z innego typu.

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

   Sortowanie przypisane jest sortowaniem domyślnym.

4. Żaden

   Wynik STRING funkcji, operatora lub operacji zestawu, na przykład UNION , który przyjmuje więcej niż jeden STRING argument z różnymi niejawnymi sortowaniami.

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

Tworzenie zasad sortowania

Podczas wyprowadzania sortowania dla STRING wyniku reguły pierwszeństwa mają zastosowanie w następujący sposób:

1. Jeśli wyrażenie pasuje do jednej z powyższych definicji, sortowanie i pierwszeństwo są zdefiniowane.

2. Jeśli wyrażenie jest funkcją lub operatorem z pojedynczym STRING parametrem zwracającym STRINGparametr , sortowanie i pierwszeństwo to te z parametru STRING .

3. Jeśli wyrażenie jest funkcją lub operatorem z co najmniej STRING dwoma parametrami:

   1. Jeśli wszystkie parametry mają takie same sortowanie i pierwszeństwo, wynik używa tego sortowania i pierwszeństwa.

   2. Jeśli parametry mają różne sortowania lub pierwszeństwo, niech C1 i C2 będą odrębnymi sortowaniami i D być sortowaniem domyślnym. Wynik jest określany przez następującą tabelę:

Kolejność i priorytet	C1 Jawne	C1 Niejawny	D domyślne	Żaden
C2 Jawne	Błąd	C2 Jawne	C2 Jawne	C2 Jawne
C2 Niejawne	Jawne C1	Żaden	C2 Niejawne	Żaden
D domyślne	C1 Jawne	C1 Niejawny	D domyślne	Żaden
Żaden	C1 Jawne	Żaden	Żaden	Żaden

Przykłady

> 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

Ograniczenia

Wiele funkcji dopasowywania wzorców i wyrażeń regularnych obsługuje tylko UTF8_BINARY sortowania i UTF8_LCASE sortowania. Azure Databricks zgłasza błąd, gdy te funkcje są używane z innymi sortowaniami, takimi jak UNICODE lub sortowania specyficzne dla ustawień regionalnych, takich jak DE lub FR_CI_AI.

Funkcje, których dotyczy problem, obejmują LIKE, ILIKE, RLIKEi rodzinę regexp_* funkcji. W przypadku dopasowywania ciągów w kolumnach z innymi sortowaniami użyj contains funkcji, startswith funkcji lub endswith funkcji.

Powiązane

• collate wyrażenie
• collation funkcja
• collations funkcja tabeli