Looker-Rezeptbücher: Abgeleitete Tabellen in Looker optimal nutzen

Abgeleitete Tabellen eröffnen Ihnen eine Fülle erweiterter Analysemöglichkeiten. Das Herangehen, Implementieren und Beheben von Problemen kann jedoch entmutigend sein. Dieses Cookbook enthält die gängigsten Anwendungsfälle für abgeleitete Tabellen in Looker.

Diese Seite enthält die folgenden Beispiele:

Ressourcen für abgeleitete Tabellen

Diese Cookbooks setzen grundlegende Kenntnisse zu LookML und abgeleiteten Tabellen voraus. Sie sollten mit dem Erstellen von Ansichten und Bearbeiten der Modelldatei vertraut sein. Wenn Sie Ihr Wissen zu diesen Themen auffrischen möchten, sehen Sie sich die folgenden Ressourcen an:

Tisch täglich um 03:00 Uhr erstellen

In diesem Beispiel werden die Daten jeden Tag um 2:00 Uhr erfasst. Eine Abfrage dieser Daten wird unabhängig davon, ob sie um 03:00 Uhr oder um 21:00 Uhr durchgeführt wird, immer gleich. Daher ist es sinnvoll, die Tabelle einmal täglich zu erstellen und die Nutzer Ergebnisse aus einem Cache abrufen zu lassen.

Wenn Sie Ihre Datengruppe in die Modelldatei aufnehmen, können Sie sie mit mehreren Tabellen und Explores wiederverwenden. Diese Datengruppe enthält einen sql_trigger_value-Parameter, der der Datengruppe mitteilt, wann die abgeleitete Tabelle ausgelöst und neu erstellt werden soll.

Weitere Beispiele für Triggerausdrücke finden Sie in der Dokumentation zu sql_trigger_value.


## in the model file

datagroup: standard_data_load {
  sql_trigger_value: SELECT FLOOR(((TIMESTAMP_DIFF(CURRENT_TIMESTAMP(),'1970-01-01 00:00:00',SECOND)) - 60*60*3)/(60*60*24)) ;;
  max_cache_age: "24 hours"
}

explore: orders {
…

Fügen Sie der derived_table-Definition in der Ansichtsdatei den Parameter datagroup_trigger hinzu und geben Sie den Namen der Datengruppe an, die Sie verwenden möchten. In diesem Beispiel lautet die Datengruppe standard_data_load.


view: orders {
 derived_table: {
  indexes: ["id"]
  datagroup_trigger: standard_data_load
    sql:
      SELECT
        user_id,
        id,
        created_at,
        status
      FROM
        demo_db.orders
      GROUP BY
        user_id ;;
    }

…
}

Neue Daten an eine große Tabelle anhängen

Eine inkrementelle PDT ist eine persistente abgeleitete Tabelle, die von Looker erstellt wird, indem neue Daten an die Tabelle angehängt werden, anstatt die gesamte Tabelle neu zu erstellen.

Im nächsten Beispiel wird auf dem Beispiel für die Tabelle orders aufgebaut, um zu zeigen, wie die Tabelle nach und nach aufgebaut wird. Täglich kommen neue Bestelldaten hinzu, die der vorhandenen Tabelle angefügt werden können, wenn Sie den Parameter increment_key und den Parameter increment_offset hinzufügen.


view: orders {
 derived_table: {
    indexes: ["id"]
    increment_key: "created_at"
    increment_offset: 3
    datagroup_trigger: standard_data_load
    distribution_style: all
    sql:
      SELECT
        user_id,
        id,
        created_at,
        status
      FROM
        demo_db.orders
      GROUP BY
        user_id ;;
    }

  dimension: id {
    primary_key: yes
    type: number
    sql: ${TABLE}.id ;;  }

…
}

Der Wert increment_key wird auf created_at gesetzt. Dies ist das Zeitinkrement, für das in diesem Beispiel neue Daten abgefragt und an die PDT angehängt werden sollen.

Der Wert für increment_offset ist auf 3 festgelegt, um die Anzahl der vorherigen Zeiträume anzugeben (in der Granularität des Inkrementschlüssels), die neu erstellt werden, um spät eintreffende Daten zu berücksichtigen.

SQL-Fensterfunktionen verwenden

Einige Datenbankdialekte unterstützen Fensterfunktionen, insbesondere zum Erstellen von Sequenznummern, Primärschlüsseln, laufenden und kumulativen Summen und anderen nützlichen mehrzeiligen Berechnungen. Nach Ausführung der primären Abfrage werden alle derived_column-Deklarationen gesondert ausgeführt.

Wenn Ihr Datenbankdialekt Fensterfunktionen unterstützt, können Sie diese in Ihrer nativen abgeleiteten Tabelle nutzen. Erstellen Sie einen derived_column-Parameter mit einem sql-Parameter, der die Fensterfunktion enthält. Beim Verweisen auf Werte sollten Sie den Spaltennamen verwenden, der in Ihrer nativen abgeleiteten Tabelle definiert wurde.

Im folgenden Beispiel wird gezeigt, wie Sie eine native abgeleitete Tabelle mit den Spalten user_id, order_id und created_time erstellen. Anschließend würden Sie eine abgeleitete Spalte mit einer SQL ROW_NUMBER()-Fensterfunktion verwenden, um eine Spalte zu berechnen, die die Sequenznummer einer Kundenbestellung enthält.

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
  }
}

