Modifier des schémas de table

Ce document décrit comment modifier les définitions de schéma pour les tables BigQuery existantes. De façon native, BigQuery permet d'effectuer les modifications de schéma suivantes :

  • Ajouter des colonnes à une définition de schéma
  • Assouplir le mode d'une colonne de REQUIRED à NULLABLE

Il est possible de créer une table sans définir de schéma initial et d'ajouter ultérieurement une définition de schéma à la table.

Les autres modifications de schéma ne peuvent pas être gérées par BigQuery et nécessitent toutes des solutions de contournement manuelles. Il s'agit, entre autres, des modifications 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 non gérées par BigQuery nécessitant des solutions de contournement, consultez l'article Modifier manuellement des schémas de table.

Ajouter des colonnes à la définition de schéma d'une table

Vous pouvez ajouter des colonnes à la définition de schéma d'une table existante :

  • manuellement, en créant une colonne vide ;
  • lorsque vous utilisez une tâche de chargement ou de requête pour écraser une table ;
  • lorsque vous ajoutez des données à une table à l'aide d'une tâche de chargement ou de requête.

Toute colonne que vous ajoutez doit respecter les règles de BigQuery pour les noms de colonnes. Pour en savoir plus sur la création de composants de schéma, consultez l'article Spécifier un schéma.

Ajouter manuellement une colonne vide

Pour ajouter une colonne vide à une table existante :

  • utilisez la console GCP ou l'interface utilisateur Web classique de BigQuery ;
  • En utilisant la commande bq update de l'outil de ligne de commande
  • appelez la méthode API tables.patch ;
  • utilisez les bibliothèques clientes.

Si vous ajoutez des colonnes à un schéma de table existant, celles-ci doivent être définies comme NULLABLE ou REPEATED. Vous ne pouvez pas ajouter une colonne REQUIRED à un schéma de table existant. Si vous essayez d'ajouter une colonne REQUIRED à un schéma de table existant dans la CLI ou l'API, l'erreur suivante est renvoyée : BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table. Cannot add required columns to an existing schema. Vous ne pouvez ajouter des colonnes définies comme REQUIRED que lorsque vous créez une table lors du chargement des données ou lorsque vous créez une table vide avec une définition de schéma.

Après avoir ajouté une nouvelle colonne à la définition de schéma d'une table, vous pouvez charger des données dans la nouvelle colonne en utilisant les éléments suivants :

Pour ajouter des colonnes vides à la définition de schéma d'une table, procédez comme suit :

Console

  1. Dans le volet Ressources, sélectionnez la table.

  2. Sous l'éditeur de requête, faites défiler la page jusqu'en bas de la section Schema (Schéma), puis cliquez sur Edit schema (Modifier le schéma).

Modifier le schéma de la table

  1. Faites défiler la page jusqu'en bas du panneau, puis cliquez sur Ajouter un champ.

    • Saisissez le nom de la colonne dans le champ Name (Nom).
    • Choisissez le type de données dans le champ Type.
    • Dans le champ Mode, choisissez NULLABLE ou REPEATED.
  2. Lorsque vous avez terminé d'ajouter des colonnes, cliquez sur Enregistrer.

Interface utilisateur classique

  1. Dans le volet de navigation, sélectionnez la table.

  2. Sur la page Détails de la table, cliquez sur Ajouter des champs.

  3. Dans la section New Fields (Nouveaux champs) :

    • Saisissez le nom de la colonne dans le champ Name (Nom).
    • Choisissez le type de données dans le champ Type.
    • Dans le champ Mode, choisissez NULLABLE ou REPEATED.

      Mettre à jour le schéma de la table

  4. Lorsque vous avez terminé d'ajouter des colonnes, cliquez sur Ajouter à la table.

CLI

Exécutez la commande bq update et fournissez un fichier de schéma JSON. Si la table que vous mettez à jour se trouve dans un projet qui n'est pas celui par défaut, ajoutez l'ID du projet au nom de l'ensemble de données en utilisant le format suivant : project_id:dataset.

bq update project_id:dataset.table schema

Où :

  • project_id est l'ID du projet.
  • dataset est le nom de l'ensemble de données qui contient la table que vous mettez à jour.
  • table est le nom de la table que vous mettez à jour.
  • schema est le chemin d'accès du fichier de schéma JSON sur votre ordinateur local.

Lorsque vous spécifiez le schéma sur la ligne de commande, vous ne pouvez pas inclure de type RECORD (STRUCT) ni de description de colonne. Vous ne pouvez pas non plus spécifier le mode de la colonne. Tous les modes par défaut sont définis comme NULLABLE.

Si vous essayez d'ajouter des colonnes à l'aide d'une définition de schéma intégrée, vous devez fournir l'intégralité de la définition de schéma, y compris les nouvelles colonnes. Étant donné que vous ne pouvez pas indiquer de mode de colonne à l'aide d'une définition de schéma intégrée, le processus de mise à jour tente de faire passer les colonnes existantes du mode REQUIRED au mode NULLABLE. L'erreur suivante est alors renvoyée : BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table. Field field has changed mode from REPEATED to NULLABLE.

Pour ajouter des colonnes à une table existante à l'aide de la CLI, il est préférable de fournir un fichier de schéma JSON.

