Update query string in scheduled query using Python Client for BigQuery Data Transfer Service

Question

I'm struggling to find documentation and examples for Python Client for BigQuery Data Transfer Service. A new query string is generated by my application from time to time and I'd like to update the existing scheduled query accordingly. This is the most helpful thing I have found so far, however I am still unsure where to pass my query string. Is this the correct method?

from google.cloud import bigquery_datatransfer_v1

def sample_update_transfer_config():
    # Create a client
    client = bigquery_datatransfer_v1.DataTransferServiceClient()

    # Initialize request argument(s)
    transfer_config = bigquery_datatransfer_v1.TransferConfig()
    transfer_config.destination_dataset_id = "destination_dataset_id_value"

    request = bigquery_datatransfer_v1.UpdateTransferConfigRequest(
        transfer_config=transfer_config,
    )

    # Make the request
    response = client.update_transfer_config(request=request)

    # Handle the response
    print(response)

score 0 · Answer 1 · answered Feb 09 '23 at 00:52

You may refer to this Update Scheduled Queries for python documentation from BigQuery for the official reference on the usage of Python Client Library in updating scheduled queries.

However, I updated the code for you to update your query string. I added the updated query string in the params and define what attributes of the TransferConfig() will be updated in the update_mask.

See updated code below:

from google.cloud import bigquery_datatransfer
from google.protobuf import field_mask_pb2

transfer_client = bigquery_datatransfer.DataTransferServiceClient()

transfer_config_name = "projects/{your-project-id}/locations/us/transferConfigs/{unique-ID-of-transferconfig}"
new_display_name = "Your Desired Updated Name if Necessary" #--remove if no need to update **scheduled query name**.

query_string_new = """
SELECT
  CURRENT_TIMESTAMP() as current_time
"""
new_params={
        "query": query_string_new,
        "destination_table_name_template": "your_table_{run_date}",
        "write_disposition": "WRITE_TRUNCATE",
        "partitioning_field": "",
}

transfer_config = bigquery_datatransfer.TransferConfig(name=transfer_config_name,
)
transfer_config.display_name = new_display_name #--remove if no need to update **scheduled query name**.
transfer_config.params = new_params

transfer_config = transfer_client.update_transfer_config(
    {
        "transfer_config": transfer_config,
        "update_mask": field_mask_pb2.FieldMask(paths=["display_name","params"]), #--remove "display_name" from the list if no need to update **scheduled query name**.
    }
)

print("Updates are executed successfully")

For you to get the value of your transfer_config_name, you may list all your scheduled queries by following this SO post.

Update query string in scheduled query using Python Client for BigQuery Data Transfer Service

1 Answers1