Configuration parameters

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

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.