我正在研究GCP，并试图通过编程创建传输配置来安排BigQuery中的查询，并且将所需权限分配给新的服务帐户(BigQuery.transfers.get，BigQuery.transfers.update(，并尝试使用以下代码创建传输配置。我能够获得关于其他已经创建的日程查询的信息，并能够更新它们。但我无法创造它们。获取403调用方没有权限。
from google.cloud import bigquery_datatransfer
transfer_client = bigquery_datatransfer.DataTransferServiceClient()
project_id = "My_Project_Id"
dataset_id = "My_dataset_id"
service_account_name = "<serviceAccount>"
query_string = "update dataservices.temp_bq_schedule set current_time=current_timestamp() where some_integer=17"
parent = transfer_client.common_project_path(project_id)
transfer_config = bigquery_datatransfer.TransferConfig(
destination_dataset_id=dataset_id,
display_name="Test_Schedule_QUERY",
data_source_id="scheduled_query",
params={
"query": query_string,
"write_disposition": "",
"partitioning_field": "",
},
schedule="every 24 hours",
)
transfer_config = transfer_client.create_transfer_config(
bigquery_datatransfer.CreateTransferConfigRequest(
parent=parent,
transfer_config=transfer_config,
service_account_name=service_account_name,
)
)
print("Created scheduled query '{}'".format(transfer_config.name))
这是执行代码时的错误
Traceback (most recent call last):
File "/env/lib/python3.7/site-packages/google/api_core/grpc_helpers.py", line 66, in error_remapped_callable
return callable_(*args, **kwargs)
File "/env/lib/python3.7/site-packages/grpc/_channel.py", line 946, in __call__
return _end_unary_response_blocking(state, call, False, None)
File "/env/lib/python3.7/site-packages/grpc/_channel.py", line 849, in _end_unary_response_blocking
raise _InactiveRpcError(state)
grpc._channel._InactiveRpcError: <_InactiveRpcError of RPC that terminated with:
status = StatusCode.PERMISSION_DENIED
details = "The caller does not have permission"
debug_error_string = "{"created":"@1637593645.956604992","description":"Error received from peer ipv4:<some ip address with port>","file":"src/core/lib/surface/call.cc","file_line":1063,"grpc_message":"The caller does not have permission","grpc_status":7}"
>
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "merge_query.py", line 36, in <module>
service_account_name=service_account_name,
File "/env/lib/python3.7/site-packages/google/cloud/bigquery_datatransfer_v1/services/data_transfer_service/client.py", line 646, in create_transfer_config
response = rpc(request, retry=retry, timeout=timeout, metadata=metadata,)
File "/env/lib/python3.7/site-packages/google/api_core/gapic_v1/method.py", line 154, in __call__
return wrapped_func(*args, **kwargs)
File "/env/lib/python3.7/site-packages/google/api_core/grpc_helpers.py", line 68, in error_remapped_callable
raise exceptions.from_grpc_error(exc) from exc
google.api_core.exceptions.PermissionDenied: 403 The caller does not have permission
仅供参考，我也尝试省略service_account，但仍然存在相同的问题。
代码片段可以在这里找到https://github.com/googleapis/python-bigquery-datatransfer/blob/main/samples/snippets/scheduled_query.py
此错误与权限有关。您需要以下权限。
要安排查询，您需要以下身份和访问管理(IAM(权限：
- bigquery.transfers.update或bigquery.jobs.create和bigquery.transfers.get创建传输
- bigquery.jobs.create运行计划查询
- 目标数据集上的bigquery.datasets.update
要修改计划查询，您必须是计划的创建者并具有以下权限：
- bigquery.jobs.create
- bigquery.transfers.update
您可以查看有关权限的更多详细信息。
您可以看到这个示例代码。
from google.cloud import bigquery_datatransfer
transfer_client = bigquery_datatransfer.DataTransferServiceClient()
# The project where the query job runs is the same as the project
# containing the destination dataset.
project_id = "your-project-id"
dataset_id = "your_dataset_id"
# This service account will be used to execute the scheduled queries. Omit
# this request parameter to run the query as the user with the credentials
# associated with this client.
service_account_name = "abcdef-test-sa@abcdef-test.iam.gserviceaccount.com"
# Use standard SQL syntax for the query.
query_string = """
SELECT
CURRENT_TIMESTAMP() as current_time,
@run_time as intended_run_time,
@run_date as intended_run_date,
17 as some_integer
"""
parent = transfer_client.common_project_path(project_id)
transfer_config = bigquery_datatransfer.TransferConfig(
destination_dataset_id=dataset_id,
display_name="Your Scheduled Query Name",
data_source_id="scheduled_query",
params={
"query": query_string,
"destination_table_name_template": "your_table_{run_date}",
"write_disposition": "WRITE_TRUNCATE",
"partitioning_field": "",
},
schedule="every 24 hours",
)
transfer_config = transfer_client.create_transfer_config(
bigquery_datatransfer.CreateTransferConfigRequest(
parent=parent,
transfer_config=transfer_config,
service_account_name=service_account_name,
)
)
print("Created scheduled query '{}'".format(transfer_config.name))
您可以查看有关代码的更多详细信息。
您可以查看有关使用转账的更多信息。
您还需要确保服务帐户应该与运行计划查询的项目来自同一个项目。否则它就不会起作用。我很难发现。
当然，请确保您作为用户和服务帐户拥有所需的所有权限，如下所述：
https://cloud.google.com/bigquery/docs/scheduling-queries#required_permissions
另请参阅：如何在定时查询中显示和更改用户