CODEWITHSUNDEEP
google-drive-apigoogle-bigquerygoogle-sheets-api
Graham Polley
Graham Polley

Reputation: 14791

"Encountered an error while globbing file pattern" error when using BigQuery API w/ Google Sheets

When trying the access a federated source (Google sheets) from BigQuery API, the following error is thrown:

[..]
 "errorResult" : {
      "location" : "/gdrive/id/<removed_file_id>",
      "message" : "Encountered an error while globbing file pattern.",
      "reason" : "invalid"
    }
[..]

The table in BigQuery is set up to point to this file. It works via the Web UI. It is only when trying to query the table through the API does it then choke with the error above.

I'm guessing it has something to do with permissions. What needs to be done to allow accessing a BigQuery table from the API which is a federated source (pointing to Google Sheets)?

Upvotes: 10

Views: 12345

Answers (2)

Paul Fentress
Paul Fentress

Reputation: 91

def create_big_query_client():

    credentials, project = google.auth.default(
        scopes=[
            "https://www.googleapis.com/auth/cloud-platform",
            "https://www.googleapis.com/auth/drive",
            "https://www.googleapis.com/auth/bigquery",
        ]
    )

    bq_client = bigquery.Client(
        credentials=credentials,
        project=GCP_PROJECT_ID
    )

    return bq_client

client_with_drive_scopes = create_big_query_client()

query = """
SELECT *
FROM TABLE
"""

client_with_drive_scopes.query(query).result()

Note:

I discovered that I was unable to set the correct scopes using GCP workflows Big Query Connector. So In order to use a query that was connected to google sheets in my workflow, I created a cloud function, and put a bq client with correct scopes inside.

Upvotes: 0

Graham Polley
Graham Polley

Reputation: 14791

There are 3 steps to follow when allowing the API to query a federated table in BigQuery - which is pointing to a file in Drive i.e. Google Sheets.

Two of the steps are documented here (I was missing the 2nd one - adding the Drive scope). The last one is the add the associated service account email that is being used to access the API to the file itself.

  1. Use the Google Cloud Platform Console to enable the Google Drive API for the project making the API call.
  2. Request the OAuth scope for Google Drive in addition to the scope for BigQuery.
  3. Add the service account email that you are using to the file in Drive. It looks something like <project-id>-<fingerprint-hash>@developer.gserviceaccount.com. "View" permission is enough.

Upvotes: 12

Related Questions