As restrições de chave primária e chave estrangeira estão em disponibilidade geral e agora permitem consultas mais rápidas

A Databricks tem o prazer de anunciar a disponibilidade geral (GA) das restrições de chave primária (PK) e chave estrangeira (FK), a partir do Databricks Runtime 15.2 e Databricks SQL 2024.30. Esta versão segue uma versão prévia pública de grande sucesso, adotada por centenas de clientes ativos semanalmente, e representa ainda mais um marco significativo na melhoria da integridade dos dados e do gerenciamento de dados relacionais no Lakehouse.

Além disso, o Databricks agora pode usar essas restrições para otimizar consultas e eliminar operações desnecessárias do plano de consulta, proporcionando um desempenho muito mais rápido.

Restrições de chave primária e chave estrangeira

As chaves primárias (PKs) e as chaves estrangeiras (FKs) são elementos essenciais em bancos de dados relacionais, atuando como blocos de construção fundamentais para a modelagem de dados. Elas fornecem informações sobre os relacionamentos de dados no esquema para usuários, ferramentas e aplicativos; e permitem otimizações que aproveitam as restrições para acelerar as consultas. As chaves primárias e estrangeiras agora estão geralmente disponíveis para suas tabelas Delta Lake hospedadas no Unity Catalog.

Linguagem SQL

Você pode definir restrições ao criar uma tabela:

No exemplo acima, definimos uma restrição de chave primária na coluna UserID. O Databricks também oferece suporte a restrições em grupos de colunas.

Você também pode modificar as tabelas Delta existentes para adicionar ou remover restrições:

Aqui, criamos a chave primária chamada products_pk na coluna não anulável ProductID em uma tabela existente. Para executar esta operação com sucesso, você deve ser o proprietário da tabela. Observe que os nomes das restrições devem ser exclusivos dentro do esquema.
O comando subsequente remove a chave primária especificando o nome.

O mesmo processo se aplica às chaves estrangeiras. A tabela a seguir define duas chaves estrangeiras no momento da criação da tabela:

Consulte a documentação sobre as instruções CREATE TABLE e ALTER TABLE para obter mais detalhes sobre a sintaxe e as operações relacionadas às restrições.

As restrições de chave primária e chave estrangeira não são aplicadas no mecanismo Databricks, mas podem ser úteis para indicar uma relação de integridade de dados que se pretende que seja verdadeira. O Databricks pode, em vez disso, aplicar restrições de chave primária upstream como parte do pipeline de ingestão. Consulte Qualidade de dados gerenciada com Delta Live Tables para obter mais informações sobre restrições aplicadas. O Databricks também oferece suporte a restrições NOT NULL e CHECK aplicadas (consulte a documentação de restrições para obter mais informações).

Ecossistema de parceiros

Ferramentas e aplicativos como a versão mais recente do Tableau e do PowerBI podem importar e utilizar automaticamente seus relacionamentos de chave primária e chave estrangeira do Databricks por meio de conectores JDBC e ODBC.

Visualizar as restrições

Existem várias maneiras de visualizar as restrições de chave primária e chave estrangeira definidas na tabela. Você também pode simplesmente usar comandos SQL para visualizar informações de restrição com o comando DESCRIBE TABLE EXTENDED:

Catalog Explorer e Diagrama de relacionamento de entidade

Você também pode visualizar as informações de restrições por meio do Catalog Explorer:

Cada coluna de chave primária e chave estrangeira tem um pequeno ícone de chave ao lado de seu nome.

E você pode visualizar as informações de chave primária e chave estrangeira e os relacionamentos entre tabelas com o Diagrama de relacionamento de entidade no Catalog Explorer. Abaixo está um exemplo de uma tabela purchases referenciando duas tabelas, users e products:

INFORMATION SCHEMA

As tabelas INFORMATION_SCHEMA a seguir também fornecem informações de restrição:

• TABLE_CONSTRAINTS: descreve os metadados para todas as restrições de chave primária e chave estrangeira dentro do catálogo.
• KEY_COLUMN_USAGE: lista as colunas das restrições de chave primária ou chave estrangeira dentro do catálogo.
• CONSTRAINT_TABLE_USAGE: descreve as restrições que referenciam tabelas no catálogo.
• CONSTRAINT_COLUMN_USAGE: descreve as restrições que referenciam colunas no catálogo.
• REFERENTIAL_CONSTRAINTS: descreve as restrições referenciais (chave estrangeira) definidas no catálogo.

Use a opção RELY para habilitar otimizações

