Modifier manuellement des schémas de table

Ce document explique comment modifier manuellement la définition de schéma pour les tables BigQuery existantes. De nombreuses modifications de schéma ne sont pas disponibles en natif dans BigQuery et nécessitent des solutions alternatives à effectuer manuellement. Ces modifications de schéma qui ne sont pas disponibles incluent les actions suivantes :

• Renommer une colonne
• Modifier le type de données d'une colonne
• Modifier le mode d'une colonne, en plus de l'assouplir de REQUIRED à NULLABLE
• Supprimer une colonne

Pour en savoir plus sur les modifications de schéma acceptées dans BigQuery, consultez la page Modifier des schémas de table.

Attention : Les options décrites sur cette page impliquent l'écrasement de la table ou le chargement des données dans une nouvelle table. Pour les tables partitionnées par date d'ingestion, ces opérations détruisent les informations de partitionnement d'origine. Après l'opération, toutes les données de la table résident dans une partition unique.

Renommer une colonne

Vous ne pouvez pas renommer de colonne à l'aide de la console, de l'outil de ligne de commande bq ou de l'API. Si vous essayez de mettre à jour un schéma de table à l'aide d'une colonne renommée, l'erreur suivante est renvoyée : BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table.

Deux options s'offrent à vous pour renommer manuellement une colonne :

• Utiliser une requête SQL. Choisissez cette option si vous privilégiez la simplicité et la facilité d'utilisation plutôt que les coûts.
• Recréer la table. Choisissez cette option si vous privilégiez les coûts plutôt que la simplicité et la facilité d'utilisation.

Option 1 : Utiliser des requêtes

Pour renommer une colonne à l'aide d'une requête SQL, sélectionnez toutes les colonnes de la table et attribuez un alias à la colonne que vous souhaitez renommer. Vous pouvez utiliser le résultat de la requête pour écraser la table existante ou créer une table de destination. Le nouveau nom de votre colonne doit respecter les règles de BigQuery relatives aux noms de colonnes.

Avantages

• L'utilisation d'une requête pour écrire les données dans une nouvelle table de destination conserve vos données d'origine.
• L'utilisation de la tâche de requête pour écraser la table d'origine entraîne des frais de stockage pour une table au lieu de deux, mais vous fait perdre les données d'origine.

Inconvénients

• Pour renommer une colonne à l'aide d'une requête, vous devez analyser l'intégralité de la table. Les frais de requête peuvent être importants si la table est très volumineuse.
• Si vous écrivez les résultats de la requête dans une nouvelle table de destination, vous serez soumis à des coûts de stockage pour l'ancienne et la nouvelle table (sauf si vous supprimez l'ancienne).
• Les colonnes renommées nécessaires peuvent avoir des valeurs nulles.

Exemple d'alias de colonne

L'exemple suivant montre une requête en SQL standard qui sélectionne toutes les données dans mytable, à l'exception des deux colonnes à renommer. Un alias sert à générer de nouveaux noms pour les deux colonnes. column_one est renommée newcolumn_one, et column_two est renommée newcolumn_two. Le résultat de la requête permet d'écraser la table existante.

Console

1. Dans la console, sélectionnez l'éditeur de requête.

2. Dans l'éditeur de requête, saisissez la requête suivante pour sélectionner toutes les données figurant dans mydataset.mytable, à l'exception des deux colonnes à renommer. mydataset.mytable se trouve dans votre projet par défaut. La requête utilise un alias pour renommer column_one en newcolumn_one et column_two en newcolumn_two.

   SELECT
    * EXCEPT(column_one, column_two),
    column_one AS newcolumn_one, column_two AS newcolumn_two
   FROM
    mydataset.mytable

3. Cliquez sur Plus et sélectionnez Paramètres de requête.

