與查詢 parameters 互動

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

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

具名參數標記語法

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

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

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

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

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

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

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

1. 從查詢中刪除數字 5。
2. 輸入冒號 (:)，後面接著字串 fare_parameter。 更新查詢的最後一行應該顯示 fare_amount < :fare_parameter。
3. 按下參數小工具附近的 齒輪圖示。 對話方塊會顯示下列欄位：
   • 關鍵字：代表查詢中參數的關鍵字。 您不能編輯此欄位。 若要變更關鍵字，請編輯 SQL 查詢中的標記。
   • 標題：出現在小工具上的標題。 根據預設，標題與關鍵字相同。
   • 類型：支援的型別為 Text、Number、Dropdown List、Date、Date and Time，以及 Date and Time（含秒）。 預設為 Text。
4. 在對話方塊中，將 [類型] 變更為 [數字]。
5. 在參數小工具中輸入數字，然後按下 [套用變更]。
6. 按一下 [儲存] 以儲存查詢。

具名參數語法範例

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

Insert 日期

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

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

Insert 一個數字

下列範例包含 Number 參數，可限制 whereo_total_price 字段的結果大於所提供的參數值。

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

Insert 一個欄位名稱

在下列範例中，field_param 會與 IDENTIFIER 函式搭配使用，在執行階段提供查詢的閾值。 參數值應該是查詢中使用的 tablecolumn 名稱。

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

Insert 資料庫物件

下列範例會建立三個 parameters：catalog、schema和 table。

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

請參閱 IDENTIFIER 子句。

串連多個 parameters

您可以在其他 SQL 函式中包含 parameters。 此範例可讓查看器 select 員工職稱和數位標識碼。 查詢會使用 format_string 函式來串連兩個字串，並篩選相符的資料列。 請參閱 format_string 函式。

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

處理字串

您可以使用 parameters 從 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)

新增日期範圍

下列範例示範如何將參數化日期範圍新增至特定時間範圍內 select 記錄。

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

在單一查詢中使用多個 values

下列範例會使用 ARRAY_CONTAINS 函式來篩選 values的 list。 TRANSFORM和 SPLIT 函式允許以字串參數的形式傳入多個逗號分隔 values。

:list_parameter 值會接受以逗號分隔的 values作為 list。 SPLIT 函式會剖析 list，將逗號分隔 values 分割成陣列。 函 TRANSFORM 式會移除任何空格元，以轉換數位中的每個元素。 ARRAY_CONTAINS 函式會檢查來自 tripstable 的 dropoff_zip 值是否包含在傳入做為 list_parameter的 values 陣列中。

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

語法變更

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

Mustache 參數語法

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

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

• 舊版儀表板篩選
• 多選值下拉選單 parameters
• 查詢型下拉式 parameters

新增 Mustache 參數

1. 輸入 Cmd + I。 參數會插入文字插入點，且 [新增參數] 對話方塊隨即出現。
   • 關鍵字：代表查詢中參數的關鍵字。
   • 標題：出現在小工具上的標題。 根據預設，標題與關鍵字相同。
   • 類型：支援的型別為文字、數位、日期、日期和時間、日期和時間（含秒）、下拉式清單 List，以及查詢型下拉式清單 List。 預設為 Text。
2. 輸入關鍵字，選擇性地覆寫標題，並設定參數類型為 select。
3. 按下 [新增參數]。
4. 在參數 Widget 中，set 參數值。
5. 按下 [套用變更]。
6. 按一下 [檔案] 。

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

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

編輯查詢參數

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

Remove 查詢參數

若要執行 remove 操作，請從查詢中刪除參數。 參數小工具會消失，然後您可以用固定的 values來重新撰寫查詢。

變更 parameters 的順序

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

查詢參數類型

• Text
• Number
• 下拉式清單 List
• Query-Based 下拉式清單 List
• 日期和時間

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

下拉式清單 List

若要在執行查詢時限制可能參數 values 的範圍，請使用 [下拉式清單] List 參數類型。 有一個範例為 SELECT * FROM users WHERE name='{{ dropdown_param }}'。 從參數設定面板選取時，會出現一個文本框，where 您輸入允許的 values，每個值會以新行分隔。 下拉式清單是文字編號 parameters。 若要在 [下拉式清單] List中使用日期或日期和時間，請以數據源所需的格式輸入它們。 字串不會逸出。 您可選擇單一值或多重值下拉式清單。

