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 exécutant la commande bq update de l'outil de ligne de commande
  • Appelez la méthode d'API tables.patch.

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 l'interface de ligne de commande 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.

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.
    • Choisissez NULLABLE ou REPEATED dans le champ Mode.

      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 de votre projet.
  • [DATASET] est le nom de l'ensemble de données contenant la table que vous mettez à jour.
  • [TABLE] est le nom de la table que vous mettez à jour.
  • [SCHEMA] correspond au chemin d'accès vers le 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) ou de description de colonne, ni 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 codée en dur, le processus de mise à jour tente de faire passer toute colonne existante 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 de votre projet.
    • [DATASET] est le nom de l'ensemble de données contenant 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. 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 de votre projet.
    • [DATASET] est le nom de l'ensemble de données contenant la table que vous mettez à jour.
    • [TABLE] est le nom de la table que vous mettez à jour.
    • [SCHEMA] correspond au chemin d'accès vers le 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 et utilisez la propriété schema pour ajouter des colonnes vides à votre définition de schéma. Comme la méthode tables.update remplace la totalité 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 de tester cet exemple, suivez la procédure de configuration de Python dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery Python.

Ajoutez un 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.

Interface utilisateur classique

L'ajout d'un champ imbriqué à une colonne RECORD existante n'est actuellement pas possible dans l'UI 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 de votre projet.
  • [DATASET] est le nom de l'ensemble de données contenant la table que vous mettez à jour.
  • [TABLE] est le nom de la table que vous mettez à jour.
  • [SCHEMA] correspond au chemin d'accès vers le 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) ou de description de colonne, ni spécifier le mode de la colonne. Tous les modes par défaut sont définis sur NULLABLE. Par conséquent, si vous ajoutez une colonne imbriquée à un 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 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 de votre projet.
    • [DATASET] est le nom de l'ensemble de données contenant 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. 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 de votre projet.
    • [DATASET] est le nom de l'ensemble de données contenant la table que vous mettez à jour.
    • [TABLE] est le nom de la table que vous mettez à jour.
    • [SCHEMA] correspond au chemin d'accès vers le 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 et utilisez la propriété schema pour ajouter les colonnes imbriquées à votre définition de schéma. Comme la méthode tables.update remplace la totalité de la ressource de table, la méthode tables.patch est préférable.

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 d'API jobs.insert et configurez une tâche de chargement

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 ;
  • obtenu automatiquement à partir des données, pour les fichiers d'exportation Avro 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.

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

Interface utilisateur classique

Vous ne pouvez pas ajouter de colonnes à une table existante lorsque vous chargez des données en utilisant l'UI 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émas 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].

Définissez l'indicateur --location sur 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 zone. L'indicateur --location est facultatif si vos données se situent dans les emplacements multirégionaux US ou EU. 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] correspond à NEWLINE_DELIMITED_JSON, CSV, AVRO ou DATASTORE_BACKUP.
  • [PROJECT_ID] est l'ID de votre projet.
  • [DATASET] correspond au nom de l'ensemble de données contenant la table.
  • [TABLE] correspond au 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. Les colonnes ajoutées pouvant être obtenues automatiquement à partir des données Avro, vous n'avez pas besoin d'utiliser l'indicateur --autodetect. L'élément mydataset se situe dans votre projet par défaut et a été créé dans la zone multirégionale US.

bq --location=US 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 est utilisé pour détecter les nouvelles colonnes. L'élément mydataset se situe dans votre projet par défaut et a été créé dans la zone multirégionale US.

bq --location=US 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, appelé /tmp/myschema.json. L'élément mydataset se situe dans myotherproject, et non au sein de votre projet par défaut. mydataset a été créé dans la zone multirégionale US.

bq --location=US load --noreplace --schema_update_option=ALLOW_FIELD_ADDITION --source_format=NEWLINE_DELIMITED_JSON myotherproject:mydataset.mytable gs://mybucket/mydata.json /tmp/myschema.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. L'indicateur --autodetect est utilisé pour détecter les nouvelles colonnes. L'élément mydataset se situe dans votre projet par défaut et a été créé dans la région asia-northeast1.

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

API

Appelez la méthode jobs.insert. Spécifiez la région où sont situées vos données dans la propriété location de la section jobReference de la ressource de tâche.

Configurez une tâche de chargement et définissez les propriétés suivantes :

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 de tester cet exemple, suivez la procédure de configuration de Python dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de 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 d'API jobs.insert et configurez une tâche de requête

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.

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

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'UI 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].

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

bq --location=[LOCATION] query --destination_table [PROJECT_ID]:[DATASET].[TABLE] --append_table --schema_update_option  --use_legacy_sql=false '[QUERY]'

Où :

  • [LOCATION] est le nom de votre zone. L'indicateur --location est facultatif si vos données se situent dans les emplacements multirégionaux US ou EU. 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 de votre projet.
  • [DATASET] correspond au nom de l'ensemble de données contenant la table que vous mettez à jour.
  • [TABLE] correspond au nom de la table que vous ajoutez.
  • [QUERY] correspond à 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. L'élément mydataset a été créé dans la zone multirégionale US.

