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.

Add a query parameter

1. Type Cmd + I. 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, type double curly braces {{ }} and click 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.

Remove a query parameter

To remove a query parameter, delete the parameter from your query. The parameter widget disappears, and you can rewrite your query using static values.

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
• Number
• Dropdown List
• Query-Based Dropdown List
• Date and Time

Text

Takes a string as input. Backslash, single, and double quotation marks are escaped, and Azure 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 }}

Dropdown List

To restrict the scope of possible parameter values when running a query, 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 enter your allowed values, each value separated by a new line. Dropdown lists are text parameters. To use dates or dates and times in your Dropdown List, 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 quotation marks around the parameter are required.
• Multi-value: Toggle the Allow multiple values option. In the Quotation drop-down, choose whether to leave the parameters as entered (no quotation marks) or wrap the parameters with single or double quotation marks. You don't need to add quotation marks around the parameter if you choose quotation marks.

Change your WHERE clause to use the IN keyword in your query.

SELECT ...
FROM   ...
WHERE field IN ( {{ Multi Select Parameter }} )

The parameter multi-selection widget lets you pass multiple values to the database. If you select the Double Quotation Mark option for the Quotation parameter, your query reflects the following format: WHERE IN ("value1", "value2", "value3")

Query-Based Dropdown List

Takes the result of a query as its input. It has the same behavior as the Dropdown List parameter. You must save the Databricks SQL dropdown list query to use it as an input in another query.

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, the 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 the following query returns the data in the table.

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

value	name
1001	John Smith
1002	Jane Doe
1003	Bobby Tables

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

Date and Time

Azure Databricks has several options to parameterize date and timestamp values, including options to simplify the parameterization of time ranges. 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; Azure Databricks requires that you wrap date and time values in single quotation marks ('). 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.

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.

Using query parameters in dashboards

Optionally, queries can use parameters or static values. When a visualization based on a parameterized query is added to a dashboard, the visualization can be configured to use either a:

• Widget parameter

  Widget parameters are specific to a single visualization in a dashboard, appear in the visualization panel, and the parameter values specified apply only to the query underlying the visualization.

• Dashboard parameter

  Dashboard parameters can apply to multiple visualizations. When you add a visualization based on a parameterized query to a dashboard, the parameter will be added as a dashboard parameter by default. Dashboard parameters are configured for one or more visualizations in a dashboard and appear at the top of the dashboard. The parameter values specified for a dashboard parameter apply to visualizations reusing that particular dashboard parameter. A dashboard can have multiple parameters, each of which can apply to some visualizations and not others.

• Static value

  Static values are used in place of a parameter that responds to changes. Static values allow you to hard code a value in place of a parameter and will make the parameter "disappear" from the dashboard or widget where it previously appeared.

When you add a visualization containing a parameterized query, you can choose the the title and the source for the parameter in the visualization query by clicking the appropriate pencil icon. You can also select the keyword and a default value. See Parameter properties.

After adding a visualization to a dashboard, access the parameter mapping interface by clicking the kebab menu on the upper-right of a dashboard widget and then clicking Change widget settings.

Parameter properties

• 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, 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 the expected results.

• Default Value: The value used if no other value is specified. To change this from the query screen, run 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?
• Can I use multiple parameters in a single query?

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