How to trigger On-Demand scheduled Query in Google Bigquery
Feb 27
I created a query in Google Bigquery which is connected to a Topic which triggers the Cloud function. I want to schedule the query on-demand run. Once I schedule the query I am not able to find any option in UI to run that particular Query. Please let me know if some more clarification is required. I only see the following options in scheduled query UI page.
1 answer
Accepted answer · original discussion
Aug 5
UPDATE Jul/2021: Google improved its UI, so now it is possible to run scheduled queries on-demand (check Julio's answer for details). However, according to him, the command line option described here runs fasters than the UI option.
Sadly, you can't. I don't know if this is a bug or not (probably a bad UI design), but you can't run on-demand scheduled queries via the UI.
This only applies to scheduled queries set to run on-demand. If your query is scheduled to run in any time frame (daily, weekly, etc), you can make it run on-demand using the option "Schedule backfill". This option ask you to provide a start date and an end date, so it force all runs that were supposed to run in the given time window (yes, using this option the number of runs will depend of the schedule). That is, if your query is set to run daily, just provide one day time span that your query will run once.
Alternatively, if you really need to run it on-demand, you will need to use the command line or the API, as shown below.
Command line solution (using Cloud Shell):
Run the command:
bq mk --transfer_run --run_time "$(date --iso-8601=seconds)" \
projects/[YOUR_PROJECT]/locations/us/transferConfigs/[YOUR_SCHEDULED_QUERY_GUID]
Notice that YOUR_SCHEDULED_QUERY_ID is a GUID (it is not the the Scheduled Query name, it looks like 1234a123-1234-1a23-1be9-12ab3c456de7). You can copy it from the browser URL or get from a list of all scheduled queries running the command bq ls --transfer_config --transfer_location='us'.
In all places, change us for any other location you may be using (e.g. eu).
API solution
Use transferConfigs.startManualRuns API using the same parameters as the command line option.
Notice the parameter requestedRunTime is mandatory. Its value is only relevant if you use @run_time parameter in your query, otherwise it is not used. You can even hardcode a date if you want. So just populate it with any valid datetime in the format 2020-08-04T00:00:0Z and you're done (in the command line solution, I populate it with the command date --iso-8601=seconds).
0 question comments
Use comments to ask for clarification. Post a solution as an answer.
No question comments on this page.