Query parameters

Preview

This feature is in Public Preview.

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 parameter

Add a query parameter

  1. Click the Add New Parameter New Parameter Button button or type Cmd + P.

    The parameter is inserted at the text caret and the Add Parameter dialog displays.

    New Parameter Dialog
    • 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.

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.

Query parameter

The <Keyword> parameter dialog displays:

Parameter settings

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 }}

Date, Date and Time, Date and Time (with seconds)

These three parameters are very similar. The only difference is their precision. They take either a specific time (12/11/2020 12:01) or a string representing time (Today, Yesterday). An example of this parameter would be SELECT * from usage_logs where date='{{ date_param }}'. You must add quote marks to parameter.

Date parameters use a familiar calendar picking interface and can default to the current date and time. You can chose from three levels of precision: Date, Date and Time, and Date and Time with seconds.

Note

Date parameters are passed to your database as strings. You must wrap them in single quotes (') or whatever your database requires to declare strings.

DateRange, Date and Time Range, Date and Time Range (with seconds)

Date range parameters take either a start date and an end date (12/09/2020 12:01 - 12/11/2020 13:01) or a string representing time (Last week, Last month). These parameters insert two markers called .start and .end which signify the beginning and end of your chosen date range. An example of this parameter would be

SELECT year(birthDate) as birthYear, count(*) AS total
FROM default.people10m
WHERE firstName = {{ Name }} AND gender = 'F' and birthDate > '{{ Date Range.start }}' and birthDate < '{{ Date Range.end }}'
GROUP BY birthYear
ORDER BY birthYear

You must add quote marks to the parameter.

Date range parameters use a combined widget to simplify range selection.

Date range parameter

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 last month, yesterday, or last year. These values update dynamically every day.

Dynamic dates

Important

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

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.

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 Parameters. 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}}'