google-bigquery - 使用服务帐户通过 API 从 BigQuery 中基于 Google 表格的表中查询数据

Question

我可以使用服务帐户从 native BigQuery 表中获取数据。

但是，当我尝试使用同一服务帐户从 BigQuery 中基于 Google 表格的表格选择时遇到错误。

from google.cloud import bigquery

client = bigquery.Client.from_service_account_json(
    json_credentials_path='creds.json',
    project='xxx',
)

# this works fine
print('test basic query: select 1')
job = client.run_sync_query('select 1')
job.run()
print('results:', list(job.fetch_data()))
print('-'*50)

# this breaks
print('attempting to fetch from sheets-based BQ table')
job2 = client.run_sync_query('select * from testing.asdf')
job2.run()

输出:

⚡  ~/Desktop ⚡  python3 bq_test.py
test basic query: select 1
results: [(1,)]
--------------------------------------------------
attempting to fetch from sheets-based BQ table
Traceback (most recent call last):
  File "bq_test.py", line 16, in <module>
    job2.run()
  File "/usr/local/lib/python3.6/site-packages/google/cloud/bigquery/query.py", line 381, in run
    method='POST', path=path, data=self._build_resource())
  File "/usr/local/lib/python3.6/site-packages/google/cloud/_http.py", line 293, in api_request
    raise exceptions.from_http_response(response)
google.cloud.exceptions.Forbidden: 403 POST https://www.googleapis.com/bigquery/v2/projects/warby-parker-1348/queries: Access Denied: BigQuery BigQuery: No OAuth token with Google Drive scope was found.

我尝试使用 oauth2client.service_account.ServiceAccountCredentials 来显式定义 scopes，包括 drive 的范围，但我得到了尝试这样做时出现以下错误:

ValueError: This library only supports credentials from google-auth-library-python. See https://google-cloud-python.readthedocs.io/en/latest/core/auth.html for help on authentication with this library.

我的理解是身份验证现在是通过 IAM 处理的，但我没有看到任何角色可以应用于此服务帐户，而这些角色与驱动器有任何关系。

如何使用 BigQuery python 客户端从工作表支持的表中进行选择？

Answer 1

我遇到了同样的问题并想出了解决办法。

探索时google.cloud.bigquery.Client类，有一个全局变量元组SCOPE它不会被任何参数或任何 Credentials 对象更新，将其默认值保留给使用它的类。

要解决此问题，您只需将新的范围 URL 添加到 google.cloud.bigquery.Client.SCOPE 元组即可。

在以下代码中，我向其中添加了 Google 云端硬盘范围:

from google.cloud import bigquery

#Add any scopes needed onto this scopes tuple.
scopes = (
     'https://www.googleapis.com/auth/drive'
)

bigquery.Client.SCOPE+=scopes

client = bigquery.Client.from_service_account_json(
    json_credentials_path='/path/to/your/credentials.json',
    project='your_project_name',
)

使用上面的代码，您将能够从 BigQuery 中基于表格的表中查询数据。

希望对您有所帮助!