# 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.

Source: https://docs.querylab.io/bigquery/exporting-data/

Updated: 2026-10-14

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

```sh
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

```sql
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](https://docs.querylab.io/bigquery/how-queries-are-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](https://docs.querylab.io/results/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

| Format                   | Nested data | Types kept       | Compression        |
| ------------------------ | ----------- | ---------------- | ------------------ |
| Parquet                  | yes         | yes              | SNAPPY, GZIP, ZSTD |
| Avro                     | yes         | yes              | DEFLATE, SNAPPY    |
| JSON (newline-delimited) | yes         | INT64 as strings | GZIP               |
| CSV                      | no          | no, all text     | GZIP               |

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](https://docs.querylab.io/bigquery/continuous-queries/).

## Limits

| Limit                                       | Value                       |
| ------------------------------------------- | --------------------------- |
| Free extract volume on the shared slot pool | 50 TiB per project per day  |
| Extract jobs                                | 100,000 per project per day |
| Table data per single output file           | 1 GB                        |
| Wildcard URIs per extract job               | 500                         |

Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Export table data to Cloud Storage](https://cloud.google.com/bigquery/docs/exporting-data)

- [EXPORT DATA statement](https://cloud.google.com/bigquery/docs/reference/standard-sql/export-statements)

- [BigQuery pricing, data extraction](https://cloud.google.com/bigquery/pricing#data_extraction_pricing)

- [Quotas and limits, export jobs](https://cloud.google.com/bigquery/quotas#export_jobs)