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

[アーティクル]
11/14/2024

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

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

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

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

SQL エディター
ノートブック
AI/BI ダッシュボードデータセットエディター
AI/BI Genie スペース (パブリックプレビュー)

コロンの後にパラメーター名 (:parameter_name など) を入力して、SQL クエリにパラメーターを挿入します。クエリに名前付きパラメーターマーカーを含めると、ウィジェットが UI に表示されます。ウィジェットを使用して、パラメーターの型と名前を編集できます。

名前付きパラメーターが SQL クエリに追加されます。SQL エディターの下にウィジェットが表示されます

名前付きパラメーターマーカーをクエリに追加する

この例では、次のクエリにパラメーターマーカーを追加します。


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

このクエリは、5 ドル未満の料金のみを含むデータセットを返します。ハードコーディングされた値 (5) の代わりにパラメーターを使用するようにクエリを編集するには、次の手順を使用します。

クエリから数値 5 を削除します。
コロン (:) の後に文字列 fare_parameter を入力します。更新されたクエリの最後の行は、fare_amount < :fare_parameter になっているはずです。
パラメーターウィジェットの近くにある歯車アイコンをクリックします。ダイアログには、次のフィールドが表示されます。
- キーワード: クエリ内のパラメーターを表すキーワード。このフィールドは編集できません。キーワードを変更するには、SQL クエリでマーカーを編集します。
- タイトル: ウィジェットの上に表示されるタイトル。既定では、タイトルはキーワードと同じです。
- 型: サポートされる型は、テキスト、数値、ドロップダウンリスト、日付、日付と時刻、および日付と時刻 (秒を含む) です。既定値は Text です。
ダイアログで、型を [数値] に変更します。
パラメーターウィジェットに数値を入力し、[変更を適用] をクリックします。
[保存] をクリックしてクエリを保存します。

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

次の例では、パラメーターの一般的なユースケースをいくつか示します。

日付を挿入する

次の例には、クエリ結果を特定の日付より後のレコードに制限する 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関数は、DAY、MONTH、YEARなどの:date_granularityパラメーター値に基づいて、tpep_pickup_datetime値を切り捨てます。切り捨てられた日付は、 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関数は、trips テーブルのdropoff_zip値が、list_parameterとして渡された値の配列に含まれているかどうかを確認します。


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

Note

この例は、文字列値に対して機能します。整数の一覧など、他のデータ型のクエリを変更するには、文字列値を目的のデータ型に変換するCAST操作でTRANSFORM操作をラップします。

構文の変更

次の表は、パラメーターの一般的な使用例、元の Databricks SQL mustache 構文、名前付きパラメーターマーカー構文を使用した同等の構文を示しています。

パラメーターの使用例	Mustache パラメーター構文	名前付きパラメーターマーカーの構文
指定された日付より前のデータのみを読み込む	`WHERE date_field < '{{date_param}}'` 日付パラメーターと中かっこを引用符で囲む必要があります。	`WHERE date_field < :date_param`
指定された数値より小さいデータのみを読み込む	`WHERE price < {{max_price}}`	`WHERE price < :max_price`
2 つの文字列を比較します。	`WHERE region = {{region_param}}`	`WHERE region = :region_param`
クエリで使用されるテーブルを指定する	`SELECT * FROM {{table_name}}`	`SELECT * FROM IDENTIFIER(:table)` ユーザーは、このパラメーターを入力する際、完全な 3 レベルの名前空間を使用してテーブルを識別する必要があります。
クエリで使用されるカタログ、スキーマ、テーブルを個別に指定する	`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 エディターでのみ使用できるクエリ構文に適用されます。つまり、ノートブックや AI/BI ダッシュボードデータセットエディターなどの他の Azure Databricks インターフェイスに、この構文を使用してクエリをコピーして貼り付ける場合、クエリをエラーなしで実行するには、名前付きパラメーターマーカーを使用するようにクエリを手動で調整する必要があります。

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

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 ドロップダウンリストクエリを別のクエリの入力として使うには、保存する必要があります。

設定パネルの [種類] の下にある [クエリベースのドロップダウンリスト] をクリックします。
[クエリ] フィールドをクリックし、クエリを選択します。ターゲットクエリから多数のレコードが返された場合、パフォーマンスが低下します。

ターゲットクエリが複数の列を返す場合、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 Tables

Azure Databricks でクエリが実行されると、データベースに渡される値は 1001、1002、または 1003 になります。

日付と時刻

Azure Databricks には、日付とタイムスタンプの値をパラメーター化するためのオプションがいくつか用意されています。これには、時間範囲のパラメーター化を簡略化するためのオプションも含まれています。さまざまな精度の 3 つのオプションから選択します。

オプション	Precision	Type
日付	day	`DATE`
日付と時刻	分	`TIMESTAMP`
日付と時刻 (秒付き)	秒	`TIMESTAMP`

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

日付パラメーターはカレンダー選択インターフェイスを使い、既定値は現在の日付と時刻です。

Note

Date Range パラメーターは、DATE 型の列に対してのみ正しい結果を返します。 TIMESTAMPの列のには、日付と時間の範囲オプションのいずれかを使用します。

動的な日付と日付範囲の値

日付または日付範囲パラメーターをクエリに追加すると、選択ウィジェットに青い稲妻アイコンが表示されます。これをクリックすると、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}}'

次の方法で共有

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

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

名前付きパラメーターマーカーをクエリに追加する

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

日付を挿入する

数値を挿入する

フィールド名を挿入する

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

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

JSON 文字列の使用

間隔を作成する

日付範囲を追加する

日、月、または年別にロールアップをパラメーター化する

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

構文の変更

Mustache パラメーター構文

mustache パラメーターを追加する

クエリパラメーターを編集する。

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

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

クエリパラメーターの型

Text

Number

日付と時刻

動的な日付と日付範囲の値

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

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

よくあるご質問 (FAQ)

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

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

フィードバック

その他のリソース

次の方法で共有

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

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

名前付きパラメーター マーカーをクエリに追加する

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

日付を挿入する

数値を挿入する

フィールド名を挿入する

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

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

JSON 文字列の使用

間隔を作成する

日付範囲を追加する

日、月、または年別にロールアップをパラメーター化する

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

構文の変更

Mustache パラメーター構文

mustache パラメーターを追加する

クエリ パラメーターを編集する。

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

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

クエリ パラメーターの型

Text

Number

ドロップダウン リスト

クエリベースのドロップダウン リスト

日付と時刻

動的な日付と日付範囲の値

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

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

よくあるご質問 (FAQ)

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

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

フィードバック

その他のリソース

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

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

名前付きパラメーターマーカーをクエリに追加する

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

クエリパラメーターを編集する。

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

クエリパラメーターの型

ドロップダウンリスト

クエリベースのドロップダウンリスト

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