使用查詢參數

發行項
10/25/2024

本文說明了如何在 Azure Databricks SQL 編輯器中使用查詢參數。

查詢參數可讓您藉由在執行階段插入變數值，使查詢更加動態和有彈性。您可以定義參數，以根據使用者輸入來篩選資料或修改輸出，而不是將特定值硬式編碼到您的查詢中。此方法可改善查詢重複使用、藉由防止 SQL 插入來增強安全性，並可更有效率地處理各種資料案例。

具名參數標記語法

具名參數標記是具類型的預留位置變數。使用此語法在 Azure Databricks UI 的下列部分撰寫查詢：

SQL 編輯器
Notebooks
AI/BI 儀表板資料集編輯器
AI/BI Genie 空間 (公開預覽)

在 SQL 查詢中插入參數，方法是鍵入冒號，接著輸入參數名稱，例如 :parameter_name。當您在查詢中包含具名參數標記時，UI 中會出現小工具。您可以使用小工具來編輯參數類型和名稱。

具名參數會新增至 SQL 查詢。小工具會出現在 SQL 編輯器下方

將具名參數標記新增至查詢

此範例會將參數標記新增至下列查詢：


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

此查詢會傳回僅包含五美元以下車費金額的資料集。使用下列步驟來編輯查詢，以使用參數，而不是硬式編碼值 (5)。

從查詢中刪除數字 5。
輸入冒號 (:)，後面接著字串 fare_parameter。更新查詢的最後一行應該顯示 fare_amount < :fare_parameter。
按下參數小工具附近的齒輪圖示。對話方塊會顯示下列欄位：
- 關鍵字：代表查詢中參數的關鍵字。您不能編輯此欄位。若要變更關鍵字，請編輯 SQL 查詢中的標記。
- 標題：出現在小工具上的標題。根據預設，標題與關鍵字相同。
- 類型：支援的類型為文字、數字、下拉式清單、日期、日期和時間，以及日期和時間 (含秒)。預設為 Text。
在對話方塊中，將 [類型] 變更為 [數字]。
在參數小工具中輸入數字，然後按下 [套用變更]。
按一下 [儲存] 以儲存查詢。

具名參數語法範例

下列範例示範參數的一些常見使用案例。

插入日期

下列範例包含 [日期] 參數，將查詢結果限制在特定日期之後的記錄。


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

插入數字

下列範例包含 [數字] 參數，將結果限製為 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 函式中包含參數。此範例可讓檢視器選取員工職稱和數字識別碼。查詢會使用 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 字串擷取屬性。下列範例使用 from_json 函式將 JSON 字串轉換成結構值。將字串 a 取代為參數 (param) 的值會傳回屬性 1。

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

建立間隔

此 INTERVAL 類型代表時間範圍，可讓您執行以時間為基礎的算術和運算。下列範例包含函式內的 format_string 參數，然後轉換成間隔類型。產生的 INTERVAL 值可用於查詢中以時間為基礎的計算或篩選。

如需完整詳細數據和語法，請參閱 INTERVAL 類型。

SELECT CAST(format_string("INTERVAL '%s' MINUTE", :param) AS INTERVAL MINUTE)

新增日期範圍

下列範例示範如何新增參數化日期範圍，以選取特定時間範圍內的記錄。

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

依日、月或年參數化匯總

下列範例會匯總數據粒度參數化層級的計程車車程數據。函DATE_TRUNC式會根據:date_granularity參數值截斷tpep_pickup_datetime值，例如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 式來篩選值清單。和 SPLIT 函TRANSFORM式允許以字串參數的形式傳入多個逗號分隔值。

值會 :list_parameter 採用逗號分隔值的清單。函 SPLIT 式會剖析該清單，將逗號分隔值分割成陣列。函 TRANSFORM 式會移除任何空格元，以轉換數位中的每個元素。函ARRAY_CONTAINS式會檢查數據表中的值trips是否dropoff_zip包含在傳入做為的值陣列中list_parameter。


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

注意

此範例適用於字串值。若要修改其他數據類型的查詢，例如整數清單，請將作業包裝 TRANSFORM 成 CAST 作業，將字串值轉換成所需的數據類型。

語法變更

下表顯示參數的常見使用案例、原始 Databricks SQL Mustache 語法，以及使用具名參數標記語法的對等語法。