4. Dans la section Destination :

   1. Cochez la case Set a destination table for query results (Définir une table de destination pour les résultats de la requête).

   2. Dans le champ Ensemble de données, choisissez myproject.mydataset.

   3. Dans le champ ID de table, saisissez mytable.

   4. Dans la section Destination table write preference (Préférence d'écriture pour la table de destination), choisissez Overwrite table (Écraser la table). Cette action écrase mytable à l'aide des résultats de la requête.

5. Cliquez sur Enregistrer pour mettre à jour les paramètres, puis sur Exécuter dans l'éditeur de requête. Une fois la tâche de requête terminée, les colonnes de mytable sont renommées.

bq

Saisissez la commande bq query suivante pour sélectionner toutes les données de mydataset.mytable, à l'exception des deux colonnes à renommer. mydataset.mytable se trouve dans votre projet par défaut. La requête utilise un alias pour renommer column_one en newcolumn_one et column_two en newcolumn_two.

Écrivez les résultats de la requête dans mydataset.mytable à l'aide de l'option --destination_table, puis spécifiez l'option --replace pour écraser mytable. Spécifiez l'option use_legacy_sql=false pour utiliser la syntaxe SQL standard.

(Facultatif) Spécifiez l'option --location et définissez la valeur correspondant à votre emplacement.

bq query \
--destination_table mydataset.mytable \
--replace \
--use_legacy_sql=false \
'SELECT
  * EXCEPT(column_one,
    column_two),
  column_one AS newcolumn_one,
  column_two AS newcolumn_two
FROM
  mydataset.mytable'

API

Pour renommer column_one en newcolumn_one et column_two en newcolumn_two, appelez la méthode jobs.insert et configurez une tâche de requête (query). (Facultatif) Spécifiez l'emplacement dans la propriété location de la section jobReference.

La requête SQL utilisée dans la tâche de requête devrait ressembler à ceci : SELECT * EXCEPT(column_one, column_two), column_one AS newcolumn_one, column_two AS newcolumn_two FROM mydataset.mytable. Cette requête sélectionne toutes les données de mytable, à l'exception des deux colonnes à renommer. Un alias sert à générer de nouveaux noms pour les deux colonnes.

Pour écraser mytable avec les résultats de la requête, incluez mydataset.mytable dans la propriété configuration.query.destinationTable, puis spécifiez WRITE_TRUNCATE dans la propriété configuration.query.writeDisposition. Pour spécifier une nouvelle table de destination, saisissez son nom dans la propriété configuration.query.destinationTable.

Option 2 : Exporter et charger les données dans une nouvelle table

Vous pouvez également renommer une colonne en exportant les données de votre table dans Cloud Storage, puis en les chargeant dans une nouvelle table avec une définition de schéma contenant le nom de colonne approprié. Vous pouvez également utiliser la tâche de chargement pour écraser la table existante.

Avantages

• Les tâches d'exportation ou de chargement ne sont pas facturées. Actuellement, les tâches de chargement et d'exportation BigQuery sont gratuites.
• L'utilisation de la tâche de chargement pour écraser la table d'origine entraîne des frais de stockage pour une table au lieu de deux, mais vous fait perdre les données d'origine.

Inconvénients

• Si vous chargez les données dans une nouvelle table, vous serez soumis à des coûts de stockage pour la table d'origine et la nouvelle table (sauf si vous supprimez l'ancienne).
• Le stockage des données exportées dans Cloud Storage entraîne des frais.

Modifier le type de données d'une colonne

Vous ne pouvez pas modifier le type de données d'une colonne à l'aide de la console, de l'outil de ligne de commande bq ou de l'API. Si vous essayez de mettre à jour une table en appliquant un schéma qui spécifie un nouveau type de données pour une colonne, l'erreur suivante est renvoyée : BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table.

Deux options s'offrent à vous pour modifier manuellement le type de données d'une colonne :

• Utiliser une requête SQL. Choisissez cette option si vous privilégiez la simplicité et la facilité d'utilisation plutôt que les coûts.
• Recréer la table. Choisissez cette option si vous privilégiez les coûts plutôt que la simplicité et la facilité d'utilisation.

Option 1 : Utiliser des requêtes

Utilisez une requête SQL pour sélectionner toutes les données de la table et convertir la colonne en un type de données différent. Vous pouvez utiliser les résultats de la requête pour écraser la table ou créer une table de destination.

Avantages

• L'utilisation d'une requête pour écrire les données dans une nouvelle table de destination conserve vos données d'origine.
• L'utilisation de la tâche de requête pour écraser la table d'origine entraîne des frais de stockage pour une table au lieu de deux, mais vous fait perdre les données d'origine.

Inconvénients

