クエリパラメータを利用すると、クエリ自体を変更せずにパラメータの値の変更のみで実行クエリを変更できるようになります。

クエリパラメータの利用方法

パラメータエディタ画面にてパラメータの定義を行い、SQL文内にパラメータを設定します。
パラメータ設定画面にはパラメータの定義内容に沿って、入力フォームが自動的に表示されます。

クエリ実行時にはフォームに値を入力し、SQL文内のパラメータを入力値に置き換え、クエリを実行します。

📘

クエリコレクション内でのパラメータの利用について

クエリコレクションでのクエリは全てクエリパラメータを利用したクエリになります。
パラメータ設定タブで各フォームの値を入力し、クエリを実行することができます。

パラメータエディタ

YAML1.2 をサポートしています。

記法のフォーマット例

parameter1:
  type: number
  label: 数値パラメータ
  default: 0
parameter2:
  type: text
  label: 文字列パラメータ
  default: 文字列
parameter3:
  type: period
  label: 期間パラメータ
  default:
    - 20190101
    - 20190201

パラメータ内のフィールド

• type : パラメータの種類を指定します。 (必須)
• label : パラメータ設定フォーム上の表示名を指定します。
• default : パラメータ未設定時のデフォルト値を指定します。
• value: パラメータ設定のフォームに入力した値が挿入されます。
• options : 表示名、デフォルト値以外の指定を行います。 (パラメータの種類に応じて異なります)

🚧

defaultとvalueの扱い

defaultとvalueの両方が定義されている場合、valueの値が参照されます。

パラメータの種類

• Number : 整数型のパラメータを指定します。　
• Text : 文字列型のパラメータを指定します。
• Period : 開始日、終了日を表す期間のパラメータを指定します。
• Select : フォームの選択肢から単一の値をパラメータに指定します。
• Multiselect : フォームの選択肢から複数の値をパラメータに指定します。

SQL内でのパラメータ参照

定義したパラメータはSQL 内で{{ }}タグ内にパラメータ名を指定することで参照可能になります。 (例 : {{parameter_name}})
Period 、Multiselect については複数の値がパラメータに格納されるため、参照方法が異なります。

🚧

karte_eventテーブル範囲指定時のパラメータ指定について

karte_eventテーブル範囲の指定にパラメータを使用する場合は{{ }}タグで囲む必要はありません。
(クエリv1の例 : {{ karte_event(period[0], period[1]) }} )
(クエリv2の例 : {% karte_event period[0], period[1] %} )

各パラメータの定義内容・参照方法

Number

整数の値を持つパラメータです。
type フィールドに number を指定します。
default フィールド内にデフォルト値を指定可能です。
value: パラメータ設定のフォームに入力した値が挿入されます。
options 内の min 、max フィールドに入力フォーム上の最小・最大値を指定可能です。

param_number:
  type: number
  label: 整数型パラメータ
  default: 1
  options:
    min: 1
    max: 10

select {{param_number}}

select {{param_number}}

Text

文字列を持つパラメータです。
type フィールドに text を指定します。
default フィールド内にデフォルト値を指定可能です。
value: パラメータ設定のフォームに入力した値が挿入されます。

param_text:
  type: text
  label: 文字列型パラメータ
  default: sample

select {{param_text}}

select '{{param_text}}'

Period

開始日と終了日を絶対日付、あるいは相対日付で指定するパラメータです。
type: フィールドに period を指定します。
default: フィールドには絶対日付形式、または相対日付形式でのデフォルト値が指定可能です。
value: パラメータ設定のフォームに入力した値が挿入されます。
options.format: 日付のフォーマットを指定できます。指定フォーマットは YYYYMMDD | YYYY-MM-DD です。

SQL内のパラメータは、開始日と終了日を持つ配列のデータ構造となります。
開始日と終了日は文字列(options.format を指定してない場合、 YYYYMMDD)となります。
開始日を参照する際は param_period[0] 、終了日を参照する際は param_period[1] を指定ください。

デフォルト値 (絶対日付形式)

default フィールドに開始日と終了日を配列として設定します。

