SET Statement

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

In Impala 2.5 and higher, SET also defines user-specified substitution variables for the impala-shell interpreter. This feature uses the SET command built into impala-shell instead of the SQL SET statement. Therefore the substitution mechanism only works with queries processed by impala-shell, not with queries submitted through JDBC or ODBC.

Important:

In Impala 2.11 and higher, the output of the SET statement changes in some important ways:

  • The options are divided into groups: Regular Query Options, Advanced Query Options, Development Query Options, and Deprecated Query Options.

  • The advanced options are intended for use in specific kinds of performance tuning and debugging scenarios. The development options are related to internal development of Impala or features that are not yet finalized; these options might be changed or removed without notice. The deprecated options are related to features that are removed or changed so that the options no longer have any purpose; these options might be removed in future versions.

  • By default, only the first two groups (regular and advanced) are displayed by the SET command. Use the syntax SET ALL to see all groups of options.

  • impala-shell options and user-specified variables are always displayed at the end of the list of query options, after all appropriate option groups.

  • When the SET command is run through the JDBC or ODBC interfaces, the result set has a new third column, level, indicating which group each option belongs to. The same distinction of SET returning the regular and advanced options, and SET ALL returning all option groups, applies to JDBC and ODBC also.

Syntax:

  1. SET [query_option=option_value]
  2. SET ALL

SET and SET ALL with no arguments return a result set consisting of all the applicable 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.

Setting query options during impala-shell invocation:

In Impala 2.11 and higher, you can use one or more command-line options of the form --query_option=option=value when running the impala-shell command. The corresponding query option settings take effect for that impala-shell session.

User-specified substitution variables:

In Impala 2.5 and higher, you can specify your own names and string substitution values within the impala-shell interpreter. Once a substitution variable is set up, its value is inserted into any SQL statement in that same impala-shell session that contains the notation ${var:varname}. Using SET in an interactive impala-shell session overrides any value for that same variable passed in through the --var=varname=value command-line option.

For example, to set up some default parameters for report queries, but then override those default within an impala-shell session, you might issue commands and statements such as the following:

  2. -- Initial setup for this example.
  3. create table staging_table (s string);
  4. insert into staging_table values ('foo'), ('bar'), ('bletch');
  6. create table production_table (s string);
  7. insert into production_table values ('North America'), ('EMEA'), ('Asia');
  8. quit;
  10. -- Start impala-shell with user-specified substitution variables,
  11. -- run a query, then override the variables with SET and run the query again.
  12. $ impala-shell --var=table_name=staging_table --var=cutoff=2
  13. ... banner message ...
  14. [localhost:21000] > select s from ${var:table_name} order by s limit ${var:cutoff};
  15. Query: select s from staging_table order by s limit 2
  16. +--------+
  17. | s      |
  18. +--------+
  19. | bar    |
  20. | bletch |
  21. +--------+
  22. Fetched 2 row(s) in 1.06s
  24. [localhost:21000] > set var:table_name=production_table;
  25. Variable TABLE_NAME set to production_table
  26. [localhost:21000] > set var:cutoff=3;
  27. Variable CUTOFF set to 3
  29. [localhost:21000] > select s from ${var:table_name} order by s limit ${var:cutoff};
  30. Query: select s from production_table order by s limit 3
  31. +---------------+
  32. | s             |
  33. +---------------+
  34. | Asia          |
  35. | EMEA          |
  36. | North America |
  37. +---------------+

The following example shows how SET ALL with no parameters displays all user-specified substitution variables, and how UNSET removes the substitution variable entirely:

  2. [localhost:21000] > set all;
  3. Query options (defaults shown in []):
  4. ABORT_ON_ERROR: [0]
  5. COMPRESSION_CODEC: []
  6. DISABLE_CODEGEN: [0]
  7. ...
  9. Advanced Query Options:
  10. APPX_COUNT_DISTINCT: [0]
  11. BUFFER_POOL_LIMIT: []
  12. DEFAULT_JOIN_DISTRIBUTION_MODE: [0]
  13. ...
  15. Development Query Options:
  16. BATCH_SIZE: [0]
  17. DEBUG_ACTION: []
  18. DECIMAL_V2: [0]
  19. ...
  21. Deprecated Query Options:
  22. ABORT_ON_DEFAULT_LIMIT_EXCEEDED: [0]
  23. ALLOW_UNSUPPORTED_FORMATS: [0]
  24. DEFAULT_ORDER_BY_LIMIT: [-1]
  25. ...
  27. Shell Options
  28.   LIVE_PROGRESS: False
  29.   LIVE_SUMMARY: False
  31. Variables:
  32.   CUTOFF: 3
  33.   TABLE_NAME: staging_table
  35. [localhost:21000] > unset var:cutoff;
  36. Unsetting variable CUTOFF
  37. [localhost:21000] > select s from ${var:table_name} order by s limit ${var:cutoff};
  38. Error: Unknown variable CUTOFF

See Running Commands and SQL Statements in impala-shell for more examples of using the --var, SET, and ${var:varname} substitution technique in impala-shell.

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:

Examples:

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:

  1. set mem_limit=64g;
  2. set DISABLE_UNSAFE_SPILLS=true;
  3. set parquet_file_size=400m;
  4. set RESERVATION_REQUEST_TIMEOUT=900000;
  5. insert overwrite parquet_table select c1, c2, count(c3) from text_table group by c1, c2, c3;

Added in: 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.

Parent topic: Impala SQL Statements