CREATE TRIGGER

CREATE TRIGGER syntax

CREATE TRIGGER plan_name.name WHEN condition DO action

plan_name.name
The dot-delimited resource plan and trigger names
condition
A supported condition
action
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:

Actions
- KILL
  Kills queries.
- 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.
Conditions
- 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. You can view all the the Tez counters for your specific version of Hive in the task counters of Tez View in Apache Ambari.

You typically use the following query-level counters with triggers:

ELAPSED_TIME
EXECUTION_TIME
TOTAL_TASKS
HDFS_BYTES_READ, HDFS_BYTES_WRITTEN, and similar counters for FILE and other file systems (see example below)
CREATED FILES
CREATED_DYNAMIC_PARTITIONS

Many other counters are aggregated by query by default, but can also be aggregated by vertex to match the value for any single vertex. In the Tez View, there are many counters having a suffix of a vertex name. For example, for INPUT_FILES_Map_1, you can set a condition on INPUT_FILES based on the combined value for the entire query. The following counters are vertex-level or query-level counters:

RAW_INPUT_SPLITS
GROUPED_INPUT_SPLITS
INPUT_FILES
INPUT_DIRECTORIES

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;