Declare e otimize com restrições UNIQUE no Databricks

As equipes de dados geralmente mantêm garantias de exclusividade em seus pipelines, mas, sem uma maneira flexível de representar todas elas no esquema da tabela, os usuários e as ferramentas não conseguem descobri-las e o otimizador de consultas não pode aproveitá-las com segurança. As entidades de negócios geralmente têm mais de um valor, ou combinação de valores, conhecidos por serem exclusivos: uma tabela users pode usar user_id como seu identificador principal de linha, enquanto seu modelo de dados também registra garantias de exclusividade separadas para username, um endereço de e-mail e (workspace_id, external_user_id).

O Databricks já oferece suporte a restrições PRIMARY KEY e FOREIGN KEY: uma chave primária identifica as linhas de uma tabela, e as chaves estrangeiras podem referenciá-la para descrever relacionamentos. No entanto, uma tabela pode ter apenas uma chave primária, e suas colunas não podem aceitar valores nulos. As restrições UNIQUE, agora disponíveis em Visualização Pública para tabelas Delta do Unity Catalog no Databricks SQL e no Databricks Runtime 18.2 e superior, adicionam uma maneira de registrar as outras garantias de exclusividade. Uma tabela pode declarar várias restrições UNIQUE, suas colunas podem aceitar valores nulos e as chaves estrangeiras podem referenciá-las. Depois de validar essas restrições e marcá-las com RELY, o otimizador de consultas também pode usá-las para eliminar junções e agregações desnecessárias.

Quando uma chave primária não consegue cobrir todas as colunas exclusivas

PRIMARY KEY e UNIQUE registram a exclusividade, mas cumprem funções de esquema diferentes:

Inclua as garantias de exclusividade do seu pipeline no esquema

A tabela a seguir tem uma chave primária e duas restrições UNIQUE:

As duas restrições exclusivas ilustram diferentes tipos de exclusividade. users_external_uq se aplica a uma combinação de colunas: cada par (workspace_id, external_user_id) deve ser exclusivo, mesmo que qualquer um dos valores possa aparecer em outros pares. users_optional_id_uq se aplica a uma coluna que aceita valores nulos. As linhas a seguir mostram quais valores atendem a essa garantia:

Registre a garantia sem pagar um custo a cada gravação

Assim como as restrições de chave primária e estrangeira no Databricks, as restrições UNIQUE são informais e não são aplicadas quando os dados são gravados. Declarar UNIQUE (optional_identifier), por exemplo, não faz com que uma gravação contendo valores duplicados não nulos falhe.

Essa separação permite que o esquema registre uma garantia de exclusividade mantida por um pipeline de ingestão, um sistema upstream ou outro processo de qualidade de dados. A garantia também se torna metadados da tabela que usuários, ferramentas e aplicativos podem inspecionar.

Fazer referência a uma restrição UNIQUE a partir de uma chave estrangeira

Uma chave estrangeira pode fazer referência a uma restrição UNIQUE além de uma chave primária. Por exemplo, a restrição composta UNIQUE declarada acima pode ser referenciada da seguinte forma:

As colunas pai referenciadas identificam qual restrição exclusiva a chave estrangeira tem como alvo. As colunas filho e pai correspondem por posição: workspace_id é mapeada para workspace_id, e source_user_id é mapeada para external_user_id. Seus nomes não precisam coincidir, mas seus tipos e posições devem corresponder. As colunas REFERENCES (pai) devem ser listadas na mesma ordem que as colunas na definição da restrição UNIQUE; caso contrário, a restrição continuará válida, mas a eliminação de junções não funcionará silenciosamente.

Para fazer referência a uma restrição UNIQUE de coluna única, nomeie explicitamente sua coluna pai, por exemplo, REFERENCES users (username). Se a lista de colunas pai for omitida, NORELY. Ela registra a semântica de esquema pretendida, mas o Databricks não a utiliza para reescritas de consultas dependentes de correção. A instrução a seguir cria uma restrição com esse padrão:

Depois de verificar os dados e garantir que seus pipelines continuarão mantendo a garantia, você pode remover e recriar a restrição com RELY:

