CREATE TRIGGER

You conditionally create a trigger in a resource plan.

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;