update scheduled query #590
Description
Our team would love to be able to use bigrquery to update a scheduled query in BigQuery. Specifically, I would like to be able to update the "query" itself with updated SQL code, but updating the scheduling would also be useful. I generate queries using R scripts and set them to run on BigQuery using Python.. or if I'm desperate I'll copy and paste them manually.
The documentation for the required API endpoint is here: https://cloud.google.com/bigquery/docs/reference/datatransfer/rest/v1/projects.locations.transferConfigs/patch
Google provides an example for updating a query's "display_name" using the Python client library here https://cloud.google.com/bigquery/docs/scheduling-queries#updating-a-scheduled-query
I modified it below to update the query string itself:
from google.cloud import bigquery_datatransfer, bigquery
from google.protobuf import field_mask_pb2
query_params = {"query": "some sql code here that I would like to schedule"}
# Update transfer configurations
transfer_config_name = "projects/1234/locations/us/transferConfigs/abcd"
transfer_client = bigquery_datatransfer.DataTransferServiceClient()
transfer_config = bigquery_datatransfer.TransferConfig(name=transfer_config_name)
transfer_config.params = query_params
transfer_config = transfer_client.update_transfer_config(
{
"transfer_config": transfer_config,
# IMPORTANT NOTE: Anything that you want to update must be in the paths list!!
# i.e., paths=["transfer_config_name","params","ETCETERA"]
"update_mask": field_mask_pb2.FieldMask(paths=["params"])
}
)I have long wished that we could do this in directly in R using bigrquery.