Otimização de consultas usando chaves primárias e restrições exclusivas

Restrições de chave primária e de unicidade, que capturam relacionamentos de unicidade entre campos em tabelas, podem ajudar usuários e ferramentas a compreender os relacionamentos em seus dados. Este artigo contém exemplos que mostram como você pode usar chaves primárias ou restrições exclusivas com a opção RELY para otimizar alguns tipos comuns de consultas.

nota

As otimizações de consulta associadas ao comando RELY exigem que as consultas sejam executadas no Photon-enabled compute. Veja o que é Photon?. Photon execução por default em SQL warehouse e serverless compute para Notebook e fluxo de trabalho. Para saber mais sobre o Photon, consulte O que é o Photon?

Adicionar chave primária ou restrições de exclusividade

É possível adicionar uma chave primária ou restrição exclusiva na sua instrução de criação de tabela, como no exemplo a seguir, ou adicionar uma a uma tabela usando a cláusula ADD CONSTRAINT.

SQL

CREATE TABLE customer (
  c_customer_sk int,
  PRIMARY KEY (c_customer_sk)
  )

Neste exemplo, c_customer_sk é a chave do ID do cliente. A restrição de chave primária especifica que cada valor de ID de cliente deve ser único na tabela. Restrições exclusivas seguem o mesmo padrão, usando UNIQUE em vez de PRIMARY KEY.

Databricks não impõe restrições de chave. Podem ser validados por meio de sua pipeline de dados existente ou ETL. Consulte Gerenciar a qualidade dos dados com expectativas de pipeline para aprender sobre expectativas em tabelas de transmissão e views materializadas. Consulte Restrições no Databricks para saber mais sobre como trabalhar com restrições em tabelas Delta.

nota

É responsabilidade do usuário verificar se uma restrição está satisfeita. Confiar em uma restrição que não está satisfeita pode levar a resultados de consulta incorretos.

Use RELY para ativar otimizações

Quando se sabe que uma chave primária ou uma restrição única é válida, é possível habilitar otimizações baseadas na restrição, especificando-a com a opção RELY. Consulte cláusula ADD CONSTRAINT para a sintaxe completa.

A opção RELY permite que o Databricks explore a restrição para reescrever as consultas. As otimizações a seguir só podem ser realizadas se a opção RELY for especificada em uma cláusula ADD CONSTRAINT ou instrução ALTER TABLE.

Usando ALTER TABLE, o senhor pode modificar o key primário de uma tabela para incluir a opção RELY, conforme mostrado no exemplo a seguir.

SQL

ALTER TABLE
  customer DROP PRIMARY KEY;
ALTER TABLE
  customer
ADD
  PRIMARY KEY (c_customer_sk) RELY;

Exemplos de otimização

Os exemplos a seguir estendem o exemplo anterior que cria uma tabela customer onde c_customer_sk é um identificador exclusivo verificado nomeado como PRIMARY KEY com a opção RELY especificada. As mesmas otimizações podem ser aplicadas a uma restrição UNIQUE com a opção RELY.

Exemplo 1: Elimine agregações desnecessárias

A seguir, é mostrada uma consulta que aplica DISTINCT operações a um primário key.

SQL

SELECT
  DISTINCT c_customer_sk
FROM
  customer;

Como a coluna c_customer_sk é uma restrição PRIMARY KEY verificada, todos os valores na coluna são exclusivos. Se a opção RELY for especificada, o site Databricks poderá otimizar a consulta não executando as operações DISTINCT.

O otimizador também pode remover DISTINCT quando a coluna selecionada é coberta por uma restrição UNIQUE válida especificada com RELY.

Exemplo 2: Eliminar junções desnecessárias

O exemplo a seguir mostra uma consulta em que Databricks pode eliminar um join desnecessário.

A consulta une uma tabela de fatos, store_sales, com uma tabela de dimensões, customer. Ele executa um left outer join, de modo que o resultado da consulta inclui todos os registros da tabela store_sales e os registros correspondentes da tabela customer. Se não houver nenhum registro correspondente na tabela customer, o resultado da consulta mostrará um valor NULL para a coluna c_customer_sk.

SQL

SELECT
  SUM(ss_quantity)
FROM
  store_sales ss
  LEFT JOIN customer c ON ss.customer_sk = c.c_customer_sk;

Para entender por que este join é desnecessário, considere a declaração da consulta. Requer apenas a coluna ss_quantity da tabela store_sales. A tabela customer é unida por sua primary key ou por uma restrição exclusiva, de modo que cada linha de store_sales corresponde a, no máximo, uma linha em customer. Como a operação é uma join externa, todos os registros da tabela store_sales são preservados, portanto, a join não altera nenhum dado dessa tabela. A agregação SUM é a mesma, independentemente de essas tabelas estarem unidas.

O uso da key primária ou da restrição de unicidade com RELY fornece ao otimizador de consulta as informação necessárias para eliminar o join. A query otimizada parece mais com isto:

SQL

SELECT
  SUM(ss_quantity)
FROM
  store_sales ss

Próximas etapas

Consulte Visualizar o Diagrama de Relacionamento entre Entidades para saber como explorar os relacionamentos primários key e externos key na interface do usuário do Catalog Explorer.