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.
Thank you in advance for all your answers.
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.