Usar chaves primárias e externas

As chaves primárias e externas são restrições de tabela que podem ajudar na otimização de consultas. Este documento explica como criar, visualizar e gerenciar restrições e usá-las para otimizar suas consultas.

O BigQuery oferece suporte às seguintes restrições de chave:

  • Chave primária: uma chave primária de uma tabela é uma combinação de uma ou mais colunas que é exclusiva para cada linha e não é NULL.
  • Chave externa: uma chave externa de uma tabela é uma combinação de uma ou mais colunas que está presente na coluna de chave primária de uma tabela referenciada ou é NULL.

As chaves primárias e externas são normalmente usadas para garantir a integridade de dados e permitir a otimização de consultas. O BigQuery não aplica restrições de chave primária e externa. Ao declarar restrições nas tabelas, você precisa garantir que os dados estejam em conformidade com elas. Para garantir a exclusividade dos valores em uma coluna, use uma coluna de identidade. O BigQuery pode usar restrições de tabela para otimizar suas consultas.

Gerenciar restrições

As relações de chave primária e externa podem ser criadas e gerenciadas pelas seguintes instruções DDL:

Também é possível gerenciar restrições de tabela pela API BigQuery atualizando o TableConstraints objeto.

Acessar restrições

As visualizações a seguir fornecem informações sobre as restrições de tabela:

  • A visualização INFORMATION_SCHEMA.TABLE_CONSTRAINTS contém informações sobre todas as restrições de chave primária e chave externa em tabelas dentro de um conjunto de dados.
  • A visualização INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE contém informações sobre as colunas de chave primária de cada tabela e as colunas referenciadas por chaves externas de outras tabelas em um conjunto de dados.
  • A visualização INFORMATION_SCHEMA.KEY_COLUMN_USAGE contém informações sobre as colunas de cada tabela que são restritas como chaves primárias ou externas.

Otimizar consultas

Ao criar e aplicar chaves primárias e externas nas tabelas, o BigQuery pode usar essas informações para eliminar ou otimizar determinadas junções de consulta. Embora seja possível imitar essas otimizações reescrevendo as consultas, essas reescritas nem sempre são práticas.

Em um ambiente de produção, você pode criar visualizações que unem muitas tabelas de fatos e dimensões. Os desenvolvedores podem consultar as visualizações em vez de consultar as tabelas subjacentes e reescrever manualmente as junções a cada vez. Se você definir as restrições adequadas, as otimizações de junção acontecerão automaticamente para todas as consultas a que se aplicam.

Os exemplos nas seções a seguir referenciam as tabelas store_sales e customer com restrições:

CREATE TABLE mydataset.customer (customer_name STRING PRIMARY KEY NOT ENFORCED);

CREATE TABLE mydataset.store_sales (
    item STRING PRIMARY KEY NOT ENFORCED,
    sales_customer STRING REFERENCES mydataset.customer(customer_name) NOT ENFORCED,
    category STRING);

Eliminar junções internas

Considere a seguinte consulta que contém uma INNER JOIN:

SELECT ss.*
FROM mydataset.store_sales AS ss
    INNER JOIN mydataset.customer AS c
    ON ss.sales_customer = c.customer_name;

A coluna customer_name é uma chave primária na tabela customer, então cada linha da tabela store_sales tem uma única correspondência ou nenhuma correspondência se sales_customer for NULL. Como a consulta seleciona apenas colunas da tabela store_sales, o otimizador de consultas pode eliminar a junção e reescrever a consulta da seguinte maneira:

SELECT *
FROM mydataset.store_sales
WHERE sales_customer IS NOT NULL;

Eliminar junções externas

Para remover uma LEFT OUTER JOIN, as chaves de junção no lado direito precisam ser exclusivas, e apenas as colunas do lado esquerdo são selecionadas. Considere a seguinte consulta:

SELECT ss.*
FROM mydataset.store_sales ss
    LEFT OUTER JOIN mydataset.customer c
    ON ss.category = c.customer_name;

Neste exemplo, não há relação entre category e customer_name. As colunas selecionadas vêm apenas da tabela store_sales e a chave de junção customer_name é uma chave primária na tabela customer, então cada valor é exclusivo. Isso significa que há exatamente uma correspondência (possivelmente NULL) na tabela customer para cada linha na tabela store_sales, e a LEFT OUTER JOIN pode ser eliminada:

SELECT ss.*
FROM mydataset.store_sales;

Reordenar junções

Quando o BigQuery não consegue eliminar uma junção, ele pode usar restrições de tabela para receber informações sobre cardinalidades de junção e otimizar a ordem em que as junções são realizadas.

Limitações

As chaves primárias e externas estão sujeitas às seguintes limitações:

  • As restrições de chave não são aplicadas no BigQuery. Você é responsável por manter as restrições em todos os momentos. Consultas em tabelas com restrições violadas podem retornar resultados incorretos.
  • As chaves primárias não podem exceder 16 colunas.
  • As chaves externas precisam ter valores presentes na coluna da tabela referenciada. Esses valores podem ser NULL.
  • As chaves primárias e externas precisam ser de um dos seguintes tipos: BIGNUMERIC, BOOLEAN, BYTES, DATE, DATETIME, INT64, NUMERIC, STRING, ou TIMESTAMP.
  • As chaves primárias e externas só podem ser definidas em colunas de nível superior.
  • As chaves primárias não podem ser nomeadas.
  • As tabelas com restrições de chave primária não podem ser renomeadas.
  • Uma tabela pode ter até 64 chaves externas.
  • Uma chave externa não pode se referir a uma coluna na mesma tabela.
  • Os campos que fazem parte de restrições de chave primária ou restrições de chave externa não podem ser renomeados nem ter o tipo alterado.
  • Se você copiar, clonar, restaurar, ou fazer um snapshot de uma tabela sem a opção -a ou --append_table, as restrições da tabela de origem serão copiadas e substituídas para a tabela de destino. Se você usar a opção -a ou --append_table, somente os registros da tabela de origem serão adicionados à tabela de destino sem as restrições da tabela.

A seguir