bq --location=US 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 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. L'élément mydataset a été créé dans la zone multirégionale US.

bq --location=US query --destination_table myotherproject:mydataset.mytable2 --append_table --schema_update_option=ALLOW_FIELD_ADDITION --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, également compris dans votre projet par défaut. L'élément mydataset a été créé dans la région asia-northeast1.

bq --location=asia-northeast1 query --destination_table 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. Spécifiez la région où sont situées vos données dans la propriété location de la section jobReference de la ressource de tâche.

Configurez une tâche de requête et définissez les propriétés suivantes :

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 de tester cet exemple, suivez la procédure de configuration de Python dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de 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
  • En exécutant la commande bq update de l'outil de ligne de commande
  • Appelez la méthode d'API tables.patch

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

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 la modification des modes, cliquez sur OK pour définir le mode sur NULLABLE. Notez que cette modification ne peut pas être annulée.

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 de votre projet.
    • [DATASET] est le nom de l'ensemble de données contenant 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 de votre projet.
    • [DATASET] est le nom de l'ensemble de données contenant la table que vous mettez à jour.
    • [TABLE] est le nom de la table que vous mettez à jour.
    • [SCHEMA] correspond au chemin d'accès vers le 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 et 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 de tester cet exemple, suivez la procédure de configuration de Python dans le guide de démarrage rapide de BigQuery : Utiliser les 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 par une liste d'objets SchemaField avec la propriété mode 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 d'API jobs.insert et configurez une tâche de chargement

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

Interface utilisateur classique

Vous ne pouvez pas assouplir le mode des colonnes existantes lorsque vous chargez des données en utilisant l'UI 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 automatique de schémas 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. 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].

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

Saisissez la commande load comme suit :

bq --location=[LOCATION] load --noreplace --schema_update_option --source_format=[FORMAT] [PROJECT_ID]:[DATASET].[TABLE] [PATH_TO_SOURCE] [SCHEMA]

où :

  • [LOCATION] est le nom de votre zone. L'indicateur --location est facultatif si vos données se situent dans les emplacements multirégionaux US ou EU. 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] correspond à NEWLINE_DELIMITED_JSON, CSV 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 de votre projet.
  • [DATASET] correspond au nom de l'ensemble de données contenant la table.
  • [TABLE] correspond au 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 est utilisée uniquement 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. Les colonnes ajoutées pouvant être automatiquement obtenues à partir des données Avro, vous n'avez pas besoin de spécifier un fichier de schéma. mydataset est compris dans votre projet par défaut et a été créé dans la zone multirégionale US.

bq --location=US load --noreplace --schema_update_option=ALLOW_FIELD_RELAXATION --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. Le schéma contenant les colonnes dont le mode a été assoupli se trouve dans un fichier de schéma JSON local appelé /tmp/myschema.json. L'élément mydataset se situe dans votre projet par défaut et a été créé dans la zone multirégionale US.

bq --location=US 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 des données dans un fichier CSV de 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 situe dans myotherproject, et non dans votre projet par défaut. mydataset été créé dans la zone multirégionale US.

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

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 colonnes dont le mode a été assoupli se trouve dans un fichier de schéma JSON local appelé /tmp/myschema.json. L'élément mydataset se situe dans votre projet par défaut et a été créé dans la région asia-northeast1.

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

API

Appelez la méthode jobs.insert. Spécifiez la région où sont situées vos données dans la propriété location de la section jobReference de la ressource de tâche. Configurez une tâche de chargement et définissez les propriétés suivantes :

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 de tester cet exemple, suivez la procédure de configuration de Python dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de 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 d'API jobs.insert et configurez une tâche de requête

L'assouplissement du mode 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 le mode des 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.

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'UI 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].

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

bq --location=[LOCATION] query --destination_table [PROJECT_ID]:[DATASET].[TABLE] --append_table --schema_update_option --use_legacy_sql=false '[QUERY]'

Où :

  • [LOCATION] est le nom de votre zone. L'indicateur --location est facultatif si vos données se situent dans les emplacements multirégionaux US ou EU. 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 de votre projet.
  • [DATASET] correspond au nom de l'ensemble de données contenant la table que vous mettez à jour.
  • [TABLE] correspond au nom de la table que vous ajoutez.
  • [QUERY] correspond à 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 fait passer toutes les colonnes en mode REQUIRED de la table de destination en mode NULLABLE. L'élément mydataset a été créé dans la zone multirégionale US.

bq --location=US 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 fait passer toutes les colonnes en mode REQUIRED de la table de destination en mode NULLABLE. L'élément mydataset a été créé dans la zone multirégionale US.

bq --location=US query --destination_table myotherproject: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, également compris dans votre projet par défaut. La commande modifie le mode de toutes les colonnes REQUIRED de la table de destination en NULLABLE. L'élément mydataset a été créé dans la région asia-northeast1.

bq --location=asia-northeast1 query --destination_table 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. Spécifiez la région où sont situées vos données dans la propriété location de la section jobReference de la ressource de tâche.

Configurez une tâche de requête et définissez les propriétés suivantes :

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 de tester cet exemple, suivez la procédure de configuration de Python dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de 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.