パラメータ

動的なノートブックとレポートを作成する。

ノートブックでパラメータを使用することで、ノートブックとレポートを動的かつインタラクティブにすることができます。これにより、SQLに直接アクセスすることなくパラメータを通じて埋め込まれたSQLを制御し、必要に応じてカスタマイズされた出力を得ることができます。

主な機能

• ノートブックとレポートの動的化:

  • ノートブックとレポートをダイナミックかつインタラクティブにします。

• 様々な種類のパラメータ:

  • 多様なデータ型と関連する入力ウィジェットから選択できます。

• 2種類のスコープ:

  • ノートブック全体で使用するパラメータか、ページごとに使用するパラメータかを選択できます。

• パラメータの上書き:

  • SQLを連鎖させる際にパラメータを上書きすることで、きめ細かな制御が可能です。

使用方法

パラメータを作成すると、ノートブック内のSQLブロックで/param/{param_name}という構文を使用して参照できます。

例えば、param1という名前の文字列パラメータを作成し、値としてval1を入力した場合、以下のSQL：

SELECT /param/param1

は以下のように変換されます：

SELECT 'val1'

パラメータの種類

1. テキスト入力 (STRING):

シンプルな文字列入力フィールドです。

• フォーマット:

  • SQLではシングルクォート(')で囲まれます。

  • 例：'val1'に変換されます

• 空の場合:

  • ''

2. テキスト複数入力 (STRING[])

複数の文字列を入力するフィールドです。

• フォーマット:

  • SQLではシングルクォート(')で囲まれます。

  • 例：'val1', 'val2'に変換されます

• 空の場合:

  • ''

3. テキスト選択 (STRING):

単一の文字列値を選択するドロップダウンです。

• ソースオプション:

  • 固定リストまたはテーブル値またはクエリ結果

• フォーマット:

  • SQLではシングルクォート(')で囲まれます。

• 空の場合:

  • ''

4. テキスト複数選択 (STRING[])

複数の文字列値を選択するドロップダウンです。

• ソースオプション:

  • 固定リストまたはテーブル値またはクエリ結果

• フォーマット:

  • SQLではシングルクォート(')で囲まれます。

  • 例：'val1', 'val2'に変換されます

• 空の場合:

  • ''

5. 数値入力 (NUMBER):

シンプルな数値入力フィールドです。

• オプション:

  • min, max, steps (間隔)

• 空の場合:

  • 0

6. 数値選択 (NUMBER):

単一の数値を選択するドロップダウンです。

• ソースオプション:

  • 固定リストまたはテーブル値またはクエリ結果

• 空の場合:

  • 0

7. 日付入力 (DATE):

シンプルな日付入力フィールドです。カレンダーからの選択または日付の入力が可能です。

• オプション:

  • Unit:

    • Date, Week, Month, Year

  • Start day of week（カレンダーに表示される最初の曜日）:

    • Sun, Mon, Tue, Wed, Thu, Fri, Sat

  • Relative valid range（相対日付での有効範囲）:

    • Days before today：この制限より前の日付は選択不可になります。例えば、'90'と入力すると、90日前より前の日付が選択不可になります。

    • Days after today：この制限より後の日付は選択不可になります。例えば、'0'と入力すると翌日以降の日付が選択不可に、'-1'と入力すると今日以降の日付が選択不可になります。

  • Absolute valid range（絶対日付での有効範囲）:

    • Lower date：指定した日付より前の日付が選択不可になります。

    • Upper date:：指定した日付より後の日付が選択不可になります。

• フォーマット:

  • SQLではyyyy-MM-dd形式でクォート(')付きで展開されます。

  • 例：'2024-01-01'に変換されます

8. 日付範囲 ([DATE, DATE]) :

開始日と終了日をペアで指定します。

• オプション:

  • 日付入力（DATE）と同じ

• SQLでの参照構文:

  • 開始日：/param/param_name.StartDate

  • 終了日：/param/param_name.EndDate

9. チェックボックス (BOOLEAN):

シンプルな真偽値のチェックボックスです。

• フォーマット:

  • SQLではTRUE/FALSEとして展開されます。

パラメータのエスケープ: セキュリティと動的な識別子の生成のサポート

パラメータのコンテキストに基づいてSQLインジェクション保護と値のエスケープを処理します。このメカニズムはセキュリティを提供するだけでなく、テーブル名などの動的な識別子の生成も可能にします。

1. 通常のコンテキストでの文字列パラメータ

-- パラメータ値が user's "data" の場合
WHERE name = /param/str_param         -> WHERE name = 'user\'s \"data\"'

-- パラメータ値が O'Reilly's "Book" の場合
SELECT name = /param/company          -> SELECT name = 'O\'Reilly\'s \"Book\"'

• パラメータは自動的にシングルクオートで囲まれます。

• 値内のシングルクオートとダブルクオートの両方がバックスラッシュでエスケープされます。

2. 引用符付きコンテキストでの文字列パラメータ

-- ダブルクオート内（"と'の両方をエスケープ）
-- パラメータ値が user"s 'data' の場合
SELECT "/param/str_param" as alias    -> SELECT "user\"s \'data\'" as alias

-- シングルクオート内（"と'の両方をエスケープ）
-- パラメータ値が user"s 'data' の場合
SELECT '/param/str_param' as alias    -> SELECT 'user\"s \'data\'' as alias

-- バッククォート内（`のみをエスケープ）
-- パラメータ値が user"s 'data` の場合
SELECT `/param/str_param` as alias    -> SELECT `user"s 'data\`` as alias

• ダブルクオートまたはシングルクオート内:

  • ダブルクオートとシングルクオートの両方がバックスラッシュでエスケープされます

• バッククォート内:

  • バッククォートのみがバックスラッシュでエスケープされます

  • その他の引用符は変更されません

3. 日付パラメータの特別処理

-- 通常の日付パラメータ
SELECT /param/date_param as normal         -> SELECT '2024-10-02' as normal

-- 引用符内の日付は同じエスケープルールに従います
SELECT '/param/date_param' as single_quote -> SELECT '2024-10-02' as single_quote
SELECT "/param/date_param" as double_quote -> SELECT "2024-10-02" as double_quote

-- 特殊ケース: BigQueryでのテーブル名生成
-- BigQuery接続を使用する場合
SELECT * FROM `table_/param/date_param`    -> SELECT * FROM `table_20241002`

• 通常のコンテキスト: 日付は'YYYY-MM-DD'形式でフォーマットされます

• BigQueryでテーブル名に使用する場合: 日付は'YYYYMMDD'形式（ハイフンなし）でフォーマットされます

• 引用符のエスケープルールは文字列パラメータと同様に適用されます

動的な識別子の生成

このようなコンテキストに合わせた処理が可能なことにより、安全な値の処理に加えてダイナミックな識別子生成（テーブル名、列名）も可能になります：

-- 安全な値の処理
WHERE status = /param/status             -> WHERE status = 'active'

-- ダイナミックなテーブル名
SELECT * FROM `table_/param/table_suffix` -> SELECT * FROM `table_sales_2024`

-- ダイナミックなフィールド選択
SELECT "column_/param/field_type"         -> SELECT "column_revenue"

パラメータを引用符内に配置することで、テーブル名や列名などのSQL識別子を安全に生成することができます。

選択パラメータのソースオプション

選択パラメータでは、選択可能な値を生成するために3種類のソースオプションをサポートしています：

1. 固定リスト

固定の値から選択したい場合に使用します。

• 設定:

  • CSV Text:

    • CSV形式のテキストで選択肢を定義します。各項目は改行で区切ります。表示名を指定する場合は、同じ行にカンマ（,）で区切って記述します。

例：

value1,表示名1
value2,表示名2

2. テーブル値

特定のテーブルのフィールドから選択可能な値を生成したい場合に使用します。

• 設定:

  • Source Table：値を取得するテーブルを選択します。

  • Value Field：選択肢の値として使用するフィールドを指定します。

  • Label Field：表示ラベルとして使用するフィールドを指定します。

3. クエリ結果

指定したSQLクエリの結果に基づいて選択肢を生成したい場合に使用します。

• 設定:

  • Connection:

    • クエリに使用するデータベース接続を選択します。

  • SQL:

    • 選択肢の値を返すSQLクエリを入力します。クエリが複数のカラムを返す場合、最初のカラムが選択肢の値として、2番目のカラム（存在する場合）が表示ラベルとして使用されます。

選択肢用のSQLクエリは頻繁に実行されるため、パフォーマンスを最適化するために、遅いクエリやリソースを多く消費するクエリの使用は避けてください。

パラメータ用SQLクエリの実行タイミング

パラメータの選択肢にSQLクエリを使用する場合、以下のタイミングでクエリが実行されます：

1. ノートブックページが読み込まれたとき：

   • そのページで使用されているパラメータのSQLクエリはページの初期化時に実行されます

   • 現在のページで使用されていないパラメータは初期化されず、そのSQLクエリも実行されません

2. パラメータのドロップダウンが開かれたとき：

   • ドロップダウンメニューが最初に開かれたときにSQLクエリが実行されます

3. 検索フィールドに入力するとき：

   • 初回のクエリ実行後は、すでに取得したデータを使用してクライアント側でフィルタリングが行われます

   • キーストロークごとにSQLが再実行されることはありません

キャッシュの動作

パラメータ用のSQLクエリは、通常のノートブックSQLブロックと同じキャッシュメカニズムを使用します：

• 結果はバックエンドで最大24時間キャッシュされます

• 同一のクエリ（パラメータ値を含む）はキャッシュされた結果を使用します

• フロントエンドも現在のセッション中はメモリキャッシュを維持します

これにより、パラメータのドロップダウンが頻繁に開かれたとしても、実際のデータベースクエリはキャッシュ期間内に1回だけ実行され、パフォーマンスが大幅に向上します。

SQLクエリのキャッシュについての詳細は、クエリの実行とキャッシュを参照してください。

パラメータのスコープ

パラメータは2種類のスコープから作成できます：

• ノートブック共通

  • ノートブックのすべてのページで共有されるパラメータ

  • 値は各ページで保持されます

• ページ固有

  • 各ページに固有のパラメータ

ノートブック共通パラメータは、例えば、ドキュメントページのSQLから作成されたチャートなど、グリッドページ上のコンポーネントを同じパラメータで制御したい場合に使用できます。

ページ固有パラメータは、例えば、特定のページ以外に影響を与えたくない場合に使用できます。

パラメータの上書き

SQLを連鎖させる際に、パラメータの依存関係を切断したい場合があります。例えば：

• ページをまたいでSQLを連鎖させる際に、各ページで固有のパラメータを設定する。

• 同じベースSQLから派生した異なるSQLで異なるパラメータを使用する。

SQLブロックでSQLを参照している箇所にカーソルを合わせると表示されるポップアップで、パラメータを上書きできます。

ユーザー間でのパラメータ値と実行結果の同期

ノートブックのパラメータ値と実行結果はユーザー間で同期されます。ただし、ノートブックバージョンとレポートを使用することで、パラメータ値と実行結果の変更がユーザー間で同期されず、値を変更したユーザーにのみ変更が反映されるようになります。

詳細については、ノートブックバージョンを参照してください。