Exporting data from BigQuery

Extract jobs copy a table to Cloud Storage for free; EXPORT DATA writes a query's result and is billed as a query. Anything over 1 GB needs a wildcard URI, CSV can't hold nested data, JSON exports INT64 as strings, and only EXPORT DATA with ORDER BY keeps row order.

There are two ways to get data out of BigQuery in bulk. An extract job copies a whole table to files in Cloud Storage and costs nothing on the shared slot pool. The EXPORT DATA statement runs a query and writes its result to Cloud Storage, Amazon S3, Azure Blob Storage, Bigtable, Spanner or Pub/Sub; it is billed like the query. Both write CSV, newline-delimited JSON, Avro or Parquet.

Extract job: a whole table, free

bq extract \
  --destination_format=PARQUET \
  --compression=SNAPPY \
  mydataset.orders \
  'gs://my-bucket/exports/orders/part-*.parquet'

An extract job reads table storage directly, so there is no query and no bytes billed. It always exports the whole table (or one partition, with a decorator like orders$20260302); there is no WHERE. Up to 50 TiB a day per project are free this way.

EXPORT DATA: a query result

EXPORT DATA OPTIONS (
  uri = 'gs://my-bucket/exports/big_orders/part-*.csv',
  format = 'CSV',
  header = TRUE,
  overwrite = TRUE
) AS
SELECT order_id, customer_id, amount
FROM mydataset.orders
WHERE ordered_at >= '2026-03-01'
ORDER BY ordered_at;

Use it when you need a subset, a join, a transformation, or a particular row order. It can’t read INFORMATION_SCHEMA views or wildcard tables. Exporting to Amazon S3 or Azure needs a connection (WITH CONNECTION); Google Cloud destinations don’t.

The query is billed like any query: How a query is billed. To export a whole table for free, use an extract job instead of EXPORT DATA AS SELECT * FROM t.

Things that catch people out

• 1 GB per file. A single output file can hold at most 1 GB of table data. For more, the URI must contain one * wildcard, which BigQuery replaces with a sequence number. File sizes and the number of files vary between runs; don’t depend on either. EXPORT DATA always requires a wildcard URI.
• No nested data in CSV. A table with STRUCT or ARRAY columns can’t be exported as CSV. Use JSON, Avro or Parquet, or flatten the data in an EXPORT DATA query.
• INT64 becomes a JSON string. In JSON exports, integers are written as strings ("id": "12345") so that 64-bit values survive readers that use floating point. Parquet and Avro keep them as integers.
• <, > and & are escaped in JSON exports as \u003c, \u003e and \u0026, so profit&loss becomes profit\u0026loss.
• Row order isn’t kept, except with EXPORT DATA and an ORDER BY. Then the order holds across all files in sequence.
• overwrite = TRUE overwrites files with the same names but never deletes others. If yesterday’s export produced 12 files and today’s produces 8, files 9 to 12 are still there. Export to a fresh prefix each time, such as one per date.
• Extract jobs write only to Cloud Storage. You can’t extract to a local file, Google Drive or Sheets. To get a query result onto your machine, download it from the results, or use the Storage Read API. Querylab.io’s Export dialog on the results footer downloads CSV, Excel, JSON, Markdown or DuckDB, sends rows to Google Sheets, and for Cloud Storage opens a tab with an EXPORT DATA statement for you to run.
• Rows written during the export aren’t included. An extract job exports the table as it was when the job started.

Formats

Parquet is the usual choice for data another engine will read. CSV only for spreadsheets and systems that need it.

Other destinations

EXPORT DATA can also write rows into Bigtable, Spanner or Pub/Sub, for serving query results to applications. These run as query jobs too. For continuous export of new rows as they arrive, see Continuous queries.

Limits