Exporter des données de table vers Cloud Storage

Cette page explique comment exporter ou extraire des données des tables BigQuery vers Cloud Storage.

Une fois que vous avez chargé des données dans BigQuery, vous pouvez les exporter sous plusieurs formats. BigQuery peut exporter jusqu'à 1 Go de données dans un seul fichier. Si les données dépassent 1 Go, vous devez les exporter dans plusieurs fichiers. Le cas échéant, la taille des fichiers varie.

Avec un service tel que Dataflow, vous pouvez lire des données depuis BigQuery au lieu de les exporter manuellement. Pour en savoir plus sur l'utilisation de Dataflow afin de lire et d'écrire dans BigQuery, consultez la page E/S BigQuery dans la documentation Apache Beam.

Vous pouvez également exporter les résultats d'une requête à l'aide de l'instruction EXPORT DATA. Vous pouvez utiliser EXPORT DATA OPTIONS pour exporter des vues vers Cloud Storage.

Limites en termes d'exportation

Lorsque vous exportez des données depuis BigQuery, tenez compte des points suivants :

  • Vous ne pouvez pas exporter de données de table vers un fichier local, Google Sheets ou Google Drive. La seule zone d'exportation acceptée est Cloud Storage. Pour en savoir plus sur l'enregistrement des résultats de requête, consultez la section Télécharger et enregistrer des résultats de requête.
  • Vous pouvez exporter jusqu'à 1 Go de données de table dans un seul fichier. Si les données dépassent 1 Go, utilisez un caractère générique pour les exporter dans plusieurs fichiers. Le cas échéant, la taille des fichiers varie. Pour limiter la taille du fichier exporté, vous pouvez partitionner vos données et exporter chaque partition.
  • La taille de fichier générée lorsque vous utilisez l'instruction EXPORT DATA n'est pas garantie.
  • Le nombre de fichiers générés par une tâche d'exportation peut varier.
  • Vous ne pouvez pas exporter des données imbriquées et répétées au format CSV. Ces données sont acceptées pour les exportations aux formats Avro, JSON et Parquet.
  • Lorsque vous exportez des données au format JSON, les types de données INT64 (entier) sont encodés sous la forme de chaînes JSON, afin de préserver la précision 64 bits lorsque les données sont lues par d'autres systèmes.
  • Vous ne pouvez pas exporter des données de plusieurs tables dans une seule tâche d'exportation.
  • Lorsque vous exportez des données à l'aide de la console Google Cloud, vous ne pouvez pas choisir un type de compression autre que GZIP.
  • Lorsque vous exportez des données vers un bucket Cloud Storage configuré avec des règles de conservation, l'écriture des fichiers dans le bucket par BigQuery peut échouer. Configurez la durée de conservation de sorte qu'elle soit plus longue que la durée des tâches d'exportation.
  • Lorsque vous exportez une table au format JSON, les symboles <, > et & sont convertis à l'aide de la notation Unicode \uNNNN, où N est un chiffre hexadécimal. Par exemple, profit&loss devient profit\u0026loss. Cette conversion Unicode est effectuée pour éviter les failles de sécurité.
  • L'ordre des données dans la table exportée n'est pas garanti, sauf si vous utilisez l'instruction EXPORT DATA et spécifiez une clause ORDER BY dans query_statement.
  • BigQuery n'accepte pas les chemins de ressource Cloud Storage qui incluent plusieurs barres obliques consécutives après la double barre oblique initiale. Le nom des objets Cloud Storage peut contenir plusieurs barres obliques ("/") consécutives. Toutefois, BigQuery convertit les barres obliques consécutives en une seule. Par exemple, le chemin d'accès à la ressource suivant, bien qu'il soit valide dans Cloud Storage, ne fonctionne pas dans BigQuery : gs://bucket/my//object//name.
  • Vous ne pouvez pas exporter les données d'instantanés de table.

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 du présent document.

Autorisations requises

Pour effectuer les tâches décrites dans ce document, vous devez disposer des autorisations suivantes.

Autorisations pour exporter des données depuis une table BigQuery

Pour exporter des données à partir d'une table BigQuery, vous devez disposer de l'autorisation IAM bigquery.tables.export.

Chacun des rôles Cloud IAM prédéfinis suivants inclut l'autorisation bigquery.tables.export :

  • roles/bigquery.dataViewer
  • roles/bigquery.dataOwner
  • roles/bigquery.dataEditor
  • roles/bigquery.admin

Autorisations d'exécuter une tâche d'exportation

Pour exécuter une tâche d'exportation, vous avez besoin de l'autorisation IAM bigquery.jobs.create.

Chacun des rôles IAM prédéfinis suivants inclut les autorisations dont vous avez besoin pour exécuter une tâche d'exportation :

  • roles/bigquery.user
  • roles/bigquery.jobUser
  • roles/bigquery.admin

