Configuration parameters

Note

This feature is available in the preview channel.

A configuration parameter is a setting which affects the behavior of Databricks SQL outside of the specified SQL syntax.

The effective value of a configuration parameter is derived from the different levels where it is set.

Configuration parameter scopes

  • System

    The system value is the default value used by Databricks SQL if there is no override.

  • Global

    An administrator can override the system default value for a parameter using the Global SQL Endpoints API. This value is then the initial value seen by any newly established session moving forward.

  • Session

    A user can override a configuration parameter temporarily within a session scope by using the SET statement. The setting remains in effect for the duration of the session unless the user overrides is again with a SET statement or RESET.

Supported configuration parameters

Here is a list of of the configuration parameters that you can set in Databricks SQL, their system defaults, and at which levels they can be set:

Parameter name Description System default Globally settable Session settable
ANSI_MODE Allows you to disable strict ANSI SQL behavior for certain functions and casting rules. TRUE Yes Yes
ENABLE_PHOTON Controls the usage of the Photon vectorized query engine. TRUE No Yes
LEGACY_TIME_PARSER_POLICY Controls how date and timestamps are parsed and formatted. CORRECTED Yes Yes
MAX_PARTITION_BYTES The maximum number of bytes to pack into a single partition when reading from file based sources. 128m No Yes
READ_ONLY_EXTERNAL_METASTORE Controls whether the an external metastore is treated as read-only. FALSE Yes No
TIMEZONE Sets the local time zone. UTC Yes Yes
USE_CACHED_RESULT Controls whether Databricks SQL caches and reuses results whenever possible. TRUE No Yes