...
CREATE SCHEDULED QUERY <scheduled_query_name>
<scheduleSpec> <scheduleSpecification>
[<executedAsSpec> ]
[<enableSpecification>]
<definedAsSpec>
Alter Scheduled query syntax
ALTER SCHEDULED QUERY <scheduled_query_name>
(<scheduleSpec>|<executedAsSpec>|<enableSpecification>|<definedAsSpec>);
Drop syntax
DROP SCHEDULED QUERY <scheduled_query_name>;
Anchor
...
scheduleSpec
scheduleSpec |
...
scheduleSpec
scheduleSpec |
...
scheduleSpecification syntax
Schedules can be specified using CRON expressions or for common cases there is a simpler form; in any case the schedule is stored as Quartz cron expression.
...
Anchor |
---|
...
|
...
|
...
CRON quartz <quartz_schedule_expression>
where expression
quartz_schedule_expression Anchor quartz_schedule_expression quartz_schedule_expression
...
The schedule have to be specified is quoted schedule in the Quartz format
https://www.freeformatter.com/cron-expression-generator-quartz.html
For example the CRON '0 */10 * * * ? *'
expression will fire every 10 minutes.
EVERY base schedule syntax
To give a more readable way to declare schedules EVERY can be used.
EVERY [<integer>] (SECOND|MINUTE|HOUR) [(OFFSET BY|AT) <timeOrDate>]
the format makes it possible to declare schedules in a more readable way:
EVERY 2 MINUTES
EVERY HOUR AT '0:07:30'
EVERY DAY AT '11:35:30'
Anchor | ||||
---|---|---|---|---|
|
...
Executed AS syntax
EXECUTED AS user<user_namename>
Scheduled queries are executed as the declaring user by default; but people with admin privileges might be able to change the executing user.
...
(ENABLE[D] | DISABLE[D])
Can be used to enable/disable a schedule.
Anchor definedAsSpec definedAsSpec
definedAsSpec | |
definedAsSpec |
...
Defined AS syntax
[DEFINED] AS query<hiveQuery>
The “query” is a single statement expression to be scheduled for execution.
System Tables
Informations about scheduled queries/executions can be obtain by using the information_schema or the sysdb - recommended way is to use the information_schema; sysdb is tables are there to build the information_schema level views - and for debugging.
...
I will transpose the resultset to describe each column
scheduled_query_id | 1 | Internally, every scheduled query also has a numeric id |
schedule_name | sc1 | The name of the schedule |
enabled | true | True if the schedule is enabled |
cluster_namespace | hive | The namespace thes scheduled query belongs to |
schedule | 0 */10 * * * ? * | The schedule described in QUARTZ cron format |
user | dev | The owner/executor of the query |
query | select 1 | The query being scheduled |
next_execution | 2020-01-29 16:50:00 | Technical column; shows when the next execution should happen |
Note: (schedule_name,cluster_namespace) is unique
...