3

(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

Dimitri Masin
  • 167
  • 2
  • 11
  • Have you gone through the 3 steps as outlined here: https://stackoverflow.com/questions/40731823/encountered-an-error-while-globbing-file-pattern-error-when-using-bigquery-api/40731962#40731962 – Graham Polley Feb 08 '18 at 00:58
  • @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 Feb 08 '18 at 08:14
  • 1
    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 Feb 08 '18 at 08:20
  • don't quite understand it. Are you using the credentials created by `gcloud init` in your python script? – Willian Fuks Feb 08 '18 at 13:59
  • 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 Feb 08 '18 at 17:59
  • This code above works fine on a normal non-Drive table. – Dimitri Masin Feb 08 '18 at 18:03

1 Answers1

2

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)

Guillem Xercavins
  • 6,938
  • 1
  • 16
  • 35
  • as of today, Mar 2021, I tried and found that the `scope` `'https://www.googleapis.com/auth/cloud-platform'` is not needed – cryanbhu Mar 16 '21 at 09:08