Pour ajouter des colonnes vides au schéma d'une table à l'aide d'un fichier de schéma JSON :

  1. Tout d'abord, exécutez la commande bq show avec l'indicateur --schema et écrivez le schéma de table existant dans un fichier. Si la table que vous mettez à jour se trouve dans un projet qui n'est pas celui par défaut, ajoutez l'ID du projet au nom de l'ensemble de données en utilisant le format suivant : project_id:dataset.

    bq show \
    --schema \
    --format=prettyjson \
    project_id:dataset.table > schema_file
    

    Où :

    • project_id est l'ID du projet.
    • dataset est le nom de l'ensemble de données qui contient la table que vous mettez à jour.
    • table est le nom de la table que vous mettez à jour.
    • schema_file est le fichier de définition de schéma écrit sur votre ordinateur local.

    Par exemple, pour écrire la définition de schéma de mydataset.mytable dans un fichier, saisissez la commande suivante. mydataset.mytable se situe dans votre projet par défaut.

       bq show \
       --schema \
       --format=prettyjson \
       mydataset.mytable > /tmp/myschema.json
    
  2. Ouvrez le fichier de schéma dans un éditeur de texte. Le fichier doit se présenter comme suit :

    [
      {
        "mode": "REQUIRED",
        "name": "column1",
        "type": "STRING"
      },
      {
        "mode": "REQUIRED",
        "name": "column2",
        "type": "FLOAT"
      },
      {
        "mode": "REPEATED",
        "name": "column3",
        "type": "STRING"
      }
    ]
    
  3. Ajoutez les colonnes à la fin de la définition de schéma. Si vous tentez d'ajouter des colonnes ailleurs dans le tableau, l'erreur suivante est renvoyée : BigQuery error in update operation: Precondition Failed.

    Avec un fichier JSON, vous pouvez spécifier des descriptions, des modes NULLABLE ou REPEATED, et des types RECORD pour les nouvelles colonnes. Par exemple, en utilisant la définition de schéma de l'étape précédente, votre nouveau tableau JSON devrait se présenter comme suit. Dans cet exemple, une colonne NULLABLE nommée column4 est ajoutée. Cette colonne column4 comprend une description.

      [
        {
          "mode": "REQUIRED",
          "name": "column1",
          "type": "STRING"
        },
        {
          "mode": "REQUIRED",
          "name": "column2",
          "type": "FLOAT"
        },
        {
          "mode": "REPEATED",
          "name": "column3",
          "type": "STRING"
        },
        {
          "description": "my new column",
          "mode": "NULLABLE",
          "name": "column4",
          "type": "STRING"
        }
      ]
      

    Pour en savoir plus sur l'utilisation des fichiers de schéma JSON, consultez l'article Spécifier un fichier de schéma JSON.

  4. Après avoir mis à jour votre fichier de schéma, exécutez la commande suivante pour mettre à jour le schéma de la table. Si la table que vous mettez à jour se trouve dans un projet qui n'est pas celui par défaut, ajoutez l'ID du projet au nom de l'ensemble de données en utilisant le format suivant : project_id:dataset.

    bq update project_id:dataset.table schema
    

    Où :

    • project_id est l'ID du projet.
    • dataset est le nom de l'ensemble de données qui contient la table que vous mettez à jour.
    • table est le nom de la table que vous mettez à jour.
    • schema est le chemin d'accès du fichier de schéma JSON sur votre ordinateur local.

    Par exemple, saisissez la commande suivante pour mettre à jour la définition de schéma de mydataset.mytable dans votre projet par défaut. Le chemin d'accès au fichier de schéma sur votre ordinateur local est le suivant : /tmp/myschema.json.

    bq update mydataset.mytable /tmp/myschema.json
    

API

Appelez la méthode tables.patch, puis utilisez la propriété schema pour ajouter des colonnes vides à votre définition de schéma. Étant donné que la méthode tables.update remplace l'intégralité de la ressource de table, la méthode tables.patch est préférable.

Go

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
tableRef := client.Dataset(datasetID).Table(tableID)
meta, err := tableRef.Metadata(ctx)
if err != nil {
	return err
}
newSchema := append(meta.Schema,
	&bigquery.FieldSchema{Name: "phone", Type: bigquery.StringFieldType},
)
update := bigquery.TableMetadataToUpdate{
	Schema: newSchema,
}
if _, err := tableRef.Update(ctx, update, meta.ETag); err != nil {
	return err
}

Python

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Python décrite dans le guide de démarrage rapide de BigQuery relatif à l'utilisation des bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery Python.

Ajoutez un nouvel objet SchemaField à une copie de la propriété Table.schema, puis remplacez la valeur de la propriété Table.schema par le schéma mis à jour.

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'my_dataset'
# table_id = 'my_table'

table_ref = client.dataset(dataset_id).table(table_id)
table = client.get_table(table_ref)  # API request

original_schema = table.schema
new_schema = original_schema[:]  # creates a copy of the schema
new_schema.append(bigquery.SchemaField("phone", "STRING"))

table.schema = new_schema
table = client.update_table(table, ["schema"])  # API request

assert len(table.schema) == len(original_schema) + 1 == len(new_schema)

Ajout d'une colonne imbriquée à un type RECORD

En plus d'ajouter des colonnes au schéma d'une table, vous pouvez également ajouter des colonnes imbriquées à un type RECORD. Le processus d'ajout des colonnes imbriquées est très similaire à celui des colonnes classiques.

Console

L'ajout d'un champ imbriqué à une colonne RECORD existante n'est actuellement pas possible dans la console GCP.

Interface utilisateur classique

L'ajout d'un champ imbriqué à une colonne RECORD existante n'est actuellement pas possible dans l'interface utilisateur Web de BigQuery.

CLI

Exécutez la commande bq update et fournissez un fichier de schéma JSON qui ajoute le champ imbriqué à la définition de schéma de la colonne RECORD existante. Si la table que vous mettez à jour se trouve dans un projet qui n'est pas celui par défaut, ajoutez l'ID du projet au nom de l'ensemble de données en utilisant le format suivant : project_id:dataset.

bq update project_id:dataset.table schema

Où :

  • project_id est l'ID du projet.
  • dataset est le nom de l'ensemble de données qui contient la table que vous mettez à jour.
  • table est le nom de la table que vous mettez à jour.
  • schema est le chemin d'accès du fichier de schéma JSON sur votre ordinateur local.

Lorsque vous spécifiez le schéma sur la ligne de commande, vous ne pouvez pas inclure de type RECORD (STRUCT) ni de description de colonne. Vous ne pouvez pas non plus spécifier le mode de la colonne. Tous les modes sont définis par défaut sur NULLABLE. Par conséquent, si vous ajoutez une nouvelle colonne imbriquée à un type RECORD, vous devez fournir un fichier de schéma JSON.

Pour ajouter une colonne imbriquée à un type RECORD en utilisant un fichier de schéma JSON, procédez comme suit :

  1. Tout d'abord, exécutez la commande bq show avec l'indicateur --schema et écrivez le schéma de table existant dans un fichier. Si la table que vous mettez à jour se trouve dans un projet autre que celui par défaut, ajoutez l'ID du projet au nom de l'ensemble de données en utilisant le format suivant : project_id:dataset.table.

    bq show \
    --schema \
    --format=prettyjson \
    project_id:dataset.table > schema_file
    

    Où :

    • project_id est l'ID du projet.
    • dataset est le nom de l'ensemble de données qui contient la table que vous mettez à jour.
    • table est le nom de la table que vous mettez à jour.
    • schema_file est le fichier de définition de schéma écrit sur votre ordinateur local.

    Par exemple, pour écrire la définition de schéma de mydataset.mytable dans un fichier, saisissez la commande suivante. mydataset.mytable se situe dans votre projet par défaut.

    bq show \
    --schema \
    --format=prettyjson \
    mydataset.mytable > /tmp/myschema.json
    
  2. Ouvrez le fichier de schéma dans un éditeur de texte. Le fichier doit se présenter comme suit. Dans cet exemple, column3 correspond à une colonne imbriquée répétée. Les colonnes imbriquées correspondent à nested1 et nested2. Le tableau fields répertorie les champs imbriqués dans column3.

    [
      {
        "mode": "REQUIRED",
        "name": "column1",
        "type": "STRING"
      },
      {
        "mode": "REQUIRED",
        "name": "column2",
        "type": "FLOAT"
      },
      {
        "fields": [
          {
            "mode": "NULLABLE",
            "name": "nested1",
            "type": "STRING"
          },
          {
            "mode": "NULLABLE",
            "name": "nested2",
            "type": "STRING"
          }
        ],
        "mode": "REPEATED",
        "name": "column3",
        "type": "RECORD"
      }
    ]
    
  3. Ajoutez la colonne imbriquée à la fin du tableau fields. Dans cet exemple, nested3 correspond à la nouvelle colonne imbriquée.

      [
        {
          "mode": "REQUIRED",
          "name": "column1",
          "type": "STRING"
        },
        {
          "mode": "REQUIRED",
          "name": "column2",
          "type": "FLOAT"
        },
        {
          "fields": [
            {
              "mode": "NULLABLE",
              "name": "nested1",
              "type": "STRING"
            },
            {
              "mode": "NULLABLE",
              "name": "nested2",
              "type": "STRING"
            },
            {
              "mode": "NULLABLE",
              "name": "nested3",
              "type": "STRING"
            }
          ],
          "mode": "REPEATED",
          "name": "column3",
          "type": "RECORD"
        }
      ]
      

    Pour en savoir plus sur l'utilisation des fichiers de schéma JSON, consultez l'article Spécifier un fichier de schéma JSON.

  4. Après avoir mis à jour votre fichier de schéma, exécutez la commande suivante pour mettre à jour le schéma de la table. Si la table que vous mettez à jour se trouve dans un projet qui n'est pas celui par défaut, ajoutez l'ID du projet au nom de l'ensemble de données en utilisant le format suivant : project_id:dataset.

    bq update project_id:dataset.table schema
    

    Où :

    • project_id est l'ID du projet.
    • dataset est le nom de l'ensemble de données qui contient la table que vous mettez à jour.
    • table est le nom de la table que vous mettez à jour.
    • schema est le chemin d'accès du fichier de schéma JSON sur votre ordinateur local.

    Par exemple, saisissez la commande suivante pour mettre à jour la définition de schéma de mydataset.mytable dans votre projet par défaut. Le chemin d'accès au fichier de schéma sur votre ordinateur local est le suivant : /tmp/myschema.json.

    bq update mydataset.mytable /tmp/myschema.json
    

