Exporting table data

This page describes how to export or extract data from BigQuery tables.

After you've loaded your data into BigQuery, you can export the data in several formats. BigQuery can export up to 1 GB of data to a single file. If you are exporting more than 1 GB of data, you must export your data to multiple files. When you export your data to multiple files, the size of the files will vary.

You can use a service such as Dataflow to read data from BigQuery instead of manually exporting it. For more information about using Dataflow to read from and write to BigQuery, see BigQuery I/O in the Apache Beam documentation.

Required permissions

To export data to Cloud Storage, you need permissions to access the BigQuery table that contains the data, permissions to run an export job, and permissions to write the data to the Cloud Storage bucket.

BigQuery permissions

• At a minimum, to export data, you must be granted bigquery.tables.export permissions. The following predefined Cloud IAM roles are granted bigquery.tables.export permissions:

  • bigquery.dataViewer
  • bigquery.dataOwner
  • bigquery.dataEditor
  • bigquery.admin

• At a minimum, to run an export job, you must be granted bigquery.jobs.create permissions. The following predefined Cloud IAM roles are granted bigquery.jobs.create permissions:

  • bigquery.user
  • bigquery.jobUser
  • bigquery.admin

Cloud Storage permissions

• To write the data to an existing Cloud Storage bucket, you must be granted storage.objects.create permissions. The following predefined Cloud IAM roles are granted storage.objects.create permissions:

  • storage.objectCreator
  • storage.objectAdmin
  • storage.admin

Export limitations

When you export data from BigQuery, note the following:

• You cannot export table data to a local file, to Google Sheets, or to Google Drive. The only supported export location is Cloud Storage. For information on saving query results, see Downloading and saving query results.
• You can export up to 1 GB of table data to a single file. If you are exporting more than 1 GB of data, use a wildcard to export the data into multiple files. When you export data to multiple files, the size of the files will vary.
• You cannot export nested and repeated data in CSV format. Nested and repeated data is supported for Avro and JSON exports.
• When you export data in JSON format, INT64 (integer) data types are encoded as JSON strings to preserve 64-bit precision when the data is read by other systems.
• You cannot export data from multiple tables in a single export job.
• You cannot choose a compression type other than GZIP when you export data using the Cloud Console or the classic BigQuery web UI.

Location considerations

When you choose a location for your data, consider the following:

• Colocate your Cloud Storage buckets for exporting data.
• When you export data, the regional or multi-regional Cloud Storage bucket must be in the same location as the BigQuery dataset. For example, if your BigQuery dataset is in the EU multi-regional location, the Cloud Storage bucket containing the data you're exporting must be in a regional or multi-regional location in the EU.
• If your dataset is in a regional location, your Cloud Storage bucket must be a regional bucket in the same location. For example, if your dataset is in the Tokyo region, your Cloud Storage bucket must be a regional bucket in Tokyo.
• Exception: If your dataset is in the US multi-regional location, you can export data into a Cloud Storage bucket in any regional or multi-regional location.
• Develop a data management plan.
• If you choose a regional storage resource such as a BigQuery dataset or a Cloud Storage bucket, develop a plan for geographically managing your data.

For more information on Cloud Storage locations, see Bucket Locations in the Cloud Storage documentation.

Moving BigQuery data between locations

You cannot change the location of a dataset after it is created, but you can make a copy of the dataset. You cannot move a dataset from one location to another, but you can manually move (recreate) a dataset.

Copying datasets

To see steps for copying a dataset, including across regions, see Copying datasets.

Copying datasets is currently in Beta

Moving a dataset

To manually move a dataset from one location to another, follow this process:

1. Export the data from your BigQuery tables to a regional or multi-region Cloud Storage bucket in the same location as your dataset. For example, if your dataset is in the EU multi-region location, export your data into a regional or multi-region bucket in the EU.

   There are no charges for exporting data from BigQuery, but you do incur charges for storing the exported data in Cloud Storage. BigQuery exports are subject to the limits on export jobs.

2. Copy or move the data from your Cloud Storage bucket to a regional or multi-region bucket in the new location. For example, if you are moving your data from the US multi-region location to the Tokyo regional location, you would transfer the data to a regional bucket in Tokyo. For information on transferring Cloud Storage objects, see Renaming, copying, and moving objects in the Cloud Storage documentation.

   Note that transferring data between regions incurs network egress charges in Cloud Storage.

