[FIXED] How to set Google bigquery environment variable on colab

Issue

I plan to create a script to extract data from Bigquery, but I don't know how to set the environment variable.

Here is an instance from the official doc:

from google.cloud import bigquery

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

query = """
    SELECT name, SUM(number) as total_people
    FROM `bigquery-public-data.usa_names.usa_1910_2013`
    WHERE state = 'TX'
    GROUP BY name, state
    ORDER BY total_people DESC
    LIMIT 20
"""
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["total_people"]))

I run this but return an error:

DefaultCredentialsError: Could not automatically determine credentials. Please set GOOGLE_APPLICATION_CREDENTIALS or explicitly create credentials and re-run the application. For more information, please see https://cloud.google.com/docs/authentication/getting-started

I follow the official doc, but I meet an issue: the second step is setting the environment variable but it just provides instances on Windows and Linux/macOS. So, how do I set the environment variable on Colab?

Also, I notice the instances ask me to provide the key path. It is OK on the local machine, but I don't think it is an idea to upload my key file and past its link in my code online.

Solution

Instead of setting an environment variable or uploading directly to Colab, you can upload your key on your Google Drive and apply necessary restrictions there. In your code, you can then mount Google Drive to Colab, use the Drive location as key file path for authentication.

from google.cloud import bigquery
from google.oauth2 import service_account
from google.colab import drive
import json
# Construct a BigQuery client object.

drive.mount('/content/drive/') # Mount to google drive

# Define full path from Google Drive.
# This example, key is in /MyDrive/Auth/
key_path = '/content/drive/MyDrive/Auth/your_key.json' 

credentials = service_account.Credentials.from_service_account_file(
    filename=key_path, scopes=["https://www.googleapis.com/auth/cloud-platform"],
)

client = bigquery.Client(credentials=credentials, project=credentials.project_id,)

query = """
    SELECT name, SUM(number) as total_people
    FROM `bigquery-public-data.usa_names.usa_1910_2013`
    WHERE state = 'TX'
    GROUP BY name, state
    ORDER BY total_people DESC
    LIMIT 20
"""
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["total_people"]))

Output:

Answered By - Ricco D

Answer Checked By - Terry (PHPFixing Volunteer)