API

Appelez la méthode tables.patch, puis utilisez la propriété schema pour ajouter des colonnes imbriquées à votre définition de schéma. Étant donné que la méthode tables.update remplace l'intégralité de la ressource de table, il est préférable d'utiliser la méthode tables.patch.

Ajouter une colonne lorsque vous écrasez ou ajoutez des données

Vous pouvez ajouter des colonnes à une table existante lorsque vous y chargez des données et choisissez d'écraser cette table. Le schéma des données chargées est alors utilisé pour écraser le schéma de la table existante. Pour en savoir plus sur le remplacement d'une table à l'aide d'une tâche de chargement, consultez les sections suivantes :

Vous pouvez également ajouter des colonnes à une table existante lorsque vous y ajoutez des données à l'aide d'une tâche de chargement ou de requête.

Ajouter une colonne dans une tâche de chargement ajoutant des données

Pour ajouter des colonnes à une table lorsque vous ajoutez des données à cette dernière via une tâche de chargement :

  • utilisez la commande bq load de l'outil de ligne de commande ;
  • appelez la méthode API jobs.insert, puis configurez une tâche load ;
  • utilisez les bibliothèques clientes.

L'ajout d'une colonne à une table existante lors d'une opération d'ajout n'est actuellement pas possible dans la console GCP ou l'interface utilisateur Web classique de BigQuery.

Lorsque vous ajoutez des colonnes à l'aide d'une opération d'ajout dans une tâche de chargement, le schéma mis à jour peut être :

  • détecté automatiquement, pour les fichiers CSV et JSON ;
  • spécifié dans un fichier de schéma JSON, pour les fichiers CSV et JSON ;
  • récupéré à partir des données source autodescriptives pour les fichiers d'exportation Avro, ORC, Parquet et Cloud Datastore.

Si vous spécifiez le schéma dans un fichier JSON, les nouvelles colonnes doivent y être définies. Si les nouvelles définitions de colonnes ne sont pas indiquées, l'erreur suivante est renvoyée lorsque vous tentez d'ajouter des données : Error while reading data, error message: parsing error in row starting at position int: No such field: field..

Lorsque vous ajoutez des colonnes lors d'une opération d'ajout, les valeurs des nouvelles colonnes sont définies sur NULL pour les lignes existantes.

Lors d'une tâche de chargement, vous pouvez ajouter une colonne pendant l'ajout de données à une table. Pour ce faire, procédez comme suit :

Console

Vous ne pouvez pas ajouter de colonnes à une table existante lorsque vous chargez des données en utilisant la console GCP.

Interface utilisateur classique

Vous ne pouvez pas ajouter de colonnes à une table existante lorsque vous chargez des données en utilisant l'interface utilisateur Web de BigQuery.

CLI

Utilisez la commande bq load pour charger vos données et spécifiez l'indicateur --noreplace pour signaler que vous ajoutez les données à une table existante.

Si les données que vous ajoutez sont au format JSON délimité par un retour à la ligne ou CSV, spécifiez l'indicateur --autodetect pour utiliser la détection automatique de schéma ou fournissez le schéma dans un fichier de schéma JSON. Les colonnes ajoutées peuvent être obtenues automatiquement à partir des fichiers d'exportation Avro ou Cloud Datastore.

Définissez l'indicateur --schema_update_option sur ALLOW_FIELD_ADDITION pour indiquer que les données que vous ajoutez contiennent de nouvelles colonnes.

Si la table que vous ajoutez se trouve dans un ensemble de données d'un projet qui n'est pas celui par défaut, ajoutez l'ID du projet au nom de l'ensemble de données en utilisant le format suivant : project_id:dataset.

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

Saisissez la commande load comme suit :

bq --location=location load \
--noreplace \
--autodetect \
--schema_update_option=ALLOW_FIELD_ADDITION \
--source_format=format \
project_id:dataset.table \
path_to_source \
schema

Où :

  • location est le nom de votre emplacement. L'indicateur --location est facultatif. Par exemple, si vous utilisez BigQuery dans la région de Tokyo, définissez la valeur de l'indicateur sur asia-northeast1. Vous pouvez définir une valeur par défaut correspondant à l'emplacement en utilisant le fichier .bigqueryrc.
  • format est NEWLINE_DELIMITED_JSON, CSV, AVRO, PARQUET, ORC ou DATASTORE_BACKUP.
  • project_id est l'ID du projet.
  • dataset est le nom de l'ensemble de données contenant la table.
  • table est le nom de la table que vous ajoutez.
  • path_to_source correspond à un URI Cloud Storage complet, à une liste d'URI séparés par une virgule ou au chemin d'accès d'un fichier de données se trouvant sur votre ordinateur local.
  • schema correspond au chemin d'accès vers un fichier de schéma JSON local. Un fichier de schéma n'est requis que pour les fichiers CSV et JSON lorsque l'indicateur --autodetect n'est pas spécifié. Les schémas Avro et Cloud Datastore sont obtenus à partir des données sources.

Exemples :

Saisissez la commande suivante pour ajouter un fichier de données Avro local, appelé /tmp/mydata.avro, à mydataset.mytable en utilisant une tâche de chargement. Comme les schémas peuvent être automatiquement obtenus à partir des données Avro, l'indicateur --autodetect n'est pas nécessaire. L'élément mydataset se trouve dans votre projet par défaut.

