update scheduled query

[jacobmpeters]

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.

[hadley]

I assume you also want to be able to create a new scheduled query, not just update an existing?

[jacobmpeters]

Yes, that would be very helpful. In our process, we often set them up manually in BigQuery and then update them automatically in R/Python scripts thereafter. But, if it were easy to create and update them from bigrquery, this would be much cleaner.

[hadley]

Note to self: look at discovery doc wrappers in googlesheets4.

[hadley]

Do you have any sense of how you get a data source setup so I could test it myself?

[jacobmpeters]

@hadley Yes, I think I can help with that...

I'm not sure what detail you need. Happy to chat if you need more info..

• Go to this link and click console to create a project.
• Navigate to BigQuery and click create dataset.
• Once you have a dataset, you can enter this query to load some data into your project from a public dataset:

CREATE OR REPLACE TABLE `your_project_name.your_dataset_name.baby_names` AS 
SELECT * 
FROM `bigquery-public-data.usa_names.usa_1910_2013` 
LIMIT 1000;

This example has the following schema:

fullname type
state STRING
gender STRING
year INTEGER
name STRING
number INTEGER

Here is some info about the dataset itself: https://console.cloud.google.com/marketplace/product/social-security-administration/us-names?project=long-base-314819

Hope this helps! Let me know if you need anything else.

[hadley]

How does that connect with a schedule query?

[hadley]

I need a bit more help to get this to a place where I can test it, so I'm going to put off until the next release of bigrquery which I'll aim to do early next year.

[jacobmpeters]

I need a bit more help to get this to a place where I can test it, so I'm going to put off until the next release of bigrquery which I'll aim to do early next year.

Ok! Sorry I missed your previous question. Thanks for the update. I'm happy to help with your testing when you are ready to resume.