11 May 20 · npack · #Bigquery ·   Bookmark   ×

How to download a table data from Google BigQuery ?

Bigquery WebUI allows you to export results to csv files. However if your table has millions of rows, this method may not be efficient. Alternatively, you can install the google  Cloud SDK (Software development kit) that helps you to extract the table data into csv files and then ftp the files back to your laptop

  1. Install the Google Cloud SDK
    Download and install the SDK version for you operating system
  2. Authenticate and Initialize your account
    From the command prompt, run 
    gcloud init
    and 
    gcloud auth login
    to initialize your account. This is a one time activity
  3. Create a GCP Storage bucket to store the exported data
  4. Run the following command to export your table to the storage bucket that we just created

    bq extract my_dataset.my_table gs://mybucket/myfilename.csv

    Replace the table name and bucket names with yours

  5. Run the below command to download the files from the cloud storage buckets to your laptop

    gsutil -m cp -r 'gs://<bucket>/prefix_*' .

  6. once the files are downloaded. Remember to cleanup your cloud storage
    gsutil rm gs://<bucket>/prefix_*

You are all set !

npack

posted on 11 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