GCP : Connecting Python to Google BigQuery

Google BigQuery is a web service from Google that is used for handling and analyzing big data. It’s part of the Google Cloud Platform.

To access BigQuery through Python, we’ll be using the google-cloud-bigquery package, which is a client library for Google BigQuery. This allows us to interact with BigQuery through Python programs, including creating, executing, and evaluating BigQuery queries.

Prerequisites

You need to have:

1. Python installed on your local system or server.
2. A Google Cloud Platform account.
3. BigQuery API enabled on your Google Cloud Platform project.
4. Appropriate permissions on the Google Cloud Platform for the operations you are going to perform.

Setting Up Google Cloud SDK

1. Create a Google Cloud Platform (GCP) project. You can follow this link to create a new project: https://console.cloud.google.com/projectcreate
2. Enable BigQuery API for your project. You can follow this link to enable it: https://console.cloud.google.com/flows/enableapi?apiid=bigquery
3. Install Google Cloud SDK on your local system. Follow the instructions here: https://cloud.google.com/sdk/docs/install

Setting Up Authentication

Google Cloud APIs use the OAuth 2.0 protocol for authentication and authorization. The simplest way for authentication is to set up service account keys, download the JSON file, and point to it with an environment variable.

1. In the Cloud Console, go to the Service Accounts page: https://console.cloud.google.com/iam-admin/serviceaccounts
2. Click on Create Service Account.
3. In the Service account name field, enter a name, and click on Create.
4. Grant the service account BigQuery roles that gives it required permissions like BigQuery Data Viewer, BigQuery Job User, etc. (Depending on the needs of your application, you might grant additional roles).
5. Click Continue, then click Done.
6. Click on the service account that you created.
7. Click Keys.
8. Click Add key, then click Create new key.
9. Click JSON. This will create a new JSON key, download it to your computer, and display a confirmation dialog.
10. Click Close.

Now, point the GOOGLE_APPLICATION_CREDENTIALS environment variable to the path of the JSON file.

export GOOGLE_APPLICATION_CREDENTIALS="[PATH]"

pip install google-cloud-bigquery

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

query = """
    SELECT name, SUM(number) as people_country
    FROM `freshers-in.login-data.usa`
    GROUP BY name
    ORDER BY people_country DESC
    LIMIT 10
"""
query_job = client.query(query)  # Make an API request.

print("The query data:")
for row in query_job:
    # Row values can be accessed by field name or index.
    print("name={}, count={}".format(row[0], row["people_country"]))