參數使用案例	Mustache 參數語法	具名參數標記語法
僅載入指定日期之前的資料	`WHERE date_field < '{{date_param}}'` 您必須在 [日期] 參數和大括弧周圍加上引號。	`WHERE date_field < :date_param`
僅載入小於指定數值的資料	`WHERE price < {{max_price}}`	`WHERE price < :max_price`
比較兩個字串	`WHERE region = {{region_param}}`	`WHERE region = :region_param`
指定查詢中使用的資料表	`SELECT * FROM {{table_name}}`	`SELECT * FROM IDENTIFIER(:table)` 當使用者輸入此參數時，他們應該使用完整的三層命名空間來識別資料表。
獨立指定查詢中使用的目錄、結構描述和資料表	`SELECT * FROM {{catalog}}.{{schema}}.{{table}}`	`SELECT * FROM IDENTIFIER(:catalog \\|\\| '.' \\|\\| :schema \\|\\| '.' \\|\\| :table)`
使用參數做為較長格式化字串的範本	“({{area_code}}) {{phone_number}}” 參數值會自動串連為字串。	format_string(“(%d)%d, :area_code, :phone_number) 如需完整範例，請參閱串連多個參數。
建立間隔	`SELECT INTERVAL {{p}} MINUTE`	`SELECT CAST(format_string("INTERVAL '%s' MINUTE", :param) AS INTERVAL MINUTE)`

Mustache 參數語法

重要

下列各節適用於您僅能在 SQL 編輯器中使用的查詢語法。這表示，如果您使用此語法將查詢複製並貼到任何其他 Azure Databricks 介面中，例如筆記本或 AI/BI 儀表板資料集編輯器，則必須手動調整查詢，以在查詢執行之前使用具名參數標記，而不會發生錯誤。

在 SQL 編輯器中，雙大括弧 {{ }} 之間的任何字串均會被視為查詢參數。小工具會出現在結果窗格上方，您可在其中設定參數值。雖然 Azure Databricks 通常建議使用具名參數標記，但某些功能僅支援使用 Mustache 參數語法。

針對下列功能使用 Mustache 參數語法：

舊版儀表板篩選
多個值下拉式清單參數
日期範圍
查詢型下拉式參數

新增 Mustache 參數

輸入 Cmd + I。參數會插入文字插入點，且 [新增參數] 對話方塊隨即出現。
- 關鍵字：代表查詢中參數的關鍵字。
- 標題：出現在小工具上的標題。根據預設，標題與關鍵字相同。
- 類型：支援的類型為文字、數字、日期、日期和時間、日期和時間 (含秒)、下拉式清單，和查詢型下拉式清單。預設為 Text。
輸入關鍵字，選擇性地覆寫標題，然後選取參數類型。
按下 [新增參數]。
在參數小工具中，設定參數值。
按下 [套用變更]。
按一下 [檔案] 。

或者，輸入雙大括弧 {{ }}，然後按下參數小工具附近的齒輪圖示來編輯設定。

若要使用不同的參數值重新執行查詢，請在小工具中輸入值，然後按下 [套用變更]。

編輯查詢參數

若要編輯參數，請按下參數小工具旁邊的齒輪圖示。若要防止未擁有查詢的使用者變更參數，請按下 [僅顯示結果]。 <Keyword> 參數對話方塊隨即出現。

移除查詢參數

若要移除查詢參數，請從查詢中刪除參數。參數小工具消失，您可使用靜態值重寫查詢。

變更參數的順序

若要變更顯示參數的順序，可以按下每個參數，並將每個參數拖曳至所需的位置。

Text

接受字串做為輸入。反斜線、單引號和雙引號都會逸出，而 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 }} )

參數多重選取小工具能讓您將多個值傳遞至資料庫。如果您為 [引號] 參數選取 [雙引號] 選項，則查詢會反映下列格式：WHERE IN ("value1", "value2", "value3")

接受查詢的結果作為其輸入。其行為與 [下拉式清單] 參數相同。您必須儲存 Databricks SQL 下拉式清單查詢，才能將其當做另一個查詢中的輸入。

按下 [設定] 面板中 [類型] 底下的 [查詢型下拉式清單]。
按下 [查詢] 欄位，然後選取查詢。如果您的目標查詢傳回大量記錄，則效能將會降低。

如果您的目標查詢傳回一個以上資料行，則 Databricks SQL 會使用第一個資料行。如果您的目標查詢傳回 name 和 value 資料行，則 Databricks SQL 會使用 name 資料行填入參數選取小工具，但會以相關聯的 value 執行查詢。

例如，假設下列查詢會傳回資料表中的資料。

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

value	NAME
1001	John Smith
1002	Jane Doe
1003	Bobby 資料表

Azure Databricks 執行查詢時，傳遞給資料庫的值會是 1001、1002 或 1003。

日期與時間

Azure Databricks 有數個選項可將日期和時間戳記參數化，包括簡化時間範圍參數化的選項。從三個不同精確度的選項中選取：

選項	精確度	類型
日期	天	`DATE`
日期和時間	分鐘	`TIMESTAMP`
日期與時間 (含秒)	second	`TIMESTAMP`

選擇 [範圍] 參數選項時，您會建立由 .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 }}'

日期參數會使用行事曆挑選介面，並預設為目前的日期和時間。

注意

