Use chaves principais e externas

As chaves principais e as chaves externas são restrições de tabelas que podem ajudar na otimização de consultas. Este documento explica como criar, ver e gerir restrições, e usá-las para otimizar as suas consultas.

O BigQuery suporta as seguintes restrições de chaves:

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

As chaves principais e externas são normalmente usadas para garantir a integridade dos dados e permitir a otimização de consultas. O BigQuery não aplica restrições de chaves principais e externas. Quando declara restrições nas tabelas, tem de garantir que os dados estão em conformidade com as mesmas. O BigQuery pode usar restrições de tabelas para otimizar as suas consultas.

Faça a gestão das restrições

As relações de chaves principais e externas podem ser criadas e geridas através das seguintes declarações DDL:

Também pode gerir restrições de tabelas através da API BigQuery atualizando o objeto TableConstraints.

Veja as restrições

As seguintes vistas dão-lhe informações sobre as restrições das tabelas:

Otimize as consultas

Quando cria e aplica chaves primárias e estrangeiras nas suas tabelas, o BigQuery pode usar essas informações para eliminar ou otimizar determinadas junções de consultas. Embora seja possível imitar estas otimizações reescrevendo as suas consultas, estas reescritas nem sempre são práticas.

Num ambiente de produção, pode criar vistas que juntam muitas tabelas de factos e dimensões. Os programadores podem consultar as vistas em vez de consultar as tabelas subjacentes e reescrever manualmente as junções de cada vez. Se definir as restrições adequadas, as otimizações de junções ocorrem automaticamente para todas as consultas às quais se aplicam.

Os exemplos nas secções seguintes 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);

Elimine as 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 principal na tabela customer, pelo que cada linha da tabela store_sales tem uma única correspondência ou nenhuma correspondência se sales_customer for NULL. Uma vez que a consulta apenas seleciona colunas da tabela store_sales, o otimizador de consultas pode eliminar a junção e reescrever a consulta da seguinte forma:

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

Elimine as junções externas

Para remover um LEFT OUTER JOIN, as chaves de junção no lado direito têm de ser únicas e apenas são selecionadas colunas do lado esquerdo. 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 existe nenhuma relação entre category e customer_name. As colunas selecionadas provêm apenas da tabela store_sales e a chave de junção customer_name é uma chave principal na tabela customer, pelo que cada valor é único. Isto significa que existe exatamente uma correspondência (possivelmente NULL) na tabela customer para cada linha na tabela store_sales e que a tabela LEFT OUTER JOIN pode ser eliminada:

SELECT ss.*
FROM mydataset.store_sales;

Reordene as associações

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

Limitações

As chaves principais e as chaves externas estão sujeitas às seguintes limitações:

  • As restrições de chaves não são aplicadas no BigQuery. É responsável por manter as restrições em todos os momentos. As consultas em tabelas com restrições violadas podem devolver resultados incorretos.
  • As chaves primárias não podem exceder 16 colunas.
  • As chaves externas têm de ter valores presentes na coluna da tabela referenciada. Estes valores podem ser NULL.
  • As chaves principais e as chaves externas têm de ser de um dos seguintes tipos: BIGNUMERIC, BOOLEAN, DATE, DATETIME, INT64, NUMERIC, STRING, ou TIMESTAMP.
  • As chaves principais e as chaves externas só podem ser definidas em colunas de nível superior.
  • Não é possível atribuir nomes às chaves principais.
  • Não é possível mudar o nome das tabelas com restrições de chave primária.
  • Uma tabela pode ter até 64 chaves externas.
  • Uma chave externa não pode referir-se a uma coluna na mesma tabela.
  • Não é possível mudar o nome nem o tipo dos campos que fazem parte de restrições de chave primária ou restrições de chave externa.
  • Se copiar, clonar, restaurar> ou criar uma imagem instantânea de uma tabela sem a opção -a ou --append_table, as restrições da tabela de origem são copiadas e substituídas na tabela de destino. Se usar a opção -a ou --append_table, apenas os registos da tabela de origem são adicionados à tabela de destino sem as restrições da tabela.

O que se segue?