How to Export Table Data to a File in Google BigQuery

You can download small datasets into your laptop directly from the WebUI for small tables (usually < 10k rows). However, for large tables, Google requires that you export the table data into a GCS (Google cloud storage) Bucket, and then download the files from there to your laptop

There are multiple ways to export a table to file. Here are two methods, you can use whichever is convenient:

Exporting Bigquery table via the WebUI

To export a BigQuery table to a file via the WebUI, the process couldn’t be simpler.

• Go to the BigQuery WebUI.
• Select the table you wish to export.
• Click on Export Table in the top-right.
• Select the Export format and Compression, if necessary.
• Alter the Google Cloud Storage URI as necessary to match the bucket, optional directories, and file-name you wish to export to. eg: gs://<bucket-name>/<file-name>
• Google exports the table in chunks of 1GB, so, if your table size is greater than 1 GB, include a * in the URL, so google knows to chunk it. eg: gs://<bucket-name>/<file-name-*.csv)
• Click OK and wait for the job to complete

Exporting Bigquery table via the BQ Command line:

If you have the Google Cloud SDK installed on your laptop, you can use this method to export a table to file and then download the files into your laptop. 

The below command will extract the table and compress and export the files into a GCS bucket:

bq --location=US extract --compression GZIP datasetname.tablename gs://<bucket-name>/<file-name-*.csv

You can then download the files from the GCS bucket into your laptop using the CP command:

gsutil cp gs://<bucket-name>/<file-name-*.gz .

victor

posted on 15 May 20