Setting GOOGLE_APPLICATION_CREDENTIALS for BigQuery Python CLI

Question

I'm trying to connect to Google BigQuery through the BigQuery API, using Python.

I'm following this page here: https://cloud.google.com/bigquery/bigquery-api-quickstart

My code is as follows:

import os
import argparse

from apiclient.discovery import build
from apiclient.errors import HttpError
from oauth2client.client import GoogleCredentials

GOOGLE_APPLICATION_CREDENTIALS = './Peepl-cb1dac99bdc0.json'

def main(project_id):
    # Grab the application's default credentials from the environment.
    credentials = GoogleCredentials.get_application_default()
    print(credentials)
    # Construct the service object for interacting with the BigQuery API.
    bigquery_service = build('bigquery', 'v2', credentials=credentials)

    try:
        query_request = bigquery_service.jobs()
        query_data = {
            'query': (
                'SELECT TOP(corpus, 10) as title, '
                'COUNT(*) as unique_words '
                'FROM [publicdata:samples.shakespeare];')
        }

        query_response = query_request.query(
            projectId=project_id,
            body=query_data).execute()

        print('Query Results:')
        for row in query_response['rows']:
            print('\t'.join(field['v'] for field in row['f']))

    except HttpError as err:
        print('Error: {}'.format(err.content))
        raise err


if __name__ == '__main__':
    parser = argparse.ArgumentParser(
        description=__doc__,
        formatter_class=argparse.RawDescriptionHelpFormatter)
    parser.add_argument('project_id', help='Your Google Cloud Project ID.')

    args = parser.parse_args()

    main(args.project_id)

However, when I run this code through the terminal, I get the following error:

oauth2client.client.ApplicationDefaultCredentialsError: The Application Default Credentials are not available. They are available if running in Google Compute Engine. Otherwise, the environment variable GOOGLE_APPLICATION_CREDENTIALS must be defined pointing to a file defining the credentials. See https://developers.google.com/accounts/docs/application-default-credentials for more information.

As you can see in the code, I've tried to set the GOOGLE_APPLICATION_CREDENTIALS as per the link in the error. However, the error persists. Does anyone know what the issue is?

Thank you in advance.

Answer 1

First - Thanks for the code - this provided to be very useful. I would also suggest adding setting the environmental variable directly in your code - as not to set it for every environment you work on. you can use the following code:

import os
os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = "path_to_your_.json_credential_file"

I found this useful when switching between different projects that require different credentials.

Answer 2

I'm not sure about BigQuery, but i'm using Google Data Store for saving. If you've installed gcloud sdk in your mac, you can try running this command

gcloud auth application-default login

Answer 3

It's looking for the environment variable in your local UNIX (or other) environment, not a variable in your python script.

You'd set that by opening up your terminal or cygwin and doing one of the following:

export GOOGLE_APPLICATION_CREDENTIALS='/path/to/your/client_secret.json'

Type that into your terminal to set the variable for just this session

Open up your .bashrc file, in UNIX by typing in nano ~/.bashrc and add this line to it underneath user specific aliases if you see that header:

GOOGLE_APPLICATION_CREDENTIALS="/full/path/to/your/client_secret.json"

Then reload it by typing source ~/.bashrc and confirm that it's set by trying echo $GOOGLE_APPLICATION_CREDENTIALS. If it returns the path, you're good.

Answer 4

Note: oauth2client is deprecated, instead of GoogleCredentials.get_application_default() you can use google.auth.default(). Install the package first with:

pip install google-auth

In your specific example, I see you know where the JSON file is located from your code. Instead of default credentials (from environment variables), you can use a service account directly with the google.oauth2.service_account module.

credentials = google.oauth2.service_account.Credentials.from_service_account_file(
    './Peepl-cb1dac99bdc0.json',
    scopes=['https://www.googleapis.com/auth/cloud-platform'])

You can use this credentials file just as you are currently doing so by passing them to googleapiclient.discovery.build or if you are using the google-cloud-bigquery library, pass the credentials to the google.cloud.bigquery.Client constructor.

Answer 5

The link provided in the error message, https://developers.google.com/identity/protocols/application-default-credentials, says to set the environment variable to point at the fail that contains the JSON service credentials. It looks like you set a Python variable. Try setting your terminal's environment variable to point at the correct file.

An alternate would be to explicitly use some other credentials when you aren't running in a GCE container, like oauth2client.client.SignedJwtAssertionCredentials and point it directly at your client secret so you don't have to indirect through an environment variable.

Answer 6

It's looking for the environment variable. But I was able to solve this problem on Windows platform by using Application Default Credentials.

Steps that I followed:

• Installed Google SDK
• Then execute gcloud init steps to specify my defaults credentials and default project which you can change as and when needed. gcloud executable can be loacted in the bin directory where you chose to install Google SDK.
• After successfully providing the credentials, you can check in at the location C:\Users\"yourusername"\AppData\Roaming\gcloud\legacy_credentials\"youremail" . You can find the credentials stored in the JSON format there.

It helped me to resolve the error.

Answer 7

GOOGLE_APPLICATION_CREDENTIALS NOT FOUND ERROR SOLUTION FOR C#

System.Environment.SetEnvironmentVariable("GOOGLE_APPLICATION_CREDENTIALS",@"C:\apikey.json");
string Pathsave = System.Environment.GetEnvironmentVariable("GOOGLE_APPLICATION_CREDENTIALS");

Answer 8

If you would like to use different credential files without setting the environmental variable, you can use the following code:

from oauth2client import service_account
from apiclient.discovery import build
import json

client_credentials = json.load(open("<path to .json credentials>"))

credentials_token = service_account._JWTAccessCredentials.from_json_keyfile_dict(client_credentials)

bigquery_service = build('bigquery', 'v2', credentials=credentials_token)
query_request = bigquery_service.jobs()
query_data = {
    'query': (
            'SELECT TOP(corpus, 10) as title, '
            'COUNT(*) as unique_words '
            'FROM [publicdata:samples.shakespeare];')
    }

query_response = query_request.query(
           projectId=project_id,
           body=query_data).execute()

print('Query Results:')
for row in query_response['rows']:
    print('\t'.join(field['v'] for field in row['f']))

Answer 9

You can create a client with service account credentials using from_service_account_json():

from google.cloud import bigquery
bigqueryClient = bigquery.Client.from_service_account_json('/path/to/keyfile.json')

Answer 10

In your project folder just type:

set GOOGLE_APPLICATION_CREDENTIALS='\path\key.json'