Abgeleitete Spalten für berechnete Werte erstellen

Sie können derived_column-Parameter hinzufügen, um Spalten anzugeben, die im explorativen Analysetool des Parameters explore_source nicht vorhanden sind. Jeder derived_column-Parameter hat einen sql-Parameter, der angibt, wie der Wert konstruiert wird.

Für die Berechnung von sql können alle Spalten verwendet werden, die Sie mithilfe der column-Parameter angegeben haben. Abgeleitete Spalten können zwar keine Summenfunktionen enthalten, aber Berechnungen, die an einer einzelnen Tabellenzeile durchgeführt werden können.

In diesem Beispiel wird eine Spalte vom Typ average_customer_order erstellt, die aus den Spalten lifetime_customer_value und lifetime_number_of_orders in der nativen abgeleiteten Tabelle berechnet wird.

view: user_order_facts {
  derived_table: {
    explore_source: order_items {
      column: user_id {
        field: users.id
      }
      column: lifetime_number_of_orders {
        field: order_items.count
      }
      column: lifetime_customer_value {
        field: order_items.total_profit
      }
      derived_column: average_customer_order {
        sql:  lifetime_customer_value / lifetime_number_of_orders ;;
      }
    }
  }

  dimension: user_id {
    hidden: yes
  }
  dimension: lifetime_number_of_orders {
    type: number
  }
  dimension: lifetime_customer_value {
    type: number
  }
  dimension: average_customer_order {
    type: number
  }
}

Optimierungsstrategien

Da PDTs in Ihrer Datenbank gespeichert werden, sollten Sie die PDTs mit den folgenden Strategien optimieren, je nach Unterstützung durch Ihren Dialekt:

Um beispielsweise Persistenz zu erhöhen, können Sie die PDT so einstellen, dass sie neu erstellt wird, wenn die Datengruppe orders_datagroup ausgelöst wird, und dann Indexe für customer_id und first_order hinzufügen, wie unten gezeigt:

view: customer_order_summary {
  derived_table: {
    explore_source: orders {
      ...
    }
    datagroup_trigger: orders_datagroup
    indexes: ["customer_id", "first_order"]
  }
}

Wenn Sie keinen Index (oder eine Entsprechung für Ihren Dialekt) hinzufügen, werden Sie von Looker gewarnt, dass Sie dies zur Steigerung der Abfrageleistung nachholen sollten.

PDTs zum Testen von Optimierungen verwenden

Sie können PDTs verwenden, um verschiedene Indexierungen, Verteilungen und andere Optimierungsoptionen zu testen, ohne viel Unterstützung von Ihren DBA- oder ETL-Entwicklern zu benötigen.

Angenommen, Sie haben eine Tabelle, möchten aber verschiedene Indizes testen. Die erste LookML-Datei für die Ansicht könnte so aussehen:

view: customer {
  sql_table_name: warehouse.customer ;;
}

Wenn Sie Optimierungsstrategien testen möchten, können Sie mit dem Parameter indexes Indizes zur LookML hinzufügen, wie unten gezeigt:

view: customer {
  # sql_table_name: warehouse.customer
  derived_table: {
    sql: SELECT * FROM warehouse.customer ;;
    persist_for: "8 hours"
    indexes: [customer_id, customer_name, salesperson_id]
  }
}

Stellen Sie eine einmalige Abfrage an die Ansicht, um die PDT zu generieren. Führen Sie dann die Testabfragen aus und vergleichen Sie die Ergebnisse. Wenn Ihre Ergebnisse positiv ausfallen, können Sie Ihr DBA- oder ETL-Team bitten, die Indexe der ursprünglichen Tabelle hinzuzufügen.

UNION zwei Tabellen

Sie können einen SQL-UNION- oder UNION ALL-Operator in beiden abgeleiteten Tabellen ausführen, sofern Ihr SQL-Dialekt dies unterstützt. Die Operatoren UNION und UNION ALL kombinieren die Ergebnismengen von zwei Abfragen.

