表示されているのは Looker のドキュメントです。Looker Studio のドキュメントについては、https://support.google.com/looker-studio をご覧ください。

集約テーブルの自動認識のチュートリアル

その他の詳細については、集約テーブルの自動認識のドキュメントページをご覧ください。

はじめに

このページは、実際のシナリオで集約テーブルの自動認識を実装するためのガイドです。実装の機会の特定、価値の集約テーブルの自己認識の促進、実際のモデルでの実装のための単純なワークフローなどについて説明しています。このページは、すべての集約テーブルの自動認識の機能やエッジケースの詳細な説明ではありません。また、そのすべての機能を網羅したカタログでもありません。

集約テーブルの自動認識とは

Looker では、ほとんどの場合、データベース内の未加工のテーブルまたはビューに対してクエリを実行します。Looker の永続的な派生テーブル（PDT）である場合もあります。

多くの場合、パフォーマンスを向上させるために、集約テーブルやロールアップが必要な、非常に大きなデータセットやテーブルが発生する可能性があります。

一般的に、限定されたディメンションを含む orders_daily テーブルのような集約テーブルを作成できます。これらは、Explore で個別に扱い、個別にモデル化する必要があり、モデルにきちんと配置されていません。これらの制限により、ユーザーが同じデータで複数の Explore を選択する必要がある場合、ユーザーエクスペリエンスが低下します。

今では、Looker の集約テーブルの自動認識により、さまざまな粒度、ディメンション、集計に対する集約テーブルを事前構築できます。そして、既存の Explore でそれらを使用する方法を Looker に知らせることができます。その後、クエリは、Looker が適切と判断するこうしたロールアップテーブルを、ユーザーからの入力なしで利用します。これにより、クエリのサイズが削減され、待ち時間が短縮されて、ユーザーエクスペリエンスが向上します。

注: Looker の集約テーブルは永続派生テーブル（PDT）の一種です。つまり、集約テーブルのデータベースと接続の要件は PDT と同じです。
データベース言語と Looker の接続が PDT をサポートしているかどうかを確認するには、Looker の派生テーブルのドキュメントページに記載されている要件をご覧ください。
データベース言語が集約テーブルの自動認識をサポートしているかどうかを確認するには、集約テーブルの自動認識のドキュメントページをご覧ください。

集約テーブルの自動認識の価値

既存の Looker モデルからさらなる価値を生み出すために、集約テーブルの自動認識には数多くの価値提案がある。

パフォーマンス向上: 集約テーブルの自動認識の実装により、ユーザークエリが高速化します。ユーザーのクエリの完了に必要なデータが含まれていれば、Looker は小さなテーブルを使用します。
費用削減: 消費モデルでのクエリのサイズにより、特定の言語料金。Looker クエリのテーブルを小さくすれば、ユーザーのクエリあたりの費用を削減可能。
ユーザーエクスペリエンスの向上: 回答をより迅速に取得するエクスペリエンスの向上に加えて、統合すると冗長な Explore の作成が不要。
LookML のフットプリントの削減: 既存の Liquid を使用した集約テーブルの自動認識の戦略を、柔軟でネイティブな実装に置き換えることで、復元力が高まりエラーが減少。
既存の LookML の活用: 集約テーブルでは、明示的なカスタム SQL を使用してロジックを複製するのではなく、既存のモデル化されたロジックを再利用する query オブジェクトを使用します。

基本的な例

ここでは、Looker モデルでの非常に単純な実装で、集約テーブルの自動認識がいかに軽量になるかを説明します。FAA を通じて記録されたすべてのフライトの行がある、データベース内の仮想的な flights テーブルを考えると、独自のビューと Explore を使用して Looker でこのテーブルをモデル化できます。Explore 用に定義できる集約テーブルの LookML は次のとおりです。

  explore: flights {
    aggregate_table: flights_by_week_and_carrier {
      query: {
        dimensions: [carrier, depart_week]
        measures: [cancelled_count, count]
      }

      materialization: {
        sql_trigger_value: SELECT CURRENT-DATE;;
      }
    }
  }

