Como trabalhar com a compilação

Sobre a compilação

A compilação determina como as strings são ordenadas e comparadas em operações compatíveis com compilação. Se você quiser personalizar a compilação para uma operação compatível com compilação, será necessário atribuir uma especificação de compilação a pelo menos uma string na operação. Algumas operações não podem usar compilação, mas podem transmiti-la.

Operações afetadas pela compilação

Quando uma operação é afetada pela compilação, isso significa que ela considera a compilação durante a operação. Essas operações de consulta são afetadas pela compilação ao ordenar e comparar strings:

Operações Notas
Operações de comparação compatíveis com compilação
Operações de mesclagem
ORDER BY Na cláusula WINDOW, ORDER BY não é compatível com compilação.
GROUP BY
WINDOW para funções de janela Na cláusula WINDOW, ORDER BY e PARTITION BY não são compatíveis com compilação.
Funções escalares compatíveis com compilação
Funções agregadas compatíveis com compilação
Operações de configuração

Operações que propagam a compilação

A compilação pode transmitir algumas operações de consulta para outras partes de uma consulta. Quando a compilação é transmitida por uma operação em uma consulta, isso é conhecido como propagação. Durante a propagação:

  • Se uma entrada não tiver uma especificação de compilação ou uma especificação de compilação vazia e outra entrada tiver uma compilação explicitamente definida, a compilação explicitamente definida será usada para todas as entradas.
  • Todas as entradas com uma especificação de compilação definida explicitamente não vazia precisam ter o mesmo tipo de especificação de compilação. Caso contrário, um erro será gerado.

O BigQuery tem várias funções, operadores e expressões que podem propagar a compilação.

No exemplo a seguir, a especificação de compilação 'und:ci' é propagada da coluna character para a operação ORDER BY.

-- With collation
SELECT *
FROM UNNEST([
  COLLATE('B', 'und:ci'),
  'b',
  'a'
]) AS character
ORDER BY character

+-----------+
| character |
+-----------+
| a         |
| B         |
| b         |
+-----------+
-- Without collation
SELECT *
FROM UNNEST([
  'B',
  'b',
  'a'
]) AS character
ORDER BY character

+-----------+
| character |
+-----------+
| B         |
| a         |
| b         |
+-----------+

Functions

Essas funções permitem que a compilação seja propagada por elas:

Função Notas
AEAD.DECRYPT_STRING
ANY_VALUE
ARRAY_AGG A compilação em argumentos de entrada é propagada como compilação no elemento de matriz.
ARRAY_TO_STRING A compilação em elementos de matriz é propagada para a saída.
COLLATE
CONCAT
FORMAT A compilação de format_string para a string retornada é propagada.
FORMAT_DATE A compilação de format_string para a string retornada é propagada.
FORMAT_DATETIME A compilação de format_string para a string retornada é propagada.
FORMAT_TIME A compilação de format_string para a string retornada é propagada.
FORMAT_TIMESTAMP A compilação de format_string para a string retornada é propagada.
GREATEST
LAG
LEAD
LEAST
LEFT
LOWER
LPAD
MAX
MIN
NET.HOST
NET.PUBLIC_SUFFIX
NET.REG_DOMAIN
NORMALIZE
NORMALIZE_AND_CASEFOLD
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
SOUNDEX
SPLIT A compilação em argumentos de entrada é propagada como compilação no elemento de matriz.
STRING_AGG
SUBSTR
UPPER

Operadores

Esses operadores permitem que a compilação seja propagada por eles:

Operador Notas
Operador de concatenação ||
Operador de subscrito da matriz Propagado para a saída.
Operadores de conjunto A compilação de uma coluna de saída é decidida pelas compilações de colunas de entrada na mesma posição.
Operador de acesso ao campo STRUCT Ao receber um STRUCT, a compilação no campo STRUCT é propagada como a compilação de saída.
UNNEST A compilação no elemento de matriz de entrada é propagada para a saída.

Expressões

Essas expressões permitem que a compilação seja propagada por elas:

Expressão Notas
ARRAY Quando você cria um ARRAY, a compilação em argumentos de entrada é propagada nos elementos do ARRAY.
CASE
Expr CASE
COALESCE
IF
IFNULL
NULLIF
STRUCT Quando você cria um STRUCT, a compilação em argumentos de entrada é propagada nos campos do STRUCT.

Onde é possível atribuir uma especificação de agrupamento

É possível atribuir uma especificação de compilação a estes tipos compatíveis com compilação:

  • Um STRING
  • Um campo STRING em um STRUCT
  • Um elemento STRING em um ARRAY

