BigQuery external table creation failed with "autodetect" schema while reading a file from Google Drive

Question

I am trying to create a BigQuery external table by reading a file from Google Drive - it works with inline scheme but failed with autodetect flag.

Document referenced:
https://cloud.google.com/bigquery/external-data-drive

Schema File:

$ bq mkdef --autodetect --source_format=CSV "https://drive.google.com/open?id=<file-id>" > schema.json

schema.json:

{
  "autodetect": true,
  "csvOptions": {
    "encoding": "UTF-8",
    "quote": "\""
  },
  "sourceFormat": "CSV",
  "sourceUris": [
    "https://drive.google.com/open?id=<file-id>"
  ]
}

External Table:

$ bq mk --external_table_definition=schema.json mydataset.mytable
BigQuery error in mk operation: Access Denied: BigQuery BigQuery: No OAuth token with Google Drive scope was found.

It works, with inline schema:

$ bq mk --external_table_definition=col1:INTEGER,col2:STRING@CSV=https://drive.google.com/open?id=<file-id> mydataset.mytable
Table 'myproject:mydataset.mytable' successfully created.

Note: I have enabled Google Drive access by using gcloud auth login --enable-gdrive-access

Answer 1

The issue actually seems to be related to the authentication. For what it's worth, gcloud uses different OAuth token than bq.

I think the best course of action at this point is to:

1. look up $HOME/.bigqueryrc, there is credential_file = line,
2. remove the credential_file referenced in the previous step (on Linux/macOS it's probably something like .config/gcloud/...),
3. run gcloud auth --enable-gdrive-access --force, the OAuth window should ask you for the permission to use GDrive too,
4. retry the creating external table definition.

If it still doesn't work, you can lookup what scopes are used with your token by previewing the file referenced in credential_file. It's a simple JSON file, and the scopes is just a list of URIs, there should be one with drive or drive.read.

Answer 2

Apparently, the culprit here is "autodetect": true parameter , being specified in table definition file --external_table_definition when creating Bigquery external table from source data resided in Google Drive.

Actually bq command-line tool is a Python script which interacts with Biqquery REST API, that means we trigger tables.insert API method to create a permanent external table, supplying appropriate ExternalDataConfiguration in Table json request body.

You can check it out performing the relevant API call to Bigquery API throughout API Explorer consuming table definition parameters from ExternalDataConfiguration:

curl --request POST \
  'https://bigquery.googleapis.com/bigquery/v2/projects/<projectid>/datasets/<datasetid>/tables?key=[YOUR_API_KEY]' \
  --header 'Authorization: Bearer [YOUR_ACCESS_TOKEN]' \
  --header 'Accept: application/json' \
  --header 'Content-Type: application/json' \
  --data '{"tableReference":{"datasetId":"datasetId","projectId":"projectId","tableId":"tableId"},"externalDataConfiguration":{"autodetect":true,"csvOptions":{"encoding":"UTF-8","quote":"\""},"sourceFormat":"CSV","sourceUris":["https://drive.google.com/open?id=<file-id>"]}}' \
  --compressed

I've received the same error in response message:

 "error": {
    "code": 403,
    "message": "Access Denied: BigQuery BigQuery: No OAuth token with Google Drive scope was found.",
    "errors": [
      {
        "message": "Access Denied: BigQuery BigQuery: No OAuth token with Google Drive scope was found.",
        "domain": "global",
        "reason": "accessDenied"
      }
    ],
    "status": "PERMISSION_DENIED"
  }

For now, you can provide the schema inline (on the command line), or you can provide a JSON file containing the schema definition to get thing worked.

In order to keep this issue evidence more visible to developers, I would encourage you to file the bug report via Public Issue tracker, thus we can keep track of any updates occurred or try to reach out Google support.