Nov 2, 2018

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


The SET command supports the following syntax:

[ALTER SESSION] SET `option_name` = value;    


This is the option name as it appears in the systems table.

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

Usage Notes

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. You can use the RESET command to change the value of an option back to the default system setting.

You can run the following query to see a complete list of planning and execution options that are currently set at the system or session level:

0: jdbc:drill:zk=local> SELECT name, type FROM sys.options WHERE type in ('SYSTEM','SESSION') order by name;
|   name                                         |    type    |
| drill.exec.functions.cast_empty_string_to_null | SYSTEM     |
| | SYSTEM     |
| exec.errors.verbose                            | SYSTEM     |
| exec.java_compiler                             | SYSTEM     |
| exec.java_compiler_debug                       | SYSTEM     |


This is a truncated version of the list.


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. This allows complicated JSON to be read using CASE and CAST.

   0: jdbc:drill:zk=local> SET `store.json.all_text_mode`= true;
   |  ok   |              summary               |
   | true  | store.json.all_text_mode updated.  |

You can issue a query to see options set at the session level. Note that the option type is case-sensitive.

   0: jdbc:drill:zk=local> SELECT name, type, bool_val FROM sys.options WHERE type = 'SESSION' order by name; 
   |           name            |   type   | bool_val  |
   | store.json.all_text_mode  | SESSION  | true      |