Colação

Aplica-se a: Databricks SQL Databricks Runtime 16.1 e superior

Um agrupamento é um conjunto de regras que determina como as comparações de cadeia de caracteres são executadas. As colações suportam comparações insensíveis a maiúsculas minúsculas, insensíveis ao acento e insensíveis ao espaço final, bem como ordenação de cadeias consciente da linguagem.

As cadeias em Azure Databricks são representadas como UTF-8 codificadas Unicode. Por defeito, Azure Databricks compara as cadeias pela sua representação binária UTF-8, conhecida como colação UTF8_BINARY. UTF8_BINARY As comparações são rápidas e apropriadas em muitos casos, mas podem não ser adequadas para aplicações que requerem ordenação ou comparações conscientes da linguagem.

Para além das comparações binárias, um caso de uso comum é a correspondência insensível a maiúsculas e minúsculas. A UTF8_LCASE colação foi concebida para este propósito. Converte as cordas para minúsculas antes de as comparar usando UTF8_BINARY.

Para comparações conscientes da língua, o Azure Databricks utiliza as seguintes tecnologias:

• International Components for Unicode (ICU) para calcular a colação.
• Tabelas do Repositório de Dados Comuns de Localidade (CLDR) para colação consciente da localidade.
• Unicode Locale Data Markup Language (LDML) para codificar ordenações internamente.

Estas tecnologias estão encapsuladas num conjunto de colações nomeadas disponíveis para uso em instruções SQL.

Nomes de agrupamento

O Azure Databricks fornece colações de sistemas nomeados para simplificar a identificação. As especificações LDML podem ser complexas de ler e usar diretamente.

Sintaxe

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

locale
  language_code [ _ script_code ] [ _ country_code ]

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

• UTF8_BINARY

  Uma colação binária que compara strings byte a byte com base na sua representação UTF-8. UTF8_BINARY é a colação padrão e mais leve no Azure Databricks.

  Nesta colação: 'A' (x'65') <'B' (x'66') < ... < 'Z' (x'90'). No entanto, 'Z' (x'90') <'a' (x'97') e 'A' (x'65') <>'a' (x'97'). Caracteres como 'Ä' (x'C384') são maiores que ambos 'Z' e 'z'.

• UTF8_LCASE

  Uma colação leve, insensível a maiúsculas minúsculas, que converte as cadeias em minúsculas antes de as comparar usando UTF8_BINARY.

  UTF8_LCASE é a colação usada para Identificadores em Azure Databricks.

  Por exemplo:

  ORDER BY col COLLATE UTF8_LCASE
  

  é equivalente a:

  ORDER BY LOWER(col) COLLATE UTF8_BINARY
  

• UNICODE

  O local raiz da ICU, conhecido em CLDR como local root (especificação LDML: und-u). Esta colação aplica uma ordem independente da língua que agrupa caracteres semelhantes. Por exemplo: 'a''Ä'<'b''A'<<. Esta colação é sensível a maiúsculas minúsculas e minúsculas e acentual por defeito.

• locale

  Uma colação consciente da localização baseada em tabelas CLDR. A localidade é especificada como um código de idioma, um código de script opcional e um código de país opcional. Os valores locais são indiferentes a maiúsculas e maiúsculas.

  • language_code: Um código de língua ISO 639-1 de duas letras.
  • script_code: Um código de escrita ISO 15924 de quatro letras.
  • country_code: Um código de país ISO 3166-1 alfa-3 de três letras.

• modifier

  Controla a sensibilidade ao maiúsculo, sensibilidade ao acento e ao comportamento do espaço final. Os modificadores são insensíveis a maiúsculas e minúsculas e podem ser especificados em qualquer ordem.

  • CS: Sensível de maiúsculas e minúsculas. O comportamento padrão.
  • CI: Indistinto a maiúsculas e minúsculas.
  • AS: Sensível ao sotaque. O comportamento padrão.
  • AI: Insensível ao sotaque.

  Aplica-se a: Databricks SQL Databricks Runtime 16.2 e superior

  • RTRIM: Insensível ao espaço final. Corta os espaços finais (u0020) antes de comparar.

  Aplica-se a: Databricks SQL Databricks Runtime 16.2 e superior

  Pode especificar RTRIM, no máximo um de CS ou CI, e no máximo um de AS ou AI.

Quando o Azure Databricks processa um nome de colação, normaliza o nome removendo os predefinidos. Por exemplo, SR_CYR_SRN_CS_AS normaliza para SR.

Para obter uma lista de agrupamentos suportados, consulte Agrupamentos suportados.

Exemplos

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

Comparação UTF8_LCASE e colações UNICODE

