Utiliser des clés primaires et étrangères

Les clés primaires et étrangères sont des contraintes de table qui peuvent vous 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 est compatible avec 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 qui n'est pas NULL.
  • Clé étrangère : une clé étrangère pour une table est une combinaison d'une ou de plusieurs colonnes qui est présente 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 garantir 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. Pour garantir l'unicité des valeurs dans une colonne, envisagez d'utiliser une colonne d'identité. BigQuery peut utiliser des contraintes de table pour optimiser vos requêtes.

Gérer les contraintes

Les relations entre clés primaires et clés étrangères peuvent être créées et gérées à 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 votre table :

  • La vue INFORMATION_SCHEMA.TABLE_CONSTRAINTS contient des informations sur toutes les contraintes de clé primaire et étrangère des tables d'un ensemble de données.
  • La vue INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE contient des informations sur les colonnes de clé primaire de chaque table et sur les colonnes référencées par des clés étrangères d'autres tables d'un ensemble de données.
  • La vue INFORMATION_SCHEMA.KEY_COLUMN_USAGE contient des informations sur les colonnes de chaque table qui sont contraintes en tant que clés primaires ou étrangères.

Optimiser les requêtes

Lorsque vous créez et appliquez des clés primaires et étrangères dans 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, ces 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 la requête suivante qui contient une 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 dans la table customer. Par conséquent, chaque ligne de la table store_sales comporte 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 une LEFT OUTER JOIN, les clés de jointure du côté droit doivent être uniques et seules les colonnes du côté gauche sont sélectionnées. Prenons l'exemple de la requête suivante :

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 dans la table customer. Par conséquent, chaque valeur est 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 la LEFT OUTER JOIN peut être éliminée :

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 de leur maintenance à tout moment. Les requêtes sur des tables dont les contraintes ont été enfreintes 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, BYTES, 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 contraintes de clé étrangère ne peuvent pas être renommés ni voir leur type modifié.
  • Si vous copiez, clonez, restaurez, ou créez un instantané d'une table sans 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.

Étape suivante