bq load \
--noreplace \
--schema_update_option=ALLOW_FIELD_ADDITION \
--source_format=AVRO \
mydataset.mytable \
/tmp/mydata.avro

Saisissez la commande suivante pour ajouter un fichier de données JSON délimité par un retour à la ligne dans Cloud Storage à mydataset.mytable en utilisant une tâche de chargement. L'indicateur --autodetect permet de détecter les nouvelles colonnes. L'élément mydataset se trouve dans votre projet par défaut.

bq load \
--noreplace \
--autodetect \
--schema_update_option=ALLOW_FIELD_ADDITION \
--source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable \
gs://mybucket/mydata.json

Saisissez la commande suivante pour ajouter un fichier de données JSON délimité par un retour à la ligne dans Cloud Storage à mydataset.mytable en utilisant une tâche de chargement. Le schéma contenant les nouvelles colonnes est spécifié dans un fichier de schéma JSON local, /tmp/myschema.json. L'élément mydataset se trouve dans myotherproject, et non dans votre projet par défaut.

bq load \
--noreplace \
--schema_update_option=ALLOW_FIELD_ADDITION \
--source_format=NEWLINE_DELIMITED_JSON \
myotherproject:mydataset.mytable \
gs://mybucket/mydata.json \
/tmp/myschema.json

API

Appelez la méthode jobs.insert. Configurez une tâche de load et définissez les propriétés suivantes :

  • Référencez vos données dans Cloud Storage à l'aide de la propriété sourceUris.
  • Spécifiez le format de données en définissant la propriété sourceFormat.
  • Spécifiez le schéma dans la propriété schema.
  • Spécifiez l'option de mise à jour de schéma à l'aide de la propriété schemaUpdateOptions.
  • Définissez la disposition d'écriture de la table de destination sur WRITE_APPEND à l'aide de la propriété writeDisposition.

Go

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
sampleSchema := bigquery.Schema{
	{Name: "full_name", Type: bigquery.StringFieldType},
}
meta := &bigquery.TableMetadata{
	Schema: sampleSchema,
}
tableRef := client.Dataset(datasetID).Table(tableID)
if err := tableRef.Create(ctx, meta); err != nil {
	return err
}
// Now, import data from a local file, but specify field additions are allowed.
// Because the data has a second column (age), the schema is amended as part of
// the load.
f, err := os.Open(filename)
if err != nil {
	return err
}
source := bigquery.NewReaderSource(f)
source.AutoDetect = true   // Allow BigQuery to determine schema.
source.SkipLeadingRows = 1 // CSV has a single header line.

loader := client.Dataset(datasetID).Table(tableID).LoaderFrom(source)
loader.SchemaUpdateOptions = []string{"ALLOW_FIELD_ADDITION"}
job, err := loader.Run(ctx)
if err != nil {
	return err
}
status, err := job.Wait(ctx)
if err != nil {
	return err
}
if err := status.Err(); err != nil {
	return err
}

Python

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Python décrite dans le guide de démarrage rapide de BigQuery relatif à l'utilisation des bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence sur l'API BigQuery Python.

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_ref = client.dataset('my_dataset')
# filepath = 'path/to/your_file.csv'

# Retrieves the destination table and checks the length of the schema
table_id = "my_table"
table_ref = dataset_ref.table(table_id)
table = client.get_table(table_ref)
print("Table {} contains {} columns.".format(table_id, len(table.schema)))

