Query parameters
Preview
This feature is in Public Preview. Contact your Databricks representative to request access.
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.
Add a query parameter
Click the Add New Parameter
button or type Cmd + P.The parameter is inserted at the text caret and the Add Parameter dialog displays.
- 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.
Enter the keyword, optionally override the title, and select the parameter type.
Click Add Parameter.
In the parameter widget, set the parameter value.
Click Apply Changes.
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.
The <Keyword> parameter dialog displays:
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.
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.
Important
Dynamic dates and date ranges aren’t compatible with scheduled queries.
Dropdown List
To restrict the scope of possible parameter values when running a query, you can use the Dropdown List parameter type. An example would be SELECT * FROM users WHERE name='{{ dropdown_param }}'. When selected from the parameter settings panel, a text box appears where you can enter your allowed values, each one separated by a new line. Dropdown lists are Text parameters, so if you want to use dates or dates and times in your Dropdown List, you should enter them in the format your data source requires. The strings are not escaped. You can choose between a single value or multi-value dropdown.
Single value: Single quotes around the parameter are required.
Multi-value: Toggle the Allow multiple values option. In the Quotation drop-down, choose whether or not to wrap the parameters with quotes or use single or double quotes. If you choose quotes, you don’t need to add quotes around the parameter.
In your query, change your WHERE clause to use the IN keyword.
SELECT ...
FROM ...
WHERE field IN ( {{ Multi Select Parameter }} )
The parameter multi-selection widget lets you pass multiple values to the database.
Query Based Dropdown List
Takes the result of a query as its input. It has the same behavior as the Dropdown List parameter.
- Click Query Based Dropdown list under Type in the settings panel.
- 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, SQL Analytics uses the first one. If your target query returns name and value columns, SQL Analytics 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:
When SQL Analytics 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.
You can also access the parameter mapping interface by clicking the 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
. 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 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}}'