You conditionally create a trigger in a resource plan.
CREATE TRIGGER syntax
CREATE TRIGGER plan_name.name WHEN condition DO action
The dot-delimited resource plan and trigger names
A supported condition
A supported action
CREATE TRIGGER examples
CREATE TRIGGER plan1.trigger1 WHEN HDFS_BYTES_READ > 10Gb DO MOVE TO large_query_pool; CREATE TRIGGER plan1.trigger2 WHEN ELAPSED_TIME > 30min DO KILL;
CREATE TRIGGER description
The trigger you create is not applied to any queries. You must add the trigger you create to one or more a query pools or unmanaged queries using ALTER TRIGGER. The trigger fires if the specified condition is met and the action executes for any query in the applicable query pool or unmanaged query.
CREATE TRIGGER supports the following actions and conditions:
- MOVE TO pool_path
Moves workload-managed queries in a query pool to the designated pool. Do not use on unmanaged queries. If the destination pool is at capacity with regard to query parallelism, the current query is killed.
- Apache Tez counter is greater than a particular value, and that value equals a time
unit suffix or size suffix that represents a timespan or number of bytes
There is no restriction on the type of Tez counter that can generate a trigger. Counter values are generated at query runtime.
- Apache Tez counter is greater than a particular value, and that value equals a time unit suffix or size suffix that represents a timespan or number of bytes
- HDFS_BYTES_READ, HDFS_BYTES_WRITTEN, and similar counters for FILE and other file systems (see example below)
- CREATED FILES
Formatting a WHEN HDFS_BYTES_WRITTEN clause
Single quotation marks are required around non-numeric arguments in a WHEN HDFS_BYTES_WRITTEN clause, as shown in the following example:
CREATE TRIGGER multiple_pools_pla ^Mn.too_large_write_trigger WHEN HDFS_BYTES_WRITTEN > '10kb' DO MOVE TO pool1;
You can omit quotation marks in numeric arguments:
CREATE TRIGGER multiple_pools_pla ^Mn.too_large_write_trigger WHEN HDFS_BYTES_WRITTEN > 503993049 DO MOVE TO pool1;