Query parameters | Databricks on AWS [2022/7/26時点]の翻訳です。

本書は抄訳であり内容の正確性を保証するものではありません。正確な内容に関しては原文を参照ください。

クエリーパラメーターを用いることで、クエリーの実行時に値を置き換えることができます。{{ }}の間の任意の文字列は、クエリーパラメーターとして取り扱われます。パラメーターの値を設定した結果ペインの上にウィジェットが表示されます。

クエリーパラメーターの追加

Cmd + Pを押します。テキストキャレットにパラメーターが挿入され、Add Parameterダイアログが表示されます。
- Keyword: クエリーでパラメーターを表現するキーワード。
- Title: ウィジェットの上に表示されるタイトル。デフォルトではキーワードと同じものになります。
- Type: サポートされているタイプは、テキスト、数値、日付と時刻、日付と時刻(秒を含む)、ドロップダウンリスト、クエリーベースのドロップダウンリストです。デフォルトはテキストです。
キーワード、オプションとしてタイトルを変更し、パラメーターのタイプを選択します。
Add Parameterをクリックします。
パラメーターウィジェットで、パラメーターの値を設定します。
Apply Changesをクリックします。
Saveをクリックします。

あるいは、マニュアルで{{ }}を入力し、設定を変更するためにパラメーターウィジェットの隣にあるギアアイコンをクリックすることもできます。

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

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

パラメーターを編集するには、パラメーターウィジェットの隣のギアアイコンをクリックします。クエリーを所有していないユーザーによるパラメーターの変更を避けるためには、Show Results Onlyをクリックします。

<Keyword> パラメーターダイアログが表示されます。

パラメーターの順番の変更

パラメーターが表示される順番を変更するには、それぞれのパラメーターをクリックして希望の場所までドラッグします。

クエリーパラメーターのタイプ

テキスト

入力としてテキストを受け付けます。バックスラッシュ、シングルクオート、ダブルクオートはエスケープされ、Databricksこのパラメーターにクオーテーションマークを追加します。例えば、mr's Li"sのような文字列は'mr\'s Li\"s'に変換されます。使用例は以下のようになります。

SQL

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

数値

入力として数値を受け付けます。使用例は以下のようになります。

SQL

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

ドロップダウンリスト

クエリーを実行する際に利用できるパラメーターの値のスコープを限定するために、パラメータータイプDropdown Listを使用することができます。例はSELECT * FROM users WHERE name='{{ dropdown_param }}'のようなものになります。パラメーター設定パネルから選択すると、許容する値を入力するテキストボックスが表示され、それぞれの値は改行で区切られます。ドロップダウンリストはテキストパラメーターなので、日付や日付・時刻をドロップダウンリストで使用したい場合には、お使いのデータソースが要求するフォーマットで入力する必要があります。文字列はエスケープされません。シングルバリューとマルチバリューを選択することができます。

Single value: パラメーターの前後のシングルクオートが必要となります。
Multi-value: Allow multiple valuesオプションを切り替えます。Quotationドロップダウンでは、パラメーターをクオートするか、シングルクオート、ダブルクオートを使うかどうかを指定します。クオートを選択した場合、値の前後にクオートを追加する必要はありません。

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

SQL

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

複数選択のパラメーターウィジェットを用いることで、データベースに複数の値を渡すことができます。

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

入力としてクエリーの結果を受け取ります。ドロップダウンリストパラメーターと同じ振る舞いをします。

設定パネルのTypeでQuery Based Dropdown listをクリックします。
Queryフィールドをクリックしクエリーを選択します。ターゲットのクエリーが大量のレコードを返却する場合には、パフォーマンスが劣化する場合があります。

ターゲットのクエリーが1つより多いカラムを返却する際、Databricks SQLは最初のカラムを使用します。ターゲットのクエリーがnameとvalueカラムを返却する際、Databricks SQLはnameカラムでパラメーター選択ウィジェットを表示しますが、valueを用いてクエリーを実行します。

