Drill Properties

Describes how to use Drill properties and options.

Boot Options

Drill has a flexible system of configuration with a property-source hierarchy. This hierarchy allows Drill to seek further for a property even if a prioritized property source does not have the property.

The following table shows the order of loading Drill properties in a Drillbit. These properties are also known as start-up options or boot options.

Drill searches for a property from top to bottom:
Options source User Modifiable? Description
drill-override.conf Yes HOCON-formatted configuration file stored in the Drill configuration directory.

This file is intended to be populated with user properties.

Java System Properties Yes Java System Properties use java options to set a boot option: -Doption=value.

Java options can be added to the drill-env.sh config to the DRILLBIT_JAVA_OPTS variable. For example:

export DRILLBIT_JAVA_OPTS="$DRILLBIT_JAVA_OPTS -Dproperty=value"

drill-distrib.conf No* HOCON-formatted configuration file stored in the Drill configuration directory.

This file contains distribution-specific options and is populated automatically based on the cluster configuration.

The file contents can change after you run the configure.sh utility.

drill-module.conf No* HOCON-formatted configuration file stored in Drill jars, one per single jar. These configuration files have default values for various Drill modules. It can be a storage plugin jar, UDF jar, Drill execution module, or other module. Developers set the default values.

This configuration file can change from release to release.

drill-default.conf No* HOCON-formatted configuration file stored in drill-common jar. Developers set the default values.

This configuration file can change from release to release.

*Users should not modify the options source.

Drill has a wide list of options. To understand the possible options that can be set, check the drill-override-example.conf file in the <Drill home>/conf directory.

To see all set Drill boot options, you can query the boot system table. For example:
apache drill> select * from sys.boot;
+-----------------------------------------------+---------+------------------+-------------+--------+-----------+----------------------------------------------------------------------------------+----------+-----------+
|                     name                      |  kind   | accessibleScopes | optionScope | status |  num_val  |                                    string_val                                    | bool_val | float_val |
+-----------------------------------------------+---------+------------------+-------------+--------+-----------+----------------------------------------------------------------------------------+----------+-----------+
| awt.toolkit                                   | STRING  | BOOT             | BOOT        | BOOT   | null      | "sun.awt.X11.XToolkit"                                                           | null     | null      |
| com.sun.management.jmxremote.authenticate     | STRING  | BOOT             | BOOT        | BOOT   | null      | "false"                                                                          | null     | null      |
| drill.classpath.scanning.cache.enabled        | BOOLEAN | BOOT             | BOOT        | BOOT   | null      | null                                                                             | true     | null      |
| drill.client.supports-complex-types           | BOOLEAN | BOOT             | BOOT        | BOOT   | null      | null                                                                             | true     | null      |
| drill.customAuthFactories                     | STRING  | BOOT             | BOOT        | BOOT   | null      | "org.apache.drill.exec.rpc.security.maprsasl.MapRSaslFactory"                    | null     | null      |
| drill.exec.allow_loopback_address_binding     | BOOLEAN | BOOT             | BOOT        | BOOT   | null      | null                                                                             | false    | null      |
...

Configuration Options

Drill configuration options are boot options that have the drill.exec.options.* prefix. The main difference between configuration options and boot options is that configuration options can be modified at runtime. Configuration options are used to tune performance or change functionality.

Configuration options have two additional layers of properties source on top of boot options:
Options source Description
Session level Options modified during runtime and stored in Drill memory until the user session ends.

Not all Drill configuration options can be set at the session level. Check the accessibleScopes of the option in the system table options.

System level Options modified during runtime, shared across all the Drill cluster, and persisted between restart of the Drillbits. System-level options are stored in persistent storage. By default, ZooKeeper stores the options.

System-level options can only be set by admins. System-level options are bonded to the Drill cluster ID and preserved even after Drill is uninstalled from a node.

