Charger des données JSON à partir de Cloud Storage

Vous pouvez charger des données JSON délimitées par un saut de ligne (ndJSON) depuis Cloud Storage dans une nouvelle table ou partition, ajouter les données à une table ou une partition existante, ou bien les utiliser pour écraser une table ou une partition. Lorsque les données sont chargées dans BigQuery, elles sont converties au format en colonnes de Capacitor (format de stockage de BigQuery).

Lorsque vous chargez des données depuis Cloud Storage dans une table BigQuery, l'ensemble de données contenant la table doit se trouver au même emplacement régional ou multirégional que le bucket Cloud Storage.

Le format ndJSON est identique au format JSON Lines.

Limites

Vous êtes soumis aux limitations suivantes lorsque vous chargez des données dans BigQuery à partir d'un bucket Cloud Storage :

  • Si l'emplacement de votre ensemble de données est défini sur une valeur autre que l'emplacement multirégional US, le bucket Cloud Storage doit se trouver dans la même région que l'ensemble de données, ou figurer dans le même emplacement multirégional que celui-ci.
  • BigQuery ne garantit pas la cohérence des données pour les sources de données externes. Les modifications apportées aux données sous-jacentes lors de l'exécution d'une requête peuvent entraîner un comportement inattendu.
  • BigQuery n'est pas compatible avec la gestion des versions d'objets Cloud Storage. Si vous incluez un numéro de génération dans l'URI Cloud Storage, la tâche de chargement échoue.

Lorsque vous chargez des fichiers JSON dans BigQuery, tenez compte des points suivants :

  • Les données JSON doivent être délimitées par un saut de ligne, c'est-à-dire le format ndJSON. Chaque objet JSON doit figurer sur une ligne distincte du fichier.
  • Si vous utilisez la compression gzip, BigQuery ne peut pas lire les données en parallèle. Le chargement de données JSON compressées dans BigQuery est plus lent que le chargement de données non compressées.
  • Vous ne pouvez pas inclure à la fois des fichiers compressés et non compressés dans la même tâche de chargement.
  • La taille maximale d'un fichier gzip est de 4 Go.

  • BigQuery accepte le type JSON même si les informations de schéma ne sont pas connues au moment de l'ingestion. Un champ déclaré en tant que type JSON est chargé avec les valeurs JSON brutes.

  • Si vous utilisez l'API BigQuery pour charger un entier compris dans la plage [-253+1, 253-1] (généralement, cela se traduit par un nombre supérieur à 9 007 199 254 740 991) dans une colonne d'entiers (INT64), transmettez-la sous forme de chaîne pour éviter la corruption des données. Ce problème est causé par une limite de taille d'entier dans JSON ou ECMAScript. Pour en savoir plus, consultez la section Nombres de la RFC 7159.

  • Lorsque vous chargez des données CSV ou JSON, les valeurs des colonnes DATE doivent utiliser le tiret (-) comme séparateur et la date doit avoir le format suivant : YYYY-MM-DD (année-mois-jour).
  • Lorsque vous chargez des données JSON ou CSV, les valeurs des colonnes TIMESTAMP doivent utiliser un tiret (-) ou une barre oblique (/) comme séparateur pour la partie date du code temporel, et la date doit être dans l'un des formats suivants : YYYY-MM-DD (année-mois-jour) ou YYYY/MM/DD (année/mois/jour). La partie hh:mm:ss (heure-minute-seconde) du code temporel doit utiliser le signe deux-points (:) comme séparateur.

  • Vos fichiers doivent respecter les limites de taille des fichiers JSON, comme décrit dans la section Limites des jobs de chargement.

Avant de commencer

Attribuez aux utilisateurs des rôles IAM (Identity and Access Management) incluant les autorisations nécessaires pour effectuer l'ensemble des tâches de ce document et créer un ensemble de données pour stocker les données.

Autorisations requises

Pour charger des données dans BigQuery, vous devez disposer d'autorisations IAM pour exécuter une tâche de chargement et charger des données dans des tables et partitions BigQuery. Si vous chargez des données à partir de Cloud Storage, vous devez également disposer d'autorisations IAM pour accéder au bucket contenant vos données.

Autorisations pour charger des données dans BigQuery

Pour charger des données dans une nouvelle table ou partition BigQuery, ou pour ajouter ou écraser une table ou une partition existante, vous avez besoin des autorisations IAM suivantes :

  • bigquery.tables.create
  • bigquery.tables.updateData
  • bigquery.tables.update
  • bigquery.jobs.create

