CODEWITHSUNDEEP
google-app-engine
Justin
Justin

Reputation: 4843

Invalid Credentials accessing Big Query tables from App Engine application

Could someone help me access Big Query from an App Engine application ?

I have completed the following steps -

Created an App Engine project.

Installed google-api-client, oauth2client dependencies (etc) into /lib.

Enabled the Big Query API for the App Engine project via the cloud console.

Created some 'Application Default Credentials' (a 'Service Account Key') [JSON] and saved it/them to the root of the App Engine application.

Created a 'Big Query Service Resource' as per the following -

def get_bigquery_service():
    from googleapiclient.discovery import build
    from oauth2client.client import GoogleCredentials
    credentials=GoogleCredentials.get_application_default()
    bigquery_service=build('bigquery', 'v2', credentials=credentials)
    return bigquery_service

Verified that the resource exists - 

<googleapiclient.discovery.Resource object at 0x7fe758496090>

Tried to query the resource with the following (ProjectId is the short name of the App Engine application) - 

bigquery=get_bigquery_service()
bigquery.tables().list(projectId=#{ProjectId},
                   datasetId=#{DatasetId}).execute()

Returns the following -

<HttpError 401 when requesting https://www.googleapis.com/bigquery/v2/projects/#{ProjectId}/datasets/#{DatasetId}/tables?alt=json returned "Invalid Credentials">

Any ideas as to steps I might have wrong or be missing here ? The whole auth process seems a nightmare, quite at odds with the App Engine/PaaS ease-of-use ethos :-(

Thank you.

Upvotes: 1

Views: 3188

Answers (1)

Justin
Justin

Reputation: 4843

OK so despite being a Google Cloud fan in general, this is definitely the worst thing I have been unfortunate enough to have to work on in a while. Poor/inconsistent/nonexistent documentation, complexity, bugs etc. Avoid if you can!

1) Ensure your App Engine 'Default Service Account' exists

https://console.cloud.google.com/apis/dashboard?project=XXX&duration=PTH1

You get the option to create the Default Service Account only if it doesn't already exist. If you've deleted it by accident you will need a new project; you can't recreate it.

How to recover Google App Engine's "default service account"

You should probably create the default set of JSON credentials, but you won't need to include them as part of your project.

You shouldn't need to create any other Service Accounts, for Big Query or otherwise.

2) Install google-api-python-client and apply fix

pip install -t lib google-api-python-client

Assuming this installs oath2client 3.0.x, then on testing you'll get the following complaint:

File "~/oauth2client/client.py", line 1392, in _get_well_known_file
default_config_dir = os.path.join(os.path.expanduser('~'),
File "/usr/lib/python2.7/posixpath.py", line 268, in expanduser
import pwd
File "~/google_appengine-1.9.40/google/appengine/tools/devappserver2/python/sandbox.py", line 963, in load_module
raise ImportError('No module named %s' % fullname)
ImportError: No module named pwd

which you can fix by changing ~/oauth2client/client.py [line 1392] from: 

os.path.expanduser('~')

to:

os.env("HOME")

and adding the following to app.yaml:

env_variables:
  HOME: '/tmp'

Ugly but works.

3) Download GCloud SDK and login from console

https://cloud.google.com/sdk/

gcloud auth login

The issue here is that App Engine's dev_appserver.py doesn't include any Big Query replication (natch); so when you're interacting with Big Query tables it's the production data you're playing with; you need to login to get access.

Obvious in retrospect, but poorly documented.

4) Enable Big Query API in App Engine console; create a Big Query ProjectID

https://console.cloud.google.com/apis/dashboard?project=XXX&duration=PTH1

https://bigquery.cloud.google.com/welcome/XXX

5) Test

from oauth2client.client import GoogleCredentials
credentials=GoogleCredentials.get_application_default()
from googleapiclient.discovery import build
bigquery=build('bigquery', 'v2', credentials=credentials)
print bigquery.datasets().list(projectId=#{ProjectId}).execute()

[or similar]

Good luck!

Upvotes: 1

Related Questions