Configuration options are divided into two types: internal and public. Internal options are intended to be used by Drill developers and are not expected to be used in production.
You can find the complete list of internal configuration options in the internal_options system table. For example:
apache drill> select * from sys.internal_options limit 10;
+--------------------------------------------------+---------+--------------------+-------+---------+-------------+----------------------------------------------------------------------------------+
|                       name                       |  kind   |  accessibleScopes  |  val  | status  | optionScope |                                   description                                    |
+--------------------------------------------------+---------+--------------------+-------+---------+-------------+----------------------------------------------------------------------------------+
| drill.exec.hashjoin.mem_limit                    | BIGINT  | SYSTEM             | 0     | DEFAULT | BOOT        | Enforces the maximum memory limit for the Hash Join operator (if non-zero); used for testing purposes. Defa... |
| drill.exec.rpc.fragrunner.timeout                | BIGINT  | SYSTEM             | 10000 | DEFAULT | BOOT        |                                                                                  |
| drill.exec.stats.logging.batch_size              | BIT     | SYSTEM_AND_SESSION | false | DEFAULT | BOOT        | Enables batch size statistics logging.                                           |
| drill.exec.stats.logging.enabled_operators       | VARCHAR | SYSTEM_AND_SESSION | all   | DEFAULT | BOOT        | Controls the list of operators for which batch sizing statistics should be enabled. |
| drill.exec.stats.logging.fine_grained.batch_size | BIT     | SYSTEM_AND_SESSION | false | DEFAULT | BOOT        | Enables fine-grained batch size statistics logging.                              |
...
You can find the complete list of public configuration options in the options system table or in the Drill Web UI. For example:
apache drill> select * from sys.options;
+------------------------------------------------+------+------------------+-------+---------+-------------+----------------------------------------------------------------------------------+
|                      name                      | kind | accessibleScopes |  val  | status  | optionScope |                                   description                                    |
+------------------------------------------------+------+------------------+-------+---------+-------------+----------------------------------------------------------------------------------+
| debug.validate_iterators                       | BIT  | ALL              | false | DEFAULT | BOOT        |                                                                                  |
| debug.validate_vectors                         | BIT  | ALL              | false | DEFAULT | BOOT        |                                                                                  |
| drill.exec.functions.cast_empty_string_to_null | BIT  | ALL              | false | DEFAULT | BOOT        | In a text file, treat empty fields as NULL values instead of empty string.       |
| drill.exec.hashagg.fallback.enabled            | BIT  | ALL              | false | DEFAULT | BOOT        | Hash Aggregates ignore memory limits when enabled (true). When disabled (false), Hash Aggregates fail when ... |
| drill.exec.hashjoin.fallback.enabled           | BIT  | ALL              | false | DEFAULT | BOOT        | Hash Joins ignore memory limits when this option is enabled (true). When disabled (false), Hash Joins fail ... |
...
IMPORTANT
If a Drill option has the DEFAULT status, it means the value is set in boot options and was not overridden in the system or session level.

Setting the Configuration Option in the Boot Scope

Be careful if you want to set a configuration option in the boot options (BOOT scope). To set the option in boot options, you need to add the drill.exec.options suffix to the name of the configuration option you see in the name column of the options system table. For example:
alter system set 'planner.enable_nljoin_for_scalar_only' = false;
But use the following entry in the drill-override.conf file:
drill.exec.options.planner.enable_nljoin_for_scalar_only=false

Setting System Options

You can set system options by using the SQL alter system command in the Drill Web UI or by using the REST API. For example:
apache drill> alter system set 'planner.enable_nljoin_for_scalar_only' = false;
+------+------------------------------------------------+
|  ok  |                    summary                     |
+------+------------------------------------------------+
| true | planner.enable_nljoin_for_scalar_only updated. |
+------+------------------------------------------------+

Setting Session Options

You can set session options by using the SQL alter session command. The syntax is similar to alter system. For example:
apache drill> alter session set 'store.parquet.flat.reader.bulk' = false;
+------+-----------------------------------------+
|  ok  |                 summary                 |
+------+-----------------------------------------+
| true | store.parquet.flat.reader.bulk updated. |
+------+-----------------------------------------+

Starting from EEP 9.3.0, you can set the session option by adding the option to either the JDBC connection string or JDBC connection properties.

To add a session option to the JDBC connection string, add a parameter in the format session.option.name=value separated with a semicolon. For example:
jdbc:drill:drillbit=localhost;auth=maprsasl;planner.enable_join_optimization=false
To add a session option to the JDBC Connection properties, put the option to the properties, using the option name as a key, and a String value as a value. Here is an example code snippet:
String connectionString = "jdbc:drill:zk=node1.cluster.com:5181/drill/drill-cluster.com;auth=maprsasl;";
Properties connectionProperties = new Properties();
connectionProperties.put("planner.enable_join_optimization", "false");
Connection connection = DriverManager.getConnection(connectionString, connectionProperties);