使用查询参数

本文介绍如何在 Azure Databricks SQL 编辑器中使用查询参数。

通过查询参数，可以在运行时插入变量值，从而提高查询的动态性和灵活性。 使用查询参数时，可以通过定义参数来基于用户输入筛选数据或修改输出，而不是将特定值硬编码到查询中。 此方法可提高查询重用性，通过防止 SQL 注入来增强安全性，并支持更高效地处理各种数据场景。

命名参数标记语法

已命名的参数标记是类型化占位符变量。 使用此语法在 Azure Databricks UI 的以下部分编写查询：

• SQL 编辑器（新版和旧版）
• Notebooks
• AI/BI 仪表板数据集编辑器
• AI/BI Genie 空间

通过键入冒号加参数名称（例如 :parameter_name），从而将参数插入 SQL 查询中。 在查询中包含命名参数标记时，UI 中会显示一个小组件。 可以使用该小组件编辑参数类型和名称。

向查询添加命名参数标记

此示例将一个参数标记添加到以下查询：

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

此查询将返回一个数据集，其中仅包含 5 美元以下的票价金额。 使用以下步骤编辑查询以使用参数而不是硬编码的值 (5)。

1. 从查询中删除数字 5。
2. 键入冒号 (:) 加字符串 fare_parameter。 更新后的查询最后一行应显示 fare_amount < :fare_parameter。
3. 单击参数小组件附近的 齿轮图标。 该对话框将显示以下字段：
   • 关键字：表示查询中的参数的关键字。 无法编辑此字段。 要更改关键字，请编辑 SQL 查询中的标记。
   • 标题：显示在小组件上方的标题。 默认情况下，标题与关键字相同。
   • 类型：支持的类型包括文本、数字、下拉列表、日期、日期和时间以及日期和时间（以秒为单位）。 默认值为 Text。
4. 在对话框中，将“类型”更改为“数字”。
5. 在参数小组件中输入数字，然后单击“应用更改”。
6. 单击“保存”以保存查询。

命名参数语法示例

以下示例演示了参数的一些常见用例。

插入日期

以下示例包含一个 Date 参数，用于将查询结果限制为特定日期后的记录。

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

插入数字

以下示例包含一个 Number 参数，用于将结果限制为 o_total_price 字段大于提供的参数值时的记录。

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

插入字段名称

在以下示例中，field_param 与 IDENTIFIER 函数搭配使用，在运行时为查询提供阈值。 参数值应是查询中使用的表中的列名。

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

插入数据库对象

以下示例创建了三个参数：catalog、schema 和 table。

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

请参阅 IDENTIFIER 条款。

连接多个参数

可以在其他 SQL 函数中包含参数。 在此示例中，查看者可以选择员工职称和编号 ID。 查询使用 format_string 函数连接两个字符串，并筛选匹配的行。 请参阅 format_string 函数。

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

使用 JSON 字符串

可以使用参数从 JSON 字符串中提取属性。 下面的示例使用 from_json 函数将 JSON 字符串转换为结构值。 将字符串 a 替换为参数 (param) 的值将返回属性 1。

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

创建间隔

INTERVAL 类型表示一段时间，并允许执行基于时间的算术和运算。 以下示例使用函数 CAST 将参数强制转换为间隔类型。 所生成的 INTERVAL 值可用于基于时间的计算或在查询中进行筛选。

有关完整详细信息和语法，请参阅 INTERVAL 类型 。

SELECT CAST(:param AS INTERVAL MINUTE)

添加日期范围

以下示例演示如何筛选日期范围内的记录。 日期参数可以使用 .min 和 .max 属性指定范围。 还可以使用两个不同的日期参数来指定范围。

使用最小和最大属性

1. 选择参数名称。 用于 .min 访问最小范围值和 .max 访问最大范围值，如以下查询中所示：

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

2. 将参数类型设置为 Date.

3. 将 小组件类型 设置为 Range。

使用两个日期参数

以下示例使用两个参数， start_date 以及 end_date设置日期范围。

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

按天、月或年份参数化汇总

以下示例以参数化粒度级别合并出租车行程数据。 DATE_TRUNC 函数根据 tpep_pickup_datetime 参数值（例如 :date_granularity、DAY 或 MONTH）截断 YEAR 值。 截断日期的别名为 date_rollup，并在 GROUP BY 子句中使用。

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

在单个查询中使用多个值