Chacun des rôles IAM prédéfinis suivants inclut les autorisations dont vous avez besoin pour charger des données dans une table ou une partition BigQuery :

  • roles/bigquery.dataEditor
  • roles/bigquery.dataOwner
  • roles/bigquery.admin (inclut l'autorisation bigquery.jobs.create)
  • bigquery.user (inclut l'autorisation bigquery.jobs.create)
  • bigquery.jobUser (inclut l'autorisation bigquery.jobs.create)

En outre, si vous disposez de l'autorisation bigquery.datasets.create, vous pouvez créer et mettre à jour des tables à l'aide d'une tâche de chargement dans les ensembles de données que vous créez.

Pour en savoir plus sur les rôles et les autorisations IAM dans BigQuery, consultez la page Rôles prédéfinis et autorisations.

Autorisations pour charger des données à partir de Cloud Storage

Pour obtenir les autorisations nécessaires pour charger des données à partir d'un bucket Cloud Storage, demandez à votre administrateur de vous accorder le rôle IAM Administrateur Storage (roles/storage.admin) sur le bucket. Pour en savoir plus sur l'attribution de rôles, consultez la page Gérer l'accès aux projets, aux dossiers et aux organisations.

Ce rôle prédéfini contient les autorisations requises pour charger des données à partir d'un bucket Cloud Storage. Pour connaître les autorisations exactes requises, développez la section Autorisations requises :

Autorisations requises

Vous devez disposer des autorisations suivantes pour charger des données à partir d'un bucket Cloud Storage :

  • storage.buckets.get
  • storage.objects.get
  • storage.objects.list (required if you are using a URI wildcard)

Vous pouvez également obtenir ces autorisations avec des rôles personnalisés ou d'autres rôles prédéfinis.

Créer un ensemble de données

Créez un ensemble de données BigQuery pour stocker vos données.

Compression JSON

Vous pouvez exploiter l'utilitaire gzip pour compresser des fichiers JSON. Sachez que gzip effectue une compression complète des fichiers, contrairement à la compression de contenu de fichier effectuée par des codecs de compression pour d'autres formats de fichiers, tels que Avro. L'utilisation de gzip pour compresser vos fichiers JSON peut avoir un impact sur les performances. Pour en savoir plus sur les compromis, consultez la section Charger des données compressées et non compressées.

Charger des données JSON dans une nouvelle table

Pour charger des données JSON à partir de Cloud Storage dans une nouvelle table BigQuery, procédez comme suit :

Console

  1. Dans la console Google Cloud, accédez à la page BigQuery.

    Accéder à BigQuery

  2. Dans le volet Explorateur, développez votre projet, puis sélectionnez un ensemble de données.
  3. Dans la section Informations sur l'ensemble de données, cliquez sur Créer une table.
  4. Dans le panneau Créer une table, spécifiez les détails suivants :
    1. Dans la section Source, sélectionnez Google Cloud Storage dans la liste Créer une table à partir de. Ensuite, procédez comme suit :
      1. Sélectionnez un fichier dans le bucket Cloud Storage ou saisissez l'URI Cloud Storage. Vous ne pouvez pas inclure plusieurs URI dans la console Google Cloud. En revanche, les caractères génériques sont acceptés. Le bucket Cloud Storage doit se trouver au même emplacement que l'ensemble de données contenant la table que vous souhaitez créer, ajouter ou écraser. Sélection d'un fichier source pour créer une table BigQuery
      2. Pour le champ Format de fichier, sélectionnez JSONL (JSON délimité par un retour à la ligne).
    2. Dans la section Destination, spécifiez les détails suivants :
      1. Pour Ensemble de données, sélectionnez l'ensemble de données dans lequel vous souhaitez créer la table.
      2. Dans le champ Table, saisissez le nom de la table que vous souhaitez créer.
      3. Vérifiez que le champ Type de table est défini sur Table native.
    3. Dans la section Schéma, saisissez la définition du schéma. Pour activer la détection automatique du schéma, sélectionnez Détection automatique. Vous pouvez saisir les informations de schéma manuellement à l'aide de l'une des méthodes suivantes :
      • Option 1 : Cliquez sur Modifier sous forme de texte et collez le schéma sous la forme d'un tableau JSON. Lorsque vous utilisez un tableau JSON, vous générez le schéma en utilisant le même processus que pour la création d'un fichier de schéma JSON. Vous pouvez afficher le schéma d'une table existante au format JSON en saisissant la commande suivante : 
            bq show --format=prettyjson dataset.table
      • Option 2 : Cliquez sur  Ajouter un champ et saisissez le schéma de la table. Spécifiez le nom, le type et le mode de chaque champ.
    4. Facultatif : spécifiez les paramètres de partitionnement et de clustering. Pour en savoir plus, consultez les pages Créer des tables partitionnées et Créer et utiliser des tables en cluster.
    5. Cliquez sur Options avancées et procédez comme suit :
      • Sous Préférence d'écriture, laissez l'option Écrire si la table est vide sélectionnée. Cette option crée une table et y charge vos données.
      • Pour le champ Nombre d'erreurs autorisées, acceptez la valeur par défaut 0 ou saisissez le nombre maximal de lignes contenant des erreurs pouvant être ignorées. Si le nombre de lignes contenant des erreurs dépasse cette valeur, la tâche renverra un message invalid et échouera. Cette option ne s'applique qu'aux fichiers CSV et JSON.
      • Si vous souhaitez ignorer les valeurs d'une ligne qui ne sont pas présentes dans le schéma de la table, sélectionnez Valeurs inconnues.
      • Pour le champ Chiffrement, cliquez sur Clé gérée par le client afin d'utiliser une clé Cloud Key Management Service. Si vous conservez le paramètre Clé gérée par Google, BigQuery chiffre les données au repos.
    6. Cliquez sur Créer la table.

SQL

Utilisez l'instruction LDD LOAD DATA : L'exemple suivant charge un fichier JSON dans la nouvelle table mytable:

  1. Dans la console Google Cloud, accédez à la page BigQuery.

    Accéder à BigQuery

  2. Dans l'éditeur de requête, saisissez l'instruction suivante :

    LOAD DATA OVERWRITE mydataset.mytable
(x INT64,y STRING)
FROM FILES (
  format = 'JSON',
  uris = ['gs://bucket/path/file.json']);

  3. Cliquez sur Exécuter.

Pour en savoir plus sur l'exécution des requêtes, consultez Exécuter une requête interactive.

bq

Exécutez la commande bq load, définissez NEWLINE_DELIMITED_JSON à l'aide de l'option --source_format et spécifiez un URI Cloud Storage. Vous pouvez inclure un seul URI, une liste d'URI séparés par des virgules ou un URI contenant un caractère générique. Fournissez le schéma de manière intégrée ou dans un fichier de définition de schéma, ou utilisez la détection automatique du schéma.

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

Les autres options facultatives sont les suivantes :

  • --max_bad_records : entier spécifiant le nombre maximal d'enregistrements incorrects autorisés avant l'échec total de la tâche. La valeur par défaut est 0. Au plus, cinq erreurs de n'importe quel type sont renvoyées, quelle que soit la valeur --max_bad_records.
  • --ignore_unknown_values : si spécifié, permet d'autoriser et d'ignorer les valeurs supplémentaires non reconnues dans les données CSV ou JSON.
  • --autodetect : permet d'activer la détection automatique du schéma pour les données CSV et JSON.
  • --time_partitioning_type : active le partitionnement temporel sur une table et définit le type de partition. Les valeurs possibles sont HOUR, DAY, MONTH et YEAR. Cette option est facultative lorsque vous créez une table partitionnée sur une colonne DATE, DATETIME ou TIMESTAMP. Le type de partition par défaut pour le partitionnement temporel est DAY. Vous ne pouvez pas modifier la spécification de partitionnement sur une table existante.
  • --time_partitioning_expiration : entier qui spécifie (en secondes) le délai au terme duquel une partition temporelle doit être supprimée. Le délai d'expiration correspond à la date UTC de la partition plus la valeur entière.
  • --time_partitioning_field : colonne DATE ou TIMESTAMP utilisée pour créer une table partitionnée. Si le partitionnement par date est activé sans cette valeur, une table partitionnée par date d'ingestion est créée.
  • --require_partition_filter : si cette option est activée, elle oblige les utilisateurs à inclure une clause WHERE spécifiant les partitions à interroger. Ce type de filtre peut réduire les coûts et améliorer les performances. Pour en savoir plus, consultez la section Exiger un filtre de partitionnement dans les requêtes.
  • --clustering_fields : liste pouvant contenir jusqu'à quatre noms de colonne séparés par une virgule, et utilisée pour créer une table en cluster.

  • --destination_kms_key : clé Cloud KMS pour le chiffrement des données de la table.

    Pour en savoir plus sur les tables partitionnées, consultez :

    Pour en savoir plus sur les tables en cluster, consultez :

    Pour en savoir plus sur le chiffrement d'une table, consultez :

Pour charger des données JSON dans BigQuery, saisissez la commande suivante :

bq --location=LOCATION load \
--source_format=FORMAT \
DATASET.TABLE \
PATH_TO_SOURCE \
SCHEMA

Remplacez les éléments suivants :

  • LOCATION : votre position. L'option --location est facultative. Par exemple, si vous utilisez BigQuery dans la région de Tokyo, vous pouvez définir la valeur de l'option sur asia-northeast1. Vous pouvez définir une valeur par défaut correspondant à l'emplacement en utilisant le fichier .bigqueryrc.
  • FORMAT : NEWLINE_DELIMITED_JSON.
  • DATASET : ensemble de données existant.
  • TABLE : nom de la table dans laquelle vous chargez des données.
  • PATH_TO_SOURCE : URI Cloud Storage complet ou liste d'URI séparés par des virgules. Les caractères génériques sont également acceptés.
  • SCHEMA : schéma valide. Ce schéma peut être un fichier JSON local ou il peut être intégré à la commande. Si vous utilisez un fichier de schéma, n'utilisez pas d'extension. Vous pouvez également utiliser l'option --autodetect au lieu de fournir une définition de schéma.

Exemples :

La commande suivante permet de charger les données de gs://mybucket/mydata.json dans la table mytable de mydataset. Le schéma est défini dans un fichier de schéma local nommé myschema.

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

La commande suivante permet de charger les données de gs://mybucket/mydata.json dans une nouvelle table partitionnée par date d'ingestion nommée mytable dans mydataset. Le schéma est défini dans un fichier de schéma local nommé myschema.

    bq load \
    --source_format=NEWLINE_DELIMITED_JSON \
    --time_partitioning_type=DAY \
    mydataset.mytable \
    gs://mybucket/mydata.json \
    ./myschema

La commande suivante permet de charger les données de gs://mybucket/mydata.json dans la table partitionnée mytable de mydataset. La table est partitionnée en fonction de la colonne mytimestamp. Le schéma est défini dans un fichier de schéma local nommé myschema.

    bq load \
    --source_format=NEWLINE_DELIMITED_JSON \
    --time_partitioning_field mytimestamp \
    mydataset.mytable \
    gs://mybucket/mydata.json \
    ./myschema

La commande suivante permet de charger les données de gs://mybucket/mydata.json dans la table mytable de mydataset. Le schéma est détecté automatiquement.

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

La commande suivante permet de charger les données de gs://mybucket/mydata.json dans la table mytable de mydataset. Le schéma est défini de manière intégrée au format FIELD:DATA_TYPE, FIELD:DATA_TYPE.

    bq load \
    --source_format=NEWLINE_DELIMITED_JSON \
    mydataset.mytable \
    gs://mybucket/mydata.json \
    qtr:STRING,sales:FLOAT,year:STRING

La commande ci-dessous permet de charger les données de plusieurs fichiers de gs://mybucket/ dans la table mytable de mydataset. L'URI Cloud Storage utilise un caractère générique. Le schéma est détecté automatiquement.

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

La commande ci-dessous permet de charger les données de plusieurs fichiers de gs://mybucket/ dans la table mytable de mydataset. La commande inclut une liste d'URI Cloud Storage séparés par une virgule. Le schéma est défini dans un fichier de schéma local nommé myschema.

    bq load \
    --source_format=NEWLINE_DELIMITED_JSON \
    mydataset.mytable \
    "gs://mybucket/00/*.json","gs://mybucket/01/*.json" \
    ./myschema

API

  1. Créez une tâche de chargement (load) qui pointe vers les données sources dans Cloud Storage.

  2. (Facultatif) Spécifiez votre emplacement dans la propriété location de la section jobReference de la ressource de tâche.

  3. La propriété source URIs doit être complète et respecter le format gs://BUCKET/OBJECT. Chaque URI peut contenir un caractère générique (*).

  4. Spécifiez le format de données JSON en définissant la propriété sourceFormat sur NEWLINE_DELIMITED_JSON.

  5. Pour vérifier l'état de la tâche, appelez jobs.get(JOB_ID*) en remplaçant JOB_ID par l'ID de tâche renvoyé par la requête initiale.

    • Si la réponse est status.state = DONE, la tâche a bien été exécutée.
    • Si la propriété status.errorResult est présente, la requête a échoué. Cet objet inclut des informations décrivant le problème rencontré. Lorsqu'une requête échoue, aucune table n'est créée et aucune donnée n'est ajoutée.
    • Si la propriété status.errorResult est absente, la tâche a bien été exécutée. Toutefois, des erreurs non fatales, telles que des problèmes d'importation de lignes, ont pu se produire. Ces erreurs sont répertoriées dans la propriété status.errors de l'objet de tâche renvoyé.

Remarques relatives à l'API :

  • Les tâches de chargement sont atomiques et cohérentes. En cas d'échec d'une tâche de chargement, aucune donnée n'est disponible. Si une tâche aboutit, toutes les données sont disponibles.

  • Nous vous recommandons de générer un ID unique et de le transmettre en tant que jobReference.jobId lorsque vous appelez jobs.insert pour créer un job de chargement. Cette approche offre une protection plus robuste contre les pannes réseau, car le client peut lancer une requête ou effectuer de nouvelles tentatives en utilisant l'ID de job connu.

  • L'appel de jobs.insert avec un ID de tâche donné est idempotent. Vous pouvez effectuer autant de tentatives que vous le souhaitez avec le même ID de tâche. L'une de ces opérations tout au plus aboutira.

C#

Avant d'essayer cet exemple, suivez les instructions de configuration pour C# du 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 pour C#.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.

Utilisez la méthode BigQueryClient.CreateLoadJob() pour démarrer un job de chargement à partir de Cloud Storage. Pour utiliser JSONL, créez un objet CreateLoadJobOptions et définissez sa propriété SourceFormat sur FileFormat.NewlineDelimitedJson.


using Google.Apis.Bigquery.v2.Data;
using Google.Cloud.BigQuery.V2;
using System;

public class BigQueryLoadTableGcsJson
{
    public void LoadTableGcsJson(
        string projectId = "your-project-id",
        string datasetId = "your_dataset_id"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        var gcsURI = "gs://cloud-samples-data/bigquery/us-states/us-states.json";
        var dataset = client.GetDataset(datasetId);
        var schema = new TableSchemaBuilder {
            { "name", BigQueryDbType.String },
            { "post_abbr", BigQueryDbType.String }
        }.Build();
        TableReference destinationTableRef = dataset.GetTableReference(
            tableId: "us_states");
        // Create job configuration
        var jobOptions = new CreateLoadJobOptions()
        {
            SourceFormat = FileFormat.NewlineDelimitedJson
        };
        // Create and run job
        BigQueryJob loadJob = client.CreateLoadJob(
            sourceUri: gcsURI, destination: destinationTableRef,
            schema: schema, options: jobOptions);
        loadJob = loadJob.PollUntilCompleted().ThrowOnAnyError();  // Waits for the job to complete.
        // Display the number of rows uploaded
        BigQueryTable table = client.GetTable(destinationTableRef);
        Console.WriteLine(
            $"Loaded {table.Resource.NumRows} rows to {table.FullyQualifiedId}");
    }
}

Go

Avant d'essayer cet exemple, suivez les instructions de configuration pour Go du 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 pour Go.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes. 

import (
	"context"
	"fmt"

	"cloud.google.com/go/bigquery"
)

// importJSONExplicitSchema demonstrates loading newline-delimited JSON data from Cloud Storage
// into a BigQuery table and providing an explicit schema for the data.
func importJSONExplicitSchema(projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	gcsRef := bigquery.NewGCSReference("gs://cloud-samples-data/bigquery/us-states/us-states.json")
	gcsRef.SourceFormat = bigquery.JSON
	gcsRef.Schema = bigquery.Schema{
		{Name: "name", Type: bigquery.StringFieldType},
		{Name: "post_abbr", Type: bigquery.StringFieldType},
	}
	loader := client.Dataset(datasetID).Table(tableID).LoaderFrom(gcsRef)
	loader.WriteDisposition = bigquery.WriteEmpty

	job, err := loader.Run(ctx)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}

	if status.Err() != nil {
		return fmt.Errorf("job completed with error: %v", status.Err())
	}
	return nil
}

Java

Avant d'essayer cet exemple, suivez les instructions de configuration pour Java du 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 pour Java.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.

Utilisez la méthode LoadJobConfiguration.builder (tableId, sourceUri) pour démarrer un job de chargement à partir de Cloud Storage. Pour utiliser le format JSON délimité par un retour à la ligne, utilisez LoadJobConfiguration.setFormatOptions (FormatOptions.json ()).

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.FormatOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.LoadJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableId;

// Sample to load JSON data from Cloud Storage into a new BigQuery table
public class LoadJsonFromGCS {

  public static void runLoadJsonFromGCS() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/us-states/us-states.json";
    Schema schema =
        Schema.of(
            Field.of("name", StandardSQLTypeName.STRING),
            Field.of("post_abbr", StandardSQLTypeName.STRING));
    loadJsonFromGCS(datasetName, tableName, sourceUri, schema);
  }

  public static void loadJsonFromGCS(
      String datasetName, String tableName, String sourceUri, Schema schema) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, tableName);
      LoadJobConfiguration loadConfig =
          LoadJobConfiguration.newBuilder(tableId, sourceUri)
              .setFormatOptions(FormatOptions.json())
              .setSchema(schema)
              .build();

      // Load data from a GCS JSON file into the table
      Job job = bigquery.create(JobInfo.of(loadConfig));
      // Blocks until this load table job completes its execution, either failing or succeeding.
      job = job.waitFor();
      if (job.isDone()) {
        System.out.println("Json from GCS successfully loaded in a table");
      } else {
        System.out.println(
            "BigQuery was unable to load into the table due to an error:"
                + job.getStatus().getError());
      }
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Column not added during load append \n" + e.toString());
    }
  }
}