Além disso:

  • É possível atribuir uma especificação de compilação padrão a um esquema ao criá-lo ou alterá-lo. Isso atribuirá uma especificação de compilação padrão a todas as tabelas futuras que serão adicionadas ao esquema se as tabelas não tiverem suas próprias especificações de compilação padrão.
  • É possível atribuir uma especificação de compilação padrão a uma tabela ao criá-la ou alterá-la. Isso atribuirá uma especificação de compilação a todas as colunas futuras compatíveis com compilação que serão adicionadas à tabela se as colunas não tiverem especificações de compilação. Isso substitui uma especificação de compilação padrão em um esquema.
  • É possível atribuir uma especificação de compilação a um tipo compatível com compilação em uma coluna. Uma coluna que contém um tipo compatível com compilação no esquema de coluna é uma coluna compatível com compilação. Isso substitui uma especificação de compilação padrão em uma tabela.
  • É possível atribuir uma especificação de compilação a uma operação de consulta compatível com compilação.
  • É possível atribuir uma especificação de compilação a uma expressão compatível com compilação com a função COLLATE. Isso substitui qualquer especificação de compilação definida anteriormente.

Em resumo:

É possível definir uma especificação de compilação padrão para um esquema. Exemplo:

CREATE SCHEMA (...)
DEFAULT COLLATE 'und:ci'

É possível definir uma especificação de compilação padrão para uma tabela. Exemplo:

CREATE TABLE (...)
DEFAULT COLLATE 'und:ci'

É possível definir uma especificação de compilação para uma coluna compatível com compilação. Exemplo:

CREATE TABLE (
  case_insensitive_column STRING COLLATE 'und:ci'
)

É possível especificar uma especificação de compilação para uma expressão compatível com compilação com a função COLLATE. Exemplo:

SELECT COLLATE('a', 'und:ci') AS character

Instruções DDL

Local Suporte Observações
Schema CREATE SCHEMA Crie um esquema e, se quiser, adicione uma especificação de compilação padrão ao esquema.
Schema ALTER SCHEMA Atualiza a especificação de compilação padrão de um esquema.
Tabela CREATE TABLE Crie uma tabela e, se quiser, adicione uma especificação de compilação padrão a uma tabela ou uma especificação de compilação a um tipo compatível com compilação em uma coluna.

Não é possível ter a compilação em uma coluna usada com CLUSTERING.

É possível usar funções compatíveis com compilação com AS SELECT ao criar uma tabela.
Tabela ALTER TABLE Atualize a especificação de compilação padrão para o tipo compatível com compilação em uma tabela.
Colunas ADD COLUMN Adicione uma especificação de compilação a um tipo compatível com compilação em uma nova coluna em uma tabela existente.
Colunas ALTER COLUMN Atualize a especificação da compilação para um tipo compatível com compilação em uma coluna de tabela.

Tipos de dados

Tipo Suporte Notas
Tipo de dado STRING É possível aplicar uma especificação de compilação diretamente a esse tipo de dados.
Tipo de dado STRUCT É possível aplicar uma especificação de compilação a um campo STRING em um STRUCT. Uma STRUCT pode ter campos STRING com especificações de compilação diferentes.
Tipo de dado ARRAY É possível aplicar uma especificação de compilação a um elemento STRING em um ARRAY. Uma ARRAY pode ter elementos STRING com especificações de compilação diferentes.

Use a função COLLATE para aplicar uma especificação de compilação a expressões compatíveis com compilação.

Funções, operadores e expressões condicionais

Functions

Tipo Suporte Notas
Escalar COLLATE
Escalar ENDS_WITH
Escalar GREATEST
Escalar INSTR
Escalar LEAST
Escalar REPLACE
Escalar SPLIT
Escalar STARTS_WITH
Escalar STRPOS
Agregar COUNT Esse operador só é afetado pela compilação quando a entrada inclui o argumento DISTINCT.
Agregar MAX
Agregar MIN

Operadores

Suporte Notas
<
<=
>
>=
=
!=
[NOT] BETWEEN
[NOT] IN Esse operador geralmente é compatível com compilação, mas [NOT] IN UNNEST não é compatível. Se usado com uma lista, requer pelo menos um item na lista.
[NOT] IN

Expressões condicionais

Suporte
CASE
Expr CASE
NULLIF

