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 = './google-creds.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

Here is a c# solution

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

Answer 6

Apart from using GOOGLE_APPLICATION_CREDENTIALS (which is already described in a bunch of answers) there is one more way to set generated json credentials as a default service account:

gcloud auth activate-service-account --key-file=<path to your generated json file>

That will activate a default account (and set credentials according to the provided json file) without explicitly setting GOOGLE_APPLICATION_CREDENTIALS, and it will be still activated after re-login or reboot without modifying .bashrc.

Answer 7

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 8

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 9

There is another workaround that I don't think was mentioned here yet. The google.oauth2.service_account.Credentials object offers the from_service_account_info method (see here: https://github.com/googleapis/google-auth-library-python/blob/main/google/oauth2/service_account.py).

So you can set any variable you want in your environment and read it in and pass it into the function something like this:

your_data = { 
"type": os.environ.get('YOUR_ENV_VAR'),
"project_id": os.environ.get('YOUR_ENV_VAR'),
"private_key_id": os.environ.get('YOUR_ENV_VAR'),
#... and so on with all the required Google variables....
}
your_credentials = service_account.Credentials.from_service_account_info(your_data, scopes=your_scopes)
service = discovery.build(api_name, api_version, credentials=your_credentials)

I basically took all the data from my google keyfile.json and stored them in the env and did the above. That way you never need to keep your keyfile.json anywhere near your code or worse, upload it somewhere public. And that's basically it. Good luck!

PS: I forgot to mention this also, which might help someone who is running into the same issues as I did. While the above should work fine in development, in some production environments the \n will not be interpreted as a new line. Instead it will remain inside the private key. Put all of the above into a try statement and if you have the error: 'no key could be detected' then this is most likely the problem. In that case you need to replace all \\n with \n similar as to to what has been suggested by Sumit Agrawal but kind of the other way round. That's because in some environments an automatic adding of \ will occur for a new line indication such as \n in order to keep them as they are if that makes any sense. So you have to basically undo this.

You can simply do the following for one of the lines above: "private_key": os.environ.get('YOUR_ENV_VAR').replace('\\n', '\n'),

But again try to print them to the log file / console to see how they actually look like. If you have any \n in the string you know you need to clean or convert them as explained. Good luck!

Answer 10

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 11

• Export the Google credential JSON from command line:
  export GOOGLE_APPLICATION_CREDENTIALS='\path\key.json'
  I hope it will work fine.

Answer 12

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 13

If there is case where you can not provide credential in a file set GOOGLE_APPLICATION_CREDENTIALS='\path\key.json'

1. As the service account is JSON and it contains double quote character, replace every double quote with \"
2. Wrap the complete JSON in double quote
3. Replace every \n with \\n ( on linux ) \\\n (on mac)

with above changes in service account if you export it as variable, then it should be recorded correctly.

try echo %variable_name to confirm if it looks good.

Answer 14

Please use gcloud auth application-default login to generate the authentication token that will be generated under .config/gcloud/application_default_credentials.json

Other way is to explicitly provide the service account file location in client() function or set the variable GOOGLE_APPLICATION_CREDENTIALS.

DOC: https://cloud.google.com/sdk/gcloud/reference/auth/application-default/login

https://cloud.google.com/bigquery/docs/authentication/service-account-file#python

Answer 15

In your project folder just type:

set GOOGLE_APPLICATION_CREDENTIALS='\path\key.json'

Answer 16

On Windows, update the 'environmental variables for your account'. You will most likely already have a variable called: GOOGLE_APPLICATION_CREDENTIALS Simply update the path to /path/to/liquid-optics-xxxxxxxx.json (you'll most likely have that file somewhere on your machine). Then refresh your environment (cmd or whatever) to pick up the change.

Answer 17

I see you're having a challenge with the Application Default Credentials. Let's tackle this step by step:

1. Setting the Environment: While you've assigned the GOOGLE_APPLICATION_CREDENTIALS in your script, this doesn't necessarily make it an environment variable for your current session. Try integrating this:

os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = './Peepl-cb1dac99bdc0.json'

Slot this in right before invoking GoogleCredentials.get_application_default().

2. Path Confirmation: Double-check the pathway to your .json credentials. If there's a directory mismatch between the script and the .json file, you'd run into such an issue. It's best to ensure you're providing a pathway that's either absolute or relative to your script's execution point.

3. Integrity of Credentials File: Double-check the content of Peepl-cb1dac99bdc0.json. It should be intact and consist of keys like type, project_id, private_key_id, among others.

4. Initiating from the Terminal: As an alternative, you might want to set the environment variable directly from your terminal, then execute your Python script:

export GOOGLE_APPLICATION_CREDENTIALS="./Peepl-cb1dac99bdc0.json"
python name_of_your_script.py

5. File Access: Make sure your .json file isn't restricted and can be accessed by your script. If you're on a Unix-like system and you need to adjust permissions, the chmod command might come in handy.

Once you've gone through these steps, give your script another shot. Should you face any other issues, drop a comment with any new error details.

Wishing you smooth coding!