3. After you transfer the data to a Cloud Storage bucket in the new location, create a new BigQuery dataset (in the new location). Then, load your data from the Cloud Storage bucket into BigQuery.

   You are not charged for loading the data into BigQuery, but you will incur charges for storing the data in Cloud Storage until you delete the data or the bucket. You are also charged for storing the data in BigQuery after it is loaded. Loading data into BigQuery is subject to the limits on load jobs.

For more information on using Cloud Storage to store and move large datasets, see Using Cloud Storage with big data.

Export formats and compression types

BigQuery supports the following data formats and compression types for exported data.

Data format	Supported compression types	Details
CSV	GZIP	You can control the CSV delimiter in your exported data by using the --field_delimiter CLI flag or the configuration.extract.fieldDelimiter. extract job property. Nested and repeated data is not supported.
JSON	GZIP	Nested and repeated data is supported.
Avro	DEFLATE, SNAPPY	GZIP is not supported for Avro exports. Nested and repeated data is supported.

Exporting data stored in BigQuery

You can export table data by:

• Using the Cloud Console or the classic BigQuery web UI
• Using the bq extract CLI command
• Submitting an extract job via the API or client libraries

Exporting table data

To export data from a BigQuery table:

Console

1. Open the BigQuery web UI in the Cloud Console.
   Go to the Cloud Console

2. In the navigation panel, in the Resources section, expand your project and click your dataset to expand it. Find and click the table that contains the data you're exporting.

3. On the right side of the window, click Export then select Export to Google Cloud Storage

   

4. In the Export table to Google Cloud Storage dialog:

   • For Select Google Cloud Storage location, browse for the bucket, folder, or file where you want to export the data.
   • For Export format, choose the format for your exported data: CSV, JSON (Newline Delimited), or Avro.
   • For Compression, accept the default value, None, or choose GZIP. The Avro format can't be used in combination with GZIP compression. To compress Avro data, use the bq command-line tool or the API and specify one of the supported compression types for Avro data: DEFLATE or SNAPPY.
   • Click Export to export the table.

To check on the progress of the job, look near the top of the navigation for Job history for an Export job.

Classic UI

1. Go to the BigQuery web UI.
   Go to the BigQuery web UI

2. In the navigation, click your dataset to expand it.

3. Find and click the down arrow icon next to the table that contains the data you're exporting.

4. Select Export table to display the Export to Google Cloud Storage dialog.

5. In the Export to Google Cloud Storage dialog:

   • For Export format, choose the format for your exported data: CSV, JSON (Newline Delimited), or Avro.
   • For Compression, accept the default value, None, or choose GZIP. The Avro format can't be used in combination with GZIP compression. To compress Avro data, use the bq command-line tool or the API and specify one of the supported compression types for Avro data: DEFLATE or SNAPPY.
   • In the Google Cloud Storage URI textbox, enter a valid URI in the format gs://bucket_name/filename.ext, where bucket_name is your Cloud Storage bucket name, and filename.ext is the name and extension of your destination file. The BigQuery dataset and the Cloud Storage bucket must be in the same location.
   • Click OK to export the table.

While the job is running, (extracting) appears next to the name of the table in the navigation. To check on the progress of the job, look near the top of the navigation for Job History for an Extract job.

CLI

Use the bq extract command with the --destination_format flag.

(Optional) Supply the --location flag and set the value to your location.

Other optional flags include:

• --compression: The compression type to use for exported files.
• --field_delimiter: The character that indicates the boundary between columns in the output file for CSV exports. Both \t and tab are allowed for tab delimiters.
• --print_header: When specified, print header rows for formats that have headers such as CSV.

bq --location=location extract \
--destination_format format \
--compression compression_type \
--field_delimiter delimiter \
--print_header=boolean \
project_id:dataset.table \
gs://bucket/filename.ext

Where:

• location is the name of your location. The --location flag is optional. For example, if you are using BigQuery in the Tokyo region, you can set the flag's value to asia-northeast1. You can set a default value for the location using the .bigqueryrc file.
• format is the format for the exported data: CSV, NEWLINE_DELIMITED_JSON, or AVRO.
• compression_type is a supported compression type for your data format. CSV and NEWLINE_DELIMITED_JSON support GZIP. AVRO supports DEFLATE and SNAPPY.
• delimiter is the character that indicates the boundary between columns in CSV exports. \t and tab are accepted names for tab.
• boolean is true or false. When set to true, header rows are printed to the exported data if the data format supports headers. The default value is true.
• project_id is your project ID.
• dataset is the name of the source dataset.
• table is the table you're exporting.
• bucket is the name of the Cloud Storage bucket to which you're exporting the data. The BigQuery dataset and the Cloud Storage bucket must be in the same location.
• filename.ext is the name and extension of the exported data file. You can export to multiple files using a wildcard.

