How to query BigQuery programmatically from Python without end-user interaction?

Question

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.

Answer 1

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 = '[email protected]'

# 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']