SET

Starting in Drill 1.3, the SET command replaces the ALTER SESSION SET command. The SET command changes a system setting for the duration of a session. Session level settings override system level settings.

Syntax

The SET command supports the following syntax:

[ALTER SESSION] SET `option_name` = value;

Parameters

option_name This is the option name as it appears in the options table that is stored in the sys database (schema).

value A value of the type listed in the val column of the sys.options table: number, string, boolean, or float. Use the appropriate value type for each option that you set.

Usage Notes

• Starting in Drill 1.16, Drill no longer writes the profiles for SET queries to the persistent store. When you run the SET command, you will not see the activity on the Profiles page of the Drill Web UI. If you want to see query profiles for the SET command, set the exec.query_profile.alter_session.skip option to false from Drill Web UI or command line, as shown:

    //At the session level
    set `exec.query_profile.alter_session.skip` = false;
    //At the system level
    alter system set `exec.query_profile.alter_session.skip` = false;
  

• By default, Drill returns a result set when you issue DDL statements, such as SET. If the client tool from which you connect to Drill (via JDBC) does not expect a result set when you issue DDL statements, set the exec.query.return_result_set_for_ddl option to false, as shown, to prevent the client from canceling queries:

    SET `exec.query.return_result_set_for_ddl` = false
    //This option is available in Drill 1.15 and later.
  

  When set to false, Drill returns the affected rows count, and the result set is null.

• You can still use the ALTER SESSION SET command; however, ALTER SESSION is now just an alias for the SET command.
• Use the SET command to change Drill query planning and execution options per session in a cluster.
• The options that you set only apply to queries that run during the current Drill connection.
• A session ends when you quit the Drill shell.
• You can set any of the system level options at the session level.
• Use the RESET command to change the value of an option back to the default system setting.

The following query returns the planning and execution options that are currently set at the system level:

SELECT * FROM sys.options WHERE optionScope IN ('SYSTEM') ORDER BY name;
|-------------------------------------------|---------|-------------------|-------------|----------|--------------|----------------------------------------------------------------------------------|
|                   name                    |  kind   | accessibleScopes  |     val     |  status  | optionScope  |                                   description                                    |
|-------------------------------------------|---------|-------------------|-------------|----------|--------------|----------------------------------------------------------------------------------|
| debug.validate_iterators                  | BIT     | ALL               | true        | CHANGED  | SYSTEM       |                                                                                  |
| exec.query.progress.update                | BIT     | ALL               | false       | CHANGED  | SYSTEM       |                                                                                  |
| planner.memory.max_query_memory_per_node  | BIGINT  | ALL               | 2147483750  | CHANGED  | SYSTEM       | Sets the maximum amount of direct memory allocated to the Sort and Hash Aggregate operators during each que... |
|-------------------------------------------|---------|-------------------|-------------|----------|--------------|----------------------------------------------------------------------------------|

Examples

This example demonstrates how to use the SET command to set the store.json.all_text_mode option to “true” for the current Drill session. Setting this option to “true” enables text mode so that Drill reads everything in JSON as a text object instead of trying to interpret data types.

   SET `store.json.all_text_mode`= true;
   |-------|------------------------------------|
   |  ok   |              summary               |
   |-------|------------------------------------|
   | true  | store.json.all_text_mode updated.  |
   |-------|------------------------------------|

Note: Drill reads complicated JSON using CASE and CAST.

The following query returns the planning and execution options currently set at the session level after changing the value of the debug.validate_iterators option to “false”:

|-------|------------------------------------|
|  ok   |              summary               |
|-------|------------------------------------|
| true  | debug.validate_iterators updated.  |
|-------|------------------------------------|

|---------------------------|-------|-------------------|--------|----------|--------------|--------------|
|           name            | kind  | accessibleScopes  |  val   |  status  | optionScope  | description  |
|---------------------------|-------|-------------------|--------|----------|--------------|--------------|
| debug.validate_iterators  | BIT   | ALL               | false  | DEFAULT  | SESSION      |              |
|---------------------------|-------|-------------------|--------|----------|--------------|--------------|