As operações anteriores compatíveis com compilação (funções, operadores e expressões condicionais) podem incluir entradas com especificações de compilação definidas explicitamente para tipos compatíveis com compilação. Em uma operação compatível com compilação:

  • Todas as entradas com uma especificação de compilação explicitamente definida e não vazia precisam ser iguais. Caso contrário, um erro será gerado.
  • Se uma entrada não contiver uma compilação explicitamente definida e outra entrada contiver uma compilação explicitamente definida, a compilação explicitamente definida será usada para ambas.

Exemplo:

-- Assume there is a table with this column declaration:
CREATE TABLE table_a
(
    col_a STRING COLLATE 'und:ci',
    col_b STRING COLLATE '',
    col_c STRING,
    col_d STRING COLLATE 'und:ci'
);

-- This runs. Column 'b' has a collation specification and the
-- column 'c' does not.
SELECT STARTS_WITH(col_b_expression, col_c_expression)
FROM table_a;

-- This runs. Column 'a' and 'd' have the same collation specification.
SELECT STARTS_WITH(col_a_expression, col_d_expression)
FROM table_a;

-- This runs. Even though column 'a' and 'b' have different
-- collation specifications, column 'b' is considered the default collation
-- because it's assigned to an empty collation specification.
SELECT STARTS_WITH(col_a_expression, col_b_expression)
FROM table_a;

-- This works. Even though column 'a' and 'b' have different
-- collation specifications, column 'b' is updated to use the same
-- collation specification as column 'a'.
SELECT STARTS_WITH(col_a_expression, COLLATE(col_b_expression, 'und:ci'))
FROM table_a;

-- This runs. Column 'c' does not have a collation specification, so it uses the
-- collation specification of column 'd'.
SELECT STARTS_WITH(col_c_expression, col_d_expression)
FROM table_a;

Detalhes da especificação de compilação

Uma especificação de compilação determina como as strings são classificadas e comparadas nas operações compatíveis com compilação. É possível definir a especificação de compilação Unicode, und:ci, para tipos compatíveis com compilação.

Se uma especificação de compilação não for definida, a especificação padrão será usada. Para saber mais, consulte as próximas seções.

Especificação de compilação padrão

Quando uma especificação de compilação não é atribuída ou está vazia, a compilação 'binary' é usada. A compilação binária indica que a operação precisa retornar dados em ordem de ponto de código Unicode. Não é possível definir a compilação binária explicitamente.

Especificação de compilação de Unicode

collation_specification:
  'language_tag:collation_attribute'

Uma especificação de compilação unicode indica que a operação precisa usar o algoritmo de compilação Unicode para classificar e comparar strings. A especificação de compilação pode ser um literal STRING ou um parâmetro de consulta.

Tag de idioma

A tag de idioma determina como as strings geralmente são classificadas e comparadas. Os valores permitidos para language_tag são:

  • und: uma string de localidade que representa a localidade indeterminada. und é uma tag de idioma especial definida no registro de subtag de idioma da IANA e usada para indicar uma localidade indeterminada. Isso também é conhecido como a localidade root e pode ser considerado o agrupamento Unicode padrão. Ele define um agrupamento agnóstico razoável de localidade.

O atributo de compilação

Além da tag de idioma, a especificação de compilação Unicode precisa ter um collation_attribute, que permite outras regras para ordenar e comparar strings. Os valores permitidos são:

  • ci: a compilação não diferencia maiúsculas de minúsculas.

Exemplo de especificação de compilação

O atributo de compilação ci fica assim quando usado com a tag de idioma und na função COLLATE:

COLLATE('orange1', 'und:ci')

Advertências

  • Strings diferentes podem ser consideradas iguais. Por exemplo, (LATIN CAPITAL LETTER SHARP S) é considerado igual a 'SS' no nível principal. Portanto, 'ẞ1' < 'SS2'. Isso é semelhante à diferenciação entre maiúsculas e minúsculas.
  • Há uma ampla variedade de pontos de código Unicode (pontuação, símbolos etc.) que são tratados como se não estivessem presentes. As strings com e sem elas são classificadas de maneira idêntica. Por exemplo, o ponto de código de controle de formato U+2060 é ignorado quando as seguintes strings são classificadas:

    SELECT *
    FROM UNNEST([
      COLLATE('oran\u2060ge1', 'und:ci'),
      COLLATE('\u2060orange2', 'und:ci'),
      COLLATE('orange3', 'und:ci')
    ]) AS fruit
    ORDER BY fruit
    
    +---------+
    | fruit   |
    +---------+
    | orange1 |
    | orange2 |
    | orange3 |
    +---------+
    
  • Pedidos podem mudar. A especificação Unicode do agrupamento und pode mudar ocasionalmente, o que pode afetar a ordem de classificação.

Limitações

