Skip to main content

Legacy notebook widgets: ${param}

Deprecated

The ${param} syntax for accessing widget values has been deprecated in Databricks Runtime 15.2 and above. Use the current Databricks widgets syntax, (:param) instead.

This page shows you how to use the legacy ${param} syntax for notebooks widgets running on Databricks Runtime 15.1 and below. Databricks recommends you migrate to the current syntax.

Use widget values in Databricks Runtime 15.1 and below​

This section describes how to pass Databricks widgets values to %sql notebook cells in Databricks Runtime 15.1 and below.

  1. Create widgets to specify text values.
Python
dbutils.widgets.text("database", "")
dbutils.widgets.text("table", "")
dbutils.widgets.text("filter_value", "100")

  1. Pass in the widget values using the ${param} syntax.

    SQL
    SELECT *
    FROM ${database}.${table}
    WHERE col == ${filter_value}
    LIMIT 100
note

To escape the $ character in a SQL string literal, use \$. For example, to express the string $1,000, use "\$1,000". The $ character cannot be escaped for SQL identifiers.

Migrate to parameter markers​

The following table shows common use cases for parameters, the original Databricks widget syntax (deprecated in Databricks Runtime 15.2 and above), and the equivalent syntax using named parameter marker syntax (supported in Databricks Runtime 15.2 and above).

Parameter use case

${param} original widget syntax (deprecated as of Databricks Runtime 15.2 and above)

:param parameter marker syntax (supported as of Databricks Runtime 15.2 and above)

Load only data before a specified date

WHERE date_field < '${date_param}'

You must include quotes around the date parameter and curly brackets.

WHERE date_field < :date_param

Load only data less than a specified numeric value

WHERE price < ${max_price}

WHERE price < :max_price

Compare two strings

WHERE region = ${region_param}

WHERE region = :region_param

Specify the table used in a query

SELECT * FROM ${table_name}

SELECT * FROM IDENTIFIER(:table)

When a user enters this parameter, they should use the full three-level namespace to identify the table.

Independently specify the catalog, schema, and table used in a query

SELECT * FROM ${catalog}.${schema}.${table}

SELECT * FROM IDENTIFIER(:catalog || '.' || :schema || '.' || :table)

Use parameters as a template in a longer, formatted string

"(${area_code}) ${phone_number}"

Parameter values are automatically concatenated as a string.

format_string((%d) %d, :area_code, :phone_number)

See Concatenate multiple parameters for a complete example.

Create an interval

SELECT INTERVAL ${p} MINUTE

SELECT CAST(:param as INTERVAL MINUTE)

Filter by a list of possible values

SELECT * from table WHERE value IN (${list_parameter})

SELECT * FROM samples.nyctaxi.trips WHERE array_contains(TRANSFORM(SPLIT(:list_parameter, ','), s -> TRIM(s)), dropoff_zip)

Last updated on