15 May 20 · victor · #Bigquery ·   Bookmark   ×

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

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds