11

This question seems like it should be so simple to answer, but after days of research and several dead ends, I can't seem to get query results out of BigQuery without it insisting on user-based OAuth. Has anyone had any luck with this? I am not using Google AppEngine for my app, it is hosted in EC2. Here is the exact situation:

User wants reporting data -->
Web server makes queries to BigQuery -->
Data is transformed for use in WebApp and returned to User.

Whenever I follow the Google examples, I end up getting a web browser popping up asking for me to select a Google account to use for authentication.

Kara
  • 5,650
  • 15
  • 48
  • 55
JawsTheGame
  • 223
  • 1
  • 3
  • 9

3 Answers3

16

Sorry this is being so challenging to find info on. You're looking for what's called Service Accounts which are documented in our Authorizing Access to the BigQuery API using OAuth 2.0 guide.

Here's an example, using the Python client library, though you'll want to look at the referenced documentation for info on acquiring the appropriate credentials:

import httplib2

from apiclient.discovery import build
from oauth2client.client import SignedJwtAssertionCredentials

# REPLACE WITH YOUR Project ID
PROJECT_NUMBER = 'XXXXXXXXXXX'
# REPLACE WITH THE SERVICE ACCOUNT EMAIL FROM GOOGLE DEV CONSOLE
SERVICE_ACCOUNT_EMAIL = 'XXXXX@developer.gserviceaccount.com'

# OBTAIN THE KEY FROM THE GOOGLE APIs CONSOLE
# More instructions here: http://goo.gl/w0YA0
f = file('key.p12', 'rb')
key = f.read()
f.close()

credentials = SignedJwtAssertionCredentials(
    SERVICE_ACCOUNT_EMAIL,
    key,
    scope='https://www.googleapis.com/auth/bigquery')

http = httplib2.Http()
http = credentials.authorize(http)

service = build('bigquery', 'v2')
datasets = service.datasets()
response = datasets.list(projectId=PROJECT_NUMBER).execute(http)

print 'Dataset list:'
for dataset in response['datasets']:
  print '%s' % dataset['datasetReference']['datasetId']
Michael Manoochehri
  • 7,333
  • 4
  • 31
  • 45
Ryan Boyd
  • 2,938
  • 1
  • 19
  • 18
  • 1
    Ryan, thanks for your response. This gets me further. I had run across the Service Accounts earlier and it was clear that this was what I was looking for, I just could not find out where to set this up. I have done so, and have run the code above with my correct ProjectId, Service Account Email, and key file, but when I grab the response for listing datasets, there is no 'datasets' key in the dict. Instead it is something like this: {u'kind': u'bigquery#datasetList', u'etag': u'"viowSXH0JIvMREGVicRUeTw4PZo/L-a0Zjajejhksaj6mKpTzCQGsr4"'} – JawsTheGame Nov 05 '12 at 17:03
  • 1
    So, to follow up, make sure you are calling the service.datasets().list() method. In the example above, the response['datasets'] object will contain a list of dataset description objects, in the form of {u'kind': u'bigquery#dataset', u'id': u'projectXXXXXXX:TestDataset', u'datasetReference': {u'projectId': u'projectXXXXXXX', u'datasetId': u'TestDataset'}} – Michael Manoochehri Nov 05 '12 at 23:25
  • Another thing: If you have no datasets in your API response, it's likely that you haven't created any yet. Can you confirm that you have done so in the project that you are using? – Michael Manoochehri Nov 09 '12 at 23:27
  • 1
    Ryan thanks for answering that. The docs are kinda all over the place. But Im glad someone from google is listening – a.m. Oct 17 '13 at 00:51
  • 1
    4 years later and same problem - docs generally assume will be using Google Servers. And the suggested SignedJwtAssertionCredentials method does not exist. Have added an answer of what currently worked for me. – hoju Dec 13 '16 at 07:51
4

If you login locally with gcloud:

gcloud auth application-default login

Then a credentials file will be stored in ~/.config/gcloud/, which can be loaded with:

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

credentials = GoogleCredentials.get_application_default()
service = build('bigquery', 'v2')

Or you can just load the BQ security key file directly with:

from google.cloud import bigquery
client = bigquery.Client.from_service_account_json(path_to_key.json)
hoju
  • 24,959
  • 33
  • 122
  • 169
  • Thanks for an answer that uses the latest libraries. Note that there is now a documentation page https://cloud.google.com/bigquery/docs/authentication/service-account-file that covers these authentication methods. – Tim Swast Dec 01 '17 at 01:18
  • Note that `gcloud auth application-default login` is no longer recommended. Downloading a JSON key file for a service account and setting the `GOOGLE_APPLICATION_CREDENTIALS` environment variable is the most reliable way to set credentials in mixed environments. – Tim Swast Dec 01 '17 at 01:23
0

I had the same issue. It is possibly because you do not have the requisite permissions in the Project for the Service Account key you are using.

Artjom B.
  • 58,311
  • 24
  • 111
  • 196
Sai J
  • 1