Node.js

Avant d'essayer cet exemple, suivez les instructions de configuration pour Node.js du 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 pour Node.js.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes. 

// Import the Google Cloud client libraries
const {BigQuery} = require('@google-cloud/bigquery');
const {Storage} = require('@google-cloud/storage');

// Instantiate clients
const bigquery = new BigQuery();
const storage = new Storage();

/**
 * This sample loads the json file at
 * https://storage.googleapis.com/cloud-samples-data/bigquery/us-states/us-states.json
 *
 * TODO(developer): Replace the following lines with the path to your file.
 */
const bucketName = 'cloud-samples-data';
const filename = 'bigquery/us-states/us-states.json';

async function loadJSONFromGCS() {
  // Imports a GCS file into a table with manually defined schema.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table";

  // Configure the load job. For full list of options, see:
  // https://cloud.google.com/bigquery/docs/reference/rest/v2/Job#JobConfigurationLoad
  const metadata = {
    sourceFormat: 'NEWLINE_DELIMITED_JSON',
    schema: {
      fields: [
        {name: 'name', type: 'STRING'},
        {name: 'post_abbr', type: 'STRING'},
      ],
    },
    location: 'US',
  };

  // Load data from a Google Cloud Storage file into the table
  const [job] = await bigquery
    .dataset(datasetId)
    .table(tableId)
    .load(storage.bucket(bucketName).file(filename), metadata);
  // load() waits for the job to finish
  console.log(`Job ${job.id} completed.`);

  // Check the job's status for errors
  const errors = job.status.errors;
  if (errors && errors.length > 0) {
    throw errors;
  }
}

