How to load Big query table data into GCP Bucket as single file

Google Cloud Data Analytics
1

Hi Team,
Need you inputs to extracting Big query table data into GCP bucket as a single, currently we are using below syntax but we are getting multiple files into GCS Bucket.

EXPORT DATA
OPTIONS (
uri = ‘gs://staging-datalake/daily/analytics_405454767/test_events/test_*.csv’,
format = ‘CSV’,
overwrite = true,
header = true,
field_delimiter = ‘,’)
AS (
SELECT * FROM public.table_name_20250128
WHERE ID = ‘6071032112_863507621.1738113799_player_load_1738113829435473’

);

But I’m getting multiple files in GCS bucket. could you please share the correct code to use for extracting into single file and please let us know how to recover deleted big query table.

2

Hi @ravimahasiva ,

Welcome to Google Cloud Community!

The reason you’re getting a multiple files when exporting BigQuery table data to a GCS bucket is that EXPORT DATA statement uses a single-wildcard URI(uri=‘gs://staging-datalake/daily/analytics_405454767/test_events/test_*.csv’), which instructs BigQuery to create multiple sharded files, and typically are used for exported data larger than 1GB, in contrast, Single URI is used when exporting data that is 1GB or less. Single URI is not supported on EXPORT DATA Statement.

You can also try a different approach of extracting the BigQuery table to a GCS Bucket like console method or bq extract command, though please note that BigQuery allows you to export up to 1 GB of table data to a single file. If you’re exceeding the 1GB, you can partition your data and export each partition separately to control the size of the exported file. You can also check this documentation if you’re already hitting the limitation on exporting BigQuery table data to a GCS bucket.

To recover a deleted BigQuery table, if applicable, you can try time travel and fail safe. Data loss beyond the Time Travel window (2-7 days, user configured) and the fail-safe window (+7 days) is not recoverable.

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.