Get all my scheduled SQL queries in BigQuery Google Cloud

Question

Im trying to get SQL codes by command-line (CLI) of my scheduled queries in BigQuery. I'm also interested if there is a way to do that by the Google Cloud Platform user interface.

I have taken a quick look to this related post, but that's not the answer that I am looking for.

• List Scheduled Queries in BigQuery

Thank you in advance for all your answers.

Answer 1

To get your scheduled queries (= datatransfers), you can also use the python API:

from google.cloud import bigquery_datatransfer

bq_datatransfer_client = bigquery_datatransfer.DataTransferServiceClient()

request_datatransfers = bigquery_datatransfer.ListTransferConfigsRequest(
    # if US, you can just do parent='projects/YOUR_PROJECT_ID'
    parent='projects/YOUR_PROJECT_ID/locations/EU',  
)

# this method will also deal with pagination
response_datatransfers = bq_datatransfer_client.list_transfer_configs(
    request=request_datatransfers)

# to convert the response to a list of scheduled queries
datatransfers = list(response_datatransfers) 

To get the actual query text from the scheduled query:

for datatransfer in datatransfers:
    print(datatransfer.display_name)
    print(datatransfer.params.get('query'))
    print('\n')

See also these SO questions:

• How do I list my scheduled queries via the Python google client API?
• List Scheduled Queries in BigQuery

Docs on this specific part of the python API: https://cloud.google.com/python/docs/reference/bigquerydatatransfer/latest/google.cloud.bigquery_datatransfer_v1.services.data_transfer_service.DataTransferServiceClient#google_cloud_bigquery_datatransfer_v1_services_data_transfer_service_DataTransferServiceClient_list_transfer_configs

Answer 2

I found how to query the scheduled queries with the bq CLI. You have to rely on the BigQuery Transfer API. Why? I don't know, but it's the right keyword here.

For listing all your schedule query, perform this (change your location if you want!):

bq ls --transfer_config --transfer_location=eu

# Result
                                             name                                               displayName    dataSourceId     state
 --------------------------------------------------------------------------------------------- ------------- ----------------- -------
  projects/763366003587/locations/europe/transferConfigs/5de1fc66-0000-20f2-bee7-089e082935bc   test          scheduled_query

For viewing the detail, copy the name and use bq show

bq show --transfer_config  \
projects/763366003587/locations/europe/transferConfigs/5de1fc66-0000-20f2-bee7-089e082935bc

# Result
       updateTime            destinationDatasetId   displayName      schedule       datasetRegion          userId                                            scheduleOptions                                       dataSourceId                  
                                                                         params
 ----------------------------- ---------------------- ------------- ----------------- --------------- ---------------------- -------------------------------------------------------------------------------------- ----------------- --------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
  2019-11-18T20:20:22.279237Z   bi_data                test          every day 20:19   europe          -7444165337568771239   {u'endTime': u'2019-11-18T21:19:36.528Z', u'startTime': u'2019-11-18T20:19:36.497Z'}   scheduled_query   {u'query': u'
SELECT * FROM `gbl-imt-homerider-basguillaueb.bi_data.device_states`', u'write_disposition': u'WRITE_TRUNCATE', u'destination_table_name_template': u'test_schedule'}

You can use json format and jq for getting only the query like this

bq show --format="json" --transfer_config  \
projects/763366003587/locations/europe/transferConfigs/5de1fc66-0000-20f2-bee7-089e082935bc \
 | jq '.params.query'

# Result
"SELECT * FROM `gbl-imt-homerider-basguillaueb.bi_data.device_states`"

I can explain how I found this unexpected solution that if you want, but it's not the topic here. I think it's not documented

On the GUI, it's easier.

• Go to BigQuery (new UI, in blue)
• Click on scheduled query on the left menu

• Click on your scheduled query name
• Click on configuration on the top on the screen