PHP

Avant d'essayer cet exemple, suivez les instructions de configuration pour PHP du 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 pour PHP.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes. 

use Google\Cloud\BigQuery\BigQueryClient;
use Google\Cloud\Core\ExponentialBackoff;

/** Uncomment and populate these variables in your code */
// $projectId  = 'The Google project ID';
// $datasetId  = 'The BigQuery dataset ID';

// instantiate the bigquery table service
$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);
$dataset = $bigQuery->dataset($datasetId);
$table = $dataset->table('us_states');

// create the import job
$gcsUri = 'gs://cloud-samples-data/bigquery/us-states/us-states.json';
$schema = [
    'fields' => [
        ['name' => 'name', 'type' => 'string'],
        ['name' => 'post_abbr', 'type' => 'string']
    ]
];
$loadConfig = $table->loadFromStorage($gcsUri)->schema($schema)->sourceFormat('NEWLINE_DELIMITED_JSON');
$job = $table->runJob($loadConfig);
// poll the job until it is complete
$backoff = new ExponentialBackoff(10);
$backoff->execute(function () use ($job) {
    print('Waiting for job to complete' . PHP_EOL);
    $job->reload();
    if (!$job->isComplete()) {
        throw new Exception('Job has not yet completed', 500);
    }
});
// check if the job has errors
if (isset($job->info()['status']['errorResult'])) {
    $error = $job->info()['status']['errorResult']['message'];
    printf('Error running job: %s' . PHP_EOL, $error);
} else {
    print('Data imported successfully' . PHP_EOL);
}

Python

Avant d'essayer cet exemple, suivez les instructions de configuration pour Python du 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 pour Python.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.

Utilisez la méthode Client.load_table_from_uri() pour démarrer une tâche de chargement à partir de Cloud Storage. Pour utiliser JSONL, définissez la propriété LoadJobConfig.source_format sur la chaîne NEWLINE_DELIMITED_JSON et transmettez la configuration du job en tant qu'argument job_config à la méthode load_table_from_uri(). 
from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the table to create.
# table_id = "your-project.your_dataset.your_table_name"

job_config = bigquery.LoadJobConfig(
    schema=[
        bigquery.SchemaField("name", "STRING"),
        bigquery.SchemaField("post_abbr", "STRING"),
    ],
    source_format=bigquery.SourceFormat.NEWLINE_DELIMITED_JSON,
)
uri = "gs://cloud-samples-data/bigquery/us-states/us-states.json"

load_job = client.load_table_from_uri(
    uri,
    table_id,
    location="US",  # Must match the destination dataset location.
    job_config=job_config,
)  # Make an API request.

load_job.result()  # Waits for the job to complete.

destination_table = client.get_table(table_id)
print("Loaded {} rows.".format(destination_table.num_rows))

Ruby

Avant d'essayer cet exemple, suivez les instructions de configuration pour Ruby du 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 pour Ruby.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.

Utilisez la méthode Dataset.load_job() pour démarrer un job de chargement à partir de Cloud Storage. Pour utiliser JSONL, définissez le paramètre format sur "json".

require "google/cloud/bigquery"

def load_table_gcs_json dataset_id = "your_dataset_id"
  bigquery = Google::Cloud::Bigquery.new
  dataset  = bigquery.dataset dataset_id
  gcs_uri  = "gs://cloud-samples-data/bigquery/us-states/us-states.json"
  table_id = "us_states"

  load_job = dataset.load_job table_id, gcs_uri, format: "json" do |schema|
    schema.string "name"
    schema.string "post_abbr"
  end
  puts "Starting job #{load_job.job_id}"

  load_job.wait_until_done! # Waits for table load to complete.
  puts "Job finished."

  table = dataset.table table_id
  puts "Loaded #{table.rows_count} rows to table #{table.id}"
end

Charger des données JSON imbriquées et répétées

