クエリ パラメーターの操作

この記事では、Azure Databricks SQL エディターでクエリ パラメーターを操作する方法について説明します。

クエリ パラメーターを使用すると、実行時に変数値を挿入することで、クエリをより動的かつ柔軟にすることができます。 クエリに特定の値をハードコーディングする代わりに、パラメーターを定義してデータをフィルター処理したり、ユーザーによる入力に基づいて出力を変更したりできます。 この方法では、クエリの再利用が向上し、SQL インジェクションを防ぐことでセキュリティが強化され、多様なデータ シナリオをより効率的に処理できるようになります。

名前付きパラメーター マーカーの構文

名前付きパラメーター マーカーは、型指定付きのプレースホルダー変数です。 この構文を使用して、Azure Databricks UI の次の部分にクエリを記述します。

• SQL エディター

• ノートブック

• 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

数値を挿入する

次の例には、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

フィールド名を挿入する

次の例では、IDENTIFIER 関数と共に field_param を使用して、実行時にクエリのしきい値を指定します。 パラメーター値は、クエリで使用されるテーブルの列名にする必要があります。

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

データベース オブジェクトを挿入する

次の例では、3 つのパラメーター catalog、schema、table を作成します。

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

「IDENTIFIER 句」を参照してください。

複数のパラメーターを連結する

他の SQL 関数にパラメーターを含めることができます。 この例では、閲覧者が従業員の役職と番号 ID を選択できるようにします。 このクエリでは、format_string 関数を使用して 2 つの文字列を連結し、一致する行をフィルター処理します。 「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 時間の範囲を表し、時間ベースの算術演算と演算を実行できます。 次の例では、関数内にパラメーターが 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

1 つのクエリで複数の値を使用する

次の例では、関数を ARRAY_CONTAINS 使用して値の一覧をフィルター処理します。 TRANSFORMおよびSPLIT関数を使用すると、複数のコンマ区切り値を文字列パラメーターとして渡すことができます。

値は :list_parameter コンマ区切りの値のリストを受け取ります。 この関数はその SPLIT リストを解析し、コンマ区切りの値を配列に分割します。 この関数は TRANSFORM 、空白を削除して配列内の各要素を変換します。 この関数はARRAY_CONTAINS、テーブルのdropoff_ziptrips値が、渡された値の配列に含まれているかどうかをチェックします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> パラメーター ダイアログが表示されます。

クエリ パラメーターを削除します

クエリ パラメーターを削除するには、クエリからパラメーターを削除します。 パラメーター ウィジェットが消え、静的な値を使ってクエリを書き直すことができます。

パラメーターの順序を変更する

パラメーターを表示する順序を変更するには、各パラメーターをクリックして目的の位置にドラッグします。

クエリ パラメーターの型

• テキスト
• Number
• ドロップダウン リスト
• クエリベースのドロップダウン リスト
• 日付と時間

Text

入力として文字列を取ります。 円記号、一重引用符、二重引用符はエスケープされ、Azure Databricks ではこのパラメーターに引用符が追加されます。 たとえば、mr's Li"s のような文字列は 'mr\'s Li\"s' に変換されます。

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

Number

入力として数値を取ります。 これを使用する例を次に示します。

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

ドロップダウン リスト

クエリの実行時に使用できるパラメーター値の範囲を制限するには、ドロップダウン リスト タイプのパラメーターを使用します。 たとえば、SELECT * FROM users WHERE name='{{ dropdown_param }}' などです。 パラメーター設定パネルから選択すると、テキスト ボックスが表示されます。ここに、使用できる値を 1 つずつ改行で区切って入力します。 ドロップダウン リストはテキスト パラメーターです。 ドロップダウン リストで日付または日付と時刻を使用するには、データ ソースで要求される形式でそれらを入力します。 文字列はエスケープされません。 単一値または複数値のドロップダウンから選択できます。

• 単一値: パラメーターを囲む単一引用符が必要です。
• 複数値: [複数の値を許可] オプションを切り替えます。 [引用符] ドロップダウンで、パラメーターを入力したままにする (引用符なし) か、パラメーターを一重引用符または二重引用符で囲むかを選びます。 引用符を選んだ場合は、パラメーターを引用符で囲む必要はありません。

クエリで IN キーワードを使うように WHERE 句を変更します。

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

パラメーター複数選択ウィジェットを使用すると、複数の値をデータベースに渡せます。 [引用符] パラメーターに [二重引用符] オプションを選択した場合、クエリには次の形式が反映されます: 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 には、日付とタイムスタンプの値をパラメーター化するためのオプションがいくつか用意されています。これには、時間範囲のパラメーター化を簡略化するためのオプションも含まれています。 さまざまな精度の 3 つのオプションから選択します。

