Spécifier une colonne d'identité

Ce document explique comment créer et utiliser des colonnes d'identité, parfois appelées colonnes à incrémentation automatique, qui permettent de créer et de gérer des clés primaires dans vos tables. Lorsque vous insérez une ligne dans une table comportant une colonne d'identité, BigQuery génère une valeur entière unique pour cette colonne.

Présentation

Une colonne d'identité est une colonne INT64 qui est remplie avec des valeurs uniques générées par le système.

Le principal cas d'utilisation des colonnes d'identité consiste à générer des clés primaires. Vous pouvez également générer des clés primaires à l'aide de la GENERATE_UUID fonction pour générer des chaînes uniques, mais les colonnes d'identité sont généralement préférées pour les raisons suivantes :

  • Les valeurs entières nécessitent moins d'espace de stockage que les valeurs de chaîne.
  • L'utilisation d'entiers pour les jointures de tables est plus efficace que l'utilisation de chaînes.

Les valeurs d'une colonne d'identité sont générées en fonction d'une valeur de départ qui définit la première valeur, et d'une valeur d'incrément qui définit la différence minimale entre les valeurs générées successivement.

Les valeurs de colonne d'identité générées présentent les propriétés suivantes :

  • Unique : les valeurs générées automatiquement sont uniques dans la table.
  • Ordre approximatif : les valeurs générées ne sont pas garanties d'être dans un ordre strictement croissant ou décroissant.
  • Éparses : les valeurs générées ne sont pas garanties d'être consécutives. Certaines valeurs peuvent être ignorées, mais les valeurs d'une colonne d'identité diffèrent toujours d'un multiple de l'incrément que vous spécifiez.

Limites

  • Une table ne peut comporter qu'une seule colonne d'identité.
  • Vous pouvez lire des tables avec des colonnes d'identité en ancien SQL, mais vous ne pouvez pas écrire dans des tables avec des colonnes d'identité en ancien SQL.
  • Vous ne pouvez pas utiliser le clustering ni le partitionnement sur une colonne d'identité.

  • Les opérations de copie de table suivantes ne sont pas compatibles si une table source ou de destination comporte une colonne d'identité :

    • Copie de table avec disposition d'écriture WRITE_APPEND ou WRITE_TRUNCATE
    • Copie de table à sources multiples

  • La diffusion de données en streaming à l'aide de l'API Storage Write (gRPC) ou de la tabledata.insertAll méthode d'API n'est pas compatible avec les tables comportant des colonnes d'identité.

Créer des colonnes d'identité

Vous pouvez créer une colonne d'identité lorsque vous créez une table à l'aide de l' CREATE TABLE instruction LDD. Utilisez la clause GENERATED AS IDENTITY pour désigner une colonne INT64 comme colonne d'identité. Une table ne peut comporter qu'une seule colonne d'identité. Vous pouvez spécifier l'un des modes de génération suivants, qui détermine si vous pouvez insérer manuellement des valeurs dans la colonne d'identité :

  • GENERATED ALWAYS AS IDENTITY : les valeurs sont toujours générées par le système. Vous ne pouvez pas fournir votre propre valeur lorsque vous insérez ou mettez à jour des données dans cette colonne. Si vous ne spécifiez pas ALWAYS ou BY DEFAULT, ALWAYS est utilisé.

  • GENERATED BY DEFAULT AS IDENTITY: vous pouvez insérer ou modifier des valeurs dans la colonne d'identité. BigQuery n'applique pas l'unicité des valeurs que vous insérez ou modifiez.

    Si vous omettez la colonne ou fournissez NULL lorsque vous insérez des données, BigQuery génère automatiquement une valeur pour vous. La colonne d'identité ne peut pas contenir de valeur NULL. Si vous souhaitez utiliser une valeur générée dans une instruction INSERT, MERGE ou UPDATE, vous pouvez utiliser le mot clé DEFAULT ou NULL.

L'exemple suivant crée la table mydataset.id_table avec une colonne d'identité id qui commence à 0 et s'incrémente de 5 :

CREATE TABLE mydataset.id_table (
  id INT64 GENERATED ALWAYS AS IDENTITY(START WITH 0 INCREMENT BY 5),
  data STRING
);