In diesem Beispiel wird gezeigt, wie eine SQL-basierte abgeleitete Tabelle mit einer UNION aussieht:

view: first_and_second_quarter_sales {
  derived_table: {
    sql:
       SELECT * AS sales_records
       FROM sales_records_first_quarter
       UNION
       SELECT * AS sales_records
       FROM sales_records_second_quarter ;;
   }
}

Die UNION-Anweisung im sql-Parameter erzeugt eine abgeleitete Tabelle, in der die Ergebnisse beider Abfragen kombiniert werden.

Der Unterschied zwischen UNION und UNION ALL besteht darin, dass bei UNION ALL keine doppelten Zeilen entfernt werden. Bei der Verwendung von UNION im Vergleich zu UNION ALL sind Leistungsaspekte zu beachten, da der Datenbankserver zusätzliche Arbeit leisten muss, um die doppelten Zeilen zu entfernen.

Summe einer Summe ermitteln (Messwertdimensionierung)

In SQL und damit auch in Looker können Sie eine Abfrage in der Regel nicht nach den Ergebnissen einer Aggregatfunktion gruppieren (in Looker als Messwerte dargestellt). Sie können nur nach nicht aggregierten Feldern gruppieren, die in Looker als Dimensionen dargestellt werden.

Um nach einem Aggregat zu gruppieren (um beispielsweise die Summe einer Summe zu ermitteln), müssen Sie „dimensionieren“. ein Maß. Eine Möglichkeit dazu ist die Verwendung einer abgeleiteten Tabelle, die eine untergeordnete Abfrage des Aggregats erstellt.

Ausgehend von einem Explore kann Looker LookML für die gesamte abgeleitete Tabelle oder einen großen Teil davon generieren. Erstellen Sie einfach ein Explore und wählen Sie alle Felder aus, die in der abgeleiteten Tabelle enthalten sein sollen. Anschließend generieren Sie den LookML-Code für die native (oder SQL-basierte) abgeleitete Tabelle wie folgt:

  1. Klicken Sie auf das Zahnrad-Menü des Explores und wählen Sie LookML abrufen aus.

  2. Klicken Sie auf den Tab Abgeleitete Tabelle, um die LookML zum Erstellen einer nativen abgeleiteten Tabelle für das Explore aufzurufen.

  3. Kopieren Sie den LookML-Code.

Fügen Sie den kopierten LookML-Code in eine Ansichtsdatei ein:

  1. Rufen Sie im Entwicklungsmodus Ihre Projektdateien auf.

  2. Klicken Sie in der Looker IDE oben in der Liste der Projektdateien auf + und wählen Sie Create View (Ansicht erstellen) aus. Alternativ können Sie die Datei im Ordner erstellen. Klicken Sie dazu auf das Menü eines Ordners und wählen Sie Ansicht erstellen aus.

  3. Geben Sie der Ansicht einen aussagekräftigen Namen.

  4. Optional können Sie Spaltennamen ändern, abgeleitete Spalten festlegen und Filter hinzufügen.

Zusammenfassungstabellen mit Aggregatfunktion

In Looker stoßen Sie häufig auf sehr große Datensätze oder Tabellen, die für eine gute Leistung Aggregationstabellen oder Aggregationen erfordern.

Mit Lookers Aggregatfunktion können Sie aggregierte Tabellen mit verschiedenen Detaillierungsgraden, Dimensionalität und Aggregation vorkonstruieren. und Sie können Looker darüber informieren, wie sie in vorhandenen Explores verwendet werden können. Abfragen verwenden dann diese Rollup-Tabellen, sofern Looker dies für angemessen hält, ohne Nutzereingabe. Dadurch wird die Abfragegröße reduziert, die Wartezeiten verkürzt und die Nutzerfreundlichkeit verbessert.

Im Folgenden sehen Sie eine sehr einfache Implementierung in einem Looker-Modell, um zu veranschaulichen, wie einfach die Aggregationserkennung sein kann. Bei einer hypothetischen Tabelle mit Flügen in der Datenbank mit einer Zeile für jeden Flug, der über die FAA aufgezeichnet wird, können Sie diese Tabelle in Looker mit einer eigenen Ansicht und einem eigenen Explore modellieren. Im Folgenden finden Sie die LookML für eine zusammengefasste Tabelle, die Sie für das Explore definieren können:

  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;;
      }
    }
  }

Mit dieser Aggregierungstabelle kann ein Nutzer das flights-Explore abfragen. Looker verwendet dann automatisch die Aggregierungstabelle, um Abfragen zu beantworten. Eine ausführlichere Anleitung zu aggregierten Markenbekanntheitswerten finden Sie im Hilfeartikel Aggregierte Markenbekanntheit.