Range パラメーター オプションを選択する場合は、.start と .end というサフィックスで指定された 2 つのパラメーターを作成します。 すべてのオプションで、文字列リテラルとしてパラメーターがクエリに渡されます。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 のような動的な値が表示されます。 これらの値は動的に更新されます。

ダッシュボードでのクエリ パラメーターの使用

必要に応じて、クエリではパラメーターまたは静的値を使用できます。 パラメーター化クエリに基づく視覚化がダッシュボードに追加されたら、次のいずれかを使用するように視覚化を構成できます。

• ウィジェット パラメーター

  ウィジェット パラメーターはダッシュボード内の 1 つの視覚化に固有であり、視覚化パネル内に表示されます。指定されたパラメーター値は視覚化の基になるクエリにのみ適用されます。

• ダッシュボード パラメーター

  ダッシュボード パラメーターは、複数の視覚化に適用できます。 パラメーター化クエリに基づく視覚化をダッシュボードに追加すると、既定でパラメーターがダッシュボード パラメーターとして追加されます。 ダッシュボード パラメーターは、ダッシュボード内の 1 つ以上の視覚化用に構成され、ダッシュボードの上部に表示されます。 ダッシュボード パラメーターに指定されたパラメーター値は、その特定のダッシュボード パラメーターを再利用する視覚化に適用されます。 ダッシュボードには複数のパラメーターを含めることができます。そのうち、視覚化に適用できるものと、できないものがあります。

• 静的な値

  静的値は、変更に対応するパラメーターの代わりに使われます。 静的な値を使用すると、パラメーターの代わりに値をハード コーディングできます。 これにより、以前にパラメーターが表示されていたダッシュボードまたはウィジェットから、パラメーターが "消える" ようになります。

パラメーター化クエリを含む視覚化を追加する場合は、適切な鉛筆アイコン をクリックして、視覚化クエリのパラメーターのタイトルとソースを選択できます。 キーワードと既定値を選択することもできます。 「パラメーターのプロパティ」を参照してください。

ダッシュボードに視覚化を追加した後は、ダッシュボード ウィジェットの右上にあるケバブ メニューをクリックし、[ウィジェット設定の変更] をクリックするとパラメーター マッピング インターフェイスにアクセスします。

パラメーターのプロパティ

• タイトル: ダッシュボードの値セレクターの横に表示される表示名。 既定値は Keyword パラメーターです。 編集するには、鉛筆アイコン をクリックします。 値セレクターが非表示のため、静的ダッシュボード パラメーターのタイトルは表示されません。 [値のソース] として [静的な値] を選んだ場合、[タイトル] フィールドは淡色表示されます。

• キーワード: 基になるクエリのこのパラメーターの文字列リテラル。 これは、ダッシュボードが予期した結果を返さない場合、デバッグに役立ちます。

• 既定値: 他の値が指定されない場合に使用される値です。 クエリ画面からこれを変更するには、目的のパラメーター値でクエリを実行して [保存] ボタンをクリックします。

• 値のソース: パラメーター値のソース。 ソースを選択するには鉛筆アイコン をクリックします。

  • 新しいダッシュボード パラメーター: 新しいダッシュボード レベルのパラメーターを作成します。 これにより、ダッシュボードの 1 か所にパラメーター値を設定し、複数の視覚化にマップできます。
  • 既存のダッシュボード パラメーター: パラメーターを既存のダッシュボード パラメーターにマップします。 既存のダッシュボード パラメーターを指定する必要があります。
  • ウィジェット パラメーター: ダッシュボード ウィジェット内に値セレクターを表示します。 これは、ウィジェット間で共有されない 1 回きりのパラメーターの場合に便利です。
  • 静的な値: 他のウィジェットで使用される値に関係なく、ウィジェットの静的な値を選択します。 静的にマップされたパラメーター値では、ダッシュボードのどこにも値セレクターは表示されず、画面がよりコンパクトになります。 これにより、特定のパラメーターが頻繁に変更されないと予想される場合は、ダッシュボードのユーザー インターフェイスを煩雑にすることなく、クエリ パラメーターの柔軟性を活用することができます。

  

よくあるご質問 (FAQ)

• 1 つのクエリで同じパラメーターを複数回再利用できますか?
• 1 つのクエリで複数のパラメーターを使用できますか?

1 つのクエリで同じパラメーターを複数回再利用できますか?

はい。 中かっこ内で同じ識別子を使用します。 この例では、 {{org_id}} パラメーターを 2 回使用しています。

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

1 つのクエリで複数のパラメーターを使用できますか?

はい。 各パラメーターに一意の名前を使用します。 この例では、 {{org_id}} と {{start_date}} という 2 つのパラメーターを使用しています。

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