Tanto UTF8_LCASE as colações baseadas em UNICODE, como UNICODE_CI e UNICODE_CI_AI suportam comparações insensíveis a maiúsculas e minúsculas, mas diferem na forma como lidam com caracteres acentuados e regras específicas da localização.

Diferenças comportamentais

UTF8_LCASE converte as cadeias para minúsculas e compara-as byte a byte usando UTF8_BINARY. É leve e rápido, mas trata as personagens com acento como distintas das suas formas sem acento.

As colações baseadas em UNICODE utilizam a biblioteca da ICU e os dados locais CLDR. Os CI modificadores e AI estendem isto para tratar os caracteres como equivalentes mesmo quando diferem em maiúscula, sotaque ou ambos. Estas colações são mais completas, mas têm um custo computacional mais elevado.

Quando usar cada colação

• Use UTF8_LCASE para comparações rápidas insensíveis a maiúsculas minúsculas quando caracteres com acentuação devem manter-se distintos. Esta colação é bem adequada para migrações de data warehouse a partir de sistemas que utilizam simples correspondência de cadeias insensíveis a maiúsculas e maiúsculas.

• Use UNICODE_CI para comparações insensíveis a maiúsculas e minúsculas que seguem regras Unicode, especialmente quando os dados incluem caracteres de várias línguas onde o simples minúsculo é insuficiente.

• Use UNICODE_CI_AI quando as diferenças tanto de caso como de sotaque devem ser ignoradas. Por exemplo, se uma pesquisa por "resume" deve corresponder "résumé"a . Isto é comum em aplicações de pesquisa orientadas para utilizadores e multilíngues.

Exemplos

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

Ordenação padrão

A colação por defeito aplica-se a STRING literais, marcadores de parâmetro, funções sem STRING parâmetros que produzem cadeias, e definições de tipo coluna, campo ou variável sem uma COLLATE cláusula.

A colação padrão é derivada da seguinte forma:

• Para instruções DDL como ALTER TABLE, CREATE VIEW, CREATE TABLE, e CREATE FUNCTION:

  • O agrupamento padrão é o agrupamento padrão do objeto que está sendo criado ou alterado.
  • Se nenhuma cláusula DEFAULT COLLATION for especificada, o agrupamento padrão será UTF8_BINARY.

• Para as instruções DML (UPDATE, DELETE FROM, INSERT, MERGE INTO) e Consulta, a colação padrão é UTF8_BINARY.

Precedência do agrupamento

O Azure Databricks aplica regras de precedência de colação para determinar qual a colação a usar para uma determinada cadeia. São definidos quatro níveis de precedência:

1. explícito

   A colação é explicitamente atribuída usando collate expressão.

   -- 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. implícito

   A colação é implicitamente atribuída por um nome de coluna, nome de campo, alias de coluna, nome de variável ou referência de nome de parâmetro , incluindo o resultado de uma subconsulta onde a colação não é nenhuma.

   -- 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. Padrão

   Um STRINGmarcador de parâmetro literal, nomeado ou não, ou STRING produzido por uma função de outro tipo.

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

   A colação atribuída é a colação padrão.

4. Nenhum

   Um STRING resultado de uma função, operador ou operação de conjuntos como UNION essa requer mais do que um STRING argumento com diferentes colações implícitas.

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

Derivação de agrupamento

Ao derivar a colação para um STRING resultado, aplicam-se regras de precedência da seguinte forma:

1. Se a expressão corresponder a uma das definições acima, a colação e a precedência são as definidas.

2. Se a expressão for uma função ou operador com um único STRING parâmetro que devolve a STRING, a colação e a precedência são as do STRING parâmetro.

3. Se a expressão for uma função ou operador com dois ou mais STRING parâmetros:

   1. Se todos os parâmetros tiverem a mesma colação e precedência, o resultado usa essa colação e precedência.

   2. Se os parâmetros tiverem colações ou precedência diferentes, sejam C1 e C2 colações distintas e D sejam a colação padrão. O resultado é determinado pela tabela seguinte:

Exemplos

> 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

Limitações

Muitas funções de correspondência de padrões e regex suportam apenas UTF8_BINARY e UTF8_LCASE colações. Azure Databricks gera um erro quando estas funções são usadas com outras colações, como UNICODE ou colações específicas da localidade como DE ou FR_CI_AI.

As funções afetadas incluem LIKE, ILIKE, RLIKE, e a regexp_* família de funciones. Para correspondência de cadeias em colunas com outras colações, use contains função, startswith função ou endswith função.

Relacionados

• collate expressão
• collation função
• collations função de tabela