• Pour modifier le type de données d'une colonne à l'aide d'une requête, vous devez analyser l'intégralité de la table. Les frais de requête peuvent être importants si la table est très volumineuse.
• Si vous écrivez les résultats de la requête dans une nouvelle table de destination, vous serez soumis à des coûts de stockage pour l'ancienne et la nouvelle table (sauf si vous supprimez l'ancienne).

Exemple CAST

L'exemple suivant montre une requête en SQL standard qui sélectionne toutes les données de column_two et column_three dans mydataset.mytable, et caste column_one de DATE en STRING. Le résultat de la requête permet d'écraser la table existante. La table écrasée stocke column_one en tant que type de données STRING.

En cas d'utilisation de CAST, une requête peut échouer si BigQuery est incapable d'effectuer le casting. Pour en savoir plus sur le casting de règles en SQL standard, consultez la section Casting dans la documentation de référence sur les fonctions et les opérateurs.

Console

1. Dans la console, cliquez sur Saisir une nouvelle requête.

2. Dans l'éditeur de requête, saisissez la requête suivante pour sélectionner toutes les données de column_two et column_three dans mydataset.mytable, ainsi que pour caster column_one de DATE en STRING. La requête utilise un alias pour caster column_one avec le même nom. mydataset.mytable se trouve dans votre projet par défaut.

   SELECT
    column_two, column_three, CAST(column_one AS STRING) AS column_one
   FROM
    mydataset.mytable

3. Cliquez sur More (Plus) et sélectionnez Query settings (Paramètres de requête).

4. Dans la section Destination, cochez la case Set a destination table for query results (Définir une table de destination pour les résultats de la requête).

5. Dans les champs suivants :

   1. Dans le champ Nom du projet, laissez la valeur définie sur votre projet par défaut. Il s'agit du projet contenant mydataset.mytable.

   2. Dans le champ Nom de l'ensemble de données, choisissez mydataset.

   3. Dans le champ Nom de la table, saisissez mytable.

   4. Cliquez sur OK.

6. Dans la section Destination table write preference (Préférence d'écriture pour la table de destination), choisissez Overwrite table (Écraser la table) comme préférence d'écriture. Cette action écrase mytable à l'aide des résultats de la requête.

7. (Facultatif) Dans le champ Zone de traitement, cliquez sur Sélection automatique et choisissez l'emplacement de vos données.

8. Cliquez sur Enregistrer pour mettre à jour les paramètres, puis sur Exécuter dans l'éditeur de requête. Une fois la tâche de requête terminée, le type de données de column_one devient STRING.

bq

Saisissez la commande bq query suivante pour sélectionner toutes les données de column_two et column_three dans mydataset.mytable, ainsi que pour caster column_one de DATE en STRING. La requête utilise un alias pour caster column_one avec le même nom. mydataset.mytable se trouve dans votre projet par défaut.

Les résultats de la requête sont écrits dans mydataset.mytable à l'aide de l'option --destination_table. L'option --replace permet quant à elle d'écraser mytable. Spécifiez l'option use_legacy_sql=false pour utiliser la syntaxe SQL standard.

(Facultatif) Spécifiez l'option --location et définissez la valeur correspondant à votre emplacement.

bq query \
--destination_table mydataset.mytable \
--replace \
--use_legacy_sql=false \
'SELECT
  column_two,
  column_three,
  CAST(column_one AS STRING) AS column_one
FROM
  mydataset.mytable'

API

Pour sélectionner toutes les données de column_two et column_three dans mydataset.mytable, et caster column_one de DATE en STRING, appelez la méthode jobs.insert et configurez une tâche de requête (query). (Facultatif) Spécifiez l'emplacement dans la propriété location de la section jobReference.

La requête SQL utilisée dans la tâche de requête devrait ressembler à ceci : SELECT column_two, column_three, CAST(column_one AS STRING) AS column_one FROM mydataset.mytable. La requête utilise un alias pour caster column_one avec le même nom.

Pour écraser mytable avec les résultats de la requête, incluez mydataset.mytable dans la propriété configuration.query.destinationTable, puis spécifiez WRITE_TRUNCATE dans la propriété configuration.query.writeDisposition.

Option 2 : Exporter et charger les données dans une nouvelle table

Vous pouvez également modifier le type de données d'une colonne en exportant les données de votre table dans Cloud Storage, puis en les chargeant dans une nouvelle table avec une définition de schéma spécifiant le type de données approprié pour la colonne. Vous pouvez également utiliser la tâche de chargement pour écraser la table existante.

Avantages

• Les tâches d'exportation ou de chargement ne sont pas facturées. Actuellement, les tâches de chargement et d'exportation BigQuery sont gratuites.
• L'utilisation de la tâche de chargement pour écraser la table d'origine entraîne des frais de stockage pour une table au lieu de deux, mais vous fait perdre les données d'origine.

Inconvénients

• Si vous chargez les données dans une nouvelle table, vous serez soumis à des coûts de stockage pour la table d'origine et la nouvelle table (sauf si vous supprimez l'ancienne).
• Le stockage des données exportées dans Cloud Storage entraîne des frais.

Modifier le mode d'une colonne

Actuellement, la seule modification qu'il est possible d'apporter au mode d'une colonne consiste à la passer du mode REQUIRED à NULLABLE. Changer le mode d'une colonne de REQUIRED en NULLABLE revient à assouplir cette colonne. Pour en savoir plus sur l'assouplissement d'une colonne REQUIRED en NULLABLE, consultez la section Assouplir le mode d'une colonne.

Si vous essayez d'appliquer une modification non compatible avec un mode de colonne, une erreur semblable à l'erreur suivante sera renvoyée. L'exemple suivant visait à changer le mode de colonne NULLABLE en REPEATED : BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table. Field field has changed mode from NULLABLE to REPEATED..

Exporter et charger les données dans une nouvelle table

Vous pouvez modifier manuellement le mode d'une colonne en exportant les données de votre table dans Cloud Storage, puis en les chargeant dans une nouvelle table avec une définition de schéma spécifiant le mode approprié pour la colonne. Vous pouvez également utiliser la tâche de chargement pour écraser la table existante.

Avantages

• Les tâches d'exportation ou de chargement ne sont pas facturées. Actuellement, les tâches de chargement et d'exportation BigQuery sont gratuites.
• L'utilisation de la tâche de chargement pour écraser la table d'origine entraîne des frais de stockage pour une table au lieu de deux, mais vous fait perdre les données d'origine.

Inconvénients

• Si vous chargez les données dans une nouvelle table, vous serez soumis à des coûts de stockage pour la table d'origine et la nouvelle table (sauf si vous supprimez l'ancienne).
• Le stockage des données exportées dans Cloud Storage entraîne des frais.

Supprimer des colonnes du schéma d'une table

Vous ne pouvez pas supprimer une colonne du schéma d'une table existante à l'aide de la console, de l'outil de ligne de commande bq ou de l'API. Si vous essayez de mettre à jour une table en appliquant un schéma qui supprime une colonne, l'erreur suivante est renvoyée : BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table.

Deux options s'offrent à vous pour supprimer manuellement une colonne :

• Utiliser une requête SQL. Choisissez cette option si vous privilégiez la simplicité et la facilité d'utilisation plutôt que les coûts.
• Recréer la table. Choisissez cette option si vous privilégiez les coûts plutôt que la simplicité et la facilité d'utilisation.

Option 1 : Utiliser des requêtes

Vous pouvez utiliser deux instructions SQL pour supprimer une colonne :

• SELECT * EXCEPT
• ALTER TABLE DROP COLUMN

La section suivante montre comment utiliser une requête SELECT * EXCEPT qui exclut la ou les colonnes que vous souhaitez supprimer, et comment utiliser le résultat de la requête pour écraser la table ou pour créer une table de destination.

Pour utiliser l'instruction ALTER TABLE DROP COLUMN, consultez les exemples de la page consacrée au Langage de définition de données.

Avantages

• L'utilisation d'une requête pour écrire les données dans une nouvelle table de destination conserve vos données d'origine.
• L'utilisation de la tâche de requête pour écraser la table d'origine entraîne des frais de stockage pour une table au lieu de deux, mais vous fait perdre les données d'origine.

Inconvénients

• La suppression d'une colonne à l'aide d'une requête nécessite d'analyser les données de toutes les colonnes, à l'exception de celle à supprimer. Les frais de requête peuvent être importants si la table est très volumineuse.
• Si vous écrivez les résultats de la requête dans une nouvelle table de destination, vous serez soumis à des coûts de stockage pour l'ancienne et la nouvelle table (sauf si vous supprimez l'ancienne).

