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

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

Query パラメーターを使用すると、ランタイムに変数値を挿入することで、クエリをより動的で柔軟にすることができます。クエリに特定の値をハードコーディングする代わりに、パラメーターを定義してデータをフィルター処理したり、ユーザー入力に基づいて出力を変更したりできます。このアプローチにより、クエリの再利用が向上し、SQL インジェクションが防止されてセキュリティが強化され、さまざまなデータシナリオのより効率的な処理が可能になります。

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

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

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

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

名前付きパラメーターが SQL クエリに追加されます。ウィジェットが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 クエリのマーカーを編集します。
- タイトル : ウィジェットの上に表示されるタイトル。デフォルトでは、タイトルはキーワードと同じです。
- タイプ : サポートされているタイプは、テキスト、数値、ドロップダウンリスト、日付、日付と時刻、および日付と時刻(秒付き)です。デフォルトは [テキスト] です。
ダイアログで、[ タイプ ]を [数値 ]に変更します。
パラメーターウィジェットに数値を入力し、[ 変更の適用 ] をクリックします。
[保存 ] をクリックしてクエリを保存します。

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

次の例は、パラメーターの一般的な使用例を示しています。

日付を挿入する

次の例には、クエリ結果を特定の日付以降のレコードに制限する Date パラメーターが含まれています。

SQL

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 フィールドが指定されたパラメーター値より大きいレコードに結果を制限する Number パラメーターが含まれています。

SQL

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関数と共に使用して、クエリのしきい値を指定します。パラメーターの値は、クエリで使用されるテーブルの列名である必要があります。

SQL

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

データベースオブジェクトの挿入

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

SQL

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

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

Concatenate multiple パラメーター

他の SQL 関数にパラメーターを含めることができます。この例では、ビューアが従業員の肩書きと番号 ID を選択できるようにします。クエリでは、 format_string 関数を使用して 2 つの文字列を連結し、一致する行をフィルター処理します。 format_string関数を参照してください。

SQL

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 が返されます。

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

インターバルを作成する

INTERVAL 型は時間の範囲を表し、時間ベースの算術演算と演算を実行できます。次の例では、 CAST 関数を使用して、パラメーターを間隔タイプとしてキャストします。結果の INTERVAL 値は、時間ベースの計算やクエリでのフィルタリングに使用できます。

詳細と構文については、INTERVAL typeを参照してください。

SQL
SELECT CAST(:param AS INTERVAL MINUTE)

日付範囲を追加する

次の例は、パラメータ化された日付範囲を追加して、特定の期間のレコードを選択する方法を示しています。

SQL
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 句で使用されます。

SQL
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。

SQL

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

注記

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

構文の変更

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

