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 テーブルがある場合、Looker では独自のビューと Explore を使用してこのテーブルをモデル化できます。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 は、上記で定義した集約テーブルを自動的に活用し、集約テーブルを使用してクエリに対応します。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 がインスタンスで最も頻繁にクエリされることを示すテーブル可視化。

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

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

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

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

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

システムアクティビティフィールドの使用状況 Explore を使用して、上記で特定した Explore でよく使用されるフィールドを確認します。ブラウザでこのシステムアクティビティフィールド使用状況 Explore リンクを開き、URL の「ホスト名」を Looker インスタンスの名前に置き換えるのが簡単です。それに応じてフィルタを置き換えます。フィールドがクエリで使用された回数を棒グラフで視覚化した Explore が表示されます。

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

上の図のシステムアクティビティ Explore では、flights.count と flights.depart_week が Explore で一般的に選択されている 2 つのフィールドであることがわかります。このため、これらは集約テーブルに含めるフィールドに適しています。

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

Summary

上記の手順は、ダッシュボード、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 で使用されている集約テーブルには、同じデータグループを使用する必要があります。データグループを使用できない場合は、代わりに sql_trigger_value パラメータを使用することもできます。sql_trigger_value の一般的な日付ベースの値を以下に示します。

sql_trigger_value: SELECT CURRENT_DATE() ;;

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

タイムフレームロジック

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

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

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

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

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

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

Summary

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

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

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

はじめに

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

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

基本的な例

販売機会の特定

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

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

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

Summary

集約テーブルの設計

集約テーブルの粒度

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

集約テーブルの構築

永続性

タイムフレーム ロジック

Summary

タイムフレームロジック