“在通配文件模式时遇到错误"使用带有 Google 表格的 BigQuery API 时出错 [英] "Encountered an error while globbing file pattern" error when using BigQuery API w/ Google Sheets

问题描述

尝试从 BigQuery API 访问联合源(Google 表格)时，抛出以下错误:

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"
    }
[..]

BigQuery 中的表设置为指向此文件.它通过 Web UI 工作.只有在尝试通过 API 查询表时，它才会因上述错误而窒息.

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.

我猜这与权限有关.需要做什么才能允许从作为联合源(指向 Google 表格)的 API 访问 BigQuery 表?

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)?

推荐答案

允许 API 查询 BigQuery 中的联合表时需要遵循 3 个步骤 - BigQuery 指向云端硬盘(即 Google 表格)中的文件.

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.

其中两个步骤记录在此处(我错过了第二个 - 添加 Drive 范围).最后一个是将用于访问 API 的关联服务帐户电子邮件添加到文件本身.

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. 使用 Google 云平台控制台为进行 API 调用的项目启用 Google Drive API.
2. 请求 Google 云端硬盘的 OAuth 范围以及 BigQuery 的范围.
3. 将您使用的服务帐户电子邮件添加到云端硬盘中的文件.它看起来像 [e-mail].查看"权限就足够了.

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.

这篇关于“在通配文件模式时遇到错误"使用带有 Google 表格的 BigQuery API 时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！