Home > Back-end >  How to get names of scheduled queries in bigquery
How to get names of scheduled queries in bigquery

Time:11-23

Using a python client to connect with bigquery, how can we get names of all the scheduled queries present in that project?

I tried following up with this link - https://cloud.google.com/bigquery/docs/reference/datatransfer/libraries

But got no information on the names of the scheduled queries.

CodePudding user response:

To list all the scheduled queries for a project with Python BigQuery Client :

def get_scheduled_queries_configs():
    from google.cloud import bigquery_datatransfer

    transfer_client = bigquery_datatransfer.DataTransferServiceClient()

    project_id = "{project_id}"
    parent = transfer_client.common_location_path(project=project_id, location='EU')

    request = bigquery_datatransfer.ListTransferConfigsRequest(
        parent=parent,
        data_source_ids=['scheduled_query']
    )

    configs = transfer_client.list_transfer_configs(request=request)
    print("Got the following configs:")
    for config in configs:
        print(f"\tID: {config.name}, Schedule: {config.schedule}")
        print(f"\tDisplay name: {config.display_name}")

        config_name = config.name
        config_schedule = config.schedule
        config_display_name = config.display_name

    return configs


if __name__ == '__main__':
    scheduled_queries_configs = get_scheduled_queries_configs()

Some explanations :

  • This code retrieves transfer configs only for scheduled queries via ListTransferConfigsRequest object. The request takes the parent argument containing the project and the location EU in this example. The request take also data_source_ids argument with scheduled_query value
  • The location is important because if your scheduled queries are in US and you execute the request in EU, the result will be empty
  • The config.display_name allows to retrieve the name of a scheduled query
  • Related