Se você souber que a restrição de chave primária é válida (por exemplo, porque seu pipeline de dados ou trabalho ETL a aplica), poderá habilitar otimizações com base na restrição, especificando-a com a opção RELY, como:

Usar a opção RELY permite que o Databricks otimize as consultas de maneiras que dependem da validade da restrição, porque você está garantindo que a integridade dos dados seja mantida. Tenha cuidado aqui, pois se uma restrição for marcada como RELY, mas os dados violarem a restrição, suas consultas poderão retornar resultados incorretos.

Quando você não especifica a opção RELY para uma restrição, o padrão é NORELY, caso em que as restrições ainda podem ser usadas para fins informativos ou estatísticos, mas as consultas não dependem delas para serem executadas corretamente.

A opção RELY e as otimizações que a utilizam estão atualmente disponíveis para chaves primárias e também estarão disponíveis em breve para chaves estrangeiras.

Você pode modificar a chave primária de uma tabela para alterar se ela é RELY ou NORELY usando ALTER TABLE, por exemplo:

Acelere suas consultas eliminando agregações desnecessárias

Uma otimização simples que podemos fazer com restrições de chave primária RELY é eliminar agregados desnecessários. Por exemplo, em uma consulta que está aplicando uma operação distinta em uma tabela com uma chave primária usando RELY:

Podemos remover a operação DISTINCT desnecessária:

Como você pode ver, esta consulta depende da validade da restrição de chave primária RELY - se houver IDs de cliente duplicados na tabela de clientes, a consulta transformada retornará resultados duplicados incorretos. Você é responsável por aplicar a validade da restrição se definir a opção RELY.

Se a chave primária for NORELY (o padrão), o otimizador não removerá a operação DISTINCT da consulta. Então, pode ser executado mais lentamente, mas sempre retorna resultados corretos, mesmo que haja duplicatas. Se a chave primária for RELY, o Databricks poderá remover a operação DISTINCT, o que pode acelerar muito a consulta - em cerca de 2x para o exemplo acima.

Acelere suas consultas eliminando junções desnecessárias

Outra otimização muito útil que podemos realizar com chaves primárias RELY é eliminar junções desnecessárias. Se uma consulta une uma tabela que não é referenciada em nenhum outro lugar, exceto na condição de junção, o otimizador pode determinar que a junção é desnecessária e remover a junção do plano de consulta.

Para dar um exemplo, digamos que temos uma consulta unindo duas tabelas, store_sales e customer, unidas na chave primária da tabela de clientes PRIMARY KEY (c_customer_sk) RELY.

Se não tivéssemos a chave primária, cada linha de store_sales poderia corresponder a várias linhas em customer, e precisaríamos executar a junção para calcular o valor SUM correto. Mas como a tabela customer é unida em sua chave primária, sabemos que a junção produzirá uma linha para cada linha de store_sales.

Portanto, a consulta realmente precisa apenas da coluna ss_quantity da tabela de fatos store_sales. Portanto, o otimizador de consulta pode eliminar completamente a junção da consulta, transformando-a em:

Isso é executado muito mais rápido, evitando toda a junção - neste exemplo, observamos a otimização acelerar a consulta de 1,5 minutos para 6 segundos!. E os benefícios podem ser ainda maiores quando a junção envolve muitas tabelas que podem ser eliminadas!

Você pode perguntar, por que alguém executaria uma consulta como esta? Na verdade, é muito mais comum do que você imagina! Uma razão comum é que os usuários constroem visualizações que unem várias tabelas, como unir muitas tabelas de fatos e dimensões. Eles escrevem consultas sobre essas visualizações que geralmente usam colunas de apenas algumas das tabelas, não de todas - e, portanto, o otimizador pode eliminar as junções contra as tabelas que não são necessárias em cada consulta. Este padrão também é comum em muitas ferramentas de Business Intelligence (BI), que geralmente geram consultas unindo muitas tabelas em um esquema, mesmo quando uma consulta usa apenas colunas de algumas das tabelas.

Conclusão

Desde sua versão prévia pública, mais de 2.600 clientes Databricks usaram restrições de chave primária e chave estrangeira. Hoje, estamos animados em anunciar a disponibilidade geral deste recurso, marcando uma nova etapa em nosso compromisso de aprimorar o gerenciamento e a integridade de dados no Databricks.

Além disso, o Databricks agora aproveita as restrições de chave com a opção RELY para otimizar consultas, como eliminando agregações e junções desnecessárias, resultando em um desempenho de consulta muito mais rápido.

(Esta publicação no blog foi traduzida utilizando ferramentas baseadas em inteligência artificial) Publicação original