GcpHow-ToBeginner · 4 min read

How to Export Data from BigQuery: Simple Steps and Examples

To export data from BigQuery, you can use the EXPORT DATA SQL statement, the Google Cloud Console export feature, or the bq extract command-line tool. These methods allow you to save query results or table data to Google Cloud Storage in formats like CSV, JSON, or Avro.

📐

Syntax

The EXPORT DATA statement exports table data to Google Cloud Storage. You specify the destination URI, file format, and optionally compression and partition filters.

uri: The Cloud Storage path where files will be saved (e.g., gs://bucket_name/file_name.csv).
format: The export file format, such as CSV, JSON, or AVRO.
compression: Optional compression like GZIP.
query: The SQL query or table to export.

sql

EXPORT DATA OPTIONS(
  uri='gs://your-bucket/filename-*.csv',
  format='CSV',
  compression='GZIP',
  overwrite=true
) AS
SELECT * FROM `project.dataset.table`;

💻

Example

This example exports all rows from a BigQuery table to CSV files in a Cloud Storage bucket with gzip compression.

sql

EXPORT DATA OPTIONS(
  uri='gs://my-bucket/exports/data-*.csv',
  format='CSV',
  compression='GZIP',
  overwrite=true
) AS
SELECT * FROM `my-project.my_dataset.my_table`;

Output

Export job started. Files will be saved as gs://my-bucket/exports/data-000000000000.csv.gz, data-000000000001.csv.gz, ...

⚠️

Common Pitfalls

Not having write permission on the Cloud Storage bucket causes export failure.
Using an incorrect URI format or missing wildcards (*) can cause errors or overwrite files.
Exporting very large tables without partitioning can create many files or timeout.
For CSV exports, forgetting to specify overwrite=true may cause the job to fail if files exist.

sql

/* Wrong: Missing wildcard causes overwrite error */
EXPORT DATA OPTIONS(
  uri='gs://my-bucket/exports/data.csv',
  format='CSV'
) AS
SELECT * FROM `my-project.my_dataset.my_table`;

/* Right: Use wildcard to create multiple files */
EXPORT DATA OPTIONS(
  uri='gs://my-bucket/exports/data-*.csv',
  format='CSV',
  overwrite=true
) AS
SELECT * FROM `my-project.my_dataset.my_table`;

📊

Quick Reference

Here is a quick summary of export options and methods:

Method	Description	Key Points
EXPORT DATA SQL	Export table/query results to Cloud Storage	Specify URI with wildcard, format, compression, and overwrite
Google Cloud Console	Use UI to export table data	Select table, click Export, choose format and destination
bq extract command	Command-line export tool	Use 'bq extract --destination_format=CSV' with Cloud Storage URI

✅

Key Takeaways

Use the EXPORT DATA SQL statement to export BigQuery data directly to Cloud Storage.

Always include a wildcard (*) in the URI to avoid overwriting files unless exporting a single file.

Ensure you have write permissions on the Cloud Storage bucket before exporting.

You can export data in CSV, JSON, or Avro formats with optional compression.

The bq command-line tool and Cloud Console UI offer easy alternatives for exporting data.