Querying federated tables (Google Drive) via BigQuery python api

Question

(There are a lot of similar threads here but unfortunately I couldn't find the answer to my error anywhere here or on Goolge)

I'm trying to query a federated table in BigQuery which is pointing to a spreadsheet in Drive.

I've run the following command to create default application credentials for gcloud:

$ gcloud auth application-default login

But this doesn't include Drive into the scope so I'm getting the following error message (which makes sense): Forbidden: 403 Access Denied: BigQuery BigQuery: No OAuth token with Google Drive scope was found.

Then I've tried to auth with explicit Drive scope:

$ gcloud auth application-default login --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform,https://www.googleapis.com/auth/bigquery

After that I'm getting the following error when I try to use bigquery python api:

"Forbidden: 403 Access Denied: BigQuery BigQuery: Access Not Configured. Drive API has not been used in project 764086051850 before or it is disabled. Enable it by visiting https://console.developers.google.com/apis/api/drive.googleapis.com/overview?project=764086051850 then retry. If you enabled this API recently, wait a few minutes for the action to propagate to our systems and retry."

The project number above does not exist in our organisation and the provided link leads to a page which says: The API "drive.googleapis.com" doesn't exist or you don't have permission to access it

Drive API is definitely enabled for the default project, so the error message doesn't make much sense. I can also query the table from the terminal using bq query_string command.

I'm currently out of ideas on how to debug this further, anyone suggestions?

Configuration:
Google Cloud SDK 187.0.0
Python 2.7
google-cloud 0.27.0
google-cloud-bigquery 0.29.0

Have you gone through the 3 steps as outlined here: stackoverflow.com/questions/40731823/… — Graham Polley
@GrahamPolley thanks for your answer! I'm not using a service account but login through my own one. But yes, Drive API is enabled and I have access to the spreadsheet (i can query the table through $ bq query). Drive scope is requested as you see above. Or is $ gcloud auth application-default login only supposed to work with service accounts? — Dimitri Masin
As I've described above the error messages mentions a project which is not even part of our org. Not to mention that it's not the default project that I've setup on $ gcloud init — Dimitri Masin
don't quite understand it. Are you using the credentials created by gcloud init in your python script? — Willian Fuks
I'm using application default credentials in my python script. Like here: from google.cloud import bigquery client = bigquery.Client() query = "SELECT * FROM tablename LIMIT 5" query_job = client.query(query) rows = query_job.result() for row in rows: print(row.name) — Dimitri Masin

Guillem Xercavins Guillem Xercavins · Accepted Answer · 2018-02-15T11:00:55

There might be issues when using the default credentials. However, you can use a service account, save the credentials in a JSON file and add the necessary scopes. I did a quick test and this code worked for me:

from google.cloud import bigquery
from google.oauth2.service_account import Credentials

scopes = (
        'https://www.googleapis.com/auth/bigquery',
        'https://www.googleapis.com/auth/cloud-platform',
        'https://www.googleapis.com/auth/drive'
)
credentials = Credentials.from_service_account_file('/path/to/credentials.json')
credentials = credentials.with_scopes(scopes)
client = bigquery.Client(credentials=credentials)

query = "SELECT * FROM dataset.federated_table LIMIT 5"
query_job = client.query(query)
rows = query_job.result()
for row in rows: print(row)

If you get a 404 not found error is because you need to share the spreadsheet with the service account (view permission)

Querying federated tables (Google Drive) via BigQuery python api

1 Answers