Looker-Cookbooks: Abgeleitete Tabellen in Looker optimal nutzen

Abgeleitete Tabellen eröffnen eine Welt erweiterter Analysemöglichkeiten, aber ihre Herangehensweise, Implementierung und Fehlerbehebung kann schwierig sein. Dieses Kochbuch enthält die beliebtesten Anwendungsfälle für abgeleitete Tabellen in Looker.

Diese Seite enthält die folgenden Beispiele:

Abgeleitete Tabellenressourcen

In diesen Kochbüchern wird vorausgesetzt, dass Sie grundlegende Kenntnisse in LookML und abgeleiteten Tabellen haben. Sie sollten mit dem Erstellen und Bearbeiten der Modelldatei vertraut sein. Wenn Sie Ihr Wissen zu einem dieser Themen auffrischen möchten, nutzen Sie die folgenden Ressourcen:

Täglich um 03:00 Uhr einen Tisch aufbauen

Die Daten in diesem Beispiel gehen täglich um 2:00 Uhr ein. Die Ergebnisse einer Abfrage für diese Daten sind immer die gleichen, unabhängig davon, ob sie um 3:00 Uhr oder um 21:00 Uhr ausgeführt wird. Daher ist es sinnvoll, die Tabelle einmal täglich zu erstellen und die Nutzer die Ergebnisse aus einem Cache abrufen zu lassen.

Wenn Sie Ihre Datengruppe in die Modelldatei aufnehmen, können Sie sie für mehrere 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 den Parameter datagroup_trigger der Definition derived_table in der Ansichtsdatei hinzu und geben Sie den Namen der Datengruppe an, die Sie verwenden möchten. In diesem Beispiel ist 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.

Das nächste Beispiel baut auf dem Tabellenbeispiel orders auf und zeigt, wie die Tabelle schrittweise erstellt wird. Jeden Tag kommen neue Bestelldaten hinzu und können an die vorhandene Tabelle angehängt werden, wenn Sie einen increment_key-Parameter und einen increment_offset-Parameter 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 neue Daten abgefragt und in diesem Beispiel an die PDT angehängt werden sollen.

Der Wert increment_offset wird auf 3 gesetzt, um die Anzahl der vorherigen Zeiträume (in der Granularität des Inkrementschlüssels) anzugeben, 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. Nachdem die primäre Abfrage ausgeführt wurde, werden alle derived_column-Deklarationen in einer separaten Karte / einem separaten Ticket ausgeführt.

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

Das folgende Beispiel zeigt, wie Sie eine native abgeleitete Tabelle erstellen, die die Spalten user_id, order_id und created_time enthält. 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 Explore des Parameters explore_source nicht vorhanden sind. Jeder derived_column-Parameter hat einen sql-Parameter, der angibt, wie der Wert zu erstellen ist.

Bei der sql-Berechnung können alle Spalten verwendet werden, die Sie mit column-Parametern 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 average_customer_order-Spalte erstellt, die aus den Spalten lifetime_customer_value und lifetime_number_of_orders 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 PATs in Ihrer Datenbank gespeichert werden, sollten Sie sie mit den folgenden Strategien optimieren, die von Ihrem Dialekt unterstützt werden:

Wenn Sie beispielsweise Persistenz schaffen möchten, können Sie festlegen, dass die PDT neu erstellt wird, wenn die Datengruppe orders_datagroup auslöst, und dann Indexe für customer_id und first_order hinzufügen, wie im Folgenden 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 tun sollten, um die Abfrageleistung zu verbessern.

PATs zum Testen von Optimierungen verwenden

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

Stellen Sie sich einen Fall vor, bei dem Sie eine Tabelle haben, aber verschiedene Indexe testen möchten. Ihr anfänglicher LookML-Code für die Ansicht könnte wie folgt aussehen:

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

Zum Testen von Optimierungsstrategien können Sie den Parameter indexes verwenden, um Indexe wie unten gezeigt zu LookML hinzuzufügen:

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

Fragen Sie die Ansicht einmal ab, um die PAT 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 den SQL-Operator UNION oder UNION ALL in beiden abgeleiteten Tabellen ausführen, wenn Ihr SQL-Dialekt dies unterstützt. Die Operatoren UNION und UNION ALL kombinieren die Ergebnismengen von zwei Abfragen.

Dieses Beispiel zeigt, wie eine SQL-basierte abgeleitete Tabelle mit einem 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 Anweisung UNION im Parameter sql erzeugt eine abgeleitete Tabelle, in der die Ergebnisse beider Abfragen kombiniert werden.

Der Unterschied zwischen UNION und UNION ALL besteht darin, dass UNION ALL keine doppelten Zeilen entfernt. Bei der Verwendung von UNION und UNION ALL sind Leistungsaspekte zu beachten, da der Datenbankserver zusätzliche Schritte ausführen muss, um die doppelten Zeilen zu entfernen.

Summe einer Summe ermitteln (Dimensionieren)

In SQL – und somit auch Looker – können Sie Abfragen 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 einer Aggregatfunktion zu gruppieren (z. B. um die Summe einer Summe zu bilden), müssen Sie eine Messung „dimensionieren“. Eine Möglichkeit, dies zu tun, ist die Verwendung einer abgeleiteten Tabelle, die effektiv eine Unterabfrage 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. Führen Sie anschließend die folgenden Schritte aus, um den LookML-Code für die native (oder SQL-basierte) abgeleitete Tabelle zu generieren:

  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 den LookML-Code zum Erstellen einer nativen abgeleiteten Tabelle für das Explore aufzurufen.

  3. Kopieren Sie den LookML-Code.

Nachdem Sie den generierten LookML-Code kopiert haben, fügen Sie ihn mithilfe der folgenden Schritte in eine Ansichtsdatei ein:

  1. Gehen Sie im Entwicklungsmodus zu Ihren Projektdateien.

  2. Klicken Sie in der Looker-IDE oben in der Projektdateiliste auf das Pluszeichen + und wählen Sie Ansicht erstellen aus. Sie können auch 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.

Sammeltabellen mit aggregierter Bekanntheit

In Looker werden Sie möglicherweise auf sehr große Datensätze oder Tabellen stoßen, die Aggregationstabellen oder Rollups erfordern, um leistungsfähig zu sein.

Mit dem Aggregatmodus von Looker können Sie vorab aggregierte Tabellen mit verschiedenen Detaillierungsgrad, Dimensionalität und Aggregation erstellen und Looker darüber informieren, wie sie in vorhandenen Explores verwendet werden sollen. Abfragen verwenden dann diese Rollup-Tabellen ohne Benutzereingabe, wenn Looker dies für angemessen hält. Dies reduziert die Größe von Abfragen, verkürzt die Wartezeiten und verbessert die Nutzerfreundlichkeit.

Im Folgenden sehen Sie eine sehr einfache Implementierung in einem Looker-Modell, um zu zeigen, wie einfach Aggregatfunktion sein kann. Mit einer hypothetischen Tabelle mit Flügen in der Datenbank, die eine Zeile für jeden über den FAA aufgezeichneten Flug enthält, können Sie diese Tabelle in Looker mit einer eigenen Ansicht und einem eigenen Explore modellieren. Im Folgenden finden Sie den LookML-Code für eine aggregierte 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 aggregierten Tabelle kann ein Nutzer das flights-Explore abfragen. Looker verwendet dann automatisch die aggregierte Tabelle, um Abfragen zu beantworten. Eine ausführlichere Schritt-für-Schritt-Anleitung zum Thema „Aggregatfunktion“ finden Sie in der Anleitung zur Aggregatfunktion.