Déclarez et optimisez avec des contraintes UNIQUE sur Databricks

Les équipes de données maintiennent souvent des garanties d'unicité dans leurs pipelines, mais sans moyen flexible de les représenter toutes dans le schéma de la table, les utilisateurs et les outils ne peuvent pas les découvrir et l'optimiseur de requêtes ne peut pas les exploiter en toute sécurité. Les entités métier possèdent souvent plus d'une valeur, ou combinaison de valeurs, connue pour être unique : une table users peut utiliser user_id comme identifiant de ligne principal tandis que son modèle de données enregistre également des garanties d'unicité séparées pour username, une adresse e-mail et (workspace_id, external_user_id).

Databricks prend déjà en charge les contraintes PRIMARY KEY et FOREIGN KEY : une clé primaire identifie les lignes d'une table et les clés étrangères peuvent y faire référence pour décrire des relations. Cependant, une table ne peut avoir qu'une seule clé primaire et ses colonnes ne doivent pas accepter les valeurs nulles. Les contraintes UNIQUE, désormais disponibles en version préliminaire publique pour les tables Delta de Unity Catalog dans Databricks SQL et Databricks Runtime 18.2 et supérieur, ajoutent un moyen d'enregistrer les autres garanties d'unicité. Une table peut déclarer plusieurs contraintes UNIQUE, leurs colonnes peuvent accepter des valeurs nulles et des clés étrangères peuvent y faire référence. Une fois que vous avez validé ces contraintes et les avez marquées avec RELY, l'optimiseur de requêtes peut également les utiliser pour éliminer les jointures et agrégations inutiles.

Lorsqu'une seule clé primaire ne peut pas couvrir toutes les colonnes uniques

PRIMARY KEY et UNIQUE enregistrent toutes deux l'unicité, mais elles jouent des rôles différents dans le schéma :

Intégrez les garanties d'unicité de votre pipeline dans le schéma

La table suivante possède une clé primaire et deux contraintes UNIQUE :

Les deux contraintes d'unicité illustrent différents types d'unicité. users_external_uq s'applique à une combinaison de colonnes : chaque paire (workspace_id, external_user_id) doit être unique même si chaque valeur peut apparaître dans d'autres paires. users_optional_id_uq s'applique à une colonne autorisant les valeurs nulles. Les lignes suivantes indiquent quelles valeurs satisfont cette garantie :

Enregistrez la garantie sans impacter chaque écriture

À l'instar des contraintes de clé primaire et étrangère sur Databricks, les contraintes UNIQUE sont informatives et ne sont pas appliquées lors de l'écriture des données. Déclarer UNIQUE (optional_identifier), par exemple, ne provoque pas l'échec d'une écriture contenant des valeurs en double non nulles.

Cette séparation permet au schéma d'enregistrer une garantie d'unicité maintenue par un pipeline d'ingestion, un système en amont ou un autre processus de qualité des données. La garantie devient également des métadonnées de table que les utilisateurs, les outils et les applications peuvent inspecter.

Faire référence à une contrainte UNIQUE à partir d'une clé étrangère

Une clé étrangère peut faire référence à une contrainte UNIQUE en plus d'une clé primaire. Par exemple, la contrainte composite UNIQUE déclarée ci-dessus peut être référencée comme suit :

Les colonnes parentes référencées identifient la contrainte d'unicité visée par la clé étrangère. Les colonnes enfants et parentes correspondent par position : workspace_id correspond à workspace_id, et source_user_id correspond à external_user_id. Leurs noms n'ont pas besoin d'être identiques, mais leurs types et positions doivent correspondre. Les colonnes (parentes) REFERENCES doivent être répertoriées dans le même ordre que les colonnes dans la définition de la contrainte UNIQUE ; sinon, la contrainte reste valide mais l'élimination des jointures ne s'activera pas silencieusement.

Pour faire référence à une contrainte UNIQUE à colonne unique, nommez explicitement sa colonne parente, par exemple REFERENCES users (username). Si la liste des colonnes parentes est omise, REFERENCES users cible plutôt la clé primaire de la table.

Optimisez les requêtes avec RELY

Par défaut, une contrainte unique est créée avec NORELY. Elle enregistre la sémantique de schéma prévue, mais Databricks ne l'utilise pas pour les réécritures de requêtes dépendantes de l'exactitude. L'instruction suivante crée une contrainte avec cette valeur par défaut :

Une fois que vous avez vérifié les données et vous êtes assuré que vos pipelines continueront à maintenir la garantie, vous pouvez supprimer et recréer la contrainte avec RELY :

Dans cet exemple, CASCADE supprime également toutes les clés étrangères qui font référence à users_username_uq, et celles-ci doivent être recréées après l'ajout de la contrainte d'unicité. Sans CASCADE, DROP CONSTRAINT utilise le comportement par défaut RESTRICT et est rejeté en présence de clés étrangères dépendantes.