As limitações dos recursos compatíveis são abordadas nas seções anteriores, mas veja a seguir algumas limitações gerais:

  • und:ci e a compilação vazia são compatíveis, mas não outras especificações de compilação.
  • As operações e funções que não são compatíveis com compilação produzirão um erro se encontrarem valores compilados.
  • Não é possível definir a compilação não vazia em um campo de clustering usando uma chamada de API ou uma consulta DDL.

    CREATE TABLE mydataset.mytable
    (
      word STRING COLLATE 'und:ci',
      number INT64
    )
    CLUSTER BY word;
    
    -- User error:
    -- “CLUSTER BY STRING column word with
    -- collation und:ci is not supported"
    
  • Uma visualização materializada com colunas de saída compiladas não é compatível.

    CREATE MATERIALIZED VIEW bigquery.public.data.samples.shakespeare
    AS SELECT COLLATE('abc', 'und:ci') x;
    
    -- User error:
    -- “Creating MATERIALIZED VIEW with collation
    -- on the output column is not supported”
    
  • Uma visualização com colunas de saída compiladas não é compatível.

    CREATE VIEW bigquery.public.data.samples.shakespeare
    AS SELECT COLLATE('abc', 'und:ci') x;
    
    -- User error:
    -- "Creating VIEW with collation on the output column
    -- is not supported"
    
  • Depois que uma visualização ou visualização materializada é criada e uma compilação de colunas da tabela base é alterada para compilar a coluna, essa visualização ou visualização materializada é invalidada e não pode ser consultada.

    CREATE TABLE dataset.base (
      col_no_collation STRING,
      col INT64
    )
    AS SELECT 'a', 1
    UNION ALL SELECT 'b', 2
    UNION ALL SELECT 'A', 3
    UNION ALL SELECT 'B', 4;
    
    CREATE VIEW/MATERIALIZED VIEW dataset.view AS SELECT * FROM dataset.base;
    
    -- Change the collation of string column of the base table so
    -- that the string column of view/materialized view is collated.
    ALTER TABLE dataset.base ALTER COLUMN col_no_collation SET DATA TYPE STRING COLLATE 'und:ci';
    
    -- Should return an error since the column of view/materialized
    -- contains collation.
    SELECT * FROM dataset.view ORDER BY col;
    
    -- User error:
    -- "Collation on the output column for view dataset.view is
    -- not supported"
    
  • Não é possível criar uma visualização materializada com chaves de ordenação compiladas em uma função agregada.

    CREATE TABLE dataset.base (
      col_no_collation STRING,
      col INT64
    )
    AS SELECT 'a', 1
    UNION ALL SELECT 'b', 2
    UNION ALL SELECT 'A', 3
    UNION ALL SELECT 'B', 4;
    
    CREATE VIEW/MATERIALIZED VIEW dataset.view AS SELECT * FROM dataset.base;
    
    -- Change the collation of string column of the base table so
    -- that the string column of view/materialized view is
    -- collated.
    ALTER TABLE dataset.base ALTER COLUMN col_no_collation SET DATA TYPE STRING COLLATE 'und:ci';
    
    -- Should return an error since the column of view/materialized
    -- contains collation.
    SELECT * FROM dataset.view ORDER BY col;
    
    -- User error:
    -- "Collation on the output column for view dataset.view is
    -- not supported"
    
  • Uma função de tabela com colunas de saída compiladas não é compatível.

    CREATE TABLE FUNCTION bigquery.public.data.samples.shakespeare()
    AS SELECT COLLATE('abc', 'und:ci') x;
    
    -- User error:
    -- "Creating TABLE FUNCTION with collation on the output
    -- column is not supported"
    
  • Funções definidas pelo usuário (UDFs, na sigla em inglês) não podem aceitar argumentos compilados.

    CREATE FUNCTION tmp_dataset.f(x STRING) AS (x);
    
    SELECT tmp_dataset.f(col_ci)
    FROM shared_dataset.table_collation_simple
    
    -- User error:
    -- “Collation is not allowed on argument x ("und:ci").
    -- Use COLLATE(arg, '') to remove collation at [1:8]”
    
  • Se uma consulta SELECT com uma tabela de destino especificada pelo usuário produzir colunas de saída com compilação, um erro será produzido.

    -- The following query returns an error since the resulting schema of
    -- the query has a column string_ci with collation ‘und:ci’.
    SELECT string_ci FROM collated_table
    
  • O BigQuery BI Engine não é compatível com tabelas com compilação ou a função COLLATE.

  • A viagem no tempo no BigQuery não é compatível com tabelas com compilação.