Especificar uma coluna de identidade

Este documento descreve como criar e usar colunas de identidade, às vezes chamadas de colunas de incremento automático, que são usadas para criar e manter chaves primárias nas tabelas. Ao inserir uma linha em uma tabela que tem uma coluna de identidade, o BigQuery gera um valor inteiro exclusivo para essa coluna.

Visão geral

Uma coluna de identidade é uma coluna INT64 preenchida com valores exclusivos gerados pelo sistema.

O principal caso de uso para colunas de identidade é gerar chaves primárias. Também é possível gerar chaves primárias usando a GENERATE_UUID função para gerar strings exclusivas, mas as colunas de identidade geralmente são preferidas pelos seguintes motivos:

  • Os valores inteiros exigem menos espaço de armazenamento do que os valores de string.
  • O uso de números inteiros para junções de tabelas é mais eficiente do que o uso de strings.

Os valores de uma coluna de identidade são gerados com base em um valor inicial que define o primeiro valor e um valor de incremento que define a diferença mínima entre os valores gerados sucessivamente.

Os valores da coluna de identidade gerada têm as seguintes propriedades:

  • Exclusivo. Os valores gerados automaticamente são exclusivos na tabela.
  • Parcialmente ordenado. Não há garantia de que os valores gerados estejam em ordem estritamente crescente ou decrescente.
  • Esparso. Não há garantia de que os valores gerados sejam consecutivos. Alguns valores podem ser ignorados, mas os valores em uma coluna de identidade sempre diferem por um múltiplo do incremento especificado.

Limitações

  • Uma tabela pode ter no máximo uma coluna de identidade.
  • É possível ler tabelas com colunas de identidade usando o SQL legado, mas não é possível gravar em tabelas com colunas de identidade usando o SQL legado.
  • Não é possível usar o clustering ou o particionamento em uma coluna de identidade.

  • As seguintes operações de cópia de tabela não são compatíveis se uma tabela de origem ou de destino tiver uma coluna de identidade:

    • Cópia de tabela com disposição de gravação WRITE_APPEND ou WRITE_TRUNCATE
    • Cópia de tabela de várias origens

  • A transmissão de dados usando a API Storage Write (gRPC) ou o tabledata.insertAll método da API não é compatível com tabelas com colunas de identidade.

Criar colunas de identidade

É possível criar uma coluna de identidade ao criar uma nova tabela usando a CREATE TABLE instrução DDL. Use a cláusula GENERATED AS IDENTITY para designar uma coluna INT64 como uma coluna de identidade. Uma tabela pode ter no máximo uma coluna de identidade. É possível especificar um dos seguintes modos de geração que determina se é possível inserir valores manualmente na coluna de identidade:

  • GENERATED ALWAYS AS IDENTITY: os valores são sempre gerados pelo sistema. Não é possível fornecer seu próprio valor ao inserir ou atualizar dados nessa coluna. Se você não especificar ALWAYS ou BY DEFAULT, ALWAYS será usado.

  • GENERATED BY DEFAULT AS IDENTITY: é possível inserir ou modificar valores na coluna de identidade. O BigQuery não aplica a exclusividade dos valores inseridos ou modificados.

    Se você omitir a coluna ou fornecer NULL ao inserir dados, o BigQuery vai gerar um valor automaticamente. A coluna de identidade não pode conter um valor NULL. Se você quiser usar um valor gerado em uma instrução INSERT, MERGE ou UPDATE, use a palavra-chave DEFAULT ou NULL.

O exemplo a seguir cria a tabela mydataset.id_table com uma coluna de identidade id que começa em 0 e incrementa em 5:

CREATE TABLE mydataset.id_table (
  id INT64 GENERATED ALWAYS AS IDENTITY(START WITH 0 INCREMENT BY 5),
  data STRING
);

Adicionar a propriedade da coluna de identidade a uma coluna

Para modificar uma coluna atual para gerar valores de identidade, use a ALTER TABLE ALTER COLUMN SET GENERATED instrução DDL. Essa instrução muda uma coluna INT64 atual para uma coluna de identidade. Ela não preenche valores para linhas atuais na coluna de identidade.

Usar instruções DML com colunas de identidade

É possível usar instruções DML, como INSERT, MERGE e UPDATE, com colunas de identidade. As seções a seguir usam a tabela mydataset.mytable, que tem uma coluna de identidade chamada id e uma coluna de string chamada data:

CREATE OR REPLACE TABLE mydataset.mytable (
  id INT64 GENERATED BY DEFAULT AS IDENTITY(START WITH 100 INCREMENT BY 10),
  data STRING
);

Inserir dados

Ao inserir dados em uma tabela com uma coluna de identidade, é possível omitir a coluna de identidade da lista de colunas para gerar um valor para ela. A instrução INSERT a seguir omite a coluna id. O BigQuery gera valores para ela:

INSERT mydataset.mytable (data) VALUES ('A'), ('B'), ('C');

O resultado é semelhante ao seguinte, embora a ordem de atribuição de valores gerados às linhas possa variar:

+-----+------+
| id  | data |
+-----+------+
| 110 | A    |
| 120 | B    |
| 100 | C    |
+-----+------+

Se uma coluna de identidade for definida com GENERATED BY DEFAULT AS IDENTITY, será possível especificar seu próprio valor para a coluna. Também é possível usar a palavra-chave DEFAULT ou NULL para que o BigQuery gere um valor.

A instrução INSERT a seguir fornece um valor para uma linha e usa DEFAULT ou NULL para gerar valores para as outras duas linhas:

INSERT mydataset.mytable (id, data)
VALUES (155, 'D'), (DEFAULT, 'E'), (NULL, 'F');

O resultado será semelhante ao seguinte:

