クエリv2を利用する

Suggest Edits

概要

クエリv2とは、従来のクエリ（以下「クエリv1」と記す）から、パラメータや KARTE Datahub がデフォルトで提供するデータテーブルの呼び出しルールを刷新した、新しいクエリの記法です。
クエリv2を利用することで、クエリへのパラメータの注入がより安全になり、クエリ作成者が意図しない形でパラメータが適用されることを防ぐことができます。また、クエリ内で Liquidテンプレートの if文 や for文 などの制御構文を使ってクエリを組み立てることができるようになります。

❗️

クエリv2への移行について

将来的に、クエリv1のサポートは廃止され、クエリv2への置き換えを予定しています。お手数をおかけして恐縮ですが、クエリv2への段階的な移行にご協力お願いいたします。

❗️

クエリv1を利用したダッシュボードの閲覧には「データの編集」権限が必要です

クエリv1には、クエリ作成者が意図しない形でパラメータが適用されるリスクがあります。
そのため、クエリv1を利用したダッシュボードの閲覧には「データの編集」権限が必要となっています。

「データの閲覧」権限のみを持つアカウントにダッシュボードの閲覧を許可するには、そのダッシュボードに含まれる全てのコンポーネントについて、紐付くクエリのバージョンがクエリv2になるようにしてください。

変更点

パラメータのエスケープ処理

パラメータを SQL 内で呼び出す際に、パラメータの type ごとに自動的にエスケープ処理が実行されるようになります。
Textパラメータを呼び出す際は、クエリv1では ' で文字列パラメータを囲う必要があったものが、クエリv2では自動的に ' で囲った形でパラメータが出力されるようになります。

SELECT '{{text}}' AS param1  /* SELECT  'text_value' AS param1 に展開 */

SELECT {{text}} AS param1  /* SELECT 'text_value' AS param1 に展開 */

Period、Select、Multiselectパラメータで文字列を出力する際も同様に ' で囲った形でパラメータが出力されるようになります。
Numberパラメータの場合は、エスケープ処理は行われないため、クエリv1とクエリv2で違いはありません。

また、クエリv1とクエリv2で、クエリパラメータの定義方法に変更はありません。

📘

正規表現の文字列をパラメータとして利用する

正規表現の文字列をパラメータ経由で渡す場合、raw_string フィルタを利用して、 {{ regexp_text | raw_string }} と書くことで、\ や ' といった文字列のエスケープ処理を回避することができます。
※ フィルタ内で文字列定数を定義する場合は、\によるエスケープが必要になります。
【例】 \w を出力する場合は {{'\\w' | raw_string}} と書く必要があります

🚧

参照先テーブルの指定方法に関する変更点

クエリv1ではFROM句で指定するテーブル名をパラメータ経由で指定できましたが、クエリv2ではパラメータを使って直接テーブル名を指定することはできません。パラメータによりクエリ対象のテーブルを切り替える場合は、Liquidの if文 を使ってテーブル名の切替を行う必要があります。
利用例については、ページ最下部の「よくある質問」をご確認ください。

🚧

パラメータ呼び出しに関する変更点

クエリv1ではパラメータの呼び出し時に、{{ string_param + "_suffix" }} や {{ num_param + 10 }} のような文字列結合や数値演算処理が行えていましたが、クエリv2では以下に示す Liquidのフィルタ機能か、BigQuery 側での関数をご利用頂く必要があります。
利用例については、ページ最下部の「よくある質問」をご確認ください。

🚧

パラメータ未定義時の挙動に関する変更点

クエリv1では SQL 内で参照されているパラメータが未定義の場合、空文字列が出力されていましたが、クエリv2ではクエリ実行時にエラーになるようになります。

📘

Multiselectのパラメータを利用する

クエリv2では、Multiselectのパラメータで選択した文字列も、選択された要素毎に'で囲って出力されるようになります。
（例：クエリv1 item1,item2 、クエリv2: 'item1', 'item2'）

IN句を利用する場合はそのまま、value in ({{multiselect_param}}) の形で利用し、文字列配列として扱う場合は、ARRAY<STRING>[{{multiselect_param}}] で文字列配列に変換してご利用ください。

Datahubがデフォルトで提供するデータテーブルの利用

karte_eventテーブルなどの、Datahub がデフォルトで提供するデータテーブルの SQL 内での呼び出し方が以下のように変更されます。

SELECT * FROM {{ karte_event('20181201', '20181203') }}
SELECT * FROM {{ karte_event(period[0], period[1]) }}  /* パラメータを利用する場合 */

SELECT * FROM {% karte_event '20181201', '20181203' %}
SELECT * FROM {% karte_event period[0], period[1] %}  /* パラメータを利用する場合 */

🚧

karte_eventテーブルでの日付指定に関する変更点

クエリv2では、karte_eventテーブルの呼び出し時の引数として BigQuery の関数指定はできなくなります。クエリ実行時間から相対的な時間指定を行う場合は、Periodパラメータの相対日付形式をご利用ください。

テンプレート構文の利用

クエリv2では、Liquidテンプレートの一部のタグ・フィルタが利用できるようになります。利用可能なタグ・フィルタを以下に列挙します。

利用可能なタグ
for, if, unless, elsif, else, case, when, assign, increment, decrement, raw, comment
分類利用可能なフィルタ
算術演算plus, minus, modulo, times, floor, ceil, round, divided_by, abs
文字列処理append, prepend, capitalize, upcase, downcase, strip, lstrip, rstrip,
strip_newlines, split, replace, replace_first, remove, remove_first, truncate, truncatewords
配列処理slice, map, sort, sort_natural, uniq, where, first, last, join, reverse
日付処理date

タグ・フィルタの利用法の詳細については、Liquidテンプレートの公式仕様をご確認ください。
（実装の状況によって、一部機能が利用できない場合があります。予めご了承ください）

テンプレート構文記述サンプル

  • 例: target_wordという文字列パラメーターの前後に固定の文字列をつなげたい
{{'%' | append: target_word | append: '%'}}
-- -> target_wordが 'sample'なら、出力は '%sample%'

よくある質問

Q: prefix_{{suffix}} のようにパラメータの値と他の文字列を結合する方法はありますか？

A: Liquidのフィルタを利用して文字列結合するか、パラメータの展開後に BigQuery の関数を使って結合してください。利用例を以下に示します。

SELECT 'prefix_{{suffix}}' AS param  /* SELECT 'prefix_test' AS param に展開 */

SELECT {{'prefix_' | append: suffix}} AS param   /* SELECT 'prefix_test' AS param に展開 */
SELECT CONCAT('prefix_', {{suffix}}) AS param  /* SELECT CONCAT('prefix_', 'test') AS param に展開 */

Q: テーブル名をパラメータで指定する方法はありますか？

A: パラメータで直接テーブル名を指定する方法はありませんが、Liquidのif文を利用することで、事前に定義されたテーブル名の中から利用するテーブルを切り替えることはできます。利用例を以下に示します。

SELECT * FROM `dataset.database.table_{{table_suffix}}`

SELECT * FROM
{% if table_suffix == "event" %}
  `dataset.database.table_event`
{% elsif table_suffix == "action" %}
  `dataset.database.table_action`
{% else %}
  `dataset.database.table_others`
{% endif %}

なお、Datahubが管理するデータセット名に含まれるKARTEプロジェクトのAPIキーについては、{{api_key|safe_param}}という特殊なパラメータ参照方法でエスケープを回避して利用できます。

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

Updated 5 months ago