BigQuery accepte le chargement de données imbriquées et répétées à partir de formats sources compatibles avec les schémas basés sur des objets, tels que JSON, Avro, ORC, Parquet, Firestore et Datastore.

Un objet JSON, y compris d'éventuels champs imbriqués ou répétés, doit apparaître sur chaque ligne.

L'exemple suivant présente un échantillon de données imbriquées ou répétées. La table contient des informations sur des personnes. Elle comporte les champs suivants :

  • id
  • first_name
  • last_name
  • dob (date de naissance)
  • addresses (champ imbriqué et répété)
    • addresses.status (actuel ou précédent)
    • addresses.address
    • addresses.city
    • addresses.state
    • addresses.zip
    • addresses.numberOfYears (années à l'adresse)

Le fichier de données JSON ressemblerait à ce qui suit. Notez que le champ d'adresse contient un tableau de valeurs (indiqué par [ ]). 

{"id":"1","first_name":"John","last_name":"Doe","dob":"1968-01-22","addresses":[{"status":"current","address":"123 First Avenue","city":"Seattle","state":"WA","zip":"11111","numberOfYears":"1"},{"status":"previous","address":"456 Main Street","city":"Portland","state":"OR","zip":"22222","numberOfYears":"5"}]}
{"id":"2","first_name":"Jane","last_name":"Doe","dob":"1980-10-16","addresses":[{"status":"current","address":"789 Any Avenue","city":"New York","state":"NY","zip":"33333","numberOfYears":"2"},{"status":"previous","address":"321 Main Street","city":"Hoboken","state":"NJ","zip":"44444","numberOfYears":"3"}]}

Le schéma de la table devrait ressembler à ceci :

[
    {
        "name": "id",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "first_name",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "last_name",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "dob",
        "type": "DATE",
        "mode": "NULLABLE"
    },
    {
        "name": "addresses",
        "type": "RECORD",
        "mode": "REPEATED",
        "fields": [
            {
                "name": "status",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "address",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "city",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "state",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "zip",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "numberOfYears",
                "type": "STRING",
                "mode": "NULLABLE"
            }
        ]
    }
]

Pour en savoir plus sur la spécification d'un schéma imbriqué et répété, consultez la page Spécifier des champs imbriqués et répétés.

Charger des données JSON semi-structurées

BigQuery accepte le chargement de données semi-structurées, dans lesquelles un champ peut accepter des valeurs de différents types. L'exemple suivant montre des données semblables à l'exemple précédent de données JSON imbriquées et répétées, à l'exception du champ address qui peut être un STRING, un STRUCT, ou un ARRAY :

{"id":"1","first_name":"John","last_name":"Doe","dob":"1968-01-22","address":"123 First Avenue, Seattle WA 11111"}

{"id":"2","first_name":"Jane","last_name":"Doe","dob":"1980-10-16","address":{"status":"current","address":"789 Any Avenue","city":"New York","state":"NY","zip":"33333","numberOfYears":"2"}}

{"id":"3","first_name":"Bob","last_name":"Doe","dob":"1982-01-10","address":[{"status":"current","address":"789 Any Avenue","city":"New York","state":"NY","zip":"33333","numberOfYears":"2"}, "321 Main Street Hoboken NJ 44444"]}

Vous pouvez charger ces données dans BigQuery à l'aide du schéma suivant :

[
    {
        "name": "id",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "first_name",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "last_name",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "dob",
        "type": "DATE",
        "mode": "NULLABLE"
    },
    {
        "name": "address",
        "type": "JSON",
        "mode": "NULLABLE"
    }
]

Le champ address est chargé dans une colonne de type JSON qui lui permet de contenir les types mixtes de l'exemple. Vous pouvez ingérer des données sous la forme JSON, qu'elles contiennent des types mixtes ou non. Par exemple, vous pouvez spécifier JSON au lieu de STRING comme type de champ first_name. Pour en savoir plus, consultez la page Utiliser des données JSON en langage GoogleSQL.

Ajouter ou écraser une table avec des données JSON

Vous pouvez charger des données supplémentaires dans une table à partir de fichiers sources ou en ajoutant des résultats de requête.

Dans la console Google Cloud, utilisez l'option Préférence d'écriture pour spécifier l'action à entreprendre lorsque vous chargez des données à partir d'un fichier source ou d'un résultat de requête.

Vous disposez des options suivantes lorsque vous chargez des données supplémentaires dans une table :

Option de la console Option de l'outil bq Propriété de l'API BigQuery Description
Écrire si la table est vide Non compatible WRITE_EMPTY N'écrit les données que si la table est vide.
Ajouter à la table --noreplace ou --replace=false. Si --[no]replace n'est pas spécifié, les données sont ajoutées par défaut. WRITE_APPEND (Par défaut) Ajoute les données à la fin de la table.
Écraser la table --replace ou --replace=true WRITE_TRUNCATE Efface toutes les données existantes d'une table avant d'écrire les nouvelles données. Cette action supprime également le schéma de la table, la sécurité au niveau des lignes et la clé Cloud KMS.

Si vous chargez des données dans une table existante, la tâche de chargement peut les ajouter ou écraser la table.

Vous pouvez ajouter ou écraser une table à l'aide de l'une des méthodes suivantes :

  • Google Cloud Console
  • Commande bq load de l'outil de ligne de commande bq
  • Méthode API jobs.insert et configuration d'une tâche load
  • Bibliothèques clientes

Console

  1. Dans la console Google Cloud, accédez à la page BigQuery.

    Accéder à BigQuery

  2. Dans le volet Explorateur, développez votre projet, puis sélectionnez un ensemble de données.
  3. Dans la section Informations sur l'ensemble de données, cliquez sur Créer une table.
  4. Dans le panneau Créer une table, spécifiez les détails suivants :
    1. Dans la section Source, sélectionnez Google Cloud Storage dans la liste Créer une table à partir de. Ensuite, procédez comme suit :
      1. Sélectionnez un fichier dans le bucket Cloud Storage ou saisissez l'URI Cloud Storage. Vous ne pouvez pas inclure plusieurs URI dans la console Google Cloud. En revanche, les caractères génériques sont acceptés. Le bucket Cloud Storage doit se trouver au même emplacement que l'ensemble de données contenant la table que vous souhaitez créer, ajouter ou écraser. Sélection d'un fichier source pour créer une table BigQuery
      2. Pour le champ Format de fichier, sélectionnez JSONL (JSON délimité par un retour à la ligne).
    2. Dans la section Destination, spécifiez les détails suivants :
      1. Pour Ensemble de données, sélectionnez l'ensemble de données dans lequel vous souhaitez créer la table.
      2. Dans le champ Table, saisissez le nom de la table que vous souhaitez créer.
      3. Vérifiez que le champ Type de table est défini sur Table native.
    3. Dans la section Schéma, saisissez la définition du schéma. Pour activer la détection automatique du schéma, sélectionnez Détection automatique. Vous pouvez saisir les informations de schéma manuellement à l'aide de l'une des méthodes suivantes :
      • Option 1 : Cliquez sur Modifier sous forme de texte et collez le schéma sous la forme d'un tableau JSON. Lorsque vous utilisez un tableau JSON, vous générez le schéma en utilisant le même processus que pour la création d'un fichier de schéma JSON. Vous pouvez afficher le schéma d'une table existante au format JSON en saisissant la commande suivante : 
            bq show --format=prettyjson dataset.table
      • Option 2 : Cliquez sur  Ajouter un champ et saisissez le schéma de la table. Spécifiez le nom, le type et le mode de chaque champ.
    4. Facultatif : spécifiez les paramètres de partitionnement et de clustering. Pour en savoir plus, consultez les pages Créer des tables partitionnées et Créer et utiliser des tables en cluster. Vous ne pouvez pas convertir une table en table partitionnée ou en cluster en y ajoutant des données ou les écrasant. La console Google Cloud ne permet pas d'ajouter ni d'écraser des données dans des tables partitionnées ou en cluster lors d'une tâche de chargement.
    5. Cliquez sur Options avancées et procédez comme suit :
      • Sous Préférences d'écriture, choisissez Ajouter à la table ou Écraser la table.
      • Pour le champ Nombre d'erreurs autorisées, acceptez la valeur par défaut 0 ou saisissez le nombre maximal de lignes contenant des erreurs pouvant être ignorées. Si le nombre de lignes contenant des erreurs dépasse cette valeur, la tâche renverra un message invalid et échouera. Cette option ne s'applique qu'aux fichiers CSV et JSON.
      • Si vous souhaitez ignorer les valeurs d'une ligne qui ne sont pas présentes dans le schéma de la table, sélectionnez Valeurs inconnues.
      • Pour le champ Chiffrement, cliquez sur Clé gérée par le client afin d'utiliser une clé Cloud Key Management Service. Si vous conservez le paramètre Clé gérée par Google, BigQuery chiffre les données au repos.
    6. Cliquez sur Créer la table.

SQL

Utilisez l'instruction LDD LOAD DATA : L'exemple suivant ajoute un fichier JSON à la table mytable :

  1. Dans la console Google Cloud, accédez à la page BigQuery.

    Accéder à BigQuery

  2. Dans l'éditeur de requête, saisissez l'instruction suivante :

    LOAD DATA INTO mydataset.mytable
FROM FILES (
  format = 'JSON',
  uris = ['gs://bucket/path/file.json']);

  3. Cliquez sur Exécuter.

Pour en savoir plus sur l'exécution des requêtes, consultez Exécuter une requête interactive.

bq

Exécutez la commande bq load, définissez NEWLINE_DELIMITED_JSON à l'aide de l'option --source_format et spécifiez un URI Cloud Storage. Vous pouvez inclure un seul URI, une liste d'URI séparés par des virgules ou un URI contenant un caractère générique.

Fournissez le schéma de manière intégrée ou dans un fichier de définition de schéma, ou utilisez la détection automatique du schéma.

Spécifiez l'option --replace pour écraser la table. Utilisez l'option --noreplace pour ajouter des données à la table. Si aucune option n'est spécifiée, les données sont ajoutées par défaut.

Il est possible de modifier le schéma de la table lorsque vous y ajoutez ou écrasez des données. Pour en savoir plus sur les modifications de schéma acceptées lors d'un chargement, consultez la page Modifier des schémas de table.

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

Les autres options facultatives sont les suivantes :

  • --max_bad_records : entier spécifiant le nombre maximal d'enregistrements incorrects autorisés avant l'échec total de la tâche. La valeur par défaut est 0. Au plus, cinq erreurs de n'importe quel type sont renvoyées, quelle que soit la valeur --max_bad_records.
  • --ignore_unknown_values : si spécifié, permet d'autoriser et d'ignorer les valeurs supplémentaires non reconnues dans les données CSV ou JSON.
  • --autodetect : permet d'activer la détection automatique du schéma pour les données CSV et JSON.
  • --destination_kms_key : clé Cloud KMS pour le chiffrement des données de la table.
bq --location=LOCATION load \
--[no]replace \
--source_format=FORMAT \
DATASET.TABLE \
PATH_TO_SOURCE \
SCHEMA

Remplacez les éléments suivants :

  • LOCATION : votre emplacement. L'option --location est facultative. Vous pouvez définir une valeur par défaut correspondant à l'emplacement en utilisant le fichier .bigqueryrc.
  • FORMAT : NEWLINE_DELIMITED_JSON.
  • DATASET : ensemble de données existant.
  • TABLE : nom de la table dans laquelle vous chargez des données.
  • PATH_TO_SOURCE : URI Cloud Storage complet ou liste d'URI séparés par des virgules. Les caractères génériques sont également acceptés.
  • SCHEMA : schéma valide. Ce schéma peut être un fichier JSON local ou il peut être intégré à la commande. Vous pouvez également utiliser l'option --autodetect au lieu de fournir une définition de schéma.

Exemples :

La commande suivante permet de charger les données de gs://mybucket/mydata.json et d'écraser la table mytable de mydataset. Le schéma est défini à l'aide de la fonctionnalité de détection automatique du schéma.

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

La commande suivante permet de charger les données de gs://mybucket/mydata.json et d'ajouter des données à la table mytable de mydataset. Le schéma est défini à l'aide d'un fichier de schéma JSON (myschema).

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

API

  1. Créez une tâche de chargement (load) qui pointe vers les données sources dans Cloud Storage.

  2. (Facultatif) Spécifiez votre emplacement dans la propriété location de la section jobReference de la ressource de tâche.

  3. La propriété source URIs doit être complète et respecter le format gs://BUCKET/OBJECT. Vous pouvez inclure plusieurs URI sous la forme d'une liste d'éléments séparés par une virgule. Les caractères génériques sont également acceptés.

  4. Spécifiez le format de données en définissant la propriété configuration.load.sourceFormat sur NEWLINE_DELIMITED_JSON.

  5. Spécifiez la préférence d'écriture en définissant la propriété configuration.load.writeDisposition sur WRITE_TRUNCATE ou WRITE_APPEND.

Go

Avant d'essayer cet exemple, suivez les instructions de configuration pour Go du 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 pour Go.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes. 

import (
	"context"
	"fmt"

	"cloud.google.com/go/bigquery"
)

// importJSONTruncate demonstrates loading data from newline-delimeted JSON data in Cloud Storage
// and overwriting/truncating data in the existing table.
func importJSONTruncate(projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	gcsRef := bigquery.NewGCSReference("gs://cloud-samples-data/bigquery/us-states/us-states.json")
	gcsRef.SourceFormat = bigquery.JSON
	gcsRef.AutoDetect = true
	loader := client.Dataset(datasetID).Table(tableID).LoaderFrom(gcsRef)
	loader.WriteDisposition = bigquery.WriteTruncate

	job, err := loader.Run(ctx)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}

	if status.Err() != nil {
		return fmt.Errorf("job completed with error: %v", status.Err())
	}

	return nil
}