# Configures the load job to append the data to the destination table,
# allowing field addition
job_config = bigquery.LoadJobConfig()
job_config.write_disposition = bigquery.WriteDisposition.WRITE_APPEND
job_config.schema_update_options = [
    bigquery.SchemaUpdateOption.ALLOW_FIELD_ADDITION
]
# In this example, the existing table contains only the 'full_name' column.
# 'REQUIRED' fields cannot be added to an existing schema, so the
# additional column must be 'NULLABLE'.
job_config.schema = [
    bigquery.SchemaField("full_name", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("age", "INTEGER", mode="NULLABLE"),
]
job_config.source_format = bigquery.SourceFormat.CSV
job_config.skip_leading_rows = 1

with open(filepath, "rb") as source_file:
    job = client.load_table_from_file(
        source_file,
        table_ref,
        location="US",  # Must match the destination dataset location.
        job_config=job_config,
    )  # API request

job.result()  # Waits for table load to complete.
print(
    "Loaded {} rows into {}:{}.".format(
        job.output_rows, dataset_id, table_ref.table_id
    )
)

# Checks the updated length of the schema
table = client.get_table(table)
print("Table {} now contains {} columns.".format(table_id, len(table.schema)))

Ajouter une colonne dans une tâche de requête ajoutant des résultats de requête

Pour ajouter des colonnes à une table lorsque vous ajoutez à cette dernière des résultats de requête :

  • utilisez la commande bq query de l'outil de ligne de commande ;
  • appelez la méthode API jobs.insert, puis configurez une tâche query ;
  • utilisez les bibliothèques clientes.

L'ajout d'une colonne lors d'une opération d'ajout n'est actuellement pas possible dans la console GCP ou l'interface utilisateur Web classique de BigQuery.

Lorsque vous ajoutez des colonnes à l'aide d'une opération d'ajout dans une tâche de requête, le schéma des résultats de requête est utilisé pour mettre à jour le schéma de la table de destination. Notez que vous ne pouvez pas interroger une table dans un emplacement spécifique et écrire les résultats dans une table dont l'emplacement est différent.

Pour ajouter une colonne lorsque vous ajoutez des données à une table lors d'une tâche de requête, procédez comme suit.

Console

Vous ne pouvez pas ajouter de colonnes à une table existante lorsque vous ajoutez des résultats de requête en utilisant la console GCP.

Interface utilisateur classique

Vous ne pouvez pas ajouter de colonnes à une table existante lorsque vous ajoutez des résultats de requête en utilisant l'interface utilisateur Web classique de BigQuery.

CLI

Utilisez la commande bq query pour interroger vos données et spécifiez l'indicateur --destination_table pour indiquer la table que vous ajoutez.

Pour spécifier que vous ajoutez des résultats de requête à une table de destination existante, spécifiez l'indicateur --append_table.

Définissez l'indicateur --schema_update_option sur ALLOW_FIELD_ADDITION pour signaler que les résultats de requête que vous ajoutez contiennent de nouvelles colonnes.

Spécifiez l'indicateur use_legacy_sql=false afin d'utiliser la syntaxe SQL standard pour la requête.

Si la table que vous ajoutez se trouve dans un ensemble de données d'un projet qui n'est pas celui par défaut, ajoutez l'ID du projet au nom de l'ensemble de données en utilisant le format suivant : project_id:dataset. Notez que la table que vous interrogez et la table de destination doivent se trouver au même emplacement.

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

bq --location=location query \
--destination_table project_id:dataset.table \
--append_table \
--schema_update_option=ALLOW_FIELD_ADDITION \
--use_legacy_sql=false \
'query'

Où :

  • location est le nom de votre emplacement. L'indicateur --location est facultatif. Par exemple, si vous utilisez BigQuery dans la région de Tokyo, définissez la valeur de l'indicateur sur asia-northeast1. Vous pouvez définir une valeur par défaut correspondant à l'emplacement en utilisant le fichier .bigqueryrc. Notez que vous ne pouvez pas ajouter les résultats d'une requête à une table dont l'emplacement est différent.
  • project_id est l'ID du projet.
  • dataset est le nom de l'ensemble de données contenant la table que vous ajoutez.
  • table est le nom de la table que vous ajoutez.
  • query est une requête en syntaxe SQL standard.

Exemples :

Saisissez la commande suivante pour interroger mydataset.mytable dans votre projet par défaut et pour ajouter les résultats de la requête à mydataset.mytable2 (également dans votre projet par défaut).

bq query \
--destination_table mydataset.mytable2 \
--append_table \
--schema_update_option=ALLOW_FIELD_ADDITION \
--use_legacy_sql=false \
'SELECT
   column1,column2
 FROM
   mydataset.mytable'

Saisissez la commande suivante pour interroger mydataset.mytable dans votre projet par défaut et pour ajouter les résultats de la requête à mydataset.mytable2 dans myotherproject.

bq query \
--destination_table myotherproject:mydataset.mytable2 \
--append_table \
--schema_update_option=ALLOW_FIELD_ADDITION \
--use_legacy_sql=false \
'SELECT
   column1,column2
 FROM
   mydataset.mytable'

API

Appelez la méthode jobs.insert. Configurez une tâche de query et définissez les propriétés suivantes :

  • Spécifiez la table de destination à l'aide de la propriété destinationTable.
  • Définissez la disposition d'écriture de la table de destination sur WRITE_APPEND à l'aide de la propriété writeDisposition.
  • Spécifiez l'option de mise à jour de schéma à l'aide de la propriété schemaUpdateOptions.
  • Spécifiez la requête SQL standard à l'aide de la propriété query.

Go

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
sampleSchema := bigquery.Schema{
	{Name: "full_name", Type: bigquery.StringFieldType, Required: true},
	{Name: "age", Type: bigquery.IntegerFieldType, Required: true},
}
original := &bigquery.TableMetadata{
	Schema: sampleSchema,
}
tableRef := client.Dataset(datasetID).Table(tableID)
if err := tableRef.Create(ctx, original); err != nil {
	return err
}
// Our table has two columns.  We'll introduce a new favorite_color column via
// a subsequent query that appends to the table.
q := client.Query("SELECT \"Timmy\" as full_name, 85 as age, \"Blue\" as favorite_color")
q.SchemaUpdateOptions = []string{"ALLOW_FIELD_ADDITION"}
q.QueryConfig.Dst = client.Dataset(datasetID).Table(tableID)
q.WriteDisposition = bigquery.WriteAppend
q.Location = "US"
job, err := q.Run(ctx)
if err != nil {
	return err
}
_, err = job.Wait(ctx)
if err != nil {
	return err
}

Python

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Python décrite dans le guide de démarrage rapide de BigQuery relatif à l'utilisation des bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence sur l'API BigQuery Python.

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_ref = client.dataset('my_dataset')

# Retrieves the destination table and checks the length of the schema
table_id = "my_table"
table_ref = dataset_ref.table(table_id)
table = client.get_table(table_ref)
print("Table {} contains {} columns.".format(table_id, len(table.schema)))

# Configures the query to append the results to a destination table,
# allowing field addition
job_config = bigquery.QueryJobConfig()
job_config.schema_update_options = [
    bigquery.SchemaUpdateOption.ALLOW_FIELD_ADDITION
]
job_config.destination = table_ref
job_config.write_disposition = bigquery.WriteDisposition.WRITE_APPEND

query_job = client.query(
    # In this example, the existing table contains only the 'full_name' and
    # 'age' columns, while the results of this query will contain an
    # additional 'favorite_color' column.
    'SELECT "Timmy" as full_name, 85 as age, "Blue" as favorite_color;',
    # Location must match that of the dataset(s) referenced in the query
    # and of the destination table.
    location="US",
    job_config=job_config,
)  # API request - starts the query

query_job.result()  # Waits for the query to finish
print("Query job {} complete.".format(query_job.job_id))

# Checks the updated length of the schema
table = client.get_table(table)
print("Table {} now contains {} columns.".format(table_id, len(table.schema)))

Assouplir 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 à NULLABLE revient à assouplir ces colonnes. Vous pouvez assouplir le mode des colonnes REQUIRED :

  • manuellement ;
  • lorsque vous écrasez une table en utilisant une tâche de chargement ou de requête ;
  • lorsque vous ajoutez des données à une table en utilisant une tâche de requête.

Passer manuellement une colonne du mode REQUIRED au mode NULLABLE

Pour modifier manuellement le mode d'une colonne de REQUIRED à NULLABLE :

  • utilisez la console GCP ou l'interface utilisateur Web classique de BigQuery ;
  • exécutez la commande bq update de l'outil de ligne de commande ;
  • appelez la méthode API tables.patch ;
  • utilisez les bibliothèques clientes.

Pour passer manuellement une colonne du mode REQUIRED au mode NULLABLE, procédez comme suit :

Console

Actuellement, il n'est pas possible d'assouplir le mode d'une colonne à l'aide de la console GCP.

Interface utilisateur classique

  1. Développez l'ensemble de données et sélectionnez la table.

  2. Sur la page Table Details (Détails de la table), cliquez sur l'onglet Schema (Schéma).

  3. Cliquez sur la flèche vers le bas située à droite de la colonne souhaitée et choisissez l'une des options suivantes :

    • Make NULLABLE (Passer en mode NULLABLE) : assouplit le mode d'une colonne individuelle
    • All REQUIRED to NULLABLE (Passer toutes les colonnes de REQUIRED à NULLABLE) : fait passer toutes les colonnes de la définition de schéma du mode REQUIRED au mode NULLABLE

      Assouplir le mode d'une colonne

  4. Dans la boîte de dialogue Confirmer le changement de mode, cliquez sur OK pour définir le mode sur NULLABLE. Notez que cette modification est irréversible.

CLI

  1. Tout d'abord, exécutez la commande bq show avec l'indicateur --schema et écrivez le schéma de table existant dans un fichier. Si la table que vous mettez à jour se trouve dans un projet qui n'est pas celui par défaut, ajoutez l'ID du projet au nom de l'ensemble de données en utilisant le format suivant : project_id:dataset.

    bq show \
    --schema \
    --format=prettyjson \
    project_id:dataset.table > schema_file
    

    Où :

    • project_id est l'ID du projet.
    • dataset est le nom de l'ensemble de données qui contient la table que vous mettez à jour.
    • table est le nom de la table que vous mettez à jour.
    • schema_file correspond au fichier de définition de schéma écrit sur votre ordinateur local.

    Par exemple, pour écrire la définition de schéma de mydataset.mytable dans un fichier, saisissez la commande suivante. mydataset.mytable se situe dans votre projet par défaut.

      bq show \
      --schema \
      --format=prettyjson \
      mydataset.mytable > /tmp/myschema.json
    
  2. Ouvrez le fichier de schéma dans un éditeur de texte. Le fichier doit se présenter comme suit :

    [
      {
        "mode": "REQUIRED",
        "name": "column1",
        "type": "STRING"
      },
      {
        "mode": "REQUIRED",
        "name": "column2",
        "type": "FLOAT"
      },
      {
        "mode": "REPEATED",
        "name": "column3",
        "type": "STRING"
      }
    ]
    
  3. Faites passer une colonne existante du mode REQUIRED au mode NULLABLE. Dans cet exemple, le mode de la colonne column1 est assoupli.

    [
      {
        "mode": "NULLABLE",
        "name": "column1",
        "type": "STRING"
      },
      {
        "mode": "REQUIRED",
        "name": "column2",
        "type": "FLOAT"
      },
      {
        "mode": "REPEATED",
        "name": "column3",
        "type": "STRING"
      }
    ]
    

    Pour en savoir plus sur l'utilisation des fichiers de schéma JSON, consultez l'article Spécifier un fichier de schéma JSON.

  4. Après avoir mis à jour votre fichier de schéma, exécutez la commande suivante pour mettre à jour le schéma de la table. Si la table que vous mettez à jour se trouve dans un projet qui n'est pas celui par défaut, ajoutez l'ID du projet au nom de l'ensemble de données en utilisant le format suivant : project_id:dataset.

    bq update project_id:dataset.table schema
    

    Où :

    • project_id est l'ID du projet.
    • dataset est le nom de l'ensemble de données qui contient la table que vous mettez à jour.
    • table est le nom de la table que vous mettez à jour.
    • schema est le chemin d'accès du fichier de schéma JSON sur votre ordinateur local.

    Par exemple, saisissez la commande suivante pour mettre à jour la définition de schéma de mydataset.mytable dans votre projet par défaut. Le chemin d'accès au fichier de schéma sur votre ordinateur local est le suivant : /tmp/myschema.json.

      bq update mydataset.mytable /tmp/myschema.json
    

API

Appelez la méthode tables.patch, puis utilisez la propriété schema pour faire passer une colonne du mode REQUIRED au mode NULLABLE dans votre définition de schéma. Étant donné que la méthode tables.update remplace l'intégralité de la ressource de table, il est préférable d'utiliser la méthode tables.patch.

Go

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
tableRef := client.Dataset(datasetID).Table(tableID)
meta, err := tableRef.Metadata(ctx)
if err != nil {
	return err
}
// Iterate through the schema to set all Required fields to false (nullable).
var relaxed bigquery.Schema
for _, v := range meta.Schema {
	v.Required = false
	relaxed = append(relaxed, v)
}
newMeta := bigquery.TableMetadataToUpdate{
	Schema: relaxed,
}
if _, err := tableRef.Update(ctx, newMeta, meta.ETag); err != nil {
	return err
}

Python

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Python décrite dans le guide de démarrage rapide de BigQuery relatif à l'utilisation des bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery Python.

Écrasez la propriété Table.schema avec une liste d'objets SchemaField dont la propriété mode est définie sur 'NULLABLE'.

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'my_dataset'
# table_id = 'my_table'

original_schema = [
    bigquery.SchemaField("full_name", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("age", "INTEGER", mode="REQUIRED"),
]
table_ref = client.dataset(dataset_id).table(table_id)
table = bigquery.Table(table_ref, schema=original_schema)
table = client.create_table(table)
assert all(field.mode == "REQUIRED" for field in table.schema)

# SchemaField properties cannot be edited after initialization.
# To make changes, construct new SchemaField objects.
relaxed_schema = [
    bigquery.SchemaField("full_name", "STRING", mode="NULLABLE"),
    bigquery.SchemaField("age", "INTEGER", mode="NULLABLE"),
]
table.schema = relaxed_schema
table = client.update_table(table, ["schema"])

assert all(field.mode == "NULLABLE" for field in table.schema)

Passer du mode REQUIRED au mode NULLABLE dans une tâche de chargement ou de requête

Vous pouvez assouplir une colonne du mode REQUIRED en mode NULLABLE dans le schéma d'une table existante lorsque vous y chargez des données et choisissez d'écraser cette table. Le schéma des données chargées est alors utilisé pour écraser le schéma de la table existante. Pour en savoir plus sur le remplacement d'une table à l'aide d'une tâche de chargement, consultez les sections suivantes :

Vous pouvez également assouplir une colonne du mode REQUIRED au mode NULLABLE dans le schéma d'une table existante lorsque vous ajoutez des données à l'aide d'une tâche de requête.

Passer du mode REQUIRED au mode NULLABLE dans une tâche de chargement ajoutant des données

Pour assouplir le mode d'une colonne lors de l'ajout de données à une table via une tâche de chargement :

  • utilisez la commande bq load de l'outil de ligne de commande ;
  • appelez la méthode API jobs.insert, puis configurez une tâche de chargement ;
  • utilisez les bibliothèques clientes.

La modification du mode d'une colonne lors d'une opération d'ajout n'est actuellement pas possible dans la console GCP ou l'interface utilisateur Web classique de BigQuery.

Lorsque vous assouplissez le mode d'une colonne en utilisant une opération d'ajout dans une tâche de chargement, vous pouvez :

  • assouplir le mode des colonnes individuelles en spécifiant un fichier de schéma JSON lors de l'ajout de données à partir de fichiers CSV et JSON ;
  • assouplir les colonnes en les passant au mode null dans votre schéma Avro, ORC ou Parquet et autoriser l'inférence de schéma à détecter les colonnes dont le mode a été assoupli.

Pour assouplir une colonne du mode REQUIRED au mode NULLABLE lorsque vous ajoutez des données à une table lors d'une tâche de chargement, procédez comme suit :

Console

Actuellement, il n'est pas possible d'assouplir le mode d'une colonne à l'aide de la console GCP.

Interface utilisateur classique

Vous ne pouvez pas assouplir le mode des colonnes existantes lorsque vous chargez des données en utilisant l'interface utilisateur Web classique de BigQuery.

CLI

Utilisez la commande bq load pour charger vos données et spécifiez l'indicateur --noreplace pour signaler que vous ajoutez les données à une table existante.

Si les données que vous ajoutez sont au format JSON délimité par un retour à la ligne ou CSV, spécifiez les colonnes dont le mode a été assoupli dans un fichier de schéma JSON local. Vous pouvez également utiliser l'indicateur --autodetect pour procéder à la détection de schéma afin d'identifier les colonnes dont le mode a été assoupli dans les données sources. Pour savoir comment assouplir les modes de colonne à l'aide d'un fichier de schéma JSON, consultez la section Passer manuellement une colonne du mode REQUIRED au mode NULLABLE.

Les colonnes dont le mode a été assoupli peuvent être automatiquement obtenues à partir des fichiers Avro, ORC et Parquet. L'assouplissement du mode des colonnes ne peut pas être appliqué aux fichiers d'exportation Cloud Datastore ajoutés. Les colonnes des tables créées en chargeant les fichiers d'exportation Cloud Datastore sont toujours définies comme NULLABLE.

Définissez l'indicateur --schema_update_option sur ALLOW_FIELD_RELAXATION pour indiquer que les données que vous ajoutez contiennent des colonnes dont les modes ont été assouplis.

Si la table que vous ajoutez se trouve dans un ensemble de données d'un projet qui n'est pas celui par défaut, ajoutez l'ID du projet au nom de l'ensemble de données en utilisant le format suivant : project_id:dataset.

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

Saisissez la commande load comme suit :

bq --location=location load \
--noreplace \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--source_format=format \
project_id:dataset.table \
path_to_source \
schema

Où :

  • location est le nom de votre emplacement. L'indicateur --location est facultatif. Par exemple, si vous utilisez BigQuery dans la région de Tokyo, définissez la valeur de l'indicateur sur asia-northeast1. Vous pouvez définir une valeur par défaut correspondant à l'emplacement en utilisant le fichier .bigqueryrc.
  • format est NEWLINE_DELIMITED_JSON, CSV, PARQUET, ORC ou AVRO. Les fichiers DATASTORE_BACKUP ne nécessitent pas que le mode des colonnes soit assoupli. Les colonnes des tables créées à partir des fichiers d'exportation Cloud Datastore sont toujours définies comme NULLABLE.
  • project_id est l'ID du projet.
  • dataset est le nom de l'ensemble de données contenant la table.
  • table est le nom de la table que vous ajoutez.
  • path_to_source correspond à un URI Cloud Storage complet, à une liste d'URI séparés par une virgule ou au chemin d'accès d'un fichier de données se trouvant sur votre ordinateur local.
  • schema correspond au chemin d'accès vers un fichier de schéma JSON local. Cette option n'est utilisée que pour les fichiers CSV et JSON. Les colonnes dont le mode a été assoupli sont automatiquement obtenues à partir des fichiers Avro.

Exemples :

Saisissez la commande suivante pour ajouter un fichier de données Avro local, appelé /tmp/mydata.avro, à mydataset.mytable en utilisant une tâche de chargement. Comme les colonnes dont le mode a été assoupli peuvent être automatiquement obtenues à partir des données Avro, vous n'avez pas besoin de spécifier un fichier de schéma. L'élément mydataset se trouve dans votre projet par défaut.

bq load \
--noreplace \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--source_format=AVRO \
mydataset.mytable \
/tmp/mydata.avro

Saisissez la commande suivante pour ajouter les données d'un fichier JSON délimité par un retour à la ligne dans Cloud Storage à mydataset.mytable en utilisant une tâche de chargement. Le schéma contenant les colonnes dont le mode a été assoupli se trouve dans un fichier de schéma JSON local nommé /tmp/myschema.json. L'élément mydataset se trouve dans votre projet par défaut.

bq load \
--noreplace \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable \
gs://mybucket/mydata.json \
/tmp/myschema.json

Saisissez la commande suivante pour ajouter les données d'un fichier CSV dans votre ordinateur local à mydataset.mytable en utilisant une tâche de chargement. La commande utilise la détection automatique de schéma pour déterminer les colonnes dont le mode a été assoupli dans les données sources. L'élément mydataset se trouve dans myotherproject, et non dans votre projet par défaut.

bq load \
--noreplace \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--source_format=CSV \
--autodetect \
myotherproject:mydataset.mytable \
mydata.csv

API

Appelez la méthode jobs.insert. Configurez une tâche de load et définissez les propriétés suivantes :

  • Référencez vos données dans Cloud Storage à l'aide de la propriété sourceUris.
  • Spécifiez le format de données en définissant la propriété sourceFormat.
  • Spécifiez le schéma dans la propriété schema.
  • Spécifiez l'option de mise à jour de schéma à l'aide de la propriété schemaUpdateOptions.
  • Définissez la disposition d'écriture de la table de destination sur WRITE_APPEND à l'aide de la propriété writeDisposition.

Go

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
sampleSchema := bigquery.Schema{
	{Name: "full_name", Type: bigquery.StringFieldType, Required: true},
	{Name: "age", Type: bigquery.IntegerFieldType, Required: true},
}
meta := &bigquery.TableMetadata{
	Schema: sampleSchema,
}
tableRef := client.Dataset(datasetID).Table(tableID)
if err := tableRef.Create(ctx, meta); err != nil {
	return err
}
// Now, import data from a local file, but specify relaxation of required
// fields as a side effect while the data is appended.
f, err := os.Open(filename)
if err != nil {
	return err
}
source := bigquery.NewReaderSource(f)
source.AutoDetect = true   // Allow BigQuery to determine schema.
source.SkipLeadingRows = 1 // CSV has a single header line.

loader := client.Dataset(datasetID).Table(tableID).LoaderFrom(source)
loader.SchemaUpdateOptions = []string{"ALLOW_FIELD_RELAXATION"}
job, err := loader.Run(ctx)
if err != nil {
	return err
}
status, err := job.Wait(ctx)
if err != nil {
	return err
}
if err := status.Err(); err != nil {
	return err
}

Python

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Python décrite dans le guide de démarrage rapide de BigQuery relatif à l'utilisation des bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence sur l'API BigQuery Python.

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_ref = client.dataset('my_dataset')
# filepath = 'path/to/your_file.csv'

# Retrieves the destination table and checks the number of required fields
table_id = "my_table"
table_ref = dataset_ref.table(table_id)
table = client.get_table(table_ref)
original_required_fields = sum(field.mode == "REQUIRED" for field in table.schema)
# In this example, the existing table has 3 required fields.
print("{} fields in the schema are required.".format(original_required_fields))

# Configures the load job to append the data to a destination table,
# allowing field relaxation
job_config = bigquery.LoadJobConfig()
job_config.write_disposition = bigquery.WriteDisposition.WRITE_APPEND
job_config.schema_update_options = [
    bigquery.SchemaUpdateOption.ALLOW_FIELD_RELAXATION
]
# In this example, the existing table contains three required fields
# ('full_name', 'age', and 'favorite_color'), while the data to load
# contains only the first two fields.
job_config.schema = [
    bigquery.SchemaField("full_name", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("age", "INTEGER", mode="REQUIRED"),
]
job_config.source_format = bigquery.SourceFormat.CSV
job_config.skip_leading_rows = 1

with open(filepath, "rb") as source_file:
    job = client.load_table_from_file(
        source_file,
        table_ref,
        location="US",  # Must match the destination dataset location.
        job_config=job_config,
    )  # API request

job.result()  # Waits for table load to complete.
print(
    "Loaded {} rows into {}:{}.".format(
        job.output_rows, dataset_id, table_ref.table_id
    )
)

# Checks the updated number of required fields
table = client.get_table(table)
current_required_fields = sum(field.mode == "REQUIRED" for field in table.schema)
print("{} fields in the schema are now required.".format(current_required_fields))

Passer du mode REQUIRED au mode NULLABLE dans une tâche de requête ajoutant des résultats de requête

Pour assouplir toutes les colonnes d'une table lorsque vous ajoutez des résultats de requête :

  • utilisez la commande bq query de l'outil de ligne de commande ;
  • appelez la méthode API jobs.insert, puis configurez une tâche requête ;
  • utilisez les bibliothèques clientes.

L'assouplissement des colonnes lors d'une opération d'ajout n'est actuellement pas possible dans la console GCP ou l'interface utilisateur Web de BigQuery.

Lorsque vous assouplissez les colonnes à l'aide d'une opération d'ajout dans une tâche de requête, vous pouvez assouplir tous les champs obligatoires dans la table de destination en définissant l'indicateur --schema_update_option sur ALLOW_FIELD_RELAXATION. Vous ne pouvez pas assouplir le mode de colonnes individuelles dans une table de destination à l'aide d'une tâche de requête ajoutant des résultats de requête.

Pour assouplir le mode de toutes les colonnes d'une table de destination lorsque vous y ajoutez des données lors d'une tâche de requête, procédez comme suit :

Console

Actuellement, il n'est pas possible d'assouplir le mode d'une colonne à l'aide de la console GCP.

Interface utilisateur classique

Vous ne pouvez pas assouplir le mode des colonnes d'une table de destination lorsque vous ajoutez des résultats de requête à l'aide de l'interface utilisateur Web de BigQuery.

CLI

Utilisez la commande bq query pour interroger vos données et spécifiez l'indicateur --destination_table pour indiquer la table que vous ajoutez.

Pour spécifier que vous ajoutez des résultats de requête à une table de destination existante, spécifiez l'indicateur --append_table.

Définissez l'indicateur --schema_update_option sur ALLOW_FIELD_RELAXATION pour indiquer que toutes les colonnes en mode REQUIRED de la table que vous ajoutez doivent être passées en mode NULLABLE.

Spécifiez l'indicateur use_legacy_sql=false afin d'utiliser la syntaxe SQL standard pour la requête.

Si la table que vous ajoutez se trouve dans un ensemble de données d'un projet qui n'est pas celui par défaut, ajoutez l'ID du projet au nom de l'ensemble de données en utilisant le format suivant : project_id:dataset.

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

bq --location=location query \
--destination_table project_id:dataset.table \
--append_table \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--use_legacy_sql=false \
'query'

Où :

  • location est le nom de votre emplacement. L'indicateur --location est facultatif. Par exemple, si vous utilisez BigQuery dans la région de Tokyo, définissez la valeur de l'indicateur sur asia-northeast1. Vous pouvez définir une valeur par défaut correspondant à l'emplacement en utilisant le fichier .bigqueryrc.
  • project_id est l'ID du projet.
  • dataset est le nom de l'ensemble de données contenant la table que vous ajoutez.
  • table est le nom de la table que vous ajoutez.
  • query est une requête en syntaxe SQL standard.

Exemples :

Saisissez la requête de commande mydataset.mytable suivante dans votre projet par défaut pour ajouter les résultats de requête à mydataset.mytable2, également compris dans votre projet par défaut. La commande modifie toutes les colonnes REQUIRED de la table de destination en mode NULLABLE.

bq query \
--destination_table mydataset.mytable2 \
--append_table \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--use_legacy_sql=false \
'SELECT
   column1,column2
 FROM
   mydataset.mytable'

Saisissez la requête de commande mydataset.mytable suivante dans votre projet par défaut pour ajouter les résultats de requête à mydataset.mytable2 dans myotherproject. La commande modifie toutes les colonnes REQUIRED de la table de destination en mode NULLABLE.

bq query \
--destination_table myotherproject:mydataset.mytable2 \
--append_table \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--use_legacy_sql=false \
'SELECT
   column1,column2
 FROM
   mydataset.mytable'

API

Appelez la méthode jobs.insert. Configurez une tâche de query et définissez les propriétés suivantes :

  • Spécifiez la table de destination à l'aide de la propriété destinationTable.
  • Définissez la disposition d'écriture de la table de destination sur WRITE_APPEND à l'aide de la propriété writeDisposition.
  • Spécifiez l'option de mise à jour de schéma à l'aide de la propriété schemaUpdateOptions.
  • Spécifiez la requête SQL standard à l'aide de la propriété query.

Go

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
sampleSchema := bigquery.Schema{
	{Name: "full_name", Type: bigquery.StringFieldType, Required: true},
	{Name: "age", Type: bigquery.IntegerFieldType, Required: true},
}
meta := &bigquery.TableMetadata{
	Schema: sampleSchema,
}
tableRef := client.Dataset(datasetID).Table(tableID)
if err := tableRef.Create(ctx, meta); err != nil {
	return err
}
// Now, append a query result that includes nulls, but allow the job to relax
// all required columns.
q := client.Query("SELECT \"Beyonce\" as full_name")
q.QueryConfig.Dst = client.Dataset(datasetID).Table(tableID)
q.SchemaUpdateOptions = []string{"ALLOW_FIELD_RELAXATION"}
q.WriteDisposition = bigquery.WriteAppend
q.Location = "US"
job, err := q.Run(ctx)
if err != nil {
	return err
}
_, err = job.Wait(ctx)
if err != nil {
	return err
}

Python

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Python décrite dans le guide de démarrage rapide de BigQuery relatif à l'utilisation des bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence sur l'API BigQuery Python.

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_ref = client.dataset('my_dataset')

# Retrieves the destination table and checks the number of required fields
table_id = "my_table"
table_ref = dataset_ref.table(table_id)
table = client.get_table(table_ref)
original_required_fields = sum(field.mode == "REQUIRED" for field in table.schema)
# In this example, the existing table has 2 required fields
print("{} fields in the schema are required.".format(original_required_fields))

# Configures the query to append the results to a destination table,
# allowing field relaxation
job_config = bigquery.QueryJobConfig()
job_config.schema_update_options = [
    bigquery.SchemaUpdateOption.ALLOW_FIELD_RELAXATION
]
job_config.destination = table_ref
job_config.write_disposition = bigquery.WriteDisposition.WRITE_APPEND

query_job = client.query(
    # In this example, the existing table contains 'full_name' and 'age' as
    # required columns, but the query results will omit the second column.
    'SELECT "Beyonce" as full_name;',
    # Location must match that of the dataset(s) referenced in the query
    # and of the destination table.
    location="US",
    job_config=job_config,
)  # API request - starts the query

query_job.result()  # Waits for the query to finish
print("Query job {} complete.".format(query_job.job_id))

# Checks the updated number of required fields
table = client.get_table(table)
current_required_fields = sum(field.mode == "REQUIRED" for field in table.schema)
print("{} fields in the schema are now required.".format(current_required_fields))
Cette page vous a-t-elle été utile ? Évaluez-la :

Envoyer des commentaires concernant…

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