この集約テーブルを使用して、ユーザーは flights Explore をクエリできます。Looker は、LookML で定義された集約テーブルを自動的に活用し、集約テーブルを使用してクエリに回答します。Looker に特別な条件を伝える必要はありません。ユーザーが選択したフィールドにテーブルが適合する場合、Looker がそのテーブルを使用します。

see_sql 権限を持つユーザーは、Explore の [SQL] タブのコメントを使用して、クエリに使用される集約テーブルを確認できます。集約テーブル flights:flights_by_week_and_carrier in teach_scratch を使用するクエリ用の Looker の [SQL] タブの例を次に示します。

基本的な SQL を表示する Explore と、使用されている集約テーブルのスクラッチスキーマを指定するコメントの [SQL] タブ。

集約テーブルがクエリに使用されているかどうかを判断する方法については、集約テーブルの自動認識のドキュメントページをご覧ください。

販売機会の特定

集約テーブルの自動認識のメリットを最大限に活用するには、最適化または集約テーブルの自動認識の価値を促進するうえで、集約テーブルの自動認識が果たす役割を特定する必要があります。

高ランタイムを持つダッシュボードを特定する

集約テーブルの自動認識が非常に優れているところは、1 つには実行時間が非常に長く、使用頻度が高いダッシュボードの集約テーブルを作成できることですお客様からは、遅いダッシュボードについて話を聞くことがあるかもしれませんが、see_system_activity がある場合は、Looker のシステムアクティビティ履歴 Explore を使用して、平均ランタイムよりも遅いダッシュボードを見つけることもできます。ブラウザでこのシステムアクティビティ履歴 Explore リンクを開き、URL の「ホスト名」を Looker インスタンスの名前に置き換えるのが簡単です。タイトル、履歴、Explore の数、キャッシュとデータベースの比率、平均以下のパフォーマンスといったインスタンスのダッシュボードに関するデータを使用して、Explore が可視化されます。

この例には、[サンプルビジュアリゼーション] ダッシュボードなど、使用率が高く、平均よりパフォーマンスが低いダッシュボードが複数あります。[サンプルビジュアリゼーション] ダッシュボードでは 2 つの Explore を使用するため、両方の Explore で集約テーブルを作成することをおすすめします。

遅く、ユーザーによって頻繁にクエリが実行される Explore を特定する

集約テーブルの自動認識のもう 1 つの機会は、ユーザーによるクエリ実行が頻繁に行われ、クエリのレスポンスが平均未満である Explore についてです。

[システムアクティビティ履歴 Explore] は、Explore を最適化する機会を特定するための出発点として使用できます。ブラウザでシステムアクティビティ履歴 Explore リンクを開き、URL の「ホスト名」を Looker インスタンスの名前に置き換えるのが簡単です。Explore、モデル、クエリ実行数、ユーザー数、平均ランタイム（秒）といった、インスタンスの Explore に関するデータを使用して Explore が可視化されます。

order_items と flights Explore がインスタンスで最も頻繁にクエリされることを示すテーブル可視化。

History Explore では、インスタンスで次のタイプの Explore を識別できます。

ユーザーによってクエリされる Explore（API からのクエリ、またはスケジュールされた配信からのクエリからではなく）
頻繁にクエリされる Explore
パフォーマンスが低い Explore（他の Explore と比較して）

前述のシステムアクティビティ履歴 Explore の例では、flights と order_items の Explore が、集約テーブルの自動認識実装の候補になります。

クエリで頻繁に使用されるフィールドを特定する

最後に、クエリやフィルタによく使用されるフィールドを把握することで、データレベルで他の機会を特定することが可能となります。

System Activity Field Usage Explore を使用して、速度が遅く、利用頻度の高い Explore 内で一般的に使用されるフィールドを確認します。ショートカットとして、ブラウザでこの System Activity Field Usage Explore リンクを開き、URL の「ホスト名」を Looker インスタンスの名前に置き換えることができます。適宜フィルタを置き換えます。フィールドがクエリで使用された回数を棒グラフで視覚化した Explore が表示されます。

