本ページの内容は、SQL と LookML の充分な知識をすでにお持ちの方向けのトピックです。
Looker では、ユーザーがディメンションおよびメジャーに基づくフィルタの作成によりクエリを操作できます。この方法は多くのユースケースを満たしますが、すべての分析ニーズには対応できません。テンプレートフィルタとLiquidパラメーターにより、対応可能なユースケースが大幅に拡張されます。
SQL の視点からは、ディメンションおよびメジャーが変更できるのはクエリの最も外側の WHERE
句または HAVING
句のみです。しかし、ユーザーによってはSQLの他の部分を操作したい場合もあるでしょう。 テンプレート フィルタおよび Liquid パラメータを使用することで、派生テーブルの一部調整、クエリ対象となるデータベース テーブルの調整、多目的なディメンションやフィルタの作成を始めとする多数の機能を実現できます。
テンプレートフィルタおよびLiquidパラメーターでは、SQLクエリへのユーザー入力の挿入にLiquidテンプレート作成言語を使用します。 まず、LookMLパラメーターを使用してユーザーの操作対象となるフィールドを作成します。 次に、ユーザー入力をSQLクエリへ挿入するLiquid変数を使用します。
例
テンプレート フィルタおよび Liquid パラメータの有用性を示す例をいくつか見てみましょう。
テンプレートフィルタを使用した動的な派生テーブルの作成
米国の北東地域におけるある顧客の生涯支出を計算する派生テーブルを例に取ります。
view: customer_facts {
derived_table: {
sql:
SELECT
customer_id, -- Can be made a dimension
SUM(sale_price) AS lifetime_spend -- Can be made a dimension
FROM
order
WHERE
region = 'northeast' -- Can NOT be made a dimension
GROUP BY 1
;;
}
}
このクエリでは、customer_id
と lifetime_spend
からディメンションを作成できます。ですが、region
を「northeast」にハードコーディングせずに指定できるようにしたいとしましょう。region
はディメンションとして表すことができないため、ユーザーは通常のようにフィルタを使用できません。
一案としては、次のようにテンプレートフィルタを使用できます。
view: customer_facts {
derived_table: {
sql:
SELECT
customer_id,
SUM(sale_price) AS lifetime_spend
FROM
order
WHERE
{% condition order_region %} order.region {% endcondition %}
GROUP BY 1
;;
}
filter: order_region {
type: string
}
}
詳しい手順については、基本的な使用セクションをご覧ください。
Liquidパラメーターを使用した動的メジャーの作成
pantsの販売数を合計する、フィルタされたメジャーについて考えます。
measure: pants_count {
filters: [category: "pants"]
}
これは単純ですが、数十個のカテゴリがある場合、それぞれに対してメジャーを作成するのは厄介です。 加えて、Exploreのユーザーエクスペリエンスが混乱する可能性があります。
代わりに、次のような動的メジャーを作成できます。
measure: category_count {
type: sum
sql:
CASE
WHEN ${category} = '{% parameter category_to_count %}'
THEN 1
ELSE 0
END
;;
}
parameter: category_to_count {
type: string
}
詳しい手順については、基本的な使用方法をご覧ください。
基本的な使い方
ステップ 1: ユーザーの操作対象の作成
- テンプレート フィルタの場合は、
filter
を追加します。 - Liquid パラメータの場合は、
parameter
を追加します。
いずれの例でも、これらのフィールドは、ユーザーのフィールド ピッカーの [Filter-Only Fields] セクションに表示されます。
filter
と parameter
の両フィールドには一連の子パラメータを入力して、操作方法をカスタマイズできます。一覧についてはフィールド パラメータのドキュメンテーション ページを参照してください。parameter
フィールドには特筆すべきオプションが 2 つあります。
1 つ目は、parameter
フィールドに unquoted という特別なタイプを使用できる点です。
parameter: table_name {
type: unquoted
}
このタイプを使用することで、ストリングのように引用符で囲まなくとも値をSQLに挿入できます。 テーブル名などのSQL値を挿入する必要があるときなどに使用できます。
2 つ目は、parameter
フィールドには、挿入する値にユーザーフレンドリーな名前を関連付けることのできる allowed values というオプションがある点です。例:
parameter: sale_price_metric_picker {
description: "Use with the Sale Price Metric measure"
type: unquoted
allowed_value: {
label: "Total Sale Price"
value: "SUM"
}
allowed_value: {
label: "Average Sale Price"
value: "AVG"
}
allowed_value: {
label: "Maximum Sale Price"
value: "MAX"
}
allowed_value: {
label: "Minimum Sale Price"
value: "MIN"
}
}
ステップ 2: ユーザー入力を適用する
2 番目のステップでは、Liquid を使用して、希望に応じてテンプレート フィルタや Liquid パラメータを追加します。
テンプレートフィルタ
テンプレートフィルタの構文は次のとおりです。
{% condition filter_name %} sql_or_lookml_reference {% endcondition %}
condition
とendcondition
という単語は決して変わりません。filter_name
は、最初の手順で作成したフィルタの名前に置き換えます。フィルタ専用のフィールドを作成していない場合は、ディメンションを使用することもできます。sql_or_lookml_reference
をユーザー入力と「等しく」なるよう設定された SQL または LookML で置き換えます(これについては、このセクションで後述します)。LookML を使用している場合は、${view_name.field_name}
LookML 構文を使用します。
前述の例(テンプレート フィルタを使用した動的な派生テーブルの作成)では、次を使用しました。
{% condition order_region %} order.region {% endcondition %}
Liquid タグとそれらの間に記述する SQL の相互作用を理解することが重要です。テンプレートフィルタタグは常に論理式へ変換されます。例えば、ユーザーが order_region
フィルタに「Northeast」と入力した場合、Looker はこれらのタグを次のように変換します。
order.region = 'Northeast'
言い換えると、Lookerがユーザー入力を理解して適切な論理式を解釈します。
テンプレート フィルタは論理式を返すので、それらの論理式を SQL WHERE
ステートメントで有効な他の論理演算子また論理式とともに使用できます。上記の例を使用して、ユーザーが選択したリージョン以外のすべての値を返すには、WHERE
ステートメントで次の値を使用することができます。
NOT ({% condition order_region %} order.region {% endcondition %})
LookMLフィールドをフィルタ条件として使用するのも有効です。 LookML フィールドに直接適用されるフィルタにより、次のように WHERE
ステートメントの値が決まります。
view: customer_facts {
derived_table: {
sql:
SELECT
customer_id,
SUM(sale_price) AS lifetime_spend
FROM
order
WHERE
{% condition region %} order.region {% endcondition %}
GROUP BY 1
;;
}
dimension: region {
type: string
sql: ${TABLE}.region ;;
}
Liquidパラメーター
Liquidパラメーターの構文は次のとおりです。
{% parameter parameter_name %}
parameter
という単語は決して変更されません。parameter_name
は、最初の手順で作成したparameter
の名前に置き換えます。
例えば、ステップ 1 の parameter
フィールドの入力を適用するために、次のようなメジャーを作成することができます。
measure: sale_price_metric {
description: "Use with the Sale Price Metric Picker filter-only field"
type: number
label_from_parameter: sale_price_metric_picker
sql: {% parameter sale_price_metric_picker %}(${sale_price}) ;;
value_format_name: usd
}
テンプレートフィルタとLiquidパラメーターのどちらを選択するか
テンプレートフィルタとLiquidパラメーターは互いに似ていますが、次の重要な相違点があります。
- Liquid パラメータは、ユーザー入力を直接(または使用できる値で定義する値を使用して)挿入します。
- テンプレート フィルタは、テンプレート フィルタのセクションで説明したように、値を論理ステートメントとして挿入します。
より柔軟な入力方法(何種類もの日付範囲やストリング検索など)をユーザーに提供する場合には、可能な限りテンプレートフィルタを使用することを推奨します。 Lookerはユーザー入力を解釈し、適切なSQLを自動で記述できます。 このため、ユーザー入力として可能性があるあらゆるタイプを考慮する必要がなくなります。
論理ステートメントを挿入できない場合や、ユーザーが入力するオプションに限りがあることがわかっている場合には、Liquid パラメータを使用します。