パラメーターの使用例	Mustache パラメーターの構文	名前付きパラメーターマーカーの構文
指定した日付より前のデータのみをロードする	`WHERE date_field < '{{date_param}}'` date パラメーターと中かっこを引用符で囲む必要があります。	`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, , ）完全な例については、「複数のパラメーターの連結」を参照してください。
インターバルを作成してください	`SELECT INTERVAL {{p}} MINUTE`	`SELECT CAST(format_string("INTERVAL '%s' MINUTE", :param) AS INTERVAL MINUTE)`

Mustache パラメーターの構文

important

次のセクションは、SQL エディタでのみ使用できるクエリ構文に適用されます。 Databricksつまり、この構文を使用してクエリをコピーして、ノートブックやAI/BI dashboard データセットエディターなどの他のインターフェイスに貼り付ける場合は、エラーなしで実行する前に、名前付きパラメーターマーカーを使用するようにクエリを手動で調整する必要があります。

SQL エディターでは、二重中括弧 {{ }} で囲まれた文字列はクエリパラメーターとして扱われます。結果ウィンドウの上にウィジェットが表示され、パラメーター値を設定します。 Databricks では一般的に名前付きパラメーターマーカーの使用が推奨されますが、一部の機能は mustache パラメーター構文を使用してのみサポートされます。

次の機能には、mustache パラメーター構文を使用します。

従来のダッシュボードフィルター
Multiple value ドロップダウンパラメーター
クエリベースのドロップダウンパラメーター

口ひげパラメーターを追加する

「 Cmd + I」と入力します。パラメーターがテキストキャレットに挿入され、[ パラメーターの追加 ] ダイアログが表示されます。
- キーワード : クエリ内のパラメーターを表すキーワード。
- タイトル : ウィジェットの上に表示されるタイトル。デフォルトでは、タイトルはキーワードと同じです。
- タイプ : サポートされているタイプは、テキスト、数値、日付、日付と時刻、日付と時刻 (秒付き)、ドロップダウンリスト、およびクエリベースのドロップダウンリストです。デフォルトは [テキスト] です。
キーワードを入力し、必要に応じてタイトルを上書きし、パラメーターの種類を選択します。
[パラメーターの追加 ] をクリックします。
パラメーターウィジェットで、パラメーター値を設定します。
「 変更の適用」 をクリックします。
[ 保存 ]をクリックします。

または、二重中括弧 {{ }} を入力し、パラメーターウィジェットの近くにある歯車アイコンをクリックして設定を編集します。

別のパラメーター値でクエリを再実行するには、ウィジェットに値を入力して [ Apply Changes] をクリックします。

クエリパラメーターの編集

パラメーターを編集するには、パラメーターウィジェットの横にある歯車アイコンをクリックします。クエリを所有していないユーザーがパラメーターを変更できないようにするには、[ 結果のみを表示 ] をクリックします。 <Keyword> パラメーターダイアログが表示されます。

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

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

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

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

テキスト

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

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

数

数値を入力として受け取ります。これの使用例は次のとおりです

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

ドロップダウンリスト

クエリの実行時に使用可能なパラメーター値のスコープを制限するには、 ドロップダウン List パラメーターの種類を使用します。例としては、 SELECT * FROM users WHERE name='{{ dropdown_param }}'があります。パラメーター設定パネルから選択すると、許可された値を入力するテキストボックスが表示され、各値は改行で区切られます。ドロップダウンリストは text パラメーターです。ドロップダウンリストで日付または日付と時刻を使用するには、データソースに必要な形式で入力します。文字列はエスケープされません。単一値または複数値のドロップダウンから選択できます。

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

WHERE句を変更して、クエリで IN キーワードを使用します。

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

パラメーター複数選択ウィジェットを使用すると、複数の値をデータベースに渡すことができます。 Quotation パラメーターに 二重引用符 オプションを選択した場合、クエリには次の形式が反映されます。 WHERE IN ("value1", "value2", "value3")

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

クエリの結果を入力として受け取ります。これは、 ドロップダウン List パラメーターと同じ動作をします。 Databricks SQL ドロップダウンリストクエリを別のクエリの入力として使用するには、保存する必要があります。

設定パネルの [タイプ ]の下にある [クエリベースのドロップダウンリスト ]をクリックします。
「クエリ 」フィールドをクリックし、クエリを選択します。ターゲットクエリが大量のレコードを返す場合、パフォーマンスは低下します。

ターゲットクエリが複数の列を返す場合、Databricks SQL は 最初の 列を使用します。ターゲットクエリが name 列と value 列を返す場合、Databricks SQL はパラメーター選択ウィジェットに name 列を設定しますが、クエリは関連付けられた valueで実行します。

たとえば、次のクエリがテーブル内のデータを返すとします。

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

値	name
1001	ジョン・スミス
1002	ジェーン・ドウ
1003	ボビーテーブル

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

日付と時刻

Databricks には、時間範囲のパラメーター化を簡略化するオプションなど、日付とタイムスタンプの値をパラメーター化するためのオプションがいくつかあります。精度の異なる 3 つのオプションから選択します。

オプション	精度	タイプ
日付	日	`DATE`
日時	分	`TIMESTAMP`
日時 (秒を含める)	秒	`TIMESTAMP`

Range パラメーターオプションを選択する場合は、.start と .end の接尾辞で指定される 2 つのパラメーターを作成します。すべてのオプションは、パラメーターを文字列リテラルとしてクエリに渡します。Databricks では、日付と時刻の値を単一引用符 (') で囲む必要があります。例えば：

SQL
-- 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 パラメーターはカレンダー選択インターフェイスを使用し、デフォルトは現在の日付と時刻を使用します。

注記

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

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

クエリに日付または日付範囲パラメーターを追加すると、選択ウィジェットに青い稲妻アイコンが表示されます。クリックすると、 today、 yesterday、 this week、 last week、 last month、 last yearなどの動的な値が表示されます。これらの値は動的に更新されます。

important

動的な日付と日付範囲は、スケジュールされたクエリと互換性がありません。

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

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

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

Widget パラメーターは、ダッシュボード内の 1 つのビジュアライゼーションに固有で、ビジュアライゼーションパネルに表示され、指定されたパラメーター値は、ビジュアライゼーションの基になるクエリにのみ適用されます。
Dashboard パラメーター

ダッシュボードパラメーターは、複数のビジュアリゼーションに適用できます。パラメーター化されたクエリに基づくビジュアリゼーションをダッシュボードに追加すると、そのパラメーターはデフォルトでダッシュボードパラメーターとして追加されます。ダッシュボードパラメーターは、ダッシュボード内の 1 つ以上のビジュアリゼーションに対して構成され、ダッシュボードの上部に表示されます。ダッシュボードパラメーターに指定されたパラメーター値は、その特定のダッシュボードパラメーターを再利用するビジュアリゼーションに適用されます。ダッシュボードには複数のパラメーターを含めることができ、各パラメーターは一部のビジュアリゼーションに適用し、他のビジュアリゼーションには適用できません。
静的な値

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

パラメーター化されたクエリを含むビジュアリゼーションを追加する場合、適切な鉛筆アイコンをクリックして、ビジュアリゼーションクエリのパラメーターのタイトルとソースを選択できます。キーワードとデフォルト値を選択することもできます。「パラメーターのプロパティ」を参照してください。

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

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

タイトル : ダッシュボードの値セレクターの横に表示される表示名。 It デフォルト to the パラメーター Keyword . 編集するには、鉛筆アイコンをクリックします。静的ダッシュボードパラメーターのタイトルは、値セレクターが非表示になっているため表示されません。 [Value Source ] として [Static value ] を選択すると、[ Title (タイトル )] フィールドはグレー表示されます。
キーワード : 基になるクエリのこのパラメーターの文字列リテラル。これは、ダッシュボードが期待した結果を返さない場合にデバッグする場合に便利です。
デフォルト値 : 他の値が指定されていない場合に使用される値。クエリ画面からこれを変更するには、目的のパラメーター値でクエリを実行し、[ 保存 ] ボタンをクリックします。
Value ソース : パラメーター値のソース。鉛筆アイコンをクリックして、ソースを選択します。
- 新しいダッシュボードパラメーター : 新しいダッシュボードレベルのパラメーターを作成します。これにより、ダッシュボード上の 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}}'

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

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

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

日付を挿入する​

数値の挿入​

フィールド名の挿入​

データベース オブジェクトの挿入​

Concatenate multiple パラメーター​

JSON 文字列の操作​

インターバルを作成する​

日付範囲を追加する​

日、月、または年によるロールアップのパラメーター化​

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

構文の変更​

Mustache パラメーターの構文​

口ひげパラメーターを追加する​

クエリ パラメーターの編集​

クエリ パラメーターを削除する​

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

クエリ パラメーターの型​

テキスト​

数​

ドロップダウンリスト​

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

日付と時刻​

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

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

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

よくある質問(FAQ)​

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

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