Export table data to Cloud Storage
This page describes how to export or extract data from BigQuery tables to Cloud Storage.
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.
You can also export the results of a query by using the
EXPORT DATA
statement. You can use EXPORT DATA OPTIONS
to export views to Cloud Storage.
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. To limit the exported file size, you can partition your data and export each partition.
- The generated file size when using the
EXPORT DATA
statement is not guaranteed. - The number of files generated by an export job can vary.
- You cannot export nested and repeated data in CSV format. Nested and repeated data are supported for Avro, JSON, and Parquet 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 Google Cloud console. - When you export data to a Cloud Storage bucket configured with a retention policy, BigQuery might fail to write the files to the bucket. Configure the retention period to be longer than the duration of the export jobs.
- When you export a table in JSON format, the symbols
<
,>
, and&
are converted by using the unicode notation\uNNNN
, whereN
is a hexadecimal digit. For example,profit&loss
becomesprofit\u0026loss
. This unicode conversion is done to avoid security vulnerabilities. - The order of exported table data is not guaranteed unless you use the
EXPORT DATA
statement and specify anORDER BY
clause in thequery_statement
. - BigQuery does not support Cloud Storage resource paths
that include multiple consecutive slashes after the initial double slash.
Cloud Storage object names can contain multiple consecutive slash
("/") characters. However, BigQuery converts multiple
consecutive slashes into a single slash. For example, the following resource
path, though valid in Cloud Storage, does not work in
BigQuery:
gs://bucket/my//object//name
. - Any new data loaded into BigQuery while an export job is running won't be included in that export job. You must create a new export job to export the new data.
Before you begin
Grant Identity and Access Management (IAM) roles that give users the necessary permissions to perform each task in this document.
Required permissions
To perform the tasks in this document, you need the following permissions.
Permissions to export data from a BigQuery table
To export data from a BigQuery table, you need the
bigquery.tables.export
IAM permission.
Each of the following predefined IAM roles includes the
bigquery.tables.export
permission:
roles/bigquery.dataViewer
roles/bigquery.dataOwner
roles/bigquery.dataEditor
roles/bigquery.admin
Permissions to run an export job
To run an export job, you need the bigquery.jobs.create
IAM permission.
Each of the following predefined IAM roles includes the permissions that you need in order to run an export job:
roles/bigquery.user
roles/bigquery.jobUser
roles/bigquery.admin
Permissions to write the data to the Cloud Storage bucket
To write the data to an existing Cloud Storage bucket, you need the following IAM permissions:
storage.objects.create
storage.objects.delete
Each of the following predefined IAM roles includes the permissions that you need in order to write the data to an existing Cloud Storage bucket:
roles/storage.objectAdmin
roles/storage.admin
For more information about IAM roles and permissions in BigQuery, see Predefined roles and permissions.
Location considerations
Colocate your Cloud Storage buckets for exporting data:- If your BigQuery dataset is in the
EU
multi-region, the Cloud Storage bucket containing the data that you export must be in the same multi-region or in a location that is contained within the multi-region. For example, if your BigQuery dataset is in theEU
multi-region, the Cloud Storage bucket can be located in theeurope-west1
Belgium region, which is within the EU.If your dataset is in the
US
multi-region, you can export data into a Cloud Storage bucket in any location. - If your dataset is in a region, your Cloud Storage bucket must be in the same region. For
example, if your dataset is in the
asia-northeast1
Tokyo region, your Cloud Storage bucket cannot be in theASIA
multi-region.
- 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 about Cloud Storage locations, see Bucket Locations in the Cloud Storage documentation.
Move 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.
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 Nested and repeated data is not supported. |
JSON | GZIP | Nested and repeated data are supported. |
Avro | DEFLATE, SNAPPY | GZIP is not supported for Avro exports. Nested and repeated data are supported. See Avro export details. |
Parquet | SNAPPY, GZIP, ZSTD | Nested and repeated data are supported. See Parquet export details. |
Export data
You can export table data by:
- Using the Google Cloud console
- Using the
bq extract
command in the bq command-line tool - Submitting an
extract
job via the API or client libraries
Export table data
To export data from a BigQuery table:
Console
Open the BigQuery page in the Google Cloud console.
In the Explorer panel, expand your project and dataset, then select the table.
In the details panel, click Export and select Export to Cloud Storage.
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), Avro, or Parquet.
- For Compression, select a compression format or select
None
for no compression. - Click Export to export the table. google3/googledata/devsite/site-cloud/en/bigquery/docs/introduction-sql.md To check on the progress of the job, look near the top of the navigation for Job history for an Export job.
To export views to Cloud Storage, use EXPORT DATA OPTIONS
statement.
SQL
Use the
EXPORT DATA
statement.
The following example exports
selected fields from a table named mydataset.table1
:
In the Google Cloud console, go to the BigQuery page.
In the query editor, enter the following statement:
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 );
Click
Run.
For more information about how to run queries, see Run an interactive query.
bq
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
andtab
are allowed for tab delimiters.--print_header
: When specified, print header rows for formats that have headers such as 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
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 toasia-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
,AVRO
, orPARQUET
. - compression_type is a supported compression type for your data format. See Export formats and compression types.
- delimiter is the character that indicates the boundary between
columns in CSV exports.
\t
andtab
are accepted names for tab. - boolean is
true
orfalse
. When set totrue
, header rows are printed to the exported data if the data format supports headers. The default value istrue
. - project_id is your project ID.
- dataset is the name of the source dataset.
- table is the table you're exporting. If you use a
partition decorator,
then you must surround the table path with single quotation marks or
escape the
$
character. - 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
The following command exports a single partition of
mydataset.my_partitioned_table
into a CSV file in Cloud Storage:
bq extract \ --destination_format CSV \ 'mydataset.my_partitioned_table$0' \ gs://example-bucket/single_partition.csv
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.
Create an extract job that points to the BigQuery source data and the Cloud Storage destination.
Specify the source table by using the
sourceTable
configuration object that contains the project ID, dataset ID, and table ID.The
destination URI(s)
property must be fully-qualified, in the formatgs://bucket/filename.ext
. Each URI can contain one '*' wildcard character and it must come after the bucket name.Specify the data format by setting the
configuration.extract.destinationFormat
property. For example, to export a JSON file, set this property to the valueNEWLINE_DELIMITED_JSON
.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'sstatus.errors
property.
- If
API notes:
As a best practice, generate a unique ID and pass it as
jobReference.jobId
when callingjobs.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.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
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.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
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.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
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.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
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.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
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.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
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.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
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 BigQueryINTEGER
type maps to an AvroLONG
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 astimestamp-micros
logical type (it annotates an AvroLONG
type) by default in both Extract jobs and Export Data SQL. (Caution: you can adduse_avro_logical_types=False
toExport Data Options
to disable the logical type so it usesstring
type instead on timestamp column, but in Extract Jobs, it always uses the Avro logical type.)DATE
data types are represented asdate
logical type (it annotates an AvroINT
types) by default in Export Data SQL, but are represented asstring
type by default in Extract jobs. (Note: you can adduse_avro_logical_types=False
toExport Data Options
to disable the logical type, or use the flag--use_avro_logical_types=True
to enable the logical type in Extract jobs.)TIME
data types are represented astimestamp-micro
logical type (it annotates an AvroLONG
types) by default in Export Data SQL, but are represented asstring
type by default in Extract jobs. (Note: you can adduse_avro_logical_types=False
toExport Data Options
to disable the logical type, or use the flag--use_avro_logical_types=True
to enable the logical type in Extract jobs.)DATETIME
data types are represented as AvroSTRING
types (a string type with custom named logical typedatetime
) by default in Export Data SQL, but are represented asstring
type by default in Extract jobs. (Note: you can adduse_avro_logical_types=False
toExport Data Options
to disable the logical type, or use the flag--use_avro_logical_types=True
to enable logical type in Extract jobs.)
Parameterized NUMERIC(P[, S])
and BIGNUMERIC(P[, S])
data types transfer
their precision and scale type parameters to the Avro decimal logical type.
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
.
Parquet export details
BigQuery converts GoogleSQL data types to the following Parquet data types:
BigQuery data type | Parquet primitive type | Parquet logical type |
---|---|---|
Integer | INT64 |
NONE |
Numeric | FIXED_LEN_BYTE_ARRAY |
DECIMAL (precision = 38, scale = 9) |
Numeric(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) |
Floating point | FLOAT |
NONE |
Boolean | BOOLEAN |
NONE |
String | BYTE_ARRAY |
STRING (UTF8) |
Bytes | BYTE_ARRAY |
NONE |
Date | INT32 |
DATE |
Datetime | INT64 |
TIMESTAMP (isAdjustedToUTC = false, unit = MICROS) |
Time | INT64 |
TIME (isAdjustedToUTC = true, unit = MICROS) |
Timestamp | INT64 |
TIMESTAMP (isAdjustedToUTC = false, unit = MICROS) |
The Parquet schema represents nested data as a group and repeated records as repeated groups. For more information about using nested and repeated data in BigQuery, see Specifying nested and repeated columns.
You can use the following workarounds for DATETIME
types:
- Load the file into a staging table. Then use a SQL query to cast the field to
a
DATETIME
and save the result to a new table. For more information, see Changing a column's data type. - Provide a schema for the table by using the
--schema
flag in the load job. Define the datetime column ascol:DATETIME
.
Exporting data into one or more files
The destinationUris
property indicates the one or more locations and filenames 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 filename 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. This option is not
supported for the
Property definition:
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 filename, be sure the path component does not exist before exporting your data. Property definition:
Creates: gs://my-bucket/file-name-000000000000.json gs://my-bucket/file-name-000000000001.json gs://my-bucket/file-name-000000000002.json ... |
Limit the exported file size
When you export more than 1 GB of data in a single export, you must use a wildcard to export the data into multiple files and the size of the files varies. If you need to limit the maximum size of each exported file, one option is to randomly partition your data and then export each partition to a file:
- Determine the number of partitions you need, which is equal to the total size of your data divided by the desired exported file size. For example, if you have 8,000 MB of data and you want each exported file to be approximately 20 MB, then you need 400 partitions.
Create a new table that is partitioned and clustered by a new randomly generated column called
export_id
. The following example shows how to create a newprocessed_table
from an existing table calledsource_table
which requiresn
partitions to achieve the desired file size: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 );
For each integer
i
between 0 andn-1
, run anEXPORT DATA
statement on the following query:SELECT * EXCEPT(export_id) FROM my_dataset.processed_table WHERE export_id = i;
Extract compressed table
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.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
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.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
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.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
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.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
Example use case
This example shows how you can export data to Cloud Storage.
Suppose you are streaming data to Cloud Storage from endpoint logs continuously. A daily snapshot is to be exported to Cloud Storage for backup and archival purposes. The best choice is an extract job subject to certain quotas and limitations.
Submit an extract job with the
API
or
client libraries,
passing in a unique ID as jobReference.jobId
. Extract Jobs are
asynchronous.
Check the job status
using the unique job ID used to create the job. The job completed successfully
if status.status
is DONE
. If status.errorResult
is present,
the job failed and needs to be retried.
Batch data processing
Suppose a nightly batch job is used to load data by a fixed deadline. After this load job completes, a table with statistics is materialized from a query as described in the preceding section. Data from this table is retrieved and compiled into a PDF report and sent to a regulator.
Since the amount of data that needs to be read is small, use the
tabledata.list
API to retrieve all rows of the table in JSON dictionary format. If there is
more than one page of data, the results have the pageToken
property
set. To retrieve the next page of results, make another tabledata.list
call and
include the token value as the pageToken
parameter. If the API call fails
with a 5xx
error,
retry with exponential backoff. Most 4xx errors are not retryable. For
better decoupling of BigQuery export and report generation,
results should be persisted to disk.
Quota policy
For information on export job quotas, see Export jobs on the Quotas and limits page.
Usage for export jobs are available in the INFORMATION_SCHEMA
.
The job entry in the JOBS_BY_*
system tables for the export job contains a
total_processed_bytes
value that can be used to monitor the aggregate usage to
ensure that it stays below 50 TB per-day. To learn how to query the
INFORMATION_SCHEMA.JOBS
view to get the total_processed_bytes
value, see
Get bytes processed by export jobs
View current quota usage
You can view your current usage of query, load, extract, or copy jobs by running
an INFORMATION_SCHEMA
query to view metadata about the jobs ran over a
specified time period. You can compare your current usage against the quota
limit to determine your quota usage for a particular type of job. The following
example query uses the INFORMATION_SCHEMA.JOBS
view to list the number of
query, load, extract, and copy jobs by project:
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()
You can set up a Cloud Monitoring alert policy that provides notification of the number of bytes exported.
In the Google Cloud console, go to the Monitoring page.
In the navigation pane, select
Metrics explorer.
In the MQL query editor, set up an alert to monitor your exported bytes per day, as seen in the following example:
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')
To set up your alert, click Run query.
For more information, see Alerting policies with MQL.
Troubleshooting
To diagnose issues with extract jobs, you can use the Logs Explorer to review the logs for a specific extract job and identify possible errors. The following Logs Explorer filter returns information about your extract jobs:
resource.type="bigquery_resource"
protoPayload.methodName="jobservice.insert"
(protoPayload.serviceData.jobInsertRequest.resource.jobConfiguration.query.query=~"EXPORT" OR
protoPayload.serviceData.jobCompletedEvent.eventName="extract_job_completed" OR
protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.query.query=~"EXPORT")
Pricing
For information on data export pricing, see the BigQuery pricing page.
Once the data is exported, you are charged for storing the data in Cloud Storage. For more information, see Cloud Storage pricing.
Table security
To control access to tables in BigQuery, see Introduction to table access controls.
What's next
- To learn more about the Google Cloud console, see Using the Google Cloud console.
- To learn more about the bq command-line tool, see Using the bq command-line tool.
- To learn how to create an application using the BigQuery API client libraries, see Client library quickstart.