Java 

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.FormatOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.LoadJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableId;

// Sample to overwrite the BigQuery table data by loading a JSON file from GCS
public class LoadJsonFromGCSTruncate {

  public static void runLoadJsonFromGCSTruncate() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/us-states/us-states.json";
    Schema schema =
        Schema.of(
            Field.of("name", StandardSQLTypeName.STRING),
            Field.of("post_abbr", StandardSQLTypeName.STRING));
    loadJsonFromGCSTruncate(datasetName, tableName, sourceUri, schema);
  }

  public static void loadJsonFromGCSTruncate(
      String datasetName, String tableName, String sourceUri, Schema schema) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, tableName);
      LoadJobConfiguration loadConfig =
          LoadJobConfiguration.newBuilder(tableId, sourceUri)
              .setFormatOptions(FormatOptions.json())
              // Set the write disposition to overwrite existing table data
              .setWriteDisposition(JobInfo.WriteDisposition.WRITE_TRUNCATE)
              .setSchema(schema)
              .build();

      // Load data from a GCS JSON file into the table
      Job job = bigquery.create(JobInfo.of(loadConfig));
      // Blocks until this load table job completes its execution, either failing or succeeding.
      job = job.waitFor();
      if (job.isDone()) {
        System.out.println("Table is successfully overwritten by JSON file loaded from GCS");
      } else {
        System.out.println(
            "BigQuery was unable to load into the table due to an error:"
                + job.getStatus().getError());
      }
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Column not added during load append \n" + e.toString());
    }
  }
}

