Query parameters

A query parameter lets you substitute values into a query at runtime. Any string between double curly braces {{ }} is treated as a query parameter. A widget appears above the results pane where you set the parameter value. Query parameters are more flexible than query filters and should only be used in cases where query filters are not sufficient.

Query parameter

Add a query parameter

  1. Type Cmd + P. The parameter is inserted at the text caret and the Add Parameter dialog appears.

    • Keyword: The keyword that represents the parameter in the query.

    • Title: The title that appears over the widget. By default the title is the same as the keyword.

    • Type: Supported types are Text, Number, Date, Date and Time, Date and Time (with Seconds), Dropdown List, and Query Based Dropdown List. The default is Text.

  2. Enter the keyword, optionally override the title, and select the parameter type.

  3. Click Add Parameter.

  4. In the parameter widget, set the parameter value.

  5. Click Apply Changes.

  6. Click Save.

Alternatively, you can also manually type double curly braces {{ }} and click on the gear icon near the parameter widget to edit the settings.

To re-run the query with a different parameter value, enter the value in the widget and click Apply Changes.

Edit a query parameter

To edit a parameter, click the gear icon beside the parameter widget. To prevent users who don’t own the query from changing the parameter, click Show Results Only.

The <Keyword> parameter dialog appears:

Parameter settings

Change the order of parameters

To change the order in which parameters are shown, you can click and drag each parameter to the desired position.

Query parameter types

Text

Takes a string as input. Backslash, single quote, and double quote are escaped, and Databricks adds quotation marks to this parameter. For example, a string like mr's Li"s is transformed to 'mr\'s Li\"s' An example of using this could be

SELECT * FROM users WHERE name={{ text_param }}

Number

Takes a number as its input. An example of using this could be

SELECT * FROM users WHERE age={{ number_param }}

Query Based Dropdown List

Takes the result of a query as its input. It has the same behavior as the Dropdown List parameter.

  1. Click Query Based Dropdown list under Type in the settings panel.

  2. Click the Query field and select a query. If your target query returns a large number of records performance will degrade.

If your target query returns more than one column, Databricks SQL uses the first one. If your target query returns name and value columns, Databricks SQL populates the parameter selection widget with the name column but executes the query with the associated value.

For example, suppose this query:

SELECT user_uuid AS 'value', username AS 'name'
FROM users

returns this data:

value

name

1001

John Smith

1002

Jane Doe

1003

Bobby Tables

The Dropdown List widget would look like:

John Smith, Jane Doe and Bobby Tables

When Databricks SQL executes the query, the value passed to the database would be 1001, 1002, or 1003.

Date and Time

Databricks SQL provides a number of options to parameterize date and timestamp values, including options to simplify parameterization of time ranges. You can select from three options of varying precision:

Option

Precision

Type

Date

day

DATE

Date and Time

minute

TIMESTAMP

Date and Time (with seconds)

second

TIMESTAMP

When choosing a Range parameter option, you create two parameters designated by .start and .end suffixes. All options pass parameters to your query as string literals; Databricks SQL requires that you wrap date and time values in single quotes ('). For example:

-- Date parameter
SELECT *
FROM usage_logs
WHERE date = '{{ date_param }}'

-- Date and Time Range parameter
SELECT *
FROM usage_logs
WHERE modified_time > '{{ date_range.start }}' and modified_time < '{{ date_range.end }}'

Date parameters use a calendar picking interface and default to the current date and time.

Note

The Date Range parameter only returns correct results for columns of DATE type. For TIMESTAMP columns, use one of the Date and Time Range options.

Dynamic date and date range values

When you add a date or date range parameter to your query, the selection widget shows a blue lightning bolt icon. Click it to display dynamic values like today, yesterday, this week, last week, last month, or last year. These values update dynamically.

Dynamic dates

Important

Dynamic dates and date ranges aren’t compatible with scheduled queries.

Query parameter mapping in dashboards

You can control query parameters within dashboards. You can link together parameters in different widgets, set static parameter values, or choose values individually for each widget.

You select parameter mappings when adding dashboard widgets that depend on a parameter value. Each parameter in the underlying query appears in the Parameters list.

Parameter mapping

You can also access the parameter mapping interface by clicking the vertical ellipsis Vertical Ellipsis on the top right of a dashboard widget then clicking Edit Widget. The parameter properties display:

  • Title: The display name that appears beside the value selector on your dashboard. It defaults to the parameter Keyword. To edit it, click the pencil icon Pencil Icon. Titles are not displayed for static dashboard parameters because the value selector is hidden. If you select Static value as your Value Source then the Title field is grayed out.

  • Keyword: The string literal for this parameter in the underlying query. This is useful for debugging if your dashboard does not return expected results.

  • Default Value: The value used if no other value is specified. To change this from the query screen, execute the query with your desired parameter value and click the Save button.

  • Value Source: The source of the parameter value. Click the pencil icon Pencil Icon to choose a source.

    • New dashboard parameter: Create a new dashboard-level parameter. This lets you set a parameter value in one place on your dashboard and map it to multiple visualizations.

    • Existing dashboard parameter: Map parameter to an existing dashboard parameter. You must specify which pre-existing dashboard parameter.

    • Widget parameter: Displays a value selector inside your dashboard widget. This is useful for one-off parameters that are not shared between widgets.

    • Static value: Choose a static value for the widget, regardless of the values used on other widgets. Statically mapped parameter values do not display a value selector anywhere on the dashboard which is more compact. This lets you take advantage of the flexibility of query parameters without cluttering the user interface on a dashboard when certain parameters are not expected to change frequently.

Frequently asked questions (FAQ)

Can I reuse the same parameter multiple times in a single query?

Yes. Use the same identifier in the curly brackets. This example uses the {{org_id}} parameter twice.

SELECT {{org_id}}, count(0)
FROM queries
WHERE org_id = {{org_id}}

Can I use multiple parameters in a single query?

Yes. Use a unique name for each parameter. This example uses two parameters: {{org_id}} and {{start_date}}.

SELECT count(0)
FROM queries
WHERE org_id = {{org_id}} AND created_at > '{{start_date}}'