Configuration parameters

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 SQL configuration parameters or 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.

FALSE

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