クエリパラメータを利用する

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

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

パラメータエディタ画面にてパラメータの定義を行い、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}})
PeriodMultiselect については複数の値がパラメータに格納されるため、参照方法が異なります。

🚧

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

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

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

Number

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

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: パラメータ設定のフォームに入力した値が挿入されます。

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

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

default フィールドに開始日と終了日を配列として設定します。
日付のフォーマットは YYYYMMDD 形式です。

param_period:
  type: period
  label: 期間型パラメータ
  default: 
    - 20190701
    - 20190702
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
select * from {% karte_event param_period[0], param_period[1] %}
select * from {{ karte_event(param_period[0], param_period[1]) }}

Select

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

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

静的な選択肢の指定方法

options フィールド内の labelsvalues フィールドに対して選択肢の表示名、値を設定します。
デフォルト値を指定する場合は 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の別プロダクトの設定値を動的に選択肢として取得します。

options依存プロダクトプルダウン表示設定される変数値サンプル値
eventKARTE Insightイベント表示名イベント名view
shorten_segmentKARTE Insightセグメント名短縮セグメントIDkBE, 1a2C
segmentKARTE Insightセグメント名セグメントID5874891d915b2a921d45e5aa
campaignKARTE Action接客サービス名接客サービスID(campaign_id)5f4df148a22b8300115f7e61

例 : 接客サービス一覧から接客サービスの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_codecountry_nameの降順で選択する場合

設定項目設定する内容必須設定例
table_id選択肢を生成する際に参照するテーブルを指定します。データセットの「テーブル情報」タブにある「プロジェクトID」「データセットID」「テーブルID」を . で連結して指定してください。
レコード数が1,000以下のテーブルのみ指定できます。
Yesprd-karte-per-client.dataset_name.country_master
value_fieldtable_id で指定したテーブル内のフィールド名を指定してください。
STRING、INTEGERもしくはFLOATのフィールドのみ指定可能です。
Yescountry_code
label_field選択肢の表示名を変更できます。table_id で指定したテーブル内のフィールド名を指定してください。
STRING、INTEGERもしくはFLOATのフィールドのみ指定可能です。
-country_name
group_fields選択肢をグループ化して表示できます。table_id で指定したテーブル内のフィールド名を配列形式で指定してください。
STRING、INTEGERもしくはFLOATのフィールドのみ指定可能です。
-- continent_name
sort_field選択肢を表示する際にソートの基準となるフィールド名をして指定してください。
STRING、INTEGERもしくはFLOATのフィールドのみ指定可能です。
-country_name
sort_directionsort_fieldで指定したフィールド名を基準にして、昇順・降順のどちらでソートをするか指定します。
ASCもしくはDESCのみ指定可能です。
何も指定がない場合には昇順でソートが行われます。
-DESC

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}}])

プリセットのパラメータ

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

パラメータ名説明
{{api_key}}プロジェクトのAPIキー
SELECT * FROM `prd-karte-per-client.item_master_{{api_key|safe_param}}.item_master`