Une clé étrangère qui fait référence à la contrainte d'unicité peut également être déclarée avec RELY :

RELY est défini indépendamment sur chaque contrainte. Il ne valide pas les données existantes et n'applique pas la contrainte lors des écritures futures. Il permet à Databricks de supposer que la contrainte est valide lors de l'optimisation des requêtes. Si les données violent une contrainte sur laquelle on s'appuie, une réécriture basée sur cette contrainte peut produire des résultats incorrects. Vous devez par conséquent utiliser RELY uniquement pour les garanties qui sont validées et maintenues en continu.

Avec RELY, les contraintes d'unicité peuvent fournir les informations nécessaires pour les réécritures de jointure et d'agrégation éligibles. L'éligibilité dépend également d'autres garanties requises par la requête, notamment la possibilité d'accepter des valeurs nulles, comme l'illustrent les exemples ci-dessous. Cette prise en charge de l'optimisation est disponible sur le calcul compatible Photon. L'article de blog sur les clés primaires et étrangères décrit les optimisations de jointure et d'agrégation basées sur des contraintes que les contraintes UNIQUE peuvent également activer. Depuis la publication de cet article, la prise en charge de RELY est devenue disponible pour les clés étrangères et désormais pour les contraintes UNIQUE.

Éliminer les jointures à l'aide de contraintes UNIQUE et de clés étrangères

Certains tableaux de bord créés à partir des mêmes données nécessitent des attributs de users, de sorte que les équipes créent souvent une vue partagée qui joint toujours user_events avec users. Un tableau de bord qui n'a besoin que d'informations provenant de user_events peut toujours hériter de cette jointure de la vue. Le code SQL généré peut donc ressembler à la requête ci-dessous, même s'il ne sélectionne aucune colonne de users :

Bien que la requête ne renvoie aucune colonne de users, l'optimiseur ne peut généralement pas supprimer la jointure. Une jointure interne pourrait supprimer un événement si aucun utilisateur correspondant n'existe, ou le dupliquer si plusieurs utilisateurs ont le même nom d'utilisateur.

Les contraintes sur lesquelles on s'appuie excluent ces deux possibilités. La contrainte UNIQUE garantit au plus un utilisateur correspondant, tandis que la clé étrangère garantit qu'une correspondance existe. Étant donné que user_events.username est également NOT NULL, chaque événement a exactement une correspondance. La jointure ne peut donc pas modifier le résultat, et l'optimiseur peut lire event_id directement à partir de user_events.

L'article de blog précédent sur les clés primaires et étrangères démontre le cas connexe LEFT OUTER JOIN, qui ne nécessite que la garantie d'au-plus-un. Cet exemple INNER JOIN nécessite également la clé étrangère pour établir qu'une correspondance existe.

Avec les contraintes NORELY, le plan optimisé conserve la jointure :

Lorsque les deux contraintes utilisent RELY, l'optimiseur peut savoir que chaque événement a exactement un utilisateur correspondant et supprimer la jointure :

Dans cet exemple, user_events.username est NOT NULL, mais l'optimisation s'applique également lorsqu'il accepte les valeurs nulles : Databricks supprime la jointure et ajoute un filtre IS NOT NULL sur user_events.username pour préserver le traitement des valeurs nulles de la jointure interne.

Nous avons évalué cette optimisation sur le jeu de données TPC-DS SF10000, qui représente un facteur d'échelle de 10 To. Sur la table store_sales de 28,8 milliards de lignes jointe à la table customer de 65 millions de lignes, nous avons déclaré customer.c_customer_sk comme UNIQUE et défini une clé étrangère à partir de store_sales.ss_customer_sk. Avec RELY sur les deux contraintes, Databricks a éliminé la jointure, supprimé plus de 99,99 % du trafic de brassage (shuffle) et réduit le temps d'exécution médian du moteur de 23,6 secondes à 3,0 secondes, soit une accélération de 7,9x.

Ignorer DISTINCT lorsque les valeurs sont déjà uniques

Les outils de BI et les générateurs de requêtes ajoutent souvent DISTINCT par précaution, car les jointures peuvent produire des lignes en double. Lorsque les valeurs sélectionnées sont déjà uniques, DISTINCT ne supprime rien, mais peut tout de même nécessiter une agrégation et un brassage (shuffle) à moins que l'optimiseur ne puisse prouver que l'opération est inutile. Une contrainte UNIQUE sur laquelle on s'appuie peut fournir cette preuve.

Considérez une table user_identifiers avec ces deux contraintes :

• UNIQUE (user_id) RELY, où user_id est NOT NULL
• UNIQUE (optional_identifier) RELY, où optional_identifier accepte les valeurs nulles

