Utiliser des clés primaires et étrangères

Les clés primaires et étrangères sont des contraintes de table qui peuvent aider à optimiser les requêtes. Ce document explique comment créer, afficher et gérer des contraintes, et comment les utiliser pour optimiser vos requêtes.

BigQuery accepte les contraintes de clé suivantes :

  • Clé primaire : une clé primaire pour une table est une combinaison d'une ou de plusieurs colonnes qui est unique pour chaque ligne et n'est pas NULL.
  • Clé étrangère : une clé étrangère pour une table est une combinaison d'une ou de plusieurs colonnes qui figurent dans la colonne de clé primaire d'une table référencée ou qui est NULL.

Les clés primaires et étrangères sont généralement utilisées pour assurer l'intégrité des données et permettre l'optimisation des requêtes. BigQuery n'applique pas les contraintes de clé primaire et étrangère. Lorsque vous déclarez des contraintes sur vos tables, vous devez vous assurer que vos données les respectent. BigQuery peut utiliser des contraintes de table pour optimiser vos requêtes.

Gérer les contraintes

Vous pouvez créer et gérer des relations de clés primaires et étrangères à l'aide des instructions LDD suivantes :

Vous pouvez également gérer les contraintes de table via l'API BigQuery en mettant à jour l'objet TableConstraints.

Afficher les contraintes

Les vues suivantes vous fournissent des informations sur les contraintes de vos tables :

Optimiser les requêtes

Lorsque vous créez et appliquez des clés primaires et étrangères à vos tables, BigQuery peut utiliser ces informations pour éliminer ou optimiser certaines jointures de requêtes. Bien qu'il soit possible d'imiter ces optimisations en réécrivant vos requêtes, de telles réécritures ne sont pas toujours pratiques.

Dans un environnement de production, vous pouvez créer des vues qui joignent de nombreuses tables de faits et de dimensions. Les développeurs peuvent interroger les vues au lieu d'interroger les tables sous-jacentes et de réécrire manuellement les jointures à chaque fois. Si vous définissez les contraintes appropriées, les optimisations de jointure se produisent automatiquement pour toutes les requêtes auxquelles elles s'appliquent.

Les exemples des sections suivantes font référence aux tables store_sales et customer avec des contraintes :

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);

Éliminer les jointures internes

Prenons l'exemple de requête suivant qui contient un INNER JOIN :

SELECT ss.*
FROM mydataset.store_sales AS ss
    INNER JOIN mydataset.customer AS c
    ON ss.sales_customer = c.customer_name;

La colonne customer_name est une clé primaire de la table customer. Par conséquent, chaque ligne de la table store_sales ne peut avoir qu'une seule correspondance ou aucune si sales_customer est NULL. Étant donné que la requête ne sélectionne que les colonnes de la table store_sales, l'optimiseur de requêtes peut éliminer la jointure et réécrire la requête comme suit :

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

Éliminer les jointures externes

Pour supprimer un LEFT OUTER JOIN, les clés de jointure à droite doivent être uniques et seules les colonnes de gauche doivent être sélectionnées. Prenons l'exemple de requête suivant :

SELECT ss.*
FROM mydataset.store_sales ss
    LEFT OUTER JOIN mydataset.customer c
    ON ss.category = c.customer_name;

Dans cet exemple, il n'existe aucune relation entre category et customer_name. Les colonnes sélectionnées proviennent uniquement de la table store_sales et la clé de jointure customer_name est une clé primaire de la table customer. Chaque valeur est donc unique. Cela signifie qu'il existe exactement une correspondance (éventuellement NULL) dans la table customer pour chaque ligne de la table store_sales, et que LEFT OUTER JOIN peut être éliminé :

SELECT ss.*
FROM mydataset.store_sales;

Réorganiser les jointures

Lorsque BigQuery ne peut pas éliminer une jointure, il peut utiliser des contraintes de table pour obtenir des informations sur les cardinalités de jointure et optimiser l'ordre dans lequel effectuer les jointures.

Limites

Les clés primaires et étrangères sont soumises aux limites suivantes :

  • Les contraintes de clé ne sont pas appliquées dans BigQuery. Vous êtes responsable du respect des contraintes à tout moment. Les requêtes sur des tables avec des contraintes non respectées peuvent renvoyer des résultats incorrects.
  • Les clés primaires ne peuvent pas dépasser 16 colonnes.
  • Les clés étrangères doivent comporter des valeurs présentes dans la colonne de la table référencée. Ces valeurs peuvent être NULL.
  • Les clés primaires et étrangères doivent être de l'un des types suivants : BIGNUMERIC, BOOLEAN, DATE, DATETIME, INT64, NUMERIC, STRING ou TIMESTAMP.
  • Les clés primaires et étrangères ne peuvent être définies que sur des colonnes de premier niveau.
  • Les clés primaires ne peuvent pas être nommées.
  • Les tables avec des contraintes de clé primaire ne peuvent pas être renommées.
  • Une table peut comporter jusqu'à 64 clés étrangères.
  • Une clé étrangère ne peut pas faire référence à une colonne de la même table.
  • Les champs qui font partie de contraintes de clé primaire ou de clé étrangère ne peuvent pas être renommés, ou subir de modifications de type.
  • Si vous effectuez des opérations de copie, de clonage, de restauration ou de création d'instantané sur une table sans utiliser l'option -a ou --append_table, les contraintes de la table source sont copiées et écrasées dans la table de destination. Si vous utilisez l'option -a ou --append_table, seuls les enregistrements de la table source sont ajoutés à la table de destination, sans les contraintes de la table.

Étapes suivantes