本ページの内容は、SQLとLookMLの充分な知識をすでにお持ちの方向けのトピックです。
Looker では、ディメンションとメジャーに基づくフィルタを作成して、クエリを自動的に操作できます。このシンプルな方法は多くのユースケースに対応していますが、すべての分析ニーズに対応できるわけではありません。テンプレートフィルタとLiquidパラメーターにより、対応可能なユースケースが大幅に拡張されます。
SQL の観点からは、ディメンションとメジャーで変更できるのはクエリの最も外側の WHERE
句か HAVING
句のみです。しかし、SQL の他の部分も操作できるようにしたい場合があります。テンプレート化されたフィルタと Liquid パラメータを使用して実現できるのは、派生テーブルの一部の調整、クエリの対象となるデータベース テーブルの調整、多目的のディメンションとフィルタの作成のみです。
テンプレート フィルタと Liquid パラメータは Liquid テンプレート言語を使用してユーザー入力を SQL クエリに挿入しますまず、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
からディメンションを作成できます。しかし、" northeast" にハードコードするのではなく、region
をユーザーが指定できるようにしたいとします。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 つのオプションがあります。
まず、parameter
フィールドには「引用符なし」という特殊な型があります。
parameter: table_name {
type: unquoted
}
この型を使用すると、文字列のように、値を引用符で囲まなくても SQL に挿入できます。テーブル名などのSQL値を挿入する必要があるときなどに使用できます。
2 つ目の項目として、parameter
フィールドには「許可された値」というオプションがあります。このオプションでは、挿入する値にわかりやすい名前を関連付けることができます。例:
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: ユーザー入力を適用する
次に、Liquid を使用して、テンプレート化されたフィルタまたは Liquid パラメータを必要に応じて追加します。
テンプレートフィルタ
テンプレートフィルタの構文は次のとおりです。
{% condition filter_name %} sql_or_lookml_reference {% endcondition %}
condition
とendcondition
は変化しません。filter_name
は、最初の手順で作成したフィルタの名前に置き換えます。フィルタ専用のフィールドを作成していない場合は、ディメンションを使用することもできます。sql_or_lookml_reference
は、ユーザー入力の「equals」に設定する SQL または LookML に置き換えます(詳しくは下記をご覧ください)。LookML を使用する場合は、${view_name.field_name}
LookML 構文を使用します。
上記の例では、
{% condition order_region %} order.region {% endcondition %}
Liquid タグと記述する SQL 間の相互作用は理解しておく必要があります。テンプレート化されたフィルタタグは、常に論理式に変換されます。たとえば、ユーザーが order_region
フィルタに「Northeast」と入力した場合、Looker はこれらのタグを order.region = 'Northeast'
に変換します。言い換えると、Lookerがユーザー入力を理解して適切な論理式を生成します。
これは多くの場合、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パラメーターは互いに似ていますが、次の重要な相違点があります。
さまざまな期間や文字列検索など、ユーザーにさらに柔軟な入力方法を提供する場合は、可能な限りテンプレート フィルタを使用してください。Looker はユーザー入力を解釈し、適切な SQL をバックグラウンドで記述できます。このため、ユーザー入力として可能性があるあらゆるタイプを考慮する必要がなくなります。
論理ステートメントを挿入できない状況や、ユーザーが入力する可能性のある選択肢が限られている場合は、Liquid パラメータを使用します。