Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://docs.databricks.com/aws/en/sql/user/queries/query-parameters below:

Work with query parameters | Databricks Documentation

This article explains how to work with query parameters in the Databricks SQL editor.

Query parameters allow you to make your queries more dynamic and flexible by inserting variable values at runtime. Instead of hard-coding specific values into your queries, you can define parameters to filter data or modify output based on user input. This approach improves query reuse, enhances security by preventing SQL injection, and enables more efficient handling of diverse data scenarios.

Named parameter markers are typed placeholder variables. Use this syntax to write queries in the following parts of the Databricks UI:

• SQL editor (new and legacy)
• Notebooks
• AI/BI dashboard dataset editor
• AI/BI Genie spaces

note

The AI/BI dashboard dataset editor and the new SQL editor support the following data types with named parameter syntax: numeric, date, and date and time parameters. In all other UI surfaces, only string-type parameters are supported using named parameter syntax. To use other parameter types outside these editors, use mustache parameters.

Insert parameters into your SQL queries by typing a colon followed by a parameter name, such as :parameter_name. When you include a named parameter marker in a query, a widget appears in the UI. You can use the widget to edit the parameter type and name.

This example adds a parameter marker to the following query:

SQL

SELECT
  trip_distance,
  fare_amount
FROM
  samples.nyctaxi.trips
WHERE
  fare_amount < 5

This query returns a dataset that includes only fare amounts that are under five dollars. Use the following steps to edit the query to use a parameter instead of the hard-coded value (5).

1. Delete the number 5 from the query.
2. Type a colon (:) followed by the string fare_parameter. The last line of your updated query should say fare_amount < :fare_parameter.
3. Click the gear icon near the parameter widget. The dialog shows the following fields:
   • Keyword: The keyword that represents the parameter in the query. You cannot edit this field. To change the keyword, edit the marker in the SQL 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, Dropdown List, Date, Date and Time, and Date and Time (with Seconds). The default is Text.
4. In the dialog, change the Type to Number.
5. Enter a number into the parameter widget and click Apply changes.
6. Click Save to save the query.

The following examples demonstrate some common use cases for parameters.

The following example includes a Date parameter that limits query results to records after a specific date.

SQL

SELECT
  o_orderdate AS Date,
  o_orderpriority AS Priority,
  sum(o_totalprice) AS `Total Price`
FROM
  samples.tpch.orders
WHERE
  o_orderdate > :date_param
GROUP BY
  1,
  2

The following example includes a Number parameter that limits results to records where the o_total_price field is greater than the provided parameter value.

SQL

SELECT
  o_orderdate AS Date,
  o_orderpriority AS Priority,
  o_totalprice AS Price
FROM
  samples.tpch.orders
WHERE
  o_totalprice > :num_param

In the following example, the field_param is used with the IDENTIFIER function to provide a threshold value for the query at runtime. The parameter value should be a column name from the table used in the query.

SQL

SELECT
  *
FROM
  samples.tpch.orders
WHERE
  IDENTIFIER(:field_param) < 10000

The following example creates three parameters: catalog, schema, and table.

SQL

SELECT
  *
FROM
  IDENTIFIER(:catalog || '.' || :schema || '.' || :table)

See IDENTIFIER clause.

You can include parameters in other SQL functions. This example allows the viewer to select an employee title and a number ID. The query uses the format_string function to concatenate the two strings and filter on the rows that match. See format_string function.

SQL

SELECT
  o_orderkey,
  o_clerk
FROM
  samples.tpch.orders
WHERE
  o_clerk LIKE format_string('%s%s', :title, :emp_number)

You can use parameters to extract an attribute from a JSON string. The following example uses the from_json function to convert the JSON string to a struct value. Substituting the string a as the value for the parameter (param) returns the attribute 1.

SQL

SELECT
  from_json('{"a": 1}', 'map<string, int>') [:param]

The INTERVAL type represents a span of time and allows you to perform time-based arithmetic and operations. The following example uses a CAST function to cast the parameter as interval type. The resulting INTERVAL value can be used for time-based calculations or filtering in the query.

See INTERVAL type for complete details and syntax.

SQL

SELECT CAST(:param AS INTERVAL MINUTE)

The following examples demonstrate how to filter for records within a date range. Date parameters can use .min and .max attributes to specify a range. You can also specifc a range using two different date parameters.

1. Choose a parameter name. Use .min to access the minimum range value and .max to access the maximum range value, as in the following query:

   SQL

   SELECT * FROM samples.nyctaxi.trips
   WHERE tpep_pickup_datetime
   BETWEEN :date_range.min AND :date_range.max
   

2. Set the parameter type to Date.

3. Set the Widget type to Range.

The following example uses two parameters, start_date and end_date, to set a date range.

SQL

SELECT * FROM samples.nyctaxi.trips
WHERE tpep_pickup_datetime
BETWEEN :start_date AND :end_date

The following example aggregates taxi trip data at a parameterized level of granularity. The DATE_TRUNC function truncates the tpep_pickup_datetime value based on the :date_granularity parameter value, such as DAY, MONTH, or YEAR. The truncated date is aliased as date_rollup and used in the GROUP BY clause.

SQL

SELECT DATE_TRUNC(:date_granularity, tpep_pickup_datetime) AS
  date_rollup,
  COUNT(*) AS total_trips
FROM samples.nyctaxi.trips
GROUP BY date_rollup

The following example uses the ARRAY_CONTAINS function to filter a list of values. The TRANSFORM, and SPLIT functions allow multiple, comma-separated values to be passed in as a string parameter.

The :list_parameter value takes a list of comma-separated values. The SPLIT function parses that list, splitting the comma-separated values into an array. The TRANSFORM function transforms each element in the array by removing any whitespace. The ARRAY_CONTAINS function checks whether the dropoff_zip value from the trips table is contained in the array of values passed in as the list_parameter.

SQL

SELECT * FROM samples.nyctaxi.trips WHERE
  array_contains(
    TRANSFORM(SPLIT(:list_parameter, ','), s -> TRIM(s)),
    dropoff_zip
  )

note

This example works for string values. To modify the query for other data types, such as a list of integers, wrap the TRANSFORM operation with a CAST operation to convert the string values into the desired data type.

The following table shows common use cases for parameters, the original Databricks SQL mustache syntax, and the equivalent syntax using named parameter marker syntax.

important

The following sections apply to query syntax that you can use in the SQL editor only. That means if you copy and paste a query using this syntax into any other Databricks interface, such as a notebook or AI/BI dashboard dataset editor, the query must be manually adjusted to use named parameter markers before it runs without error.

In the SQL editor, 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. While Databricks generally recommends using named parameter markers, some functionality is only supported using mustache parameter syntax.

Use mustache parameter syntax for the following functionality:

• Legacy dashboard filters
• Multiple value dropdown parameters
• Query based drop-down parameters

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.

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.

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

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

• Text
• Number
• Dropdown List
• Query-Based Dropdown List
• Date and Time

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

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

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

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

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.

SQL

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

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

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:

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 requires that you wrap date and time values in single quotation marks ('). For example:

SQL

SELECT *
FROM usage_logs
WHERE date = '{{ date_param }}'


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.

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.

important

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

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. They 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 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.

• 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.

• Can I reuse the same parameter multiple times in a single query?
• Can I use multiple parameters 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}}

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

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4