[日期範圍] 參數只會傳回 DATE 類型的資料行的正確結果。針對 TIMESTAMP 資料行，請使用其中一個 [日期和時間範圍] 選項。

動態日期和日期範圍值

當您將 [日期或日期範圍] 參數新增至查詢時，選取小工具會顯示藍色閃電圖示。按下此選項，以顯示動態值，例如 today、yesterday、this week、last week、last month 或 last year。這些值會動態更新。

重要

動態日期和日期範圍與排程查詢不相容。

在儀表板中使用查詢參數

或者，查詢可使用參數或靜態值。將以參數化查詢為基礎的視覺效果新增至儀表板時，可將視覺效果設定為使用下列其中一項：

[小工具] 參數

小工具參數專屬於儀表板中的單一視覺效果、出現在視覺效果面板中，而指定的參數值僅會套用至視覺效果底層的查詢。
儀表板參數

儀表板參數可套用至多個視覺效果。當您根據參數化查詢將視覺效果新增至儀表板時，參數預設會新增為儀表板參數。儀表板參數針對儀表板中的一或多個視覺效果所設定，並出現在儀表板頂端。為儀表板參數指定的參數值會套用至重複使用該特定儀表板參數的視覺效果。儀表板可以有多個參數，每個參數都可以套用至某些視覺效果，而不適用於其他。
靜態值

靜態值會用來取代回應變更的參數。靜態值可讓您硬式編碼值來取代參數。它們會使參數從先前出現的儀表板或小工具「消失」。

當您新增包含參數化查詢的視覺效果時，可以按下適當的鉛筆圖示，選擇視覺效果查詢中參數的標題和來源。您也可以選取關鍵字和預設值。請參閱參數屬性。

將視覺效果新增至儀表板之後，按下儀表板小工具右上角的 Kebab 功能表，然後按下 [變更小工具設定]，以存取參數對應介面。

參數屬性

標題：顯示在儀表板上值選取器旁邊的顯示名稱。它預設為參數 [關鍵字]。按下鉛筆圖示加以編輯。靜態儀表板參數不會顯示標題，因為值選取器已隱藏。如果您選取 [靜態值] 作為 [值來源]，[標題] 欄位會呈現灰色。
關鍵字：基礎查詢中此參數的字串常值。如果您的儀表板未傳回預期的結果，這對於偵錯非常有用。
預設值：如果沒有指定其他值，預設值會是。若要從查詢畫面變更此項目，請使用所需的參數值執行查詢，然後按下 [儲存] 按鈕。
值來源：參數值的來源。按下鉛筆圖示以選擇來源。
- 新的儀表板參數：建立新的儀表板層級參數。這可讓您在儀表板的一個位置設定參數值，並將其對應至多個視覺效果。
- 現有的儀表板參數：將參數對應至現有的儀表板參數。您必須指定哪些預先存在的儀表板參數。
- 小工具參數：在儀表板小工具內顯示值選取器。這適用於在小工具之間不共用的一次性參數。
- 靜態值：不論在其他小工具上使用的值為何，均選擇小工具的靜態值。靜態對應的參數值不會在儀表板上任何位置顯示值選取器，更為精簡。當某些參數預期不會經常變更時，這可讓您充分利用查詢參數的彈性，而不會使儀表板上的使用者介面雜亂無章。

常見問題集 (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}}'

共用方式為

使用查詢參數

具名參數標記語法

將具名參數標記新增至查詢

具名參數語法範例

插入日期

插入數字

插入欄位名稱

插入資料庫物件

串連多個參數

處理字串

建立間隔

新增日期範圍

依日、月或年參數化匯總

在單一查詢中使用多個值

語法變更

Mustache 參數語法

新增 Mustache 參數

編輯查詢參數

移除查詢參數

變更參數的順序

查詢參數類型

Text

數字

日期與時間

動態日期和日期範圍值

在儀表板中使用查詢參數

參數屬性

常見問題集 (FAQ)

我能否在單一查詢中多次重複使用相同的參數？

我能否在單一查詢中使用多個參數？

意見反應

其他資源

共用方式為

使用查詢參數

具名參數標記語法

將具名參數標記新增至查詢

具名參數語法範例

插入日期

插入數字

插入欄位名稱

插入資料庫物件

串連多個參數

處理字串

建立間隔

新增日期範圍

依日、月或年參數化匯總

在單一查詢中使用多個值

語法變更

Mustache 參數語法

新增 Mustache 參數

編輯查詢參數

移除查詢參數

變更參數的順序

查詢參數類型

Text

數字

下拉式清單

查詢型下拉式清單

日期與時間

動態日期和日期範圍值

在儀表板中使用查詢參數

參數屬性

常見問題集 (FAQ)

我能否在單一查詢中多次重複使用相同的參數？

我能否在單一查詢中使用多個參數？

意見反應

其他資源