派生テーブルとは、クエリ結果を派生テーブルがデータベース内の物理テーブルのように使用できるクエリのことです。ネイティブ派生テーブルは、LookML 用語を使用して定義したクエリに基づいています。これは、SQL 用語を使用して定義したクエリに基づく SQL ベースの派生テーブルとは異なります。SQLベースの派生テーブルと比較すると、ネイティブ派生テーブルでは、データをモデル化するときの読みやすさと理解しやすさが向上します。 詳しくは、Looker の派生テーブルのドキュメント ページにあるネイティブ派生テーブルと SQL ベースの派生テーブルセクションをご覧ください。
ネイティブ派生テーブルと SQL ベースの派生テーブルはどちらも、ビューレベルで derived_table パラメータを使用して LookML で定義されます。ただし、ネイティブ派生テーブルでは、SQLクエリを作成する必要がありません。 代わりに、explore_source パラメータを使用して、派生テーブル、必要な列、その他の必要な特性の基になる Explore を指定します。
SQL Runner を使用して派生テーブルを作成するドキュメント ページに説明されている方法で、Looker で SQL Runner クエリから派生テーブル LookML を作成することもできます。
Exploreを使用したネイティブ派生テーブルの定義の開始
Exploreで開始すると、Lookerは大半の派生テーブルのLookMLを生成できます。 まずはExploreを作成し、派生テーブルに含めるすべてのフィールドを選択します。 次に、ネイティブ派生テーブル LookML を生成するには、次の手順に従います。
[Explore アクション] の歯車アイコンを選択し、[Get LookML] を選択します。
[派生テーブル] タブをクリックすると、Explore のネイティブ派生テーブルを作成するための LookML が表示されます。
そのLookMLをコピーします。
生成されたLookMLをコピーしたら、ビューファイルにペーストします。
Development Mode で、プロジェクト ファイルに移動します。
Looker IDEのプロジェクトファイルリストの上部にある [+] をクリックし、[ビューを作成] を選択します。または、フォルダのメニューをクリックして、メニューから [ビューを作成] を選択し、フォルダ内にファイルを作成します。
内容がわかりやすいビュー名を設定します。
必要に応じて、列名の変更、派生列の指定、フィルタの追加を行います。
Explore で
type: countの measure を使用する場合、可視化された値には、Count という単語ではなく、ビュー名でラベルが付けられます。混乱を避けるために、ビュー名は複数形で使用します。ビュー名を変更するには、ビジュアリゼーション設定の [シリーズ] で [完全なフィールド名を表示] を選択するか、ビュー名の複数形でview_labelパラメータを使用します。
LookMLでのネイティブ派生テーブルの定義
SQL とネイティブ LookML のいずれの方法で宣言された派生テーブルを使用する場合でも、derived_table クエリの出力は一連の列を含むテーブルになります。派生テーブルがSQLで記述される場合、出力列名はSQLクエリによって暗黙的に示されます。 たとえば、次の SQL クエリには、出力列 user_id、lifetime_number_of_orders、lifetime_customer_value が含まれます。
SELECT
user_id
, COUNT(DISTINCT order_id) as lifetime_number_of_orders
, SUM(sale_price) as lifetime_customer_value
FROM order_items
GROUP BY 1
Lookerでは、クエリはExploreに基づき、メジャーフィールドとディメンションフィールドが含まれます。また、適用可能なフィルタも追加され、ソート順を指定することもできます。 ネイティブ派生テーブルには、これらすべての要素に加え、その列の出力名が含まれます。
次の簡単な例では、user_id、lifetime_customer_value、lifetime_number_of_orders の 3 つの列を含む派生テーブルを生成します。クエリを SQL で手動で記述する必要はありません。代わりに、Looker が指定の Explore order_items と Explore のフィールドの一部（order_items.user_id、order_items.total_revenue、order_items.order_count）を使用してクエリを作成します。
view: user_order_facts {
derived_table: {
explore_source: order_items {
column: user_id {
field: order_items.user_id
}
column: lifetime_number_of_orders {
field: order_items.order_count
}
column: lifetime_customer_value {
field: order_items.total_revenue
}
}
}
# Define the view's fields as desired
dimension: user_id {
hidden: yes
}
dimension: lifetime_number_of_orders {
type: number
}
dimension: lifetime_customer_value {
type: number
}
}
include ステートメントを使用した参照フィールドの有効化
ネイティブ派生テーブルのビューファイルで、explore_source パラメータを使用して Explore を指し、ネイティブ派生テーブルの列やその他の特性を定義します。
ネイティブ派生テーブルのビューファイルでは、include パラメータを使用して Explore の定義を含むファイルを参照する必要はありません。include ステートメントがない場合、Looker IDE はネイティブ派生テーブルの作成時にフィールド名の自動提案やフィールド参照の検証を行いません。代わりに、LookML バリデーターを使用して、ネイティブ派生テーブルで参照しているフィールドを確認できます。
ただし、Looker IDE で自動候補と即時フィールド検証を有効にする場合や、名前が同じか循環参照の可能性がある複数の Explore を含む複雑な LookML プロジェクトがある場合は、include パラメータを使用して Explore の定義の場所を指します。
Explore は多くの場合、モデルファイル内で定義されますが、ネイティブ派生テーブルの場合は Explore 用に別のファイルを作成する方が明確です。Explore ファイルの作成のドキュメントで説明されているように、LookML Explore ファイルの拡張子は .explore.lkml です。この方法によって、ネイティブ派生テーブルのビューファイル内に、モデルファイル全体ではなく、単一のExploreファイルを含めることができます。
別の Explore ファイルを作成し、include パラメータを使用してネイティブ派生テーブルのビューファイル内の Explore ファイルを指す場合は、LookML ファイルが次の要件を満たしていることを確認してください。
- ネイティブ派生テーブルのビューファイルには次のように Explore のファイルを含める必要があります。例:
include: "/explores/order_items.explore.lkml"
- Explore のファイルには次のように必要なビューファイルを含める必要があります。例:
include: "/views/order_items.view.lkml"include: "/views/users.view.lkml"
- モデルには次のように Explore のファイルを含める必要があります。例:
include: "/explores/order_items.explore.lkml"
ネイティブ派生テーブルの列の定義
前の例に示すように、column を使用して派生テーブルの出力列を指定します。
列名の指定
user_id 列については、列名は元の Explore 内の指定されたフィールド名に一致します。
元のExploreのフィールド名ではなく、出力テーブルの別の列名を使用したい場合もよくあることでしょう。 上の例では、order_items Explore を使用してユーザー別のライフタイム バリューの計算を生成しました。出力テーブルでは、total_revenue は実際には顧客の lifetime_customer_value です。
column 宣言では、入力フィールドとは異なる出力名の宣言に対応しています。たとえば、次のコードは、「フィールド order_items.total_revenue から lifetime_value という名前の出力列を作成する」ように Looker に指示しています。
column: lifetime_value {
field: order_items.total_revenue
}
暗黙的な列名
field パラメータが列宣言で省略されている場合は、<explore_name>.<field_name> とみなされます。たとえば、explore_source: order_items を指定した場合:
column: user_id {
field: order_items.user_id
}
上記は次と等しくなります。
column: user_id {}
計算値の派生列の作成
derived_column パラメータを追加して、explore_source パラメータの Explore に存在しない列を指定できます。各 derived_column パラメータには、値の作成方法を指定する sql パラメータがあります。
sql 計算では、column パラメータで指定した任意の列を使用できます。派生列に集計関数を含めることはできませんが、テーブルの単一行に対して実行される計算を含めることができます。
次の例では、前の例と同じ派生テーブルを生成します。ただし、計算された average_customer_order 列が追加されます。この列は、ネイティブ派生テーブルの lifetime_customer_value 列と lifetime_number_of_orders 列から計算されています。
view: user_order_facts {
derived_table: {
explore_source: order_items {
column: user_id {
field: order_items.user_id
}
column: lifetime_number_of_orders {
field: order_items.order_count
}
column: lifetime_customer_value {
field: order_items.total_revenue
}
derived_column: average_customer_order {
sql: lifetime_customer_value / lifetime_number_of_orders ;;
}
}
}
# Define the view's fields as desired
dimension: user_id {
hidden: yes
}
dimension: lifetime_number_of_orders {
type: number
}
dimension: lifetime_customer_value {
type: number
}
dimension: average_customer_order {
type: number
}
}
SQLウィンドウ関数の使用
一部のデータベース言語はウィンドウ関数をサポートしており、特にシーケンス番号、主キー、実行中の累積合計、その他の便利な複数行の計算を行うことができます。プライマリクエリが実行された後、derived_column 宣言が個別に実行されます。
データベースダイアレクトがウィンドウ関数に対応している場合には、ネイティブ派生テーブルでこれを使用できます。 目的のウィンドウ関数を含む sql パラメータで derived_column パラメータを作成します。値を参照する際には、ネイティブ派生テーブルで定義された列名を使用する必要があります。
次の例では、user_id、order_id、created_time の各列を含むネイティブ派生テーブルを作成します。その後、SQL ROW_NUMBER() ウィンドウ関数を含む派生列を使用して、お客様の注文のシーケンス番号を含む列を計算します。
view: user_order_sequences {
derived_table: {
explore_source: order_items {
column: user_id {
field: order_items.user_id
}
column: order_id {
field: order_items.order_id
}
column: created_time {
field: order_items.created_time
}
derived_column: user_sequence {
sql: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_time) ;;
}
}
}
dimension: order_id {
hidden: yes
}
dimension: user_sequence {
type: number
}
}
ネイティブ派生テーブルへのフィルタの追加
過去 90 日間のお客様の値の派生テーブルを作成するとします。前の例で実行したのと同じ計算を行いますが、過去 90 日間の購入のみを含めます。
過去 90 日間のトランザクションをフィルタするフィルタを derived_table に追加するだけです。派生テーブルの filters パラメータは、フィルタされた measure の作成に使用するものと同じ構文を使用します。
view: user_90_day_facts {
derived_table: {
explore_source: order_items {
column: user_id {
field: order_items.user_id
}
column: number_of_orders_90_day {
field: order_items.order_count
}
column: customer_value_90_day {
field: order_items.total_revenue
}
filters: [order_items.created_date: "90 days"]
}
}
# Add define view's fields as desired
dimension: user_id {
hidden: yes
}
dimension: number_of_orders_90_day {
type: number
}
dimension: customer_value_90_day {
type: number
}
}
Looker が派生テーブルの SQL を書き込むと、フィルタが WHERE 句に追加されます。
また、explore_source の dev_filters サブパラメータを、ネイティブ派生テーブルとともに使用できます。dev_filters パラメータを使用すると、Looker が派生テーブルの開発バージョンにのみ適用するフィルタを指定できます。こうすることで、フィルタによって絞り込まれた、より小さなテーブルを作成して繰り返しやテストを行えるため、変更のたびにテーブル全体が作成されるのを待たなくてすむようになります。
dev_filters パラメータは filters パラメータと連動して動作し、すべてのフィルタがテーブルの開発バージョンに適用されるようになります。dev_filters と filters の両方が同じ列のフィルタを指定した場合、テーブルの開発バージョンには dev_filters が優先されます。
詳しくは、Development Mode での作業の高速化をご覧ください。
テンプレートフィルタの使用
bind_filters を使用して、テンプレート化されたフィルタを含めることができます。
bind_filters: {
to_field: users.created_date
from_field: filtered_lookml_dt.filter_date
}
これは基本的に sql ブロックで次のコードを使用する場合と同じです。
{% condition filtered_lookml_dt.filter_date %} users.created_date {% endcondition %}
to_field は、フィルタの適用先のフィールドです。to_field は、基になる explore_source のフィールドである必要があります。
実行時にフィルタが存在する場合、from_field はフィルタの取得先のフィールドを指定します。
上記の bind_filters の例では、Looker は、filtered_lookml_dt.filter_date フィールドに適用されたフィルタを取得して users.created_date フィールドに適用します。
また、explore_source の bind_all_filters サブパラメータを使用して、すべてのランタイム フィルタを Explore からネイティブ派生テーブルのサブクエリに渡すこともできます。詳しくは、explore_source パラメータのドキュメント ページをご覧ください。
ネイティブ派生テーブルのソートおよび制限
sorts: [order_items.count: desc]
limit: 10
Exploreでは基礎となるソートと異なる順で行が表示される場合があるので注意してください。
ネイティブ派生テーブルの異なるタイムゾーンへの変換
timezone サブパラメータを使用して、ネイティブ派生テーブルのタイムゾーンを指定できます。
timezone: "America/Los_Angeles"
timezone サブパラメータを使用すると、ネイティブ派生テーブルのすべての時間ベースのデータが指定したタイムゾーンに変換されます。サポートされているタイムゾーンの一覧については、timezone 値のドキュメント ページをご覧ください。
ネイティブ派生テーブルの定義でタイムゾーンを指定しない場合、ネイティブ派生テーブルは時間ベースのデータに対してタイムゾーン変換を行いません。代わりに、時間ベースのデータがデータベースのタイムゾーンのデフォルトとなります。
ネイティブ派生テーブルが永続的ではない場合、タイムゾーン値を "query_timezone" に設定して、現在実行中のクエリのタイムゾーンを自動的に使用できます。