• 單一值：需要參數周圍的單引號。
• 多重值：切換 允許多個 values 選項。 在 [引號] 下拉式清單中，選擇是否將 parameters 保留為原輸入（無引號），或使用單引號或雙引號來包圍 parameters。 如果您選擇引號，則不需要在參數周圍加上引號。

將 WHERE 子句變更為在查詢中使用 IN 關鍵字。

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

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

Query-Based 下拉式清單 List

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

1. 在 [設定] 面板中，按兩下 [類型] 底下的 [查詢型下拉式清單] list。
2. 點擊 查詢 字段，然後 select 查詢。 如果您的目標查詢傳回大量記錄，則效能將會降低。

如果您的目標查詢傳回超過一個 column，則 Databricks SQL 會使用第一個 。 如果您的目標查詢傳回 name 和 valuecolumns，Databricks SQL 會將 namecolumn 用於參數選擇工具，但會使用相關聯的 value執行查詢。

例如，假設下列查詢會傳回 table中的數據。

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

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

日期與時間

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

選擇 Range 參數時，您會建立兩個由 .start 和 .end 作為後綴指定的 parameters。 所有選項都會將 parameters 傳遞至查詢作為字串字面值；Azure Databricks 要求您將日期和時間 values 用單引號包裹起來（'）。 例如：

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

日期 parameters 使用行事曆選擇介面，並預設為目前的日期和時間。

動態日期和時間範圍 values

當您將 [日期或日期範圍] 參數新增至查詢時，選取小工具會顯示藍色閃電圖示。 點選即可顯示動態 values，例如 today、yesterday、this week、last week、last month或 last year。 這些 valuesupdate 動態。

在儀錶板中使用查詢 parameters

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

• [小工具] 參數

  小工具 parameters 專屬於儀錶板中的單一視覺效果、出現在視覺效果面板中，而指定的參數 values 僅適用於視覺效果基礎的查詢。

• 儀表板參數

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

• 靜態值

  靜態 values 用於取代回應變更的參數。 靜態 values 可讓您硬式編碼值來取代參數。 它們會將參數從先前出現的儀錶板或小工具 where 中隱藏。

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

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

參數屬性

• 標題：顯示在儀表板上值選取器旁邊的顯示名稱。 它預設為參數 [關鍵字]。 按下鉛筆圖示 加以編輯。 靜態儀錶板 parameters 不會顯示標題，因為值選取器已隱藏。 如果您 select靜態值 做為 值來源，Title 字段會呈現灰色。

• 關鍵字：基礎查詢中此參數的字串常值。 如果您的儀表板未傳回預期的結果，這對於偵錯非常有用。

• 預設值：如果沒有指定其他值，預設值會是 。 若要從查詢畫面變更此項目，請使用所需的參數值執行查詢，然後按下 [儲存] 按鈕。

• 值來源：參數值的來源。 按下鉛筆圖示 以選擇來源。

  • 新的儀表板參數：建立新的儀表板層級參數。 這可讓您 set 儀錶板上一個位置的參數值，並將其對應至多個視覺效果。
  • 現有的儀表板參數：將參數對應至現有的儀表板參數。 您必須指定哪些預先存在的儀表板參數。
  • 小工具參數：在儀表板小工具內顯示值選取器。 這適用於未在小工具間共用的一次性 parameters。
  • 靜態值：選擇小工具的靜態值，不考慮其他小工具上使用的 values。 靜態對應的參數 values 不會在儀表板上的任何位置顯示值選擇器，使其更為簡潔。 這可讓您利用查詢 parameters 的彈性，而在儀錶板上不至於因特定 parameters 項目不常變更而顯示雜亂。

  

常見問題集 (FAQ)

• 我能否在單一查詢中多次重複使用相同的參數？
• 是否可以在單一查詢中使用多個 parameters？

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

是。 在大括弧中使用相同的符號 identifier。 此範例會使用 {{org_id}} 參數兩次。

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

我可以在單一查詢中使用多個 parameters 嗎？

是。 針對每個參數使用唯一的名稱。 此範例使用兩個 parameters：{{org_id}} 和 {{start_date}}。

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