faa モデルの flights Explore の flights.count と flight.depart_week が最もよく使用されるフィールドであることを示す棒グラフ。

画像に示されている System Activity Explore の例では、flights.count と flights.depart_week が Explore で最も一般的に選択されている 2 つのフィールドであることがわかります。したがって、これらのフィールドは、集約テーブルに含めるフィールドに適しています。

このような具体的なデータは便利ですが、選択基準の指針となる主観的な要素があります。たとえば、前の 4 つの項目を確認すると、ユーザーは予定したフライト数とキャンセルされたフライト数をよく見ており、そのデータを週間別、運送業者別の両方に分類することを望んでいると確かに推測できます。これは、フィールドと指標の、明確で論理的な実際の組み合わせの例です。

概要

このドキュメントページの手順は、最適化を考慮する必要があるダッシュボード、Explore、フィールドを見つけるためのガイドとしてご利用ください。また、これら 3 つはすべて相互排他的である可能性があることも理解しておく必要があります。問題のあるダッシュボードは、問題のある Explore を活用していない可能性があります。また、よく使用されるフィールドを含む集約テーブルを作成しても、これらのダッシュボードでまったく機能しない可能性もあります。3 つの個別の集約テーブルの自動認識の実装が考えられます。

集約テーブルの設計

集約テーブルの自動認識の機会を特定したら、これらの機会に最適な集約テーブルを設計できます。集約テーブルでサポートされているフィールド、メジャー、期間、および集約テーブルを設計するためのその他のガイドラインについては、集約テーブルの自動認識のドキュメントページをご覧ください。

注: 集約テーブルは、クエリを使用するために完全に一致する必要はありません。クエリが 1 週間ごとの粒度で、日次ロールアップテーブルがある場合は、Looker は未加工のタイムスタンプテーブルの代わりに集約テーブルを使用します。同様に、brand と date レベルにロールアップされる集約テーブルと、brand レベルにのみロールアップされるユーザークエリがある場合、そのテーブルは引き続き、Looker で集約テーブルの自動認識に使用するための候補になります。

集約テーブルの自動認識は、以下のメジャーでサポートされています。

標準メジャー: タイプ SUM、COUNT、AVERAGE、MIN、MAX のメジャー
複合メジャー: タイプ NUMBER、STRING、YESNO、DATE のメジャー
近似区分メジャー: HyperLogLog 機能を使用可能な言語

集約テーブルの自動認識は、以下のメジャーではサポートされていません。

区分メジャー: 区分性は、アトミックな非集約データでのみ計算可能なため、*_DISTINCT メジャーは、HyperLogLog を使用するこれらの近似の外ではサポートされていません。
カーディナリティベースのメジャー: 区分メジャーと同様に、中央値とパーセンタイルは事前集約ができず、サポートされていません。

注: 集約テーブルの自動認識でサポートされないメジャーのタイプを使用したユーザークエリがあることがわかっている場合は、クエリに完全に一致する集約テーブルを作成する場合があります。クエリと完全に一致する集約テーブルは、集約テーブルの自動認識でサポートされることのないメジャータイプを使用したクエリに対して回答を得るために使用することができます。

集約テーブルの粒度

ディメンションとメジャーの組み合わせのテーブルを構築する前に、使用とフィールド選択の共通パターンを特定し、使用頻度が高く、影響の大きい集約テーブルを作成します。テーブルをクエリで使用するようにするには、クエリで使用されるすべてのフィールド（選択済みまたはフィルタ済み）が集約テーブルに存在する必要があります。ただし前述したように、クエリに使用される集約テーブルは、クエリと完全に一致している必要はありません。1 つの集約テーブル内の多数の潜在的ユーザークエリに対処でき、かつ、パフォーマンスが大幅に向上します。

クエリで頻繁に使用されるフィールドの特定の例では、頻繁に選択される 2 つのディメンション（flights.depart_week と flights.carrier）と 2 つのメジャー（ flights.count と flights.cancelled_count）があります。したがって、これら 4 つのフィールドすべてを使用する集約テーブルを構築することは理にかなっています。さらに、flights_by_week_and_carrier 用の 1 つの集約テーブルを作成すると、flights_by_week テーブルと flights_by_carrier テーブルの 2 つの異なる集約テーブルを使用するよりも集約テーブルの使用頻度が高くなります。

