如何验证服务帐户以对 GDrive 表支持的 BigQuery 表进行查询? [英] How do I Authenticate a Service Account to Make Queries against a GDrive Sheet Backed BigQuery Table?

问题描述

我的情况如下:

Google 帐户 A 在 BigQuery 中有一些数据.

Google Account A has some data in BigQuery.

Google 帐号 B 管理帐号 A 的 BigQuery 数据，并且还获得了帐号 A 的 Cloud Platform 项目的编辑权限.

Google Account B manages Account A's BigQuery data, and has also been given editor privileges for Account A's Cloud Platform project.

帐户 B 在 Google 云端硬盘中有一个工作表，其中包含一些很酷的参考数据.账户 B 登录 BQ Web 控制台，并在账户 A 的 BQ 项目中创建一个表，该表由该表支持.

Account B has a Sheet in Google Drive that has some cool reference data in it. Account B logs into the BQ Web console, and creates a table in Account A's BQ project that is backed by this sheet.

一切都很好.账户 B 可以通过 Web UI 在账户 A 的 BQ 数据中成功查询并加入该表.

问题:

Google 帐户 A 也有一个服务帐户，它是 Google 帐户 A 的云平台项目的编辑器.此服务帐户使用 python google-cloud API 管理和查询 BQ 中的数据.当此服务帐户尝试查询由帐户 B 的 GDrive 表支持的引用表时，作业失败并显示以下错误:

Google Account A also has a service account that is an editor for Google Account A's Cloud Platform Project. This service account manages and queries the data in BQ using the python google-cloud API. When this service account attempts to query the reference table that is backed by Account B's GDrive Sheet, the job fails with this error:

Encountered an error while globbing file pattern.  JobID: "testing_gdrivesheet_query_job1"

据我所知，这实际上是一个身份验证问题.我如何为账户 A 的服务账户授予对账户 B 的 GDrive 的适当访问权限，以便它可以访问该引用表?

Near as I can tell this is actually an authentication issue. How can I give Account A's service account appropriate access to Account B's GDrive so it can access that reference table?

奖励积分:由 GDrive Sheet 支持的表与原生 BQ 表之间是否有任何性能差异?

Bonus Points: Is there any performance difference between a table backed by a GDrive Sheet vs a native BQ table?

推荐答案

您应该能够通过以下步骤使其工作:

You should be able to get this working with the following steps:

首先使用与服务帐户关联的电子邮件/服务帐户 ID"共享工作表.

First share the sheet with the email/"service account id" associated with the service account.

然后，如果您使用 bigquery 和 drive 范围创建客户端，您将能够访问您的工作表支持的表.(您可能需要在服务帐户上启用域范围委派).

Then you'll be able to access your sheet-backed table if you create a Client with the bigquery and drive scopes. (You might need to have domain-wide-delegation enabled on the service account).

scopes = ['https://www.googleapis.com/auth/bigquery', 'https://www.googleapis.com/auth/drive']

credentials = ServiceAccountCredentials.from_json_keyfile_name(
'<path_to_json>', scopes=scopes)

# Instantiates a client
client = bigquery.Client(project = PROJECT, credentials = credentials)

bqQuery = client.run_sync_query(q)
bqQuery.run()
bqQuery.fetch_data()

这篇关于如何验证服务帐户以对 GDrive 表支持的 BigQuery 表进行查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！