Ajouter la propriété de colonne d'identité à une colonne

Pour modifier une colonne existante afin de générer des valeurs d'identité, utilisez l' ALTER TABLE ALTER COLUMN SET GENERATED instruction LDD. Cette instruction transforme une colonne INT64 existante en colonne d'identité. Elle ne remplit pas les valeurs des lignes existantes dans la colonne d'identité.

Utiliser des instructions LMD avec des colonnes d'identité

Vous pouvez utiliser des instructions LMD telles que INSERT, MERGE et UPDATE avec des colonnes d'identité. Les sections suivantes utilisent la table mydataset.mytable qui comporte une colonne d'identité appelée id et une colonne de chaîne appelée data :

CREATE OR REPLACE TABLE mydataset.mytable (
  id INT64 GENERATED BY DEFAULT AS IDENTITY(START WITH 100 INCREMENT BY 10),
  data STRING
);

Insérer des données

Lorsque vous insérez des données dans une table comportant une colonne d'identité, vous pouvez omettre la colonne d'identité de la liste des colonnes pour générer une valeur pour celle-ci. L'instruction INSERT suivante omet la colonne id, BigQuery génère des valeurs pour celle-ci :

INSERT mydataset.mytable (data) VALUES ('A'), ('B'), ('C');

Le résultat ressemble à ce qui suit, bien que l'ordre d'attribution des valeurs générées aux lignes puisse varier :

+-----+------+
| id  | data |
+-----+------+
| 110 | A    |
| 120 | B    |
| 100 | C    |
+-----+------+

Si une colonne d'identité est définie avec GENERATED BY DEFAULT AS IDENTITY, vous pouvez spécifier votre propre valeur pour la colonne. Vous pouvez également utiliser le mot clé DEFAULT ou NULL pour que BigQuery génère une valeur.

L'instruction INSERT suivante fournit une valeur pour une ligne et utilise DEFAULT ou NULL pour générer des valeurs pour les deux autres lignes :

INSERT mydataset.mytable (id, data)
VALUES (155, 'D'), (DEFAULT, 'E'), (NULL, 'F');

Le résultat ressemble à ce qui suit :

+-----+------+
| id  | data |
+-----+------+
| 110 | A    |
| 120 | B    |
| 100 | C    |
| 155 | D    |
| 140 | E    |
| 130 | F    |
+-----+------+

Si une colonne d'identité est définie avec GENERATED ALWAYS AS IDENTITY, vous ne pouvez utiliser que le mot clé DEFAULT pour que BigQuery génère une valeur. Vous ne pouvez pas fournir votre propre valeur ni utiliser NULL.

Fusionner les données

Vous pouvez utiliser l' MERGE instruction pour fusionner des données dans une table comportant une colonne d'identité. Si votre colonne d'identité utilise le mode de génération GENERATED BY DEFAULT AS IDENTITY, vous pouvez utiliser les mots clés DEFAULT ou NULL pour générer une valeur lorsque vous insérez ou mettez à jour des données dans le cadre d'une instruction MERGE.

L'exemple suivant fusionne mydataset.source_table dans mydataset.mytable, en insérant une nouvelle ligne s'il n'y a pas de correspondance dans la colonne data, et en mettant à jour la colonne id avec une nouvelle valeur générée en cas de correspondance :

CREATE OR REPLACE TABLE mydataset.source_table(data STRING)
AS SELECT * FROM UNNEST(['A', 'C', 'G']);

MERGE mydataset.mytable T
USING mydataset.source_table S
ON T.data = S.data
WHEN MATCHED THEN
  UPDATE SET id = DEFAULT
WHEN NOT MATCHED THEN
  INSERT(data)
  VALUES(S.data);

Le résultat ressemble à ce qui suit :

+-----+------+
| id  | data |
+-----+------+
| 160 | A    |
| 120 | B    |
| 150 | C    |
| 155 | D    |
| 140 | E    |
| 130 | F    |
| 170 | G    |
+-----+------+