Examples:

For example, the following command exports mydataset.mytable into a gzip compressed file named myfile.csv. myfile.csv is stored in a Cloud Storage bucket named example-bucket.

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

The default destination format is CSV. To export into JSON or Avro, use the destination_format flag and set it to either NEWLINE_DELIMITED_JSON or AVRO. For example:

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

The following command exports mydataset.mytable into an Avro file that is compressed using Snappy. The file is named myfile.avro. myfile.avro is exported to a Cloud Storage bucket named example-bucket.

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

API

To export data, create an extract job and populate the job configuration.

(Optional) Specify your location in the location property in the jobReference section of the job resource.

1. Create an extract job that points to the BigQuery source data and the Cloud Storage destination.

2. Specify the source table by using the sourceTable configuration object that contains the project ID, dataset ID, and table ID.

3. The destination URI(s) property must be fully-qualified, in the format gs://bucket/filename.ext. Each URI can contain one '*' wildcard character and it must come after the bucket name.

4. Specify the data format by setting the configuration.extract.destinationFormat property. For example, to export a JSON file, set this property to the value NEWLINE_DELIMITED_JSON.

5. To check the job status, call jobs.get(job_id) with the ID of the job returned by the initial request.

   • If status.state = DONE, the job completed successfully.
   • If the status.errorResult property is present, the request failed, and that object will include information describing what went wrong.
   • If status.errorResult is absent, the job finished successfully, although there might have been some non-fatal errors. Non-fatal errors are listed in the returned job object's status.errors property.

API notes:

• As a best practice, generate a unique ID and pass it as jobReference.jobId when calling jobs.insert to create a job. This approach is more robust to network failure because the client can poll or retry on the known job ID.

• Calling jobs.insert on a given job ID is idempotent; in other words, you can retry as many times as you like on the same job ID, and at most one of those operations will succeed.

C#

Before trying this sample, follow the C# setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery C# API reference documentation.

View on GitHub Feedback

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

Before trying this sample, follow the Go setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Go API reference documentation.

View on GitHub Feedback

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

Before trying this sample, follow the Java setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Java API reference documentation.

View on GitHub Feedback

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 ExtractTableToJson {

  public static void runExtractTableToJson() {
    // 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";
    extractTableToJson(projectId, datasetName, tableName, destinationUri);
  }

  // Exports datasetName:tableName to destinationUri as raw CSV
  public static void extractTableToJson(
      String projectId, String datasetName, String tableName, String destinationUri) {
    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);

      // 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
      Job job = table.extract("CSV", 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 CSV file.");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Table extraction job was interrupted. \n" + e.toString());
    }
  }
}

Node.js

Before trying this sample, follow the Node.js setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Node.js API reference documentation.

View on GitHub Feedback

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

Before trying this sample, follow the PHP setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery PHP API reference documentation.

View on GitHub Feedback

use Google\Cloud\BigQuery\BigQueryClient;

/** Uncomment and populate these variables in your code */
// $projectId  = 'The Google project ID';
// $datasetId  = 'The BigQuery dataset ID';
// $tableId    = 'The BigQuery table ID';
// $bucketName = 'The Cloud Storage bucket Name';

$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

Before trying this sample, follow the Python setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Python API reference documentation.

View on GitHub Feedback

# 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 = client.dataset(dataset_id, project=project)
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

Before trying this sample, follow the Ruby setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Ruby API reference documentation.

View on GitHub Feedback

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

Avro export details

BigQuery expresses Avro formatted data in the following ways:

• The resulting export files are Avro container files.
• Each BigQuery row is represented as an Avro record. Nested data is represented by nested record objects.
• REQUIRED fields are represented as the corresponding Avro types. For example, a BigQuery INTEGER type maps to an Avro LONG type.
• NULLABLE fields are represented as an Avro union of the corresponding type and "null".
• REPEATED fields are represented as Avro arrays.
• TIMESTAMP data types are represented as Avro timestamp-micros logical types.
• DATE data types are represented as Avro INT types by default, or Avro date logical types if the flag --use_avro_logical_types is specified.
• TIME data types are represented as Avro LONG types by default, or Avro time-micros logical types if the flag --use_avro_logical_types is specified.
• DATETIME data types are represented as Avro STRING types. The encoding follows the Internet Engineering Task Force RFC 3339 spec.

