Configuration parameters

  • Article

Applies to: check marked yes Databricks SQL

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 Configure SQL parameters or SQL Warehouse 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

The following is a list of the supported configuration parameters that you can set in Databricks SQL, their system defaults, and at which levels they can be set. Databricks SQL does not support setting Spark configurations that aren’t listed.

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. EXCEPTION Yes Yes
MAX_FILE_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 an external metastore is treated as read-only. FALSE Yes No
STATEMENT_TIMEOUT Sets a SQL statement timeout in seconds 172800 seconds Yes Yes
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

The ANSI_MODE system default value is FALSE for accounts added before Databricks SQL 2022.35.