SET

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Sets a Databricks parameter at the session level, returns the value of an existing parameter or returns all parameters with value and meaning. When using Databricks Runtime, parameters are known as SQL Conf properties.

To set a SQL variable use SET VARIABLE.

Syntax

SET
SET [ -v ]
SET parameter_key [ = parameter_value ]

Parameters

  • (none)

    Applies to: check marked yes Databricks SQL

    Outputs the key and value of changed Databricks parameters.

  • -v

    Outputs the key, value and meaning of existing parameters.

  • parameter_key

    Returns the value of the specified parameter.

  • parameter_key = parameter_value

    Sets the value for a given parameter. If an old value exists for a given parameter, then it gets overridden by the new value.

Databricks SQL Examples

-- Set a property.
> SET ansi_mode = true;

-- List all configuration parameters with their value and description.
> SET -v;

-- List all configuration parameters with a set value for the current session.
> SET;

-- List the value of specified property key.
> SET ansi_mode;
  key        value
  ---------  -----
  ansi_mode  true

-- Use SET VARIABLE to set SQL variables
> DECLARE var INT;
> SET var = 5;
Error: UNSUPPORTED_FEATURE.SET_VARIABLE_USING_SET
> SET VAR var = 5;
> SELECT var;
  5

Databricks Runtime Examples

-- Set a property.
> SET spark.sql.variable.substitute=false;

-- List all SQLConf properties with value and meaning.
> SET -v;

-- List all SQLConf properties with value for current session.
> SET;

-- List the value of specified property key.
> SET spark.sql.variable.substitute;
                           key value
 ----------------------------- -----
 spark.sql.variable.substitute false