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 non 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 le passer de REQUIRED à NULLABLE
  • Supprimer une colonne

Pour en savoir plus sur les modifications de schéma compatibles avec BigQuery, consultez la section Modifier des schémas de table.

Renommer une colonne

L'UI Web de BigQuery, l'outil de ligne de commande et l'API ne permettent pas de renommer une colonne. 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 :

  • En utilisant une requête SQL - Choisissez cette option si vous privilégiez la simplicité et la facilité d'utilisation aux coûts.
  • En recréant une table - Choisissez cette option si vous privilégiez les coûts à la simplicité et la facilité d'utilisation.

Option 1 : Utiliser une requête

Pour renommer une colonne à l'aide d'une requête SQL, sélectionnez toutes les colonnes du tableau 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 colonne.

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

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

Vous pouvez également renommer une colonne en exportant vos données de table dans Google Cloud Storage, puis en chargeant les données 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.

Exemple d'alias de colonne

L'exemple suivant montre une requête SQL standard qui sélectionne toutes les données dans mytable, à l'exception des deux colonnes à renommer. Un alias permet de 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.

UI Web

  1. Dans l'interface Web BigQuery, cliquez sur Saisir une requête.

  2. Dans la zone Nouvelle requête, saisissez la requête 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.

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

  3. Cliquez sur Afficher les options.

  4. Dans la section Table de destination, cliquez sur Sélectionner une table.

  5. Dans la boîte de dialogue Sélectionner une table de destination :

    1. Dans le champ Projet, conservez la valeur définie pour votre projet par défaut. Il s'agit du projet contenant mydataset.mytable.

    2. Dans le champ Ensemble de données, sélectionnez mydataset.

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

    4. Cliquez sur OK.

  6. Dans la section Table de destination, pour Préférence d'écriture, sélectionnez Écraser la table. Cette action écrasera mytable à l'aide des résultats de la requête.

  7. Dans le champ Zone de traitement, cliquez sur Non spécifiée et sélectionnez l'emplacement de vos données. Vous pouvez laisser le champ zone de traitement définie sur Non spécifiée si vos données se trouvent dans la zone multirégionale US ou EU. Lorsque vos données sont dans la zone US ou EU, la zone de traitement est automatiquement détectée.

  8. Cliquez sur Exécuter la requête. Une fois la tâche de requête terminée, les colonnes de mytable sont renommées.

CLI

Saisissez la commande bq query suivante pour sélectionner toutes les données 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.

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

Définissez l'indicateur --location sur la valeur correspondant à votre emplacement.

bq --location=[LOCATION] 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. Spécifiez votre région 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 dans mytable, à l'exception des deux colonnes à renommer. Un alias sert à générer de nouveaux noms pour les deux colonnes.

Pour remplacer mytable par 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 le nom de la table dans la propriété configuration.query.destinationTable.

Modifier le type de données d'une colonne

L'UI Web de BigQuery, l'outil de ligne de commande et l'API ne permettent pas de changer le type de données d'une colonne. 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 :

  • En utilisant une requête SQL - Choisissez cette option si vous privilégiez la simplicité et la facilité d'utilisation aux coûts.
  • En recréant une table - Choisissez cette option si vous privilégiez les coûts à la simplicité et la facilité d'utilisation.

Option 1 : Utiliser une requête

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 changer 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).

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

Vous pouvez également changer le type de données d'une colonne en exportant vos données de table dans Google Cloud Storage, puis en chargeant les données 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.

Exemple CAST

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

Lors de l'utilisation de CAST, une requête peut échouer si BigQuery est incapable d'effectuer la conversion. Pour en savoir plus sur la conversion de règles en SQL standard, consultez la section Conversion dans la documentation de référence relative aux fonctions et opérateurs.

UI Web

  1. Dans l'interface Web BigQuery, cliquez sur Saisir une requête.

  2. Dans la zone Nouvelle requête, saisissez la requête suivante pour sélectionner toutes les données de column_two et de column_three dans mydataset.mytable, et pour convertir column_one de DATE à 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.

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

  3. Cliquez sur Afficher les options.

  4. Dans la section Table de destination, cliquez sur Sélectionner une table.

  5. Dans la boîte de dialogue Sélectionner une table de destination :

    1. Dans le champ Projet, conservez la valeur définie pour votre projet par défaut. Il s'agit du projet contenant mydataset.mytable.

    2. Dans le champ Ensemble de données, sélectionnez mydataset.

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

    4. Cliquez sur OK.

  6. Dans la section Table de destination, pour Préférence d'écriture, sélectionnez Écraser la table. Cette action écrasera mytable à l'aide des résultats de la requête.

  7. Dans le champ Zone de traitement, cliquez sur Non spécifiée et sélectionnez l'emplacement de vos données. Vous pouvez laisser le champ zone de traitement définie sur Non spécifiée si vos données se trouvent dans la zone multirégionale US ou EU. Lorsque vos données sont dans la zone US ou EU, la zone de traitement est automatiquement détectée.

  8. Cliquez sur Exécuter la requête. Une fois la tâche de requête terminée, le type de données de column_one devient STRING.