Node.js

Avant d'essayer cet exemple, suivez les instructions de configuration pour Node.js du 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 pour Node.js.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes. 

// Import the Google Cloud client libraries
const {BigQuery} = require('@google-cloud/bigquery');
const {Storage} = require('@google-cloud/storage');

// Instantiate clients
const bigquery = new BigQuery();
const storage = new Storage();

/**
 * This sample loads the JSON file at
 * https://storage.googleapis.com/cloud-samples-data/bigquery/us-states/us-states.json
 *
 * TODO(developer): Replace the following lines with the path to your file.
 */
const bucketName = 'cloud-samples-data';
const filename = 'bigquery/us-states/us-states.json';

async function loadJSONFromGCSTruncate() {
  /**
   * Imports a GCS file into a table and overwrites
   * table data if table already exists.
   */

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table";

  // Configure the load job. For full list of options, see:
  // https://cloud.google.com/bigquery/docs/reference/rest/v2/Job#JobConfigurationLoad
  const metadata = {
    sourceFormat: 'NEWLINE_DELIMITED_JSON',
    schema: {
      fields: [
        {name: 'name', type: 'STRING'},
        {name: 'post_abbr', type: 'STRING'},
      ],
    },
    // Set the write disposition to overwrite existing table data.
    writeDisposition: 'WRITE_TRUNCATE',
  };

  // Load data from a Google Cloud Storage file into the table
  const [job] = await bigquery
    .dataset(datasetId)
    .table(tableId)
    .load(storage.bucket(bucketName).file(filename), metadata);
  // load() waits for the job to finish
  console.log(`Job ${job.id} completed.`);

  // Check the job's status for errors
  const errors = job.status.errors;
  if (errors && errors.length > 0) {
    throw errors;
  }
}

PHP

Avant d'essayer cet exemple, suivez les instructions de configuration pour PHP du 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 pour PHP.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes. 

use Google\Cloud\BigQuery\BigQueryClient;
use Google\Cloud\Core\ExponentialBackoff;

/** Uncomment and populate these variables in your code */
// $projectId = 'The Google project ID';
// $datasetId = 'The BigQuery dataset ID';
// $tableID = 'The BigQuery table ID';

// instantiate the bigquery table service
$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);
$table = $bigQuery->dataset($datasetId)->table($tableId);

// create the import job
$gcsUri = 'gs://cloud-samples-data/bigquery/us-states/us-states.json';
$loadConfig = $table->loadFromStorage($gcsUri)->sourceFormat('NEWLINE_DELIMITED_JSON')->writeDisposition('WRITE_TRUNCATE');
$job = $table->runJob($loadConfig);

// poll the job until it is complete
$backoff = new ExponentialBackoff(10);
$backoff->execute(function () use ($job) {
    print('Waiting for job to complete' . PHP_EOL);
    $job->reload();
    if (!$job->isComplete()) {
        throw new Exception('Job has not yet completed', 500);
    }
});

// check if the job has errors
if (isset($job->info()['status']['errorResult'])) {
    $error = $job->info()['status']['errorResult']['message'];
    printf('Error running job: %s' . PHP_EOL, $error);
} else {
    print('Data imported successfully' . PHP_EOL);
}

Python

Pour remplacer les lignes d'une table existante, définissez la propriété LoadJobConfig.write_disposition sur la chaîne WRITE_TRUNCATE.

Avant d'essayer cet exemple, suivez les instructions de configuration pour Python du 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 pour Python.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes. 

import io

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the table to create.
# table_id = "your-project.your_dataset.your_table_name

job_config = bigquery.LoadJobConfig(
    schema=[
        bigquery.SchemaField("name", "STRING"),
        bigquery.SchemaField("post_abbr", "STRING"),
    ],
)

body = io.BytesIO(b"Washington,WA")
client.load_table_from_file(body, table_id, job_config=job_config).result()
previous_rows = client.get_table(table_id).num_rows
assert previous_rows > 0

job_config = bigquery.LoadJobConfig(
    write_disposition=bigquery.WriteDisposition.WRITE_TRUNCATE,
    source_format=bigquery.SourceFormat.NEWLINE_DELIMITED_JSON,
)

uri = "gs://cloud-samples-data/bigquery/us-states/us-states.json"
load_job = client.load_table_from_uri(
    uri, table_id, job_config=job_config
)  # Make an API request.

load_job.result()  # Waits for the job to complete.

destination_table = client.get_table(table_id)
print("Loaded {} rows.".format(destination_table.num_rows))

Ruby

Pour remplacer les lignes d'une table existante, définissez le paramètre write de Table.load_job() sur "WRITE_TRUNCATE".

Avant d'essayer cet exemple, suivez les instructions de configuration pour Ruby du 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 pour Ruby.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes. 

require "google/cloud/bigquery"

def load_table_gcs_json_truncate dataset_id = "your_dataset_id",
                                 table_id   = "your_table_id"
  bigquery = Google::Cloud::Bigquery.new
  dataset  = bigquery.dataset dataset_id
  gcs_uri  = "gs://cloud-samples-data/bigquery/us-states/us-states.json"

  load_job = dataset.load_job table_id,
                              gcs_uri,
                              format: "json",
                              write:  "truncate"
  puts "Starting job #{load_job.job_id}"

  load_job.wait_until_done! # Waits for table load to complete.
  puts "Job finished."

  table = dataset.table table_id
  puts "Loaded #{table.rows_count} rows to table #{table.id}"
end

Charger des données JSON partitionnées avec Hive

BigQuery accepte le chargement de données JSON partitionnées avec Hive et stockées dans Cloud Storage. Il insère alors les colonnes de partitionnement Hive en tant que colonnes dans la table de destination gérée par BigQuery. Pour en savoir plus, consultez la page Charger des données partitionnées externes.

Détails du chargement de données JSON

Cette section décrit comment BigQuery analyse divers types de données lorsque vous chargez des données JSON.

Types de données

Booléen. BigQuery peut analyser n'importe laquelle des paires suivantes pour les données booléennes : 1 ou 0, true ou false, t ou f, yes ou no, ou y ou n (tous non sensibles à la casse). La détection automatique de schéma détecte automatiquement ces paires, à l'exception de 0 et 1.

