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