CLI

Renseignez la commande suivante bq query pour sélectionner toutes les données de column_two et column_three dans mydataset.mytable, et pour convertir column_one de DATE à STRING. La requête utilise un alias pour convertir 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 du paramètre --destination_table. Le paramètre --replace permet d'écraser mytable. Spécifiez le paramètre use_legacy_sql=false pour utiliser la syntaxe SQL standard.

Définissez l'indicateur --location sur la valeur correspondant à votre emplacement.

bq --location=[LOCATION] 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 de column_three dans mydataset.mytable, et pour caster column_one de DATE à STRING, appelez la méthode jobs.insert et configurez une tâche de requête. Spécifiez votre région 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 remplacer mytable par 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.

Modifier le mode d'une colonne

Actuellement, la seule modification possible de mode de colonne consiste à la passer du mode REQUIRED à NULLABLE. Changer le mode d'une colonne de REQUIRED à NULLABLE revient à convertir ces colonnes. Pour en savoir plus sur la conversion d'une colonne REQUIRED en NULLABLE, consultez la section Modifier 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 vise à convertir 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 vos données et les charger dans une nouvelle table

Vous pouvez changer manuellement le mode d'une colonne en exportant vos données de table dans Google Cloud Storage, puis en chargeant les données 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 une colonne d'un schéma de table

L'UI Web de BigQuery, l'outil de ligne de commande et l'API ne permettent pas de supprimer une colonne du schéma d'une table existante. 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 :

  • En utilisant une requête SQL - Choisissez cette option si vous privilégiez la simplicité et la facilité d'utilisation aux coûts.
  • En recréant une table - Choisissez cette option si vous privilégiez les coûts à la simplicité et la facilité d'utilisation.

Option 1 : Utiliser une requête

Utilisez une requête SELECT * EXCEPT qui exclut la colonne (ou les colonnes) à supprimer, puis servez-vous du résultat 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

  • La suppression d'une colonne à l'aide d'une requête nécessite l'analyse des données dans 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).

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

Vous pouvez également supprimer une ou plusieurs colonnes en exportant vos données de table dans Google 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 n'incluant 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.

Exemple SELECT * EXCEPT

L'exemple suivant montre une requête 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.

UI Web

  1. Dans l'interface Web BigQuery, cliquez sur Saisir une requête.

  2. Dans la zone Nouvelle 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.

       #standardSQL
       SELECT
         * EXCEPT(column_two)
       FROM
         mydataset.mytable
       

  3. Cliquez sur Afficher les options.

  4. Dans la section Table de destination, cliquez sur Sélectionner une table.

  5. Dans la boîte de dialogue Sélectionner une table de destination :

    1. Dans le champ Projet, conservez la valeur définie pour votre projet par défaut. Il s'agit du projet contenant mydataset.mytable.

    2. Dans le champ Ensemble de données, sélectionnez mydataset.

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

    4. Cliquez sur OK.

  6. Dans la section Table de destination, pour Préférence d'écriture, sélectionnez Écraser la table. Cette action écrasera mytable à l'aide des résultats de la requête.

  7. Dans le champ Zone de traitement, cliquez sur Non spécifiée et sélectionnez l'emplacement de vos données. Vous pouvez laisser le champ zone de traitement définie sur Non spécifiée si vos données se trouvent dans la zone multirégionale US ou EU. Lorsque vos données sont dans la zone US ou EU, la zone de traitement est automatiquement détectée.

  8. Cliquez sur Exécuter la requête. Une fois la tâche de requête terminée, la table inclut toutes les colonnes à l'exception de column_two.

CLI

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 du paramètre --destination_table. Le paramètre --replace permet d'écraser mytable. Spécifiez le paramètre use_legacy_sql=false pour utiliser la syntaxe SQL standard.

Définissez l'indicateur --location sur la valeur correspondant à votre emplacement.

bq --location=[LOCATION] 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 à l'exception de column_two, appelez la méthode jobs.insert et configurez une tâche de requête. Spécifiez votre région 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 remplacer mytable par 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.

Cette page vous a-t-elle été utile ? Évaluez-la :

Envoyer des commentaires concernant…

Besoin d'aide ? Consultez notre page d'assistance.