Octets. Les colonnes de type OCTETS doivent être encodées en Base64.

Date. Les colonnes de type DATE doivent être au format YYYY-MM-DD.

Datetime. Les colonnes de type DATETIME doivent être au format YYYY-MM-DD HH:MM:SS[.SSSSSS].

Géographie. Les colonnes de type GEOGRAPHY doivent contenir des chaînes à l'un des formats suivants :

  • Texte connu (WKT)
  • Binaire connu (WKB)
  • GeoJSON

Si vous utilisez le langage WKB, la valeur doit être encodée en hexadécimal.

La liste suivante présente des exemples de données valides :

  • WKT : POINT(1 2)
  • GeoJSON : { "type": "Point", "coordinates": [1, 2] }
  • WKB encodé en hexadécimal : 0101000000feffffffffffef3f0000000000000040

Avant de charger des données de type GEOGRAPHY, consultez également la section Charger des données géospatiales.

Interval. Les colonnes de type INTERVAL doivent être au format ISO 8601 PYMDTHMS, où :

  • P = indicateur signalant que la valeur représente une durée. Vous devez toujours l'inclure.
  • Y = année
  • M = mois
  • D = jour
  • T = Indicateur de la partie temporelle de la durée. Vous devez toujours l'inclure.
  • H = heure
  • M = minute
  • S = seconde. Les secondes peuvent être exprimées comme des valeurs entières ou fractionnaires de six chiffres au maximum avec une précision de l'ordre de la microseconde.

Vous pouvez indiquer une valeur négative en ajoutant un tiret (-).

La liste suivante présente des exemples de données valides :

  • P-10000Y0M-3660000DT-87840000H0M0S
  • P0Y0M0DT0H0M0.000001S
  • P10000Y0M3660000DT87840000H0M0S

Pour charger des données de type INTERVAL, vous devez utiliser la commande bq load et utiliser l'option --schema pour spécifier un schéma. Vous ne pouvez pas importer de données de type INTERVAL à l'aide de la console.

Time. Les colonnes de type TIME doivent être au format HH:MM:SS[.SSSSSS].

Timestamp. BigQuery accepte différents formats de code temporel. Le code temporel doit inclure une partie date et une partie heure.

  • La partie date peut être au format YYYY-MM-DD ou YYYY/MM/DD.

  • La partie du code temporel doit être au format HH:MM[:SS[.SSSSSS]] (les secondes et les fractions de secondes sont facultatives).

  • La date et l'heure doivent être séparées par un espace ou le caractère "T".

  • La date et l'heure peuvent également être suivies d'un décalage UTC ou de l'indicateur de zone UTC (Z). Pour en savoir plus, consultez la section Fuseaux horaires.

Par exemple, les valeurs de code temporel suivantes sont valides :

  • 2018-08-19 12:11
  • 2018-08-19 12:11:35
  • 2018-08-19 12:11:35.22
  • 2018/08/19 12:11
  • 2018-07-05 12:54:00 UTC
  • 2018-08-19 07:11:35.220 -05:00
  • 2018-08-19T12:11:35.220Z

Si vous fournissez un schéma, BigQuery accepte également l'epoch Unix comme référentiel pour les valeurs de code temporel. Toutefois, la détection automatique de schéma ne détecte pas ce cas et traite la valeur comme un type numérique ou une chaîne.

Exemples de valeurs de code temporel avec l'epoch Unix :

  • 1534680695
  • 1.534680695e11

Tableau (champ répété). La valeur doit être un tableau JSON ou null. La valeur JSON null est convertie en valeur SQL NULL. Le tableau lui-même ne peut pas contenir de valeurs null.

Détection automatique de schéma

Cette section décrit le comportement de la détection automatique de schéma lors du chargement de fichiers JSON.

Champs JSON imbriqués et répétés

BigQuery infère les champs imbriqués et répétés dans les fichiers JSON. Si une valeur de champ est un objet JSON, BigQuery charge la colonne sous la forme d'un type RECORD. Si une valeur de champ est un tableau, BigQuery charge la colonne en tant que colonne répétée. Pour obtenir un exemple de données JSON avec des données imbriquées et répétées, consultez la section Charger des données JSON imbriquées et répétées.

Conversion de chaîne

Si vous activez la détection automatique de schéma, BigQuery convertit les chaînes en types booléens, numériques ou de date/heure, lorsque cela est possible. Par exemple, en utilisant les données JSON suivantes, la détection automatique de schéma convertit le champ id en une colonne INTEGER :

{ "name":"Alice","id":"12"}
{ "name":"Bob","id":"34"}
{ "name":"Charles","id":"45"}

Types d'encodages

BigQuery s'attend à ce que les données JSON soient encodées au format UTF-8. Si vous avez des fichiers JSON avec d'autres types d'encodages compatibles, vous devez spécifier explicitement l'encodage à l'aide de l'option --encoding afin que BigQuery convertisse les données au format UTF-8.

BigQuery accepte les types d'encodages suivants pour les fichiers JSON :

  • UTF-8
  • ISO-8859-1
  • UTF-16BE (UTF-16 Big Endian)
  • UTF-16LE (UTF-16 Little Endian)
  • UTF-32BE (UTF-32 Big Endian)
  • UTF-32LE (UTF-32 Little Endian)

Options JSON

Pour modifier la façon dont BigQuery analyse les données JSON, spécifiez des options supplémentaires dans la console Google Cloud, l'outil de ligne de commande bq, l'API ou les bibliothèques clientes.

Option JSON Option de la console Option de l'outil bq Propriété de l'API BigQuery Description
Nombre d'enregistrements incorrects autorisés Nombre d'erreurs autorisées --max_bad_records maxBadRecords (Java, Python) (Facultatif) Nombre maximal d'enregistrements incorrects pouvant être ignorés par BigQuery lors de l'exécution de la tâche. Si le nombre d'enregistrements incorrects dépasse cette valeur, une erreur "non valide" est renvoyée dans le résultat de la tâche. La valeur par défaut est "0", ce qui nécessite que tous les enregistrements soient valides.
Valeurs inconnues Ignorer les valeurs inconnues --ignore_unknown_values ignoreUnknownValues (Java, Python) (Facultatif) Indique si BigQuery doit autoriser des valeurs supplémentaires qui ne sont pas représentées dans le schéma de la table. Si le champ est défini sur "true", les valeurs supplémentaires sont ignorées. Si la valeur est "false", les enregistrements comportant des colonnes supplémentaires sont traités comme des enregistrements incorrects et, si le nombre d'enregistrements incorrects est trop élevé, une erreur "non valide" est renvoyée dans le résultat de la tâche. La valeur par défaut est "false". La propriété "sourceFormat" détermine ce que BigQuery traite comme valeur supplémentaire : CSV : colonnes finales ; JSON : valeurs nommées ne correspondant à aucun nom de colonne.
Encodage Aucun -E ou --encoding encoding (Python) (Facultatif) Codage des caractères des données. Les valeurs acceptées sont UTF-8, ISO-8859-1, UTF-16BE, UTF-16LE, UTF-32BE ou UTF-32LE. La valeur par défaut est UTF-8.

Étapes suivantes