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 é compatível com as 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 geralmente são usadas para garantir a integridade dos dados e permitir a otimização de consultas. O BigQuery não impõe restrições de chave primária e externa. Ao declarar restrições nas tabelas, verifique se os dados estão em conformidade com elas. 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 com as seguintes instruções DDL:

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

Acessar restrições

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

Otimizar consultas

Ao criar e aplicar chaves primárias e estrangeiras nas tabelas, o BigQuery pode usar essas informações para eliminar ou otimizar determinadas junções de consultas. Embora seja possível imitar essas otimizações reescrevendo suas consultas, isso nem sempre é prático.

Em um ambiente de produção, é possível criar visualizações que combinam 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 vão acontecer automaticamente para todas as consultas a que se aplicam.

Os exemplos nas seções a seguir fazem referência às 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 um 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. Portanto, 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 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 um LEFT OUTER JOIN, as chaves de junção à direita precisam ser exclusivas, e apenas as colunas do lado esquerdo podem ser 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. Portanto, cada valor é único. Isso significa que há exatamente uma correspondência (possivelmente NULL) na tabela customer para cada linha na tabela store_sales, e o LEFT OUTER JOIN pode ser eliminado:

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 ultrapassar 16 colunas.
  • As chaves estrangeiras precisam ter valores que estejam 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, DATE, DATETIME, INT64, NUMERIC, STRING ou TIMESTAMP.
  • As chaves primárias e externas só podem ser definidas em colunas de nível superior.
  • Não é possível nomear as chaves primárias.
  • Não é possível renomear tabelas com restrições de chave primária.
  • Uma tabela pode ter até 64 chaves externas.
  • Uma chave externa não pode ter referência a uma coluna na mesma tabela.
  • Os campos que fazem parte das restrições de chave primária ou chave externa não podem ser renomeados ou ter o tipo alterado.
  • Se você copiar, clonar, restaurar ou fazer um snapshot de uma tabela sem as opções -a ou --append_table, as restrições da tabela de origem sã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