Si votre colonne d'identité utilise le mode de génération GENERATED ALWAYS AS IDENTITY, vous ne pouvez pas inclure la colonne d'identité dans une clause de mise à jour de fusion. Pour utiliser une clause d'insertion de fusion, vous pouvez omettre la colonne d'identité de la liste des colonnes ou utiliser le mot clé DEFAULT.

Mettre à jour des données

Vous pouvez utiliser l' UPDATE instruction pour mettre à jour les valeurs d'une colonne d'identité qui utilise le mode de génération GENERATED BY DEFAULT AS IDENTITY. Vous pouvez utiliser les mots clés DEFAULT ou NULL pour générer une nouvelle valeur.

L'exemple suivant met à jour toutes les valeurs de la colonne id avec des valeurs nouvellement générées :

UPDATE mydataset.mytable
SET id = NULL
WHERE TRUE;

Le résultat ressemble à ce qui suit :

+-----+------+
| id  | data |
+-----+------+
| 190 | A    |
| 210 | B    |
| 240 | C    |
| 230 | D    |
| 180 | E    |
| 200 | F    |
| 220 | G    |
+-----+------+

Si votre colonne d'identité utilise le mode de génération GENERATED ALWAYS AS IDENTITY, vous ne pouvez pas mettre à jour la colonne d'identité.

Ajouter à une table

Vous pouvez utiliser la commande bq query avec l'indicateur --append_table pour ajouter les résultats d'une requête à une table de destination comportant une colonne d'identité. Si la requête omet la colonne d'identité, une valeur est générée pour celle-ci.

L'exemple suivant ajoute des données uniquement pour la colonne data à mydataset.mytable :

bq query \
    --nouse_legacy_sql \
    --append_table \
    --destination_table=mydataset.mytable \
    'SELECT "H" AS data'

Une nouvelle ligne avec une valeur id générée est ajoutée à mydataset.mytable.

Charger les données

Vous pouvez charger des données dans une table comportant une colonne d'identité à l'aide de la bq load commande ou de l' LOAD DATA instruction. Si la colonne d'identité est omise des données ou du schéma source, des valeurs sont générées pour celle-ci. Si la colonne d'identité est GENERATED ALWAYS AS IDENTITY, elle doit être omise.

L'exemple suivant charge des données à partir d'un fichier CSV data.csv dans mydataset.mytable. Le fichier ne contient que des données pour la colonne data :

"X"
"Y"

La commande bq load suivante charge data.csv dans mydataset.mytable, en omettant la ligne d'en-tête et en spécifiant uniquement la colonne data dans le schéma :

bq load --source_format=CSV --skip_leading_rows=0 \
mydataset.mytable data.csv data:STRING

La tâche de chargement génère des valeurs id pour les nouvelles lignes.

Supprimer la propriété de colonne d'identité

Vous pouvez supprimer la propriété d'identité d'une colonne à l'aide de l' ALTER TABLE ALTER COLUMN DROP GENERATED instruction LDD.

L'exemple suivant supprime les propriétés de colonne d'identité de la colonne id dans mydataset.mytable :

ALTER TABLE mydataset.mytable
ALTER COLUMN id DROP GENERATED;

Afficher des informations sur les colonnes d'identité

Pour afficher la configuration de la colonne d'identité d'une colonne, interrogez la INFORMATION_SCHEMA.COLUMNS vue.

L'exemple suivant affiche les informations de la colonne d'identité pour les colonnes de mydataset.mytable :

SELECT
  column_name,
  is_identity,
  identity_generation,
  identity_start,
  identity_increment
FROM
  mydataset.INFORMATION_SCHEMA.COLUMNS
WHERE
  table_name = 'mytable';

Le résultat ressemble à ce qui suit :

+-------------+-------------+---------------------+----------------+--------------------+
| column_name | is_identity | identity_generation | identity_start | identity_increment |
+-------------+-------------+---------------------+----------------+--------------------+
| id          | YES         | BY DEFAULT          | 100            | 10                 |
| data        | NO          | NULL                | NULL           | NULL               |
+-------------+-------------+---------------------+----------------+--------------------+

Vous pouvez également interroger la colonne ddl de la INFORMATION_SCHEMA.TABLES vue pour afficher la définition de la colonne d'identité dans l'instruction LDD CREATE TABLE d'une table.

Étape suivante