SET Statement

Specifies values for query options that control the runtime behavior of other statements within the same session.


SET [query_option=option_value]

SET with no arguments returns a result set consisting of all available query options and their current values.

The query option name and any string argument values are case-insensitive.

Each query option has a specific allowed notation for its arguments. Boolean options can be enabled and disabled by assigning values of either true and false, or 1 and 0. Some numeric options accept a final character signifying the unit, such as 2g for 2 gigabytes or 100m for 100 megabytes. See Query Options for the SET Statement for the details of each query option.

Usage notes:

MEM_LIMIT is probably the most commonly used query option. You can specify a high value to allow a resource-intensive query to complete. For testing how queries would work on memory-constrained systems, you might specify an artificially low value.

Complex type considerations:


The following example sets some numeric and some Boolean query options to control usage of memory, disk space, and timeout periods, then runs a query whose success could depend on the options in effect:

set mem_limit=64g;
set parquet_file_size=400m;
insert overwrite parquet_table select c1, c2, count(c3) from text_table group by c1, c2, c3;

Added in: CDH 5.2.0 (Impala 2.0.0)

SET has always been available as an impala-shell command. Promoting it to a SQL statement lets you use this feature in client applications through the JDBC and ODBC APIs.

Cancellation: Cannot be cancelled.

HDFS permissions: This statement does not touch any HDFS files or directories, therefore no HDFS permissions are required.

Related information:

See Query Options for the SET Statement for the query options you can adjust using this statement.