查询参数Query parameters

重要

此功能目前以公共预览版提供。This feature is in Public Preview. 请联系 Azure Databricks 代表,以申请访问权限。Contact your Azure 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.

查询参数Query parameter

有关演示,请查看查询参数视频For a demo, see the query parameters video.

添加查询参数Add a query parameter

  1. 单击“添加新参数”“新建参数”按钮 按钮或键入 Cmd + P 。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. 默认值为 Text。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

<Keyword>”参数对话框会显示:The <Keyword> parameter dialog displays:

参数设置Parameter settings

查询参数类型Query parameter types

文本Text

采用字符串作为输入。Takes a string as input. 反斜杠、单引号和双引号将被转义,Azure Databricks 将向此参数添加引号。Backslash, single quote, and double quote are escaped, and Azure Databricks adds quotation marks to this parameter. 例如,mr's Li"s 之类的字符串会被转换成 'mr\'s Li\"s'。使用此形式的示例可以是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 }}

NumberNumber

采用数字作为其输入。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. 它们采用特定的时间 (12/11/2020 12:01),或采用表示时间 (Today, Yesterday) 的字符串。They take either a specific time (12/11/2020 12:01) or a string representing time (Today, Yesterday). 此参数的示例是 SELECT * from usage_logs where date='{{ date_param }}'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. 可从 3 个精度级别中进行选择:日期、日期和时间,以及日期和时间(以秒为单位)You can chose from three levels of precision: Date, Date and Time, and Date and Time with seconds.

备注

日期参数作为字符串传递给数据库。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)

日期范围参数采用开始日期和结束日期 (12/09/2020 12:01 - 12/11/2020 13:01),或者采用表示时间 (Last week, Last month) 的字符串。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). 这些参数会插入两个名为 .start.end 的标记,表示所选日期范围的开始和结束。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. 单击它可显示动态值,例如 last monthyesterdaylast yearClick it to display dynamic values like last month, yesterday, or last year. 这些值每天都会动态更新。These values update dynamically every day.

动态日期Dynamic dates

重要

动态日期和日期范围与计划查询不兼容。Dynamic dates and date ranges aren’t compatible with scheduled queries.

若要在运行查询时限制可能参数值的范围,可使用“下拉列表”参数类型。To restrict the scope of possible parameter values when running a query, you can use the Dropdown List parameter type. 例如 SELECT * FROM users WHERE name='{{ dropdown_param }}'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.

    允许多个值Allow multiple values

在查询中,将 WHERE 子句更改为使用 IN 关键字。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.

多选小组件Multi-selection-widget

基于查询的下拉列表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.

如果目标查询返回多个列,SQL Analytics 将使用第一个列。If your target query returns more than one column, SQL Analytics uses the first one. 如果目标查询返回 name 列和 value 列,则 SQL Analytics 将使用 name 列填充参数选择小组件,但使用关联的 value 列执行查询。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 namename
10011001 John SmithJohn Smith
10021002 Jane DoeJane Doe
10031003 Bobby 表Bobby Tables

下拉列表小组件如下所示:The Dropdown List widget would look like:

John Smith、Jane Doe 和 Bobby 表John Smith, Jane Doe and Bobby Tables

当 SQL Analytics 执行查询时,传递给数据库的值将是 1001、1002 或 1003。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.

参数映射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.

常见问题 (FAQ)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. 此示例使用 {{org_id}} 参数两次。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. 此示例使用两个参数:{{org_id}}{{start_date}}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}}'