param_period:
  type: period
  label: 期間型パラメータ
  default: 
    - 20190701
    - 20190702
  options:
    format: 'YYYYMMDD'

select * from　{% karte_event param_period[0], param_period[1] %}

select * from {{ karte_event(param_period[0], param_period[1]) }}

デフォルト値 (相対日付形式)

default フィールドに現在の日付から相対的な日付を指定します。
この際、現在の日付はUTCを基準としています。
指定フォーマットは {past | future | within} [digit] {day | days | week | weeks | month | months | year | years} です。

param_period:
  type: period
  label: 期間型パラメータ
  default: past 1 day
  options:
    format: 'YYYY-MM-DD'

select * from　{% karte_event param_period[0], param_period[1] %}

select * from {{ karte_event(param_period[0], param_period[1]) }}

• 例: 2023年02月10日にクエリ実行した場合
  • past 1 day → 2023年02月09日〜2023年02月10日
  • future 1 day → 2023年02月10日〜2023年02月11日
  • within 1 day → 2023年02月09日〜2023年02月11日

Select

入力フォーム上で選択肢の中から単一の値をパラメータとして指定できます。
type フィールドに select を指定します。
default フィールド内にデフォルト値を指定可能です。
value: パラメータ設定のフォームに入力した値が挿入されます。
options フィールドには入力フォーム上での選択肢を指定します。

options フィールドには静的な選択肢だけではなく、動的な選択肢も指定できます。

静的な選択肢の指定方法

options フィールド内の labels 、 values フィールドに対して選択肢の表示名、値を設定します。
デフォルト値を指定する場合は values 内の値を指定する必要があります。

選択肢の表示名と値を区別する必要がない場合は以下のように配列での指定が可能です。

options:
 - value1
 - value2
 - value3

param_select:
  type: select
  label: 選択式パラメータ
  default: 'value1'
  options:
    labels:
      - label1
      - label2
      - label3
    values:
      - value1
      - value2
      - value3

select {{param_select}}

select '{{param_select}}'

動的な選択肢の指定方法

options フィールドに特定の文字列を指定すると、KARTEの別プロダクトの設定値を動的に選択肢として取得します。

例 : 接客サービス一覧から接客サービスのIDを指定するパラメータ

campaign_id:
  type: select
  label: 選択式パラメータ
  options: campaign

テーブルのデータを使った選択肢の指定方法

テーブル内に保存されているデータから選択肢を動的に生成することができます。

select_from_table:
  type: select
  label: 選択式パラメータ
  options:
    source: table
    table_id: project_id.dataset_id.table_id  # 詳細は下記設定例を参照
    value_field: value_field  # 詳細は下記設定例を参照
    label_field: label_field  # 詳細は下記設定例を参照
    group_fields:  # 詳細は下記設定例を参照
      - group_field1
      - group_field2
   sort_field: sort_field
   sort_direction: ASC

例： country_code, country_name, continent_name というフィールドを持つ、 country_master というテーブルから country_code をcountry_nameの降順で選択する場合

Multiselect

入力フォーム上で選択肢の中から複数の値をパラメータとして指定できます。
type フィールドに multiselect を指定します。
default フィールドには配列で複数の値を指定が可能です。
value: パラメータ設定のフォームに入力した値が挿入されます。
options フィールドについては Select パラメータの場合と同じ指定方法です。
動的な選択肢も同様に指定が可能です。

campaign_ids:
  type: multiselect
  label: 対象の接客サービス
  options: campaign

MultiselectパラメータのSQL内での利用例

• クエリv2の場合、Multiselectパラメータは'A','B'のように文字列のカンマ区切り値として出力されます
• SQL内では、[]で囲むことで配列リテラルになります
• それをUNNESTで分解することで、WHERE句の条件に使うことができます

SELECT
  campaign_id
  , some_field
FROM some_table
WHERE campaign_id IN UNNEST([{{campaign_ids}}])

プリセットのパラメータ

以下の値については、プリセットされており定義しなくても使うことができます。

SELECT * FROM `prd-karte-per-client.item_master_{{api_key|safe_param}}.item_master`