Neste exemplo, CASCADE também remove quaisquer chaves estrangeiras que façam referência a users_username_uq, e elas devem ser recriadas após a adição da restrição exclusiva. Sem CASCADE, DROP CONSTRAINT usa o comportamento padrão RESTRICT e é rejeitado quando existem chaves estrangeiras dependentes.

Uma chave estrangeira que faz referência à restrição exclusiva também pode ser declarada com RELY:

RELY é definido de forma independente em cada restrição. Ele não valida dados existentes nem aplica a restrição em gravações futuras. Ele permite que o Databricks assuma que a restrição é válida ao otimizar consultas. Se os dados violarem uma restrição confiável, uma reescrita baseada nessa restrição poderá produzir resultados incorretos. Portanto, você deve usar RELY apenas para garantias que sejam validadas e mantidas continuamente.

Com RELY, restrições exclusivas podem fornecer as informações necessárias para reescritas qualificadas de junção e agregação. A elegibilidade também depende de outras garantias exigidas pela consulta, incluindo a nulidade, como ilustram os exemplos abaixo. Esse suporte de otimização está disponível em computação habilitada para Photon. O blog sobre chaves primárias e estrangeiras descreve otimizações de junção e agregação baseadas em restrições que as restrições UNIQUE também podem habilitar. Desde que essa postagem foi publicada, o suporte a RELY ficou disponível para chaves estrangeiras e agora para restrições UNIQUE.

Elimine junções usando restrições UNIQUE e de chave estrangeira

Alguns dashboards criados com os mesmos dados precisam de atributos de users, portanto, as equipes geralmente criam uma visualização compartilhada que sempre une user_events a users. Um dashboard que precisa apenas de informações de user_events ainda pode herdar essa junção da visualização. Portanto, o SQL gerado pode se parecer com a consulta abaixo, mesmo que não selecione nenhuma coluna de users:

Embora a consulta não retorne nenhuma coluna de users, o otimizador normalmente não consegue remover a junção. Uma junção interna (inner join) pode remover um evento se nenhum usuáriocorrespondente existir, ou duplicá-lo se vários usuários tiverem o mesmo nome de usuário.

As restrições confiáveis eliminam ambas as possibilidades. A restrição UNIQUE garante no máximo um usuário correspondente, enquanto a chave estrangeira garante que exista uma correspondência. Como user_events.username também é NOT NULL, cada evento tem exatamente uma correspondência. Portanto, a junção não pode alterar o resultado, e o otimizador pode ler event_id diretamente de user_events.

O blog anterior sobre chaves primárias e estrangeiras demonstra o caso relacionado LEFT OUTER JOIN, que precisa apenas da garantia de no máximo um. Este exemplo de INNER JOIN também precisa da chave estrangeira para estabelecer que existe uma correspondência.

Com as restrições NORELY, o plano otimizado retém a junção:

Quando ambas as restrições usam RELY, o otimizador pode saber que cada evento tem exatamente um usuário correspondente e remover a junção:

Neste exemplo, user_events.username é NOT NULL, mas a otimização também se aplica quando ele aceita valores nulos (nullable): o Databricks removerá a junção e adicionará um filtro IS NOT NULL no user_events.username para preservar o tratamento de nulos da junção interna.

Fizemos o benchmark dessa otimização no conjunto de dados TPC-DS SF10000, que representa um fator de escala de 10 TB. Na tabela store_sales de 28,8 bilhões de linhas unida à tabela customer de 65 milhões de linhas, declaramos customer.c_customer_sk como UNIQUE e definimos uma chave estrangeira de store_sales.ss_customer_sk. Com RELY em ambas as restrições, o Databricks eliminou a junção, removeu mais de 99,99% do shuffle e reduziu o tempo médio de execução do mecanismo de 23,6 segundos para 3,0 segundos — uma aceleração de 7,9x.

Ignore DISTINCT quando os valores já forem exclusivos

As ferramentas de BI e os geradores de consulta geralmente adicionam DISTINCT de forma defensiva, pois as junções podem produzir linhas duplicadas. Quando os valores selecionados já são exclusivos, DISTINCT não remove nada, mas ainda pode exigir agregação e shuffle, a menos que o otimizador possa provar que a operação é desnecessária. Uma restrição confiável UNIQUE pode fornecer essa prova.

Considere uma tabela user_identifiers com estas duas restrições:

• UNIQUE (user_id) RELY, onde user_id é NOT NULL
• UNIQUE (optional_identifier) RELY, onde optional_identifier aceita valores nulos

A tabela contém os seguintes dados, que satisfazem ambas as restrições:

Para user_id, o otimizador pode usar as garantias de exclusividade e não nulidade juntas:

O plano otimizado não contém agregação:

A agregação desapareceu porque a restrição confiável estabelece que cada user_id já é distinto.

A consulta equivalente sobre optional_identifier mostra por que o otimizador também considera a nulidade:

O plano otimizado retém a agregação:

Ambas as restrições usam RELY, mas a restrição que aceita nulos permite as duas linhas NULL mostradas acima. O SQL DISTINCT deve recolher essas linhas em uma só, portanto a agregação ainda é necessária.

Restrições UNIQUE que aceitam nulos ainda podem habilitar a otimização quando a consulta fornece a garantia de não nulidade ausente:

O otimizador pode remover a agregação:

O filtro remove os valores que podem se repetir na semântica de UNIQUE que aceita nulos. Os valores restantes têm garantia de serem distintos, portanto o Databricks pode evitar a agregação novamente.

Fizemos o benchmark dessa otimização no mesmo conjunto de dados TPC-DS SF10000, que representa um fator de escala de 10 TB. Na tabela store_sales de 28,8 bilhões de linhas, declaramos (ss_item_sk, ss_ticket_number) como uma restrição composta UNIQUE e executamos uma consulta DISTINCT nessas colunas. Com RELY, o Databricks eliminou a agregação DISTINCT, removeu mais de 99,99% do shuffle e reduziu o tempo médio de execução do mecanismo de 33,7 segundos para 2,8 segundos — uma aceleração de 12,0x.

Valide seus dados antes que o otimizador use uma restrição

Encontre linhas duplicadas e órfãs

Antes de usar RELY, verifique se os dados satisfazem a garantia. Para uma restrição UNIQUE que aceita nulos, exclua as linhas que contêm NULL porque UNIQUE permite que essas linhas se repitam. Esta consulta encontra identificadores não nulos duplicados:

Para uma restrição UNIQUE composta, agrupe por cada coluna restrita e adicione uma condição IS NOT NULL para cada coluna que aceita valores nulos. Você também pode encontrar linhas filhas sem pai correspondente usando um left anti join:

Essas verificações validam os dados atuais. Seu pipeline de ingestão ou de qualidade de dados deve continuar mantendo as garantias após você ativar RELY.

Confirmar as restrições declaradas

Após declarar ou recriar uma restrição, você pode confirmar a definição dela de várias maneiras:

• SHOW CREATE TABLE
• DESCRIBE TABLE EXTENDED
• Relações do Unity Catalog INFORMATION_SCHEMA:
  • TABLE_CONSTRAINTS
  • KEY_COLUMN_USAGE
  • CONSTRAINT_TABLE_USAGE
  • CONSTRAINT_COLUMN_USAGE
  • REFERENTIAL_CONSTRAINTS

Consulte Restrições no Databricks e a cláusula CONSTRAINT para ver a sintaxe completa de criação, alteração, inspeção e exclusão.

Implementar UNIQUE e RELY de forma incremental

Você pode adotar UNIQUE de forma incremental:

1. Declare garantias de unicidade existentes com o padrão NORELY.
2. Faça referência a chaves exclusivas a partir de chaves estrangeiras onde o relacionamento faz parte do esquema.
3. Valide e mantenha continuamente a unicidade e a integridade referencial em seus pipelines de dados.
4. Use RELY na restrição UNIQUE somente após verificar a unicidade, e na chave estrangeira somente após verificar que cada valor filho aplicável possui uma linha pai correspondente.
5. Compare planos com EXPLAIN para confirmar reescritas elegíveis.

Conclusão

As restrições UNIQUE permitem que uma tabela registre várias garantias de unicidade além de sua única PRIMARY KEY não nula, e as colunas restritas podem aceitar valores nulos. Chaves estrangeiras podem fazer referência a uma restrição UNIQUE, bem como a uma chave primária. As restrições UNIQUE são informativas por padrão; após a validação, RELY permite que o Databricks as utilize para otimizar junções elegíveis e consultas DISTINCT.

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