次に示すのは、一般的なフィールドに対するクエリ用に作成する集約テーブルの例です。

  explore: flights {
    aggregate_table: flights_by_week_and_carrier {
      query: {
        dimensions: [carrier, depart_week]
        measures: [cancelled_count, count]
      }

      materialization: {
        sql_trigger_value: SELECT CURRENT-DATE;;
      }
    }
  }

ビジネスユーザーと事例証拠、さらには Looker のシステムアクティビティからのデータは、意思決定プロセスのガイドとして役立ちます。

適用範囲とパフォーマンスのバランス

次の例は、flights_by_week_and_carrier 集約テーブルの Flights Depart Week、Flights Details Carrier、Flights Count、Flights Detailed Cancelled Count の各フィールドの Explore クエリを示しています。

flights_by_week_and_Carrier の集約テーブルから 4 つのフィールドがあるデータテーブルを探索します。

元のデータベーステーブルからのこのクエリの実行は 15.8 秒かかり、Amazon Redshift を使用して結合せずに 3,800 万行をスキャンしました。通常のユーザーオペレーションであるクエリのピボットには 29.5 秒かかりました。

flights_by_week_and_carrier 集約テーブルを実装した後、後続のクエリは 7.2 秒 かかり、4,592 行をスキャンしました。これは、99.98% のテーブルのサイズの削減です。クエリのピボットに 9.8 秒かかりました。

システムアクティビティフィールド使用状況 Explore で、ユーザーがクエリにこれらのフィールドを含める頻度を確認できます。この例では、flights.count が 47,848 回、flights.depart_week が 18,169 回、flights.cancelled_count が 16,570 回、flights.carrier が 13,517 回使用されています。

控え目に概算して、クエリの 25% が最も単純な方法（単純な SELECT、ピボットなし）で 4 つのフィールドをすべて使用したとすると、3,379 × 8.6 秒 = 8 時間 4 分の合計ユーザー待機時間が推定されます。

注: ここで使用したサンプルモデルは、ごく基本的なものです。これらの結果をモデルのベンチマークや参照フレームとして使用しないでください。

e コマースモデル order_items（インスタンスで頻繁に使用される Explore）にまったく同じフローを適用すると、結果は次のようになります。

ソース	Query Time	スキャンされた行数
ベーステーブル	13.1 秒	285,000
Aggregate Table	5.1 秒	138,000
デルタ	8 秒	147,000

クエリと後続の集約テーブルで使用されるフィールドは、2 つの結合を使用した brand、created_date、orders_count、total_revenue でした。フィールドは、合計 11,000 回使用されました。同じく合計の使用を 25% と推定すると、ユーザーの合計短縮時間は 6 時間 6 分（8 秒 × 2,750 = 22,000 秒）になります。集約テーブルの構築には 17.9 秒かかりました。

以上の結果を見れば、しばらく考えて、生み出される可能性のあるメリットを評価することをおすすめします。

「許容できる」パフォーマンスで、より良いモデリング手法からパフォーマンスの向上が得られるかも知れない、より大規模で複雑なモデル / Explore の最適化

対

集約テーブルの自動認識を使用して、使用頻度が高くパフォーマンスが低い、より単純なモデルの最適化

Looker とデータベースからパフォーマンスをぎりぎりまで得ようとすると、努力に対するリターンが減少していくのがわかります。特にビジネスユーザーからのベースラインパフォーマンスの期待値と、データベースに課される制限（同時実行、クエリのしきい値、コストなど）を常に認識しておく必要があります。このような制限を克服するために、集約テーブルに期待すべきではありません。

また、集約テーブルを設計する際は、フィールドが多いほど集約テーブルのサイズが大きく、遅くなることに注意してください。テーブルのサイズが大きいほど多くのクエリを最適化できるため、より多くの状況で使用できますが、大きいテーブルは、より小さく、単純なテーブルほど高速ではありません。

次に例を示します。

  explore: flights {
    aggregate_table: flights_by_week_and_carrier {
      query: {
        dimensions: [carrier, depart_week,flights.distance, flights.arrival_week,flights.cancelled]
        measures: [cancelled_count, count, flights.average_distance, flights.total_distance]
      }

      materialization: {
        sql_trigger_value: SELECT CURRENT-DATE;;
      }
    }
  }

これにより、表示されるディメンションとメジャーの組み合わせに集約テーブルが使用されるため、このテーブルを使用して、さまざまなユーザークエリに答えることができます。しかし、このテーブルを carrier と count のシンプルな SELECT クエリに使用するには、885,000 行のテーブルをスキャンする必要があります。一方、テーブルが 2 つのディメンションに基づいている場合、同じクエリで必要なスキャンは 4,592 行のみです。885,000 行のテーブルは、（以前の 3,800 万行から）テーブルサイズを 97% 削減したものですが、ディメンションをもう 1 つ追加すると、テーブルサイズが 2,000 万行になります。そのため、クエリに対する適用範囲を増やすために集約テーブルに含めるフィールドを増やすにつれて、リターンは減少します。

集約テーブルの構築

最適化の機会として特定したフライト Explore の例の場合、最適な戦略は、3 つの異なる集約テーブルを作成することです。

flights_by_week_and_carrier
flights_by_month_and_distance
flights_by_year

これらの集約テーブルを作成する最も簡単な方法は、Explore のクエリまたはダッシュボードから LookML の集約テーブルを取得して、Looker プロジェクトファイルに LookML を追加することです。

集約テーブルを LookML プロジェクトに追加し、更新内容を本番環境にデプロイすると、Explore では集約テーブルをユーザーのクエリに利用します。

永続性

集約テーブルの自動認識にアクセスするには、データベースで集約テーブルを保持する必要があります。データグループを利用して、これらの集約テーブルの自動更新をキャッシュポリシーに合わせることをおすすめします。関連付けられた Explore で使用されている集約テーブルには、同じデータグループを使用する必要があります。datagroup を使用できない場合は、代わりに sql_trigger_value パラメータを使用します。sql_trigger_value の一般的な日付ベースの値を以下に示します。

sql_trigger_value: SELECT CURRENT_DATE() ;;

これにより、毎日深夜に集約テーブルが自動的に構築される。

期間のロジック

Looker で集約テーブルが作成されると、集約テーブルを作成した時点までのデータが格納されます。その後データベース内のベーステーブルに追加されたデータは、通常、その集約テーブルを使用したクエリの結果から除外されます。

次の図は、注文を受け取ってデータベースのログに記録されたタイムラインと Orders 集約テーブルが作成された日時を比較したものです。集約テーブルの作成後に注文を受け取ったため、注文集約テーブルにはない、今日受け付けた 2 つの注文があります。

集約テーブルの作成後に発生した 2 つのデータポイントを除く、本日と昨日の注文のタイムライン。

ただし、同じタイムライン図に示すように、ユーザーが集約テーブルと重複する期間をクエリすると、Looker が集約テーブルに新しいデータを UNION で結合できます。

ユーザーのクエリには、集約テーブルの作成後に発生したタイムラインのデータポイントが含まれます。

Looker では集約テーブルに新しいデータを UNION で結合できるため、集約テーブルとベーステーブルの両方の最後で重複する期間を設定すると、集約テーブルの作成後に受信した注文がユーザー結果に含まるようになります。詳細、および、新しいデータを集約テーブルのクエリに UNION 結合するために必要な条件については、集約テーブルの自動認識のドキュメントページをご覧ください。

概要

まとめると、集約テーブルの自動認識の実装の構築には、次の 3 つの基本的なステップがあります。

集約テーブルを使用した最適化が適切で、影響の大きい箇所を特定する。
一般的なユーザークエリのほとんどをカバーしつつ、クエリのサイズを十分に小さくできる集約テーブルを設計する。
集約テーブルを Looker モデルで構築し、テーブルの永続性を Explore のキャッシュの永続性と組み合わせる。