例えば、以下のクエリーを考えます。

SQL

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

これは以下のデータを返却します。

value	name
1001	John Smith
1002	Jane Doe
1003	Bobby Tables

ドロップダウンリストウィジェットは以下のようになります。

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

日付と時刻

Databricks SQLは期間のパラメーター化をシンプルにするオプションを含め、日付やタイムスタンプをパラメーター化する様々な選択肢を提供します。様々な精度に対して3つの選択肢から選択することができます。

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

Rangeパラメーターオプションを選択すると、.startと.endのサフィックスが割り当てられた2つのパラメーターを作成します。すべてのオプションは、クエリーに文字列リテラルを渡します。Databricks SQLでは、以下のように日付や時刻の値をシングルクオート(')でラップする必要があります。

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タイプに対して適切な結果のみを返却します。TIMESTAMPカラムに対しては、日付・時刻レンジオプションを使用してください。

動的な日付と期間

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

重要！
動的な日付、日付レンジはスケジューリングされたクエリーとは互換性がありません。

ダッシュボードにおけるクエリーパラメーターのマッピング

ダッシュボード内のクエリーパラメーターをコントロールすることができます。異なるウィジェットのパラメーターをリンクさせ、静的なパラメーターを設定したり、それぞれのウィジェットの値を別々に選択することができます。

パラメーターの値に依存するダッシュボードウィジェットを追加する際にパラメーターのマッピングを選択します。クエリーに含まれるそれぞれのパラメーターがParametersリストに表示されます。

また、ダッシュボードウィジェットの右上にあるをクリックし、Edit Widgetをクリックした際にパラメーターマッピングインタフェースにアクセスすることができます。パラメータープロパティは以下を表示します。

Title: ダッシュボードのセレクターの隣に表示される表示名です。デフォルトはパラメーターのキーワードです。編集するには鉛筆アイコンをクリックします。静的なダッシュボードパラメーターでは値のセレクターが非表示となっているのでタイトルは表示されません。Value SourceでStatic valueを選択した場合、Titleフィールドはグレーアウトされます。
Keyword: クエリーに含まれているこのパラメーターの文字列リテラルです。これはダッシュボードが期待した値を返さない際にデバッグするときに役立ちます。
Default Value: 他の値が指定されない場合にこの値が使用されます。クエリー画面からこれを変更するために、希望するパラメーターでクエリーを実行し、Saveボタンをクリックします。
Value Source: パラメーターの値のソースです。ソースを選択するには鉛筆アイコンをクリックします。
- New dashboard parameter: 新たにダッシュボードレベルのパラメーターを作成します。これによって、ダッシュボードのパラメーターの値を一箇所で設定し、複数のビジュアライゼーションにマッピングすることができます。
- Existing dashboard parameter: パラメーターを既存のダッシュボードパラメーターにマッピングします。どの既存ダッシュボードパラメーターにマッピングするのかを指定する必要があります。
- Widget parameter: ダッシュボードウィジェット内に値のセレクターを表示します。これは、ウィジェット間で共有されないワンオフのパラメーターを使う際に有用です。
- Static value: 他のウィジェットで使用される値に関係なく、ウィジェットに静的な値を選択します。静的にマッピングされたパラメーターの値は、よりコンパクトなダッシュボード上のセレクターには表示されません。これによって、特定のパラメーターが頻繁に変更されない場合、ダッシュボードのユーザーインタフェースを煩雑にすることなしにクエリーパラメーターの柔軟性を活用できるようになります。

FAQ

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

はい。カーリーブラケットで同じIDを使用することができます。この例では、{{org_id}}パラメーターを二回使っています。

SQL

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

一つのクエリーで複数のパラメーターを使えますか？

はい。それぞれのパラメーターにユニークな名前を使用できます。この例では、{{org_id}}と{{start_date}}を使用しています。

SQL

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

Databricks SQLのクエリーパラメーター