下面的示例使用 ARRAY_CONTAINS 函数来筛选值列表。 TRANSFORM 和 SPLIT 函数允许以字符串参数形式传入多个逗号分隔值。

:list_parameter 值采用逗号分隔的值列表。 SPLIT 函数会分析该列表，将逗号分隔的值拆分为数组。 TRANSFORM 函数通过删除任意空格来转换数组中的每个元素。 ARRAY_CONTAINS 函数检查 dropoff_zip 表中的 trips 值是否包含以 list_parameter 形式传入的值数组中。

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

语法更改

下表显示了参数的常见用例、原始 Databricks SQL mustache 语法以及使用命名参数标记语法的等效语法。

Mustache 参数语法

在 SQL 编辑器中，双大括号 {{ }} 之间的任何字符串都被视为查询参数。 结果窗格上方会出现一个小组件，你可在其中设置参数值。 虽然 Azure Databricks 通常建议使用命名参数标记，但某些功能仅支持使用 mustache 参数语法。

对以下功能使用 mustache 参数语法：

• 旧版仪表板筛选器
• 多值下拉列表参数
• 基于查询的下拉列表参数

添加 mustache 参数

1. 键入 Cmd + I。 参数将插入到文本脱字号处，并显示“添加参数”对话框。
   • 关键字：表示查询中的参数的关键字。
   • 标题：显示在小组件上方的标题。 默认情况下，标题与关键字相同。
   • 类型：支持的类型包括文本、数字、日期、日期和时间、日期和时间（以秒为单位）、下拉列表和基于查询的下拉列表。 默认值为 Text。
2. 输入关键字，可选择覆盖标题，然后选择参数类型。
3. 单击添加参数。
4. 在参数小组件中，设置参数值。
5. 单击“应用更改”。
6. 单击“保存”。

也可键入双花括号 {{ }}，然后单击参数小组件附近的齿轮图标来编辑设置。

若要使用其他参数值重新运行查询，请在小组件中输入值，然后单击应用更改。

编辑查询参数

若要编辑参数，请单击参数小组件旁边的齿轮图标。 若要防止不拥有查询的用户更改参数，请单击“仅显示结果”。 此时将出现 <Keyword> 参数对话框。

删除查询参数

若要删除查询参数，请从查询中删除该参数。 参数小组件将消失，你可以使用静态值重写查询。

更改参数的顺序

要更改参数的显示顺序，可以单击每个参数并将其拖动到所需位置。

查询参数类型

• 文字
• 数字
• 下拉列表
• 基于查询的下拉列表
• 日期和时间

文本

采用字符串作为输入。 反斜杠、单引号和双引号将被转义，Azure Databricks 将向此参数添加引号。 例如，mr's Li"s 之类的字符串会被转换成 'mr\'s Li\"s'。使用此形式的示例可以是

SELECT * FROM users WHERE name={{ text_param }}

数字

采用数字作为其输入。 使用此形式的示例可以是

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

下拉列表

若要在运行查询时限制可能的参数值的范围，请使用下拉列表参数类型。 例如 SELECT * FROM users WHERE name='{{ dropdown_param }}'。 从参数设置面板中进行选择后，会显示一个文本框，请在其中输入允许的值，每个值之间用新行分隔。 下拉列表是文本参数。 若要在下拉列表中使用日期或者日期和时间，请按数据源要求的格式输入它们。 字符串不会进行转义。 可选择单值或多值下拉列表。

• 单值：参数两侧需要单引号。
• 多值：切换允许多值选项。 在“引号”下拉列表中，选择是将参数保留为输入值（无引号），还是用单引号或双引号括起参数。 如果选择了引号，则无需在参数周围添加引号。

在查询中，将 WHERE 子句更改为使用 IN 关键字。

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

通过参数多选小组件，可向数据库传递多个值。 如果为 Quotation 参数选择“双引号”选项，则查询会反映以下格式：WHERE IN ("value1", "value2", "value3")

基于查询的下拉列表

将查询结果作为其输入。 它与“下拉列表”参数的行为相同。 必须保存 Databricks SQL 下拉列表查询，以将其用作另一个查询中的输入。

1. 单击设置面板中“类型”下的“基于查询的下拉列表”。
2. 单击查询字段并选择一个查询。 如果目标查询返回大量记录，则性能将降低。

