How to authenticate BigQuery account in Python ?

When trying to execute your python program that connects to Bigquery, you might stumble on the below error:

OSError: Project was not passed and could not be determined from the environment

To solve this error, follow the below method:

Authenticate your Google service account in python

To authenticate your google service account, first export the json credentials of the service account (your admin might be able to help you). Store the JSON credentials file in the same server / system from where you are running the python program

Here's how a sample JSON credentials file looks like:

{
 "type": "service_account",
 "project_id": "your project",
 "private_key_id": "your private key id",
 "private_key": "private key",
 "client_email": "email",
 "client_id": "client id",
 "auth_uri": "https://accounts.google.com/o/oauth2/auth",
 "token_uri": "https://accounts.google.com/o/oauth2/token",
 "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
 "client_x509_cert_url":  "https://www.googleapis.com/robot/v1/metadata/x509/email_id"
}

Now, you can either export the file path to environment variable in the OS

export GOOGLE_APPLICATION_CREDENTIALS=/path/to/key.json

Or you can in your own script build the Client using the JSON file directly:

import google.cloud.bigquery as bq
client = bq.Client.from_service_account_json("path/to/key.json")
query_job = client.query("""
    SELECT date, totals.visits AS visits
    FROM `myproject.mydataset.ga_sessions_20180111`
    GROUP BY date
    """)

results = query_job.result()  # Waits for job to complete.

for row in results:
    print("{}: {}".format(row.title, row.unique_words))

DataFreak

posted on 08 Mar 19