La table contient les données suivantes, qui satisfont aux deux contraintes :

Pour user_id, l'optimiseur peut utiliser conjointement les garanties d'unicité et de non-nullité :

Le plan optimisé ne contient aucune agrégation :

L'agrégation a disparu parce que la contrainte sur laquelle on s'appuie établit que chaque user_id est déjà distinct.

La requête équivalente sur optional_identifier montre pourquoi l'optimiseur prend également en compte la possibilité d'accepter des valeurs nulles :

Le plan optimisé conserve l'agrégation :

Les deux contraintes utilisent RELY, mais la contrainte acceptant les valeurs nulles autorise les deux lignes NULL illustrées ci-dessus. Le mot-clé SQL DISTINCT doit fusionner ces lignes en une seule, l'agrégation reste donc nécessaire.

Les contraintes UNIQUE acceptant les valeurs nulles peuvent toujours permettre l'optimisation lorsque la requête fournit la garantie de non-nullité manquante :

L'optimiseur peut supprimer l'agrégation :

Le filtre supprime les valeurs qui peuvent se répéter sous la sémantique UNIQUE acceptant les valeurs nulles. Les valeurs restantes sont garanties d'être distinctes, ce qui permet à Databricks d'éviter à nouveau l'agrégation.

Nous avons évalué cette optimisation sur le même jeu de données TPC-DS SF10000, qui représente un facteur d'échelle de 10 To. Sur la table store_sales de 28,8 milliards de lignes, nous avons déclaré (ss_item_sk, ss_ticket_number) comme une contrainte UNIQUE composite et exécuté une requête DISTINCT sur ces colonnes. Avec RELY, Databricks a éliminé l'agrégation DISTINCT, supprimé plus de 99,99 % du trafic de brassage (shuffle) et réduit le temps d'exécution médian du moteur de 33,7 secondes à 2,8 secondes, soit une accélération de 12,0x.

Valider vos données avant qu'un optimiseur n'utilise une contrainte

Rechercher les lignes en double et orphelines

Avant d'utiliser RELY, vérifiez que les données satisfont la garantie. Pour une contrainte UNIQUE acceptant les valeurs nulles, excluez les lignes contenant NULL car UNIQUE autorise la répétition de ces lignes. Cette requête recherche les identifiants non nuls en double :

Pour une contrainte UNIQUE composite, groupez par chaque colonne contrainte et ajoutez une condition IS NOT NULL pour chaque colonne pouvant accepter des valeurs nulles. Vous pouvez également rechercher les lignes enfants sans parent correspondant en utilisant une jointure anti à gauche (left anti join) :

Ces vérifications valident les données actuelles. Votre pipeline d'ingestion ou de qualité des données doit continuer à garantir ces critères après l'activation de RELY.

Confirmer les contraintes déclarées

Après avoir déclaré ou recréé une contrainte, vous pouvez vérifier sa définition de plusieurs manières :

• SHOW CREATE TABLE
• DESCRIBE TABLE EXTENDED
• Relations INFORMATION_SCHEMA de Unity Catalog :
  • TABLE_CONSTRAINTS
  • KEY_COLUMN_USAGE
  • CONSTRAINT_TABLE_USAGE
  • CONSTRAINT_COLUMN_USAGE
  • REFERENTIAL_CONSTRAINTS

Consultez la page Contraintes sur Databricks et la clause CONSTRAINT pour connaître la syntaxe complète de création, de modification, d'inspection et de suppression.

Déployer UNIQUE et RELY de manière incrémentielle

Vous pouvez adopter UNIQUE de manière incrémentielle :

1. Déclarez les garanties d'unicité existantes avec l'option par défaut NORELY.
2. Référencez des clés uniques à partir de clés étrangères lorsque la relation fait partie du schéma.
3. Validez et maintenez continuellement l'unicité et l'intégrité référentielle dans vos pipelines de données.
4. Utilisez RELY sur la contrainte UNIQUE uniquement après avoir vérifié l'unicité, et sur la clé étrangère uniquement après avoir vérifié que chaque valeur enfant applicable possède une ligne parente correspondante.
5. Comparez les plans avec EXPLAIN pour confirmer les réécritures éligibles.

Conclusion

Les contraintes UNIQUE permettent à une table d'enregistrer plusieurs garanties d'unicité en plus de sa clé PRIMARY KEY unique et non nulle, et les colonnes contraintes peuvent accepter des valeurs nulles. Les clés étrangères peuvent référencer une contrainte UNIQUE ainsi qu'une clé primaire. Les contraintes UNIQUE sont informatives par défaut ; après validation, RELY permet à Databricks de les utiliser pour optimiser les jointures éligibles et les requêtes DISTINCT.

(Cet article de blog a été traduit à l'aide d'outils basés sur l'intelligence artificielle) Article original