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

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

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

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

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

  • SQL エディタ

  • ノートブック

  • AI/BI ダッシュボードデータセットエディター

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

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

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

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

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 クエリのマーカーを編集します。

    • タイトル: ウィジェット上に表示されるタイトル。 デフォルトでは、タイトルはキーワードと同じです。

    • タイプ: サポートされているタイプは、テキスト、数値、ドロップダウンリスト、日付、日付と時刻、および日付と時刻(秒付き)です。 デフォルトは [テキスト] です。

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

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

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

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

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]

構文の変更

次の表は、パラメーターの一般的な使用例、元の 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, :area_code, :p hone_number)

完全な例については、「複数のパラメーターを連結する」を参照してください。

Mustache パラメーターの構文

重要

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

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

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

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

  1. Cmd + Iと入力します。パラメーターがテキストキャレットに挿入され、[ パラメーターの追加 ] ダイアログが表示されます。

    • キーワード: クエリー内のパラメーターを表すキーワード。

    • タイトル: ウィジェット上に表示されるタイトル。 デフォルトでは、タイトルはキーワードと同じです。

    • タイプ: サポートされているタイプは、テキスト、数値、日付、日付と時刻、日付と時刻(秒付き)、ドロップダウンリスト、およびクエリーベースのドロップダウンリストです。 デフォルトは [テキスト] です。

  2. キーワードを入力し、必要に応じてタイトルをオーバーライドして、パラメーター タイプを選択します。

  3. [ パラメーターの追加] をクリックします。

  4. パラメーター ウィジェットで、パラメーター値を設定します。

  5. [ 変更の適用] をクリックします。

  6. [保存]をクリックします。

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

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

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

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

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

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

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

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

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

テキスト

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

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

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

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

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

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

  1. 設定パネルの「 タイプ 」の下にある 「クエリー・ベースのドロップダウン・リスト」 をクリックします。

  2. [ クエリ ] フィールドをクリックし、クエリを選択します。 ターゲットクエリが多数のレコードを返すと、パフォーマンスが低下します。

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

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

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

名前

1001

ジョン・スミス

1002

ジェーン・ドウ

1003

ボビーテーブル

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

日付と時刻

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

オプション

精度

タイプ

日付

DATE

日付と時刻

TIMESTAMP

日付と時刻 (秒単位)

TIMESTAMP

[範囲パラメーター] オプションを選択する場合は、.start.end のサフィックスで指定された 2 つのパラメーターを作成します。すべてのオプションは、パラメーターを文字列リテラルとしてクエリに渡します。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 パラメーターは、カレンダー選択インターフェイスを使用し、既定値は現在の日付と時刻です。

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

動的な日付および日付範囲の値

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

重要

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

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

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

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

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

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

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

  • 静的な値

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

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

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

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

  • タイトル: ダッシュボードの値セレクタの横に表示される表示名。 パラメータ キーワードにデフォルト設定します。 編集するには、鉛筆アイコン 鉛筆アイコンをクリックします。 静的ダッシュボード パラメーターのタイトルは、値セレクターが非表示になっているため表示されません。 「値のソース」として「静的な値」を選択した場合、「タイトル」フィールドはグレー表示されます。

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

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

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

    • 新しいダッシュボード パラメーター: 新しいダッシュボード レベルのパラメーターを作成します。 これにより、ダッシュボードの 1 か所でパラメーター値を設定し、それを複数のビジュアリゼーションにマップできます。

    • 既存のダッシュボード パラメーター: パラメーターを既存のダッシュボード パラメーターにマップします。 既存のダッシュボード パラメーターを指定する必要があります。

    • ウィジェットパラメーター: ダッシュボードウィジェット内に値セレクターを表示します。 これは、ウィジェット間で共有されない 1 回限りのパラメーターに役立ちます。

    • 静的な値: 他のウィジェットで使用されている値に関係なく、ウィジェットの静的な値を選択します。 静的にマッピングされたパラメーター値は、ダッシュボードのどこにも値セレクターを表示せず、よりコンパクトになります。 これにより、特定のパラメーターが頻繁に変更されることが予想される場合に、ダッシュボード上のユーザー インターフェイスを乱雑にすることなく、クエリ パラメーターの柔軟性を活用できます。

    パラメーターマッピングの変更

よくある質問(FAQ)

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