+-----+------+
| id  | data |
+-----+------+
| 110 | A    |
| 120 | B    |
| 100 | C    |
| 155 | D    |
| 140 | E    |
| 130 | F    |
+-----+------+

Se uma coluna de identidade for definida com GENERATED ALWAYS AS IDENTITY, só será possível usar a palavra-chave DEFAULT para que o BigQuery gere um valor. Não é possível fornecer seu próprio valor ou usar NULL.

Mesclar dados

É possível usar a MERGE instrução para mesclar dados em uma tabela com uma coluna de identidade. Se a coluna de identidade usar o modo de geração GENERATED BY DEFAULT AS IDENTITY, será possível usar as palavras-chave DEFAULT ou NULL para gerar um valor ao inserir ou atualizar dados como parte de uma instrução MERGE.

O exemplo a seguir mescla mydataset.source_table em mydataset.mytable, inserindo uma nova linha se não houver correspondência na coluna data e atualizando a coluna id para um novo valor gerado se houver uma correspondência:

CREATE OR REPLACE TABLE mydataset.source_table(data STRING)
AS SELECT * FROM UNNEST(['A', 'C', 'G']);

MERGE mydataset.mytable T
USING mydataset.source_table S
ON T.data = S.data
WHEN MATCHED THEN
  UPDATE SET id = DEFAULT
WHEN NOT MATCHED THEN
  INSERT(data)
  VALUES(S.data);

O resultado será semelhante ao seguinte:

+-----+------+
| id  | data |
+-----+------+
| 160 | A    |
| 120 | B    |
| 150 | C    |
| 155 | D    |
| 140 | E    |
| 130 | F    |
| 170 | G    |
+-----+------+

Se a coluna de identidade usar o modo de geração GENERATED ALWAYS AS IDENTITY, não será possível incluir a coluna de identidade em nenhuma cláusula de atualização de mesclagem. Para usar uma cláusula de inserção de mesclagem, é possível omitir a coluna de identidade da lista de colunas ou usar a palavra-chave DEFAULT.

Atualizar dados

É possível usar a UPDATE instrução para atualizar valores em uma coluna de identidade que usa o GENERATED BY DEFAULT AS IDENTITY modo de geração. É possível usar as palavras-chave DEFAULT ou NULL para gerar um novo valor.

O exemplo a seguir atualiza todos os valores na coluna id para valores recém-gerados:

UPDATE mydataset.mytable
SET id = NULL
WHERE TRUE;

O resultado será semelhante ao seguinte:

+-----+------+
| id  | data |
+-----+------+
| 190 | A    |
| 210 | B    |
| 240 | C    |
| 230 | D    |
| 180 | E    |
| 200 | F    |
| 220 | G    |
+-----+------+

Se a coluna de identidade usar o modo de geração GENERATED ALWAYS AS IDENTITY, não será possível atualizar a coluna de identidade.

Anexar a uma tabela

É possível usar o comando bq query com a flag --append_table para anexar os resultados de uma consulta a uma tabela de destino que tenha uma coluna de identidade. Se a consulta omitir a coluna de identidade, um valor será gerado para ela.

O exemplo a seguir anexa dados apenas para a coluna data a mydataset.mytable:

bq query \
    --nouse_legacy_sql \
    --append_table \
    --destination_table=mydataset.mytable \
    'SELECT "H" AS data'

Uma nova linha com um valor id gerado é adicionada a mydataset.mytable.

Carregar dados

É possível carregar dados em uma tabela com uma coluna de identidade usando o bq load comando ou a LOAD DATA instrução. Se a coluna de identidade for omitida dos dados de origem ou do esquema, os valores serão gerados para ela. Se a coluna de identidade for GENERATED ALWAYS AS IDENTITY, ela precisará ser omitida.

O exemplo a seguir carrega dados de um arquivo CSV data.csv em mydataset.mytable. O arquivo contém apenas dados para a coluna data:

"X"
"Y"

O comando bq load a seguir carrega data.csv em mydataset.mytable, omitindo a linha de cabeçalho e especificando apenas a coluna data no esquema:

bq load --source_format=CSV --skip_leading_rows=0 \
mydataset.mytable data.csv data:STRING

O job de carregamento gera valores id para as novas linhas.

Remover a propriedade da coluna de identidade

É possível remover a propriedade de identidade de uma coluna usando a ALTER TABLE ALTER COLUMN DROP GENERATED instrução DDL.

O exemplo a seguir remove as propriedades da coluna de identidade da coluna id em mydataset.mytable:

ALTER TABLE mydataset.mytable
ALTER COLUMN id DROP GENERATED;

Conferir informações sobre colunas de identidade

Para conferir a configuração da coluna de identidade de uma coluna, consulte a INFORMATION_SCHEMA.COLUMNS visualização.

O exemplo a seguir mostra informações da coluna de identidade para colunas em mydataset.mytable:

SELECT
  column_name,
  is_identity,
  identity_generation,
  identity_start,
  identity_increment
FROM
  mydataset.INFORMATION_SCHEMA.COLUMNS
WHERE
  table_name = 'mytable';

O resultado será semelhante ao seguinte:

+-------------+-------------+---------------------+----------------+--------------------+
| column_name | is_identity | identity_generation | identity_start | identity_increment |
+-------------+-------------+---------------------+----------------+--------------------+
| id          | YES         | BY DEFAULT          | 100            | 10                 |
| data        | NO          | NULL                | NULL           | NULL               |
+-------------+-------------+---------------------+----------------+--------------------+

Como alternativa, é possível consultar a coluna ddl da visualização INFORMATION_SCHEMA.TABLES para conferir a definição da coluna de identidade na instrução DDL CREATE TABLE de uma tabela.

A seguir