Share via

Legacy notebook widgets: ${param}

:::warning 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")

Scala

 dbutils.widgets.text("database", "")
 dbutils.widgets.text("table", "")
 dbutils.widgets.text("filter_value", "100")

R

dbutils.widgets.text("database", "")
dbutils.widgets.text("table", "")
dbutils.widgets.text("filter_value", "100")

SQL

CREATE WIDGET TEXT database DEFAULT ""
CREATE WIDGET TEXT table DEFAULT ""
CREATE WIDGET TEXT filter_value DEFAULT "100"

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

    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 Azure 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)