Autorisations d'écrire les données dans le bucket Cloud Storage

Pour écrire les données dans un bucket Cloud Storage existant, vous devez disposer des autorisations IAM suivantes :

  • storage.objects.create
  • storage.objects.delete

Chacun des rôles IAM prédéfinis suivants inclut les autorisations dont vous avez besoin pour écrire les données dans un bucket Cloud Storage existant :

  • roles/storage.objectAdmin
  • roles/storage.admin

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

Considérations relatives aux emplacements

Cohébergez vos buckets Cloud Storage pour exporter des données :
  • Si votre ensemble de données BigQuery se trouve dans la zone multirégionale EU, le bucket Cloud Storage contenant les données que vous exportez doit se trouver dans la même zone multirégionale ou dans un emplacement inclus dans la zone multirégionale. Par exemple, si votre ensemble de données BigQuery se trouve dans la zone multirégionale EU, le bucket Cloud Storage peut être situé dans la région de Belgique europe-west1, qui se trouve dans l'Union européenne.

    Si votre ensemble de données se trouve dans la zone multirégionale US, vous pouvez exporter des données dans un bucket Cloud Storage situé à n'importe quel emplacement.

  • Si votre ensemble de données se trouve dans une région, votre bucket Cloud Storage doit se situer dans la même région. Par exemple, si votre ensemble de données se trouve dans la région de Tokyo asia-northeast1, votre bucket Cloud Storage ne peut pas se trouver dans l'emplacement multirégional ASIA.
Élaborez un plan de gestion des données :
  • Si vous choisissez une ressource de stockage régionale, telle qu'un ensemble de données BigQuery ou un bucket Cloud Storage, élaborez un plan de gestion géographique des données.

Pour en savoir plus sur les emplacements Cloud Storage, consultez la section Emplacements des buckets dans la documentation de Cloud Storage.

Déplacer des données BigQuery entre des emplacements

Il n'est pas possible de modifier l'emplacement d'un ensemble de données après sa création. Toutefois, vous pouvez effectuer une copie de l'ensemble de données. Vous ne pouvez pas déplacer un ensemble de données d'un emplacement à un autre, mais vous pouvez déplacer (recréer) un ensemble de données manuellement.

Formats d'exportation et types de compression

BigQuery accepte les formats de données et les types de compression suivants pour les données exportées.

Format des données Types de compression acceptés Détails
CSV GZIP

Vous pouvez contrôler le délimiteur CSV dans vos données exportées à l'aide de l'option --field_delimiter de l'outil de ligne de commande bq ou de la propriété du job d'extraction configuration.extract.fieldDelimiter.

Les données imbriquées et répétées ne sont pas acceptées.
JSON GZIP Les données imbriquées et répétées sont acceptées.
Avro DEFLATE, SNAPPY

GZIP n'est pas compatible avec les exportations au format Avro.

Les données imbriquées et répétées sont acceptées. Consultez la section Détails sur les exportations au format Avro.
Parquet SNAPPY, GZIP, ZSTD

Les données imbriquées et répétées sont acceptées. Consultez la section Détails sur les exportations au format Parquet.

Exporter les données

Vous pouvez exporter les données de la table de plusieurs façons :

  • En utilisant la console Google Cloud
  • En utilisant la commande bq extract de l'outil de ligne de commande bq
  • En envoyant une tâche extract via l'API ou les bibliothèques clientes

Les exporter

Pour exporter des données depuis une table BigQuery :

Console

  1. Ouvrez la page BigQuery dans la console Google Cloud.

    Accéder à BigQuery

  2. Dans le panneau Explorateur, développez votre projet et votre ensemble de données, puis sélectionnez la table.

  3. Dans le panneau de détails, cliquez sur Exporter et sélectionnez Exporter vers Cloud Storage.

  4. Dans la boîte de dialogue Exporter le tableau vers Google Cloud Storage :

    • Pour le champ Sélectionner l'emplacement Google Cloud Storage, recherchez le bucket, dossier ou fichier dans lequel vous souhaitez exporter les données.
    • Pour le champ Format d'exportation, choisissez le format des données exportées : CSV, JSON (délimité par un retour à la ligne), Avro ou Parquet.
    • Pour le champ Compression, sélectionnez un format de compression ou None pour n'appliquer aucune compression.
    • Cliquez sur Exporter pour exporter la table.

Pour vérifier la progression de la tâche, consultez en haut de la fenêtre de navigation l'historique de la tâche pour une tâche d'exportation.

Pour exporter des vues vers Cloud Storage, utilisez l'instruction EXPORT DATA OPTIONS.

SQL

Utilisez l'instruction EXPORT DATA. L'exemple suivant exporte les champs sélectionnés à partir d'une table nommée mydataset.table1 :

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

    Accéder à BigQuery

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

    EXPORT DATA
  OPTIONS (
    uri = 'gs://bucket/folder/*.csv',
    format = 'CSV',
    overwrite = true,
    header = true,
    field_delimiter = ';')
AS (
  SELECT field1, field2
  FROM mydataset.table1
  ORDER BY field1
);

  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 extract avec l'option --destination_format.

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

Les autres options facultatives sont les suivantes :

  • --compression : type de compression à utiliser pour les fichiers exportés.
  • --field_delimiter : caractère indiquant la délimitation entre les colonnes du fichier de sortie pour les exportations au format CSV. Les options \t et tab sont autorisées pour les délimiteurs de tabulation.
  • --print_header : si spécifié, affiche les lignes d'en-tête pour les formats comportant des en-têtes (par exemple, les fichiers CSV).
bq extract --location=location \
--destination_format format \
--compression compression_type \
--field_delimiter delimiter \
--print_header=boolean \
project_id:dataset.table \
gs://bucket/filename.ext

Où :

  • location est le nom du site. 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 est le format des données exportées : CSV, NEWLINE_DELIMITED_JSON, AVRO ou PARQUET.
  • compression_type est un type de compression compatible pour votre format de données. Consultez la page Formats d'exportation et types de compression.
  • delimiter est le caractère qui indique la limite entre les colonnes dans les exportations au format CSV. \t et tab sont les noms acceptés pour la tabulation.
  • boolean est true ou false. Lorsque la valeur est true, les lignes d'en-tête sont imprimées dans les données exportées si le format de ces dernières accepte les en-têtes. La valeur par défaut est true.
  • project_id est l'ID de votre projet.
  • dataset est le nom de l'ensemble de données source.
  • table est la table que vous exportez. Si vous utilisez un décorateur de partition, vous devez entourer le chemin d'accès de la table avec des guillemets simples ou échapper le caractère $.
  • bucket est le nom du bucket Cloud Storage vers lequel vous exportez les données. L'ensemble de données BigQuery et le bucket Cloud Storage doivent se trouver dans le même emplacement.
  • filename.ext est le nom et l'extension du fichier de données exporté. Pour effectuer l'exportation dans plusieurs fichiers, utilisez un caractère générique.

Exemples :

Par exemple, la commande suivante permet d'exporter mydataset.mytable dans un fichier compressé gzip nommé myfile.csv. myfile.csv est stocké dans un bucket Cloud Storage nommé example-bucket.

bq extract \
--compression GZIP \
'mydataset.mytable' \
gs://example-bucket/myfile.csv

Le format de destination par défaut est CSV. Pour exporter au format JSON ou Avro, spécifiez l'option destination_format, puis définissez-le sur NEWLINE_DELIMITED_JSON ou AVRO. Exemple :

bq extract \
--destination_format NEWLINE_DELIMITED_JSON \
'mydataset.mytable' \
gs://example-bucket/myfile.json

La commande suivante permet d'exporter mydataset.mytable dans un fichier Avro compressé à l'aide de Snappy. Le fichier se nomme myfile.avro. myfile.avro est exporté vers un bucket Cloud Storage nommé example-bucket.

bq extract \
--destination_format AVRO \
--compression SNAPPY \
'mydataset.mytable' \
gs://example-bucket/myfile.avro

La commande suivante exporte une seule partition de mydataset.my_partitioned_table dans un fichier CSV dans Cloud Storage :

bq extract \
--destination_format CSV \
'mydataset.my_partitioned_table$0' \
gs://example-bucket/single_partition.csv

API

Pour exporter des données, créez une tâche extract et insérez la configuration de la tâche.

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

  1. Créez une tâche d'extraction qui pointe vers les données sources BigQuery et la destination Cloud Storage.

  2. Spécifiez la table source à l'aide de l'objet de configuration sourceTable contenant les ID du projet, de l'ensemble de données et de la table.

  3. La propriété destination URI(s) doit être complète et respecter le format gs://bucket/filename.ext. Chaque URI peut contenir un caractère générique "*", qui doit être placé après le nom du bucket.

  4. Spécifiez le format de données en définissant la propriété configuration.extract.destinationFormat. Par exemple, pour exporter un fichier JSON, définissez cette propriété sur la valeur NEWLINE_DELIMITED_JSON.

  5. Pour vérifier l'état de la tâche, appelez jobs.get(job_id) avec l'ID de la tâche renvoyée 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 inclura des informations décrivant le problème rencontré.
    • Si la propriété status.errorResult est absente, la tâche a bien été exécutée. Toutefois, des erreurs non fatales 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 :

  • 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 une tâche. 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 tâche connu.

  • L'appel de jobs.insert avec un ID de tâche donné est idempotent. En d'autres termes, 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. 


using Google.Cloud.BigQuery.V2;
using System;

public class BigQueryExtractTable
{
    public void ExtractTable(
        string projectId = "your-project-id",
        string bucketName = "your-bucket-name")
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        // Define a destination URI. Use a single wildcard URI if you think
        // your exported data will be larger than the 1 GB maximum value.
        string destinationUri = $"gs://{bucketName}/shakespeare-*.csv";
        BigQueryJob job = client.CreateExtractJob(
            projectId: "bigquery-public-data",
            datasetId: "samples",
            tableId: "shakespeare",
            destinationUri: destinationUri
        );
        job = job.PollUntilCompleted().ThrowOnAnyError();  // Waits for the job to complete.
        Console.Write($"Exported table to {destinationUri}.");
    }
}

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

// exportTableAsCompressedCSV demonstrates using an export job to
// write the contents of a table into Cloud Storage as CSV.
func exportTableAsCSV(projectID, gcsURI string) error {
	// projectID := "my-project-id"
	// gcsUri := "gs://mybucket/shakespeare.csv"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	srcProject := "bigquery-public-data"
	srcDataset := "samples"
	srcTable := "shakespeare"

	gcsRef := bigquery.NewGCSReference(gcsURI)
	gcsRef.FieldDelimiter = ","

	extractor := client.DatasetInProject(srcProject, srcDataset).Table(srcTable).ExtractorTo(gcsRef)
	extractor.DisableHeader = true
	// You can choose to run the job in a specific location for more complex data locality scenarios.
	// Ex: In this example, source dataset and GCS bucket are in the US.
	extractor.Location = "US"

	job, err := extractor.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
	}
	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. 

import com.google.cloud.RetryOption;
import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.Table;
import com.google.cloud.bigquery.TableId;
import org.threeten.bp.Duration;

public class ExtractTableToCsv {

  public static void runExtractTableToCsv() {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "bigquery-public-data";
    String datasetName = "samples";
    String tableName = "shakespeare";
    String bucketName = "my-bucket";
    String destinationUri = "gs://" + bucketName + "/path/to/file";
    // For more information on export formats available see:
    // https://cloud.google.com/bigquery/docs/exporting-data#export_formats_and_compression_types
    // For more information on Job see:
    // https://googleapis.dev/java/google-cloud-clients/latest/index.html?com/google/cloud/bigquery/package-summary.html

    String dataFormat = "CSV";
    extractTableToCsv(projectId, datasetName, tableName, destinationUri, dataFormat);
  }

  // Exports datasetName:tableName to destinationUri as raw CSV
  public static void extractTableToCsv(
      String projectId,
      String datasetName,
      String tableName,
      String destinationUri,
      String dataFormat) {
    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(projectId, datasetName, tableName);
      Table table = bigquery.getTable(tableId);

      Job job = table.extract(dataFormat, destinationUri);

      // Blocks until this job completes its execution, either failing or succeeding.
      Job completedJob =
          job.waitFor(
              RetryOption.initialRetryDelay(Duration.ofSeconds(1)),
              RetryOption.totalTimeout(Duration.ofMinutes(3)));
      if (completedJob == null) {
        System.out.println("Job not executed since it no longer exists.");
        return;
      } else if (completedJob.getStatus().getError() != null) {
        System.out.println(
            "BigQuery was unable to extract due to an error: \n" + job.getStatus().getError());
        return;
      }
      System.out.println(
          "Table export successful. Check in GCS bucket for the " + dataFormat + " file.");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Table extraction job was interrupted. \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');

const bigquery = new BigQuery();
const storage = new Storage();

async function extractTableToGCS() {
  // Exports my_dataset:my_table to gcs://my-bucket/my-file as raw CSV.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table";
  // const bucketName = "my-bucket";
  // const filename = "file.csv";

  // Location must match that of the source table.
  const options = {
    location: 'US',
  };

  // Export data from the table into a Google Cloud Storage file
  const [job] = await bigquery
    .dataset(datasetId)
    .table(tableId)
    .extract(storage.bucket(bucketName).file(filename), options);

  console.log(`Job ${job.id} created.`);

  // 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;

/**
 * Extracts the given table as json to given GCS bucket.
 *
 * @param string $projectId The project Id of your Google Cloud Project.
 * @param string $datasetId The BigQuery dataset ID.
 * @param string $tableId The BigQuery table ID.
 * @param string $bucketName Bucket name in Google Cloud Storage
 */
function extract_table(
    string $projectId,
    string $datasetId,
    string $tableId,
    string $bucketName
): void {
    $bigQuery = new BigQueryClient([
      'projectId' => $projectId,
    ]);
    $dataset = $bigQuery->dataset($datasetId);
    $table = $dataset->table($tableId);
    $destinationUri = "gs://{$bucketName}/{$tableId}.json";
    // Define the format to use. If the format is not specified, 'CSV' will be used.
    $format = 'NEWLINE_DELIMITED_JSON';
    // Create the extract job
    $extractConfig = $table->extract($destinationUri)->destinationFormat($format);
    // Run the job
    $job = $table->runJob($extractConfig);  // Waits for the job to complete
    printf('Exported %s to %s' . PHP_EOL, $table->id(), $destinationUri);
}

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. 

# from google.cloud import bigquery
# client = bigquery.Client()
# bucket_name = 'my-bucket'
project = "bigquery-public-data"
dataset_id = "samples"
table_id = "shakespeare"

destination_uri = "gs://{}/{}".format(bucket_name, "shakespeare.csv")
dataset_ref = bigquery.DatasetReference(project, dataset_id)
table_ref = dataset_ref.table(table_id)

extract_job = client.extract_table(
    table_ref,
    destination_uri,
    # Location must match that of the source table.
    location="US",
)  # API request
extract_job.result()  # Waits for job to complete.

print(
    "Exported {}:{}.{} to {}".format(project, dataset_id, table_id, destination_uri)
)

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. 

require "google/cloud/bigquery"

def extract_table bucket_name = "my-bucket",
                  dataset_id  = "my_dataset_id",
                  table_id    = "my_table_id"

  bigquery = Google::Cloud::Bigquery.new
  dataset  = bigquery.dataset dataset_id
  table    = dataset.table    table_id

  # Define a destination URI. Use a single wildcard URI if you think
  # your exported data will be larger than the 1 GB maximum value.
  destination_uri = "gs://#{bucket_name}/output-*.csv"

  extract_job = table.extract_job destination_uri do |config|
    # Location must match that of the source table.
    config.location = "US"
  end
  extract_job.wait_until_done! # Waits for the job to complete

  puts "Exported #{table.id} to #{destination_uri}"
end

Détails sur les exportations au format Avro

BigQuery exprime les données au format Avro comme suit :

  • Les fichiers d'exportation obtenus sont des fichiers de conteneur Avro.
  • Chaque ligne BigQuery est représentée par un enregistrement Avro. Les données imbriquées sont représentées par des objets Enregistrement imbriqués.
  • Les champs REQUIRED sont représentés par les types Avro correspondants. Par exemple, un type INTEGER BigQuery est mappé sur un type LONG Avro.
  • Les champs NULLABLE sont représentés par une union Avro du type correspondant et la valeur "null".
  • Les champs REPEATED sont représentés par des tableaux Avro.
  • Les types de données TIMESTAMP sont représentés par le type logique timestamp-micros (il annote un type LONG Avro) par défaut dans les tâches d'extraction et d'exportation des données SQL. (Attention : vous pouvez ajouter use_avro_logical_types=False à Export Data Options pour désactiver le type logique afin d'utiliser le type string de la colonne d'horodatage. Toutefois, il utilise toujours le type logique Avro dans les tâches d'extraction.)
  • Les types de données DATE sont représentés par le type logique date (il annote un type INT Avro) par défaut dans les tâches d'exportation des données SQL, mais ils sont représentés par le type string par défaut dans les tâches d'extraction. (Remarque : vous pouvez ajouter use_avro_logical_types=False à Export Data Options pour désactiver le type logique ou utiliser l'option --use_avro_logical_types=True pour activer le type logique dans les tâches d'extraction.)
  • Les types de données TIME sont représentés par le type logique timestamp-micro (il annote un type LONG Avro) par défaut dans les tâches d'exportation des données SQL, mais ils sont représentés par le type string par défaut dans les tâches d'extraction. (Remarque : vous pouvez ajouter use_avro_logical_types=False à Export Data Options pour désactiver le type logique ou utiliser l'option --use_avro_logical_types=True pour activer le type logique dans les tâches d'extraction.)
  • Les types de données DATETIME sont représentés par des types STRING Avro (un type de chaîne avec le type logique personnalisé datetime) par défaut dans les tâches d'exportation des données SQL, mais sont représentés par le type string par défaut dans les tâches d'extraction. (Remarque : vous pouvez ajouter use_avro_logical_types=False à Export Data Options pour désactiver le type logique ou utiliser l'option --use_avro_logical_types=True pour activer le type logique dans les tâches d'extraction.)

Les types de données paramétrés NUMERIC(P[, S]) et BIGNUMERIC(P[, S]) transfèrent leurs paramètres de type précision et échelle vers le type logique décimal Avro.

Le format Avro ne peut pas être utilisé en association avec la compression GZIP. Pour compresser des données Avro, utilisez l'outil de ligne de commande bq ou l'API, et spécifiez l'un des types de compression acceptés pour ces données : DEFLATE ou SNAPPY.

Détails sur les exportations au format Parquet

BigQuery convertit les types de données GoogleSQL en types de données au format Parquet suivants :

Type de données BigQuery Type primitif Parquet Type logique Parquet
Entier INT64 NONE
Numérique FIXED_LEN_BYTE_ARRAY DECIMAL (precision = 38, scale = 9)
Numerique(P[, S]) FIXED_LEN_BYTE_ARRAY DECIMAL (precision = P, scale = S)
BigNumeric FIXED_LEN_BYTE_ARRAY DECIMAL (precision = 76, scale = 38)
BigNumeric(P[, S]) FIXED_LEN_BYTE_ARRAY DECIMAL (precision = P, scale = S)
Virgule flottante FLOAT NONE
Booléen BOOLEAN NONE
Chaîne BYTE_ARRAY STRING (UTF8)
Bytes BYTE_ARRAY NONE
Date INT32 DATE
Date/Heure INT64 TIMESTAMP (isAdjustedToUTC = false, unit = MICROS)
Durée INT64 TIME (isAdjustedToUTC = true, unit = MICROS)
Horodatage INT64 TIMESTAMP (isAdjustedToUTC = false, unit = MICROS)

Le schéma Parquet représente les données imbriquées sous forme de groupe et les enregistrements répétés sous forme de groupes répétés. Pour plus d'informations sur l'utilisation de données imbriquées et répétées dans BigQuery, consultez la section Spécifier des colonnes imbriquées et répétées.

Vous pouvez utiliser les solutions suivantes pour les types DATETIME :

  • Chargez le fichier dans une table de transfert. Ensuite, utilisez une requête SQL pour convertir le champ en DATETIME et enregistrez le résultat dans une nouvelle table. Pour en savoir plus, consultez la section Modifier le type de données d'une colonne.
  • Fournissez un schéma pour la table à l'aide de l'option --schema de la tâche de chargement. Définissez la colonne date/heure en tant que col:DATETIME.

Exporter des données dans un ou plusieurs fichiers

La propriété destinationUris indique le ou les emplacements et noms de fichiers dans lesquels BigQuery doit exporter vos fichiers.

BigQuery accepte un seul opérateur générique (*) dans chaque URI. Le caractère générique peut s'afficher n'importe où dans l'URI, sauf dans le nom du bucket. L'opérateur générique permet d'indiquer à BigQuery qu'il doit créer plusieurs fichiers segmentés en fonction du modèle fourni. Cet opérateur est remplacé par un numéro (en commençant à 0) et il est complété à gauche de façon à obtenir 12 chiffres. Par exemple, un URI avec un caractère générique à la fin du nom du fichier crée des fichiers où 000000000000 est ajouté au premier fichier, 000000000001 est ajouté au deuxième fichier, etc.

Le tableau suivant décrit plusieurs options possibles pour la propriété destinationUris :

Options destinationUris
Un seul URI

Utilisez un seul URI si vous exportez des données de table d'une taille inférieure ou égale à 1 Go. Cette option est le cas d'utilisation le plus courant, car les données exportées sont généralement inférieures à la valeur maximale de 1 Go. Cette option n'est pas compatible avec l'instruction EXPORT DATA. Vous devez utiliser un seul URI générique.

Définition de la propriété :

['gs://my-bucket/file-name.json']

Crée :


gs://my-bucket/file-name.json
Un seul URI générique

Utilisez un seul URI générique si vous pensez que les données exportées dépasseront la valeur maximale de 1 Go. BigQuery segmente les données en plusieurs fichiers en fonction du modèle fourni. La taille des fichiers exportés varie.

Si vous utilisez un caractère générique dans un composant d'URI autre que le nom du fichier, assurez-vous que le composant de chemin d'accès n'existe pas avant d'exporter les données.

Définition de la propriété :

['gs://my-bucket/file-name-*.json']

Crée :


gs://my-bucket/file-name-000000000000.json
gs://my-bucket/file-name-000000000001.json
gs://my-bucket/file-name-000000000002.json
...

Limiter la taille du fichier exporté

Lorsque vous exportez plus de 1 Go de données en une seule exportation, vous devez utiliser un caractère générique pour exporter les données dans plusieurs fichiers et la taille des fichiers varie. Si vous devez limiter la taille maximale de chaque fichier exporté, vous pouvez partitionner vos données de manière aléatoire, puis exporter chaque partition vers un fichier :

  1. Déterminez le nombre de partitions dont vous avez besoin, qui est égal à la taille totale de vos données divisée par la taille de fichier exportée souhaitée. Par exemple, si vous disposez de 8 000 Mo de données et que vous souhaitez que chaque fichier exporté fasse environ 20 Mo, vous avez besoin de 400 partitions.

  2. Créez une table partitionnée et mise en cluster par une nouvelle colonne générée de manière aléatoire appelée export_id. L'exemple suivant montre comment créer une table processed_table à partir d'une table existante appelée source_table qui nécessite des partitions n pour atteindre la taille de fichier souhaitée :

    CREATE TABLE my_dataset.processed_table
PARTITION BY RANGE_BUCKET(export_id, GENERATE_ARRAY(0, n, 1))
CLUSTER BY export_id
AS (
  SELECT *, CAST(FLOOR(n*RAND()) AS INT64) AS export_id
  FROM my_dataset.source_table
);

  3. Pour chaque entier i compris entre 0 et n-1, exécutez une instruction EXPORT DATA sur la requête suivante :

    SELECT * EXCEPT(export_id)
FROM my_dataset.processed_table
WHERE export_id = i;

Extraire une table compressée

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

// exportTableAsCompressedCSV demonstrates using an export job to
// write the contents of a table into Cloud Storage as compressed CSV.
func exportTableAsCompressedCSV(projectID, gcsURI string) error {
	// projectID := "my-project-id"
	// gcsURI := "gs://mybucket/shakespeare.csv"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %w", err)
	}
	defer client.Close()

	srcProject := "bigquery-public-data"
	srcDataset := "samples"
	srcTable := "shakespeare"

	gcsRef := bigquery.NewGCSReference(gcsURI)
	gcsRef.Compression = bigquery.Gzip

	extractor := client.DatasetInProject(srcProject, srcDataset).Table(srcTable).ExtractorTo(gcsRef)
	extractor.DisableHeader = true
	// You can choose to run the job in a specific location for more complex data locality scenarios.
	// Ex: In this example, source dataset and GCS bucket are in the US.
	extractor.Location = "US"

	job, err := extractor.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
	}
	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. 

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.ExtractJobConfiguration;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.TableId;

// Sample to extract a compressed table
public class ExtractTableCompressed {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String projectName = "MY_PROJECT_NAME";
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String bucketName = "MY-BUCKET-NAME";
    String destinationUri = "gs://" + bucketName + "/path/to/file";
    // For more information on export formats available see:
    // https://cloud.google.com/bigquery/docs/exporting-data#export_formats_and_compression_types
    String compressed = "gzip";
    // For more information on Job see:
    // https://googleapis.dev/java/google-cloud-clients/latest/index.html?com/google/cloud/bigquery/package-summary.html
    String dataFormat = "CSV";

    extractTableCompressed(
        projectName, datasetName, tableName, destinationUri, dataFormat, compressed);
  }

  public static void extractTableCompressed(
      String projectName,
      String datasetName,
      String tableName,
      String destinationUri,
      String dataFormat,
      String compressed) {
    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(projectName, datasetName, tableName);

      ExtractJobConfiguration extractConfig =
          ExtractJobConfiguration.newBuilder(tableId, destinationUri)
              .setCompression(compressed)
              .setFormat(dataFormat)
              .build();

      Job job = bigquery.create(JobInfo.of(extractConfig));

      // Blocks until this job completes its execution, either failing or succeeding.
      Job completedJob = job.waitFor();
      if (completedJob == null) {
        System.out.println("Job not executed since it no longer exists.");
        return;
      } else if (completedJob.getStatus().getError() != null) {
        System.out.println(
            "BigQuery was unable to extract due to an error: \n" + job.getStatus().getError());
        return;
      }
      System.out.println("Table extract compressed successful");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Table extraction job was interrupted. \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');

const bigquery = new BigQuery();
const storage = new Storage();

async function extractTableCompressed() {
  // Exports my_dataset:my_table to gcs://my-bucket/my-file as a compressed file.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table";
  // const bucketName = "my-bucket";
  // const filename = "file.csv";

  // Location must match that of the source table.
  const options = {
    location: 'US',
    gzip: true,
  };

  // Export data from the table into a Google Cloud Storage file
  const [job] = await bigquery
    .dataset(datasetId)
    .table(tableId)
    .extract(storage.bucket(bucketName).file(filename), options);

  console.log(`Job ${job.id} created.`);

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

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. 

# from google.cloud import bigquery
# client = bigquery.Client()
# bucket_name = 'my-bucket'

destination_uri = "gs://{}/{}".format(bucket_name, "shakespeare.csv.gz")
dataset_ref = bigquery.DatasetReference(project, dataset_id)
table_ref = dataset_ref.table("shakespeare")
job_config = bigquery.job.ExtractJobConfig()
job_config.compression = bigquery.Compression.GZIP

extract_job = client.extract_table(
    table_ref,
    destination_uri,
    # Location must match that of the source table.
    location="US",
    job_config=job_config,
)  # API request
extract_job.result()  # Waits for job to complete.

Exemple d'utilisation

Cet exemple montre comment exporter des données vers Cloud Storage.

Supposons que vous insérez des données en flux continu dans Cloud Storage à partir de journaux de points de terminaison de manière continue. Un instantané quotidien doit être exporté vers Cloud Storage à des fins de sauvegarde et d'archivage. Le meilleur choix est une tâche d'extraction soumise à certains quotas et limites.

Envoyez une tâche d'extraction avec l'API ou les bibliothèques clientes, en transmettant un ID unique en tant que jobReference.jobId. Les tâches d'extraction sont asynchrones. Vérifiez l'état de la tâche à l'aide de l'ID de tâche unique utilisé pour la créer. La tâche a bien été exécutée si status.status est défini sur DONE. Si status.errorResult est présent, la tâche a échoué et doit être relancée.

Traitement de données par lot

Supposons qu'une tâche par lot quotidienne soit utilisée pour charger des données dans un délai fixe. Une fois cette tâche de chargement terminée, une table avec des statistiques est matérialisée à partir d'une requête, comme décrit dans la section précédente. Les données de cette table sont extraites et compilées dans un rapport PDF et envoyées à un régulateur.

La quantité de données à lire étant petite, utilisez l'API tabledata.list pour récupérer toutes les lignes de la table au format de dictionnaire JSON. S'il existe plusieurs pages de données, la propriété pageToken sera définie dans les résultats. Pour extraire la page de résultats suivante, effectuez un autre appel tabledata.list et incluez la valeur du jeton en tant que paramètre pageToken. Si l'appel d'API échoue avec une erreur 5xx, réessayez avec un intervalle exponentiel entre les tentatives. La plupart des erreurs 4xx ne peuvent pas faire l'objet d'une nouvelle tentative. Pour un meilleur découplage de l'exportation BigQuery et de la génération de rapports, les résultats doivent être conservés sur le disque.

Règles de quotas

Pour en savoir plus sur les quotas appliqués aux tâches d'exportation, consultez la section Tâches d'exportation de la page "Quotas et limites".

L'utilisation pour les tâches d'exportation est disponible dans le champ INFORMATION_SCHEMA. L'entrée de la tâche dans les tables système JOBS_BY_* pour la tâche d'exportation contiendra une valeur total_processed_bytes qui peut être utilisée pour surveiller l'utilisation globale afin de s'assurer qu'elle reste inférieure à 50 To par jour. Pour savoir comment interroger la vue INFORMATION_SCHEMA.JOBS pour obtenir la valeur total_processed_bytes, consultez la section Obtenir le nombre d'octets traités par les tâches d'exportation.

Afficher l'utilisation actuelle des quotas

Vous pouvez afficher l'utilisation actuelle des jobs de requête, de chargement, d'extraction ou de copie en exécutant une requête INFORMATION_SCHEMA pour afficher les métadonnées des tâches exécutées sur une période spécifiée. Vous pouvez comparer votre utilisation actuelle à la limite de quota pour déterminer votre utilisation de quotas pour un type de job particulier. L'exemple de requête suivant utilise la vue INFORMATION_SCHEMA.JOBS pour répertorier le nombre de jobs de requête, de chargement, d'extraction et de copie par projet :

SELECT
  sum(case  when job_type="QUERY" then 1 else 0 end) as QRY_CNT,
  sum(case  when job_type="LOAD" then 1 else 0 end) as LOAD_CNT,
  sum(case  when job_type="EXTRACT" then 1 else 0 end) as EXT_CNT,
  sum(case  when job_type="COPY" then 1 else 0 end) as CPY_CNT
FROM `region-eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE date(creation_time)= CURRENT_DATE()

Vous pouvez configurer une règle d'alerte Cloud Monitoring qui fournit une notification sur le nombre d'octets exportés.

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

    Accéder à Monitoring

  2. Dans le volet de navigation, sélectionnez  Explorateur de métriques.

  3. Dans l'éditeur de requête MQL, configurez une alerte pour surveiller vos octets exportés par jour, comme illustré dans l'exemple suivant :

    fetch consumer_quota
  | filter resource.service == 'bigquery.googleapis.com'
  | { metric serviceruntime.googleapis.com/quota/rate/net_usage
      | align delta_gauge(1m)
      | group_by [resource.project_id, metric.quota_metric, resource.location],
          sum(value.net_usage)
    ; metric serviceruntime.googleapis.com/quota/limit
      | filter metric.limit_name == 'ExtractBytesPerDay'
      | group_by [resource.project_id, metric.quota_metric, resource.location],
          sliding(1m), max(val()) }
  | ratio
  | every 1m
  | condition gt(val(), 0.01 '1')

  4. Pour configurer votre alerte, cliquez sur Exécuter la requête.

Pour en savoir plus, consultez la page Règles d'alerte avec MQL.

Tarification

Pour en savoir plus sur la tarification de l'exportation de données, consultez la page Tarifs de BigQuery.

Une fois les données exportées, leur stockage dans Cloud Storage vous est facturé. Pour en savoir plus, consultez la page Tarifs de Cloud Storage.

Sécurité des tables

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

Étape suivante