How to run a BigQuery SQL using Python?

BigQuery API Client libraries

BigQuery API client libraries are used to run a BigQuery job programmatically. It calls the BigQuery api’s internally. Google Cloud offering BigQuery client libraries for different programming languages. In this tutorial, we are going to use the BigQuery Python client library.

Prerequisite to access BigQuery using Python

  • Service account – In order to make a request to BigQuery API, we need to use a Service account. It belongs to our GCP project and it is used by the BigQuery Python client library to make a BigQuery API request.
  • Service account key – To authenticate the service account , we need a service account private key JSON file. It can be used to obtain credentials for a service account. BigQuery verify the client identity using this key.
  • roles – BigQuery has a number of predefined roles (user, dataOwner, dataViewer etc.) that we can assign to our Service account. Using that, we can control the access of our BigQuery resources(dataset, tables, etc)
  • BigQuery Python client library – Install the BigQuery client library using pip command as below

Steps to run a BigQuery SQL using Python

Once we set up all the requirements, we can start writing the Python program to run BigQuery SQL. Let’s write the program step by step

Step 1: Import BigQuery and service account library

First we need to import bigquery and service_account libraries in the program

Step 2: Get credentials for service account

Let’s use service_account.Credentials.from_service_account_file and obtain credentials for service account. Here we are passing the path of service account key file.

Step 3: Construct bigquery client

Using project id and credentials, we need to create a bigquery client as below. Here we set our project id as my-rcs-project.

Step 4: Run the query

Now we can run the BigQuery using authenticated BigQuery client

Step 5: Display the query result

Example

Consider that we have a table users_sleep_tracker in BigQuery dataset rc_fin_test_tables. It contains the records as below.

Access BigQuery table using Python
Access BigQuery table using Python

Read from BigQuery table

The below code fetch the records from BigQuery table users_sleep_tracker using Python.

Output

BigQuery Delete rows using Python

The below code deletes a row from BigQuery table users_sleep_tracker using Python. Similarly we can run the Insert or Update query in BigQuery using Python.

Please note that the service account should have IAM role “BigQuery Data Editor” to make change in the table.

Output

python bigquery delete rows
python bigquery delete rows

Recommended Articles

References from GCP official documentation