Exemple SELECT * EXCEPT

L'exemple suivant montre une requête en SQL standard qui sélectionne toutes les données de mydataset.mytable, à l'exception de column_two. Le résultat de la requête permet d'écraser la table existante.

Console

1. Dans la console, cliquez sur Saisir une nouvelle requête.

2. Dans l'éditeur de requête, saisissez la requête suivante pour sélectionner toutes les données de mydataset.mytable, à l'exception de column_two. mydataset.mytable se trouve dans votre projet par défaut.

   SELECT
    * EXCEPT(column_two)
   FROM
    mydataset.mytable

3. Cliquez sur More (Plus) et sélectionnez Query settings (Paramètres de requête).

4. Dans la section Destination, cochez la case Set a destination table for query results (Définir une table de destination pour les résultats de la requête).

5. Dans les champs suivants :

   1. Dans le champ Nom du projet, laissez la valeur définie sur votre projet par défaut. Il s'agit du projet contenant mydataset.mytable.

   2. Dans le champ Nom de l'ensemble de données, choisissez mydataset.

   3. Dans le champ Nom de la table, saisissez mytable.

   4. Cliquez sur OK.

6. Dans la section Destination table write preference (Préférence d'écriture pour la table de destination), choisissez Overwrite table (Écraser la table) comme préférence d'écriture. Cette action écrase mytable à l'aide des résultats de la requête.

7. (Facultatif) Dans le champ Zone de traitement, cliquez sur Sélection automatique et choisissez l'emplacement de vos données.

8. Cliquez sur Enregistrer pour mettre à jour les paramètres, puis sur Exécuter dans l'éditeur de requête. Une fois la tâche de requête terminée, la table inclut désormais toutes les colonnes sauf column_two.

bq

Saisissez la commande bq query suivante pour sélectionner toutes les données de mydataset.mytable, à l'exception de column_two. mydataset.mytable se trouve dans votre projet par défaut. Les résultats de la requête sont écrits dans mydataset.mytable à l'aide de l'option --destination_table. L'option --replace permet quant à elle d'écraser mytable. Spécifiez l'option use_legacy_sql=false pour utiliser la syntaxe SQL standard.

(Facultatif) Spécifiez l'option --location et définissez la valeur correspondant à votre emplacement.

bq query \
--destination_table mydataset.mytable \
--replace \
--use_legacy_sql=false \
'SELECT
  * EXCEPT(column_two)
FROM
  mydataset.mytable'

API

Pour sélectionner toutes les données de mydataset.mytable sauf column_two, appelez la méthode jobs.insert et configurez une tâche de requête (query). (Facultatif) Spécifiez l'emplacement dans la propriété location de la section jobReference.

La requête SQL utilisée dans la tâche de requête devrait ressembler à ceci : SELECT * EXCEPT(column_two) FROM mydataset.mytable.

Pour écraser mytable avec les résultats de la requête, incluez mydataset.mytable dans la propriété configuration.query.destinationTable, puis spécifiez WRITE_TRUNCATE dans la propriété configuration.query.writeDisposition.

Option 2 : Exporter et charger les données dans une nouvelle table

Vous pouvez également supprimer une ou plusieurs colonnes en exportant les données de votre table dans Cloud Storage, en supprimant les données correspondant aux colonnes à supprimer, puis en chargeant les données restantes dans une nouvelle table avec une définition de schéma qui n'inclut pas les colonnes supprimées. Vous pouvez également utiliser la tâche de chargement pour écraser la table existante.

Avantages

• Les tâches d'exportation ou de chargement ne sont pas facturées. Actuellement, les tâches de chargement et d'exportation BigQuery sont gratuites.
• L'utilisation de la tâche de chargement pour écraser la table d'origine entraîne des frais de stockage pour une table au lieu de deux, mais vous fait perdre les données d'origine.

Inconvénients

• Si vous chargez les données dans une nouvelle table, vous serez soumis à des coûts de stockage pour la table d'origine et la nouvelle table (sauf si vous supprimez l'ancienne).
• Le stockage des données exportées dans Cloud Storage entraîne des frais.

Sécurité des tables

Pour savoir comment contrôler l'accès aux tables dans BigQuery, consultez la page Présentation des contrôles d'accès aux tables.