The Avro format can't be used in combination with GZIP compression. To compress Avro data, use the bq command-line tool or the API and specify one of the supported compression types for Avro data: DEFLATE or SNAPPY.

Exporting data into one or more files

The destinationUris property indicates the location(s) and file name(s) where BigQuery should export your files.

BigQuery supports a single wildcard operator (*) in each URI. The wildcard can appear anywhere in the URI except as part of the bucket name. Using the wildcard operator instructs BigQuery to create multiple sharded files based on the supplied pattern. The wildcard operator is replaced with a number (starting at 0), left-padded to 12 digits. For example, a URI with a wildcard at the end of the file name would create files with000000000000 appended to the first file, 000000000001 appended to the second file, and so on.

The following table describes several possible options for the destinationUris property:

`destinationUris` options
Single URI	Use a single URI if you are exporting table data that is 1 GB or less. This option is the most common use case, as exported data is generally less than the 1 GB maximum value. Property definition: ['gs://my-bucket/file-name.json'] Creates: gs://my-bucket/file-name.json
Single wildcard URI	Use a single wildcard URI if you think your exported data will be larger than the 1 GB maximum value. BigQuery shards your data into multiple files based on the provided pattern. The size of the exported files will vary. If you use a wildcard in a URI component other than the file name, be sure the path component does not exist before exporting your data. Property definition: ['gs://my-bucket/file-name-*.json'] Creates: gs://my-bucket/file-name-000000000000.json gs://my-bucket/file-name-000000000001.json gs://my-bucket/file-name-000000000002.json ...
Multiple wildcard URIs	Use multiple wildcard URIs if you want to partition the export output. You would use this option if you're running a parallel processing job with a service like Dataproc. Determine how many workers are available to process the job, and create one URI per worker. BigQuery treats each URI location as a partition, and uses parallel processing to shard your data into multiple files in each location. You can use whatever pattern you'd like in your file name, assuming there is a single wildcard operator in each URI, each URI is unique, and the number of URIs does not exceed the quota policy. When you pass more than one wildcard URI, BigQuery creates a special file at the end of each partition that indicates the final file in the set. This file name indicates how many shards BigQuery created. For example, if your wildcard URI is gs://my-bucket/file- name-<worker number>-*.json, and BigQuery creates 80 sharded files, the zero record file name is gs://my-bucket/file-name-<worker number>-000000000080.json. You can use this file name to determine that BigQuery created 80 sharded files (named 000000000000-000000000079). Note that a zero record file might contain more than 0 bytes depending on the data format, such as when exporting data in CSV format with a column header. String pattern: gs://my-bucket/file-name-<worker number>-*.json Property definition: ['gs://my-bucket/file-name-1-*.json', 'gs://my-bucket/file-name-2-*.json', 'gs://my-bucket/file-name-3-*.json'] Creates: This example assumes that BigQuery creates 80 sharded files in each partition. gs://my-bucket/file-name-1-000000000000.json gs://my-bucket/file-name-1-000000000001.json ... gs://my-bucket/file-name-1-000000000080.json gs://my-bucket/file-name-2-000000000000.json gs://my-bucket/file-name-2-000000000001.json ... gs://my-bucket/file-name-2-000000000080.json gs://my-bucket/file-name-3-000000000000.json gs://my-bucket/file-name-3-000000000001.json ... gs://my-bucket/file-name-3-000000000080.json

Quota policy

For information on export job quotas, see Export jobs on the Quotas and limits page.

Pricing

There is currently no charge for exporting data from BigQuery, but exports are subject to BigQuery's Quotas and limits. For more information on BigQuery pricing, see the Pricing page.

Once the data is exported, you are charged for storing the data in Cloud Storage. For more information on Cloud Storage pricing, see the Cloud Storage Pricing page.

What's next

• To learn more about the BigQuery web UI, see BigQuery web UI.
• To learn more about the bq command-line tool, see bq command-line tool.
• To learn how to create an application using the BigQuery API client libraries, see Client library quickstart.