如果目标查询返回多个列，Databricks SQL 则使用第一个列。 如果目标查询返回 name 列和 value 列，则 Databricks SQL 使用 name 列填充参数选择小组件，但使用关联的 value 列执行查询。

例如，假设以下查询返回表中的数据。

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

当 Azure Databricks 运行查询时，传递给数据库的值将是 1001、1002 或 1003。

日期和时间

Azure Databricks 具有多个用于将日期和时间戳值参数化的选项，包括用于简化时间范围参数化的选项。 从三个不同精度的选项中进行选择：

选择范围参数选项时，将会创建两个由 .start 和 .end 后缀指定的参数。 所有选项都将参数作为字符串字面量传递给查询；Azure Databricks 要求你将日期和时间值用单引号 (') 括起来。 例如：

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

日期参数使用“日历选取”界面，默认为当前日期和时间。

动态日期和日期范围值

将日期或日期范围参数添加到查询时，选择小组件将显示蓝色闪电图标。 单击它可显示动态值，例如 today、yesterday、this week、last week、last month 或 last year。 这些值会动态更新。

在仪表板中使用查询参数

查询可以选择性地使用参数或静态值。 将基于参数化查询的可视化效果添加到仪表板时，可以将可视化效果配置为使用：

• 小组件参数

  小组件参数特定于仪表板中的单个可视化效果，显示在可视化效果面板中，指定的参数值仅适用于以可视化效果为基础的查询。

• 仪表板参数

  仪表板参数可以应用于多个可视化效果。 将基于参数化查询的可视化效果添加到仪表板时，默认情况下，该参数将添加为仪表板参数。 仪表板参数针对仪表板中的一个或多个可视化效果进行配置，并显示在仪表板顶部。 为仪表板参数指定的参数值适用于重用该特定仪表板参数的可视化效果。 仪表板可以有多个参数，每个参数可能适用于某些可视化效果，而不适用于其他可视化效果。

• 静态值

  静态值用于代替响应更改的参数。 使用静态值时可以通过硬编码值来代替参数。 它们使参数从之前出现的仪表板或小部件中“消失”。

添加包含参数化查询的可视化效果时，可以通过单击相应的铅笔图标 在可视化效果查询中选择参数的标题和源。 还可以选择关键字和默认值。 请参阅参数属性。

将可视化效果添加到仪表板后，可以通过单击仪表板小组件右上角的垂直三点菜单并单击“更改小组件设置”来访问参数映射界面。

参数属性

• 标题：显示在仪表板上的值选择器旁边的显示名称。 它默认为参数关键字。 若要编辑它，请单击铅笔图标 。 静态仪表板参数不显示标题，因为值选择器是隐藏的。 如果选择“静态值”作为“值源”，则“标题”字段将灰显。

• 关键字：基础查询中此参数的字符串字面量。 这有助于在仪表板未返回预期结果的情况下进行调试。

• 默认值：如果未指定其他值，则使用该值。 若要在查询屏幕中更改此设置，请使用所需的参数值运行查询，然后单击“保存”按钮。

• 值源：参数值的源。 若要选择源，请单击铅笔图标 。

  • 新的仪表板参数：创建新的仪表板级别参数。 这样，你就可在仪表板上的一个位置设置参数值，并将其映射到多个可视化效果。
  • 现有仪表板参数：将参数映射到现有仪表板参数。 必须指定预先存在的仪表板参数。
  • 小组件参数：显示仪表板小组件中的值选择器。 对于不在小组件之间共享的一次性参数，这非常有用。
  • 静态值：为小组件选择一个静态值，而不考虑在其他小组件上使用的值。 静态映射的参数值不会在更紧凑的仪表板上的任何位置显示值选择器。 这样就能够利用查询参数的灵活性，当某些参数预计不会频繁更改时，仪表板上的用户界面不因此混乱。

  

常见问题（FAQ）

• 能否在单个查询中多次重用同一个参数？
• 能否在单个查询中使用多个参数？

能否在单个查询中多次重用同一个参数？

是的。 请在大括号中使用同一个标识符。 此示例使用 {{org_id}} 参数两次。

SELECT {{org_id}}, count(0)
FROM queries
WHERE org_id = {{org_id}}

能否在单个查询中使用多个参数？

是的。 请为每个参数使用唯一的名称。 此示例使用两个参数：{{org_id}} 和 {{start_date}}。

SELECT count(0)
FROM queries
WHERE org_id = {{org_id}} AND created_at > '{{start_date}}'