SQL-Konzepte für abgeleitete Tabellen

LookML-Ansichten basieren in der Regel auf vorhandenen Tabellen in Ihrer Datenbank. Sie können aber auch Ansichten erstellen, die auf SQL-SELECT-Anweisungen basieren. In Looker wird diese Art von Ansicht als abgeleitete Tabelle bezeichnet. Abgeleitete Tabellen sind Abfragen, deren Ergebnisse so verwendet werden, als wären sie tatsächliche Tabellen in der Datenbank.

In diesem Leitfaden werden die folgenden Themen behandelt:

So generiert Looker den SQL-Code für abgeleitete Tabellen

Wenn Sie abgeleitete Tabellen in LookML abfragen, übersetzt Looker Ihre Abfragen je nach Dialekt in SQL-Anweisungen als Common Table Expressions (CTEs) oder Inline-Ansichten. Die generierten SQL-Abfragen können also einem der folgenden Beispiele ähneln.

Im folgenden Beispiel wird gezeigt, wie Looker eine SQL-Abfrage generieren kann, in der ein CTE verwendet wird, um eine abgeleitete Tabelle abzufragen:

WITH (
    SELECT o.user_id as id
    FROM orders AS o
    INNER JOIN order_items AS oi ON o.id == oi.order_id
    ORDER BY SUM(oi.total_sale_price) DESC
    GROUP BY o.customer_id
    LIMIT 100
    ) AS top_100_users
SELECT ...
FROM users AS u
INNER JOIN top_100_users ON u.id == top_100_users.id
WHERE ...

Im folgenden Beispiel wird gezeigt, wie Looker eine SQL-Abfrage generieren kann, die eine Inline-Ansicht verwendet, um eine abgeleitete Tabelle abzufragen:

SELECT ...
FROM users AS u
INNER JOIN (
    SELECT o.user_id as id
    FROM orders AS o
    INNER JOIN order_items AS oi ON o.id == oi.order_id
    ORDER BY SUM(oi.total_sale_price) DESC
    GROUP BY o.customer_id
    LIMIT 100
    ) AS top_100_users ON u.id == top_100_users.id
WHERE ...

SQL-basierte abgeleitete Tabellen

Um eine SQL-basierte abgeleitete Tabelle zu erstellen, definieren Sie eine SQL-Abfrage direkt in der LookML. Verwenden Sie dazu den Parameter sql innerhalb des Parameters derived_table einer Ansicht. So können Sie die Spalten der abgeleiteten Tabelle mit SQL definieren.

Im folgenden Beispiel-LookML wird eine abgeleitete Tabelle namens top_100_users definiert, in der die 100 Nutzer mit den höchsten Gesamtausgaben für alle ihre Bestellungen ermittelt werden:

view: top_100_users {
  derived_table: {
    sql: SELECT o.user_id as id
         FROM orders AS o
         INNER JOIN order_items AS oi ON o.id == oi.order_id
         ORDER BY SUM(oi.total_sale_price) DESC
         GROUP BY o.customer_id
         LIMIT 100 ;;
  }

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

Wenn in einer Explore-Abfrage auf die Ansicht top_100_users verwiesen wird, verwendet Looker diese SQL-Anweisung SELECT im generierten SQL-Code als CTE oder Inline-Ansicht, je nach Dialekt.

Die Definition abgeleiteter Tabellen mit SQL kann einige Einschränkungen haben. Im Beispiel aus dem Abschnitt „SQL-basierte abgeleitete Tabellen“ gelten beispielsweise die folgenden Überlegungen:

  • Die Beziehung zwischen den Tabellen orders und order_items ist wahrscheinlich bereits im LookML-Modell definiert. Wenn sich die zugrunde liegenden Tabellennamen in Ihrer Datenbank ändern, müssen die Tabellennamen an mehreren Stellen aktualisiert werden, auch in der Definition der SQL-basierten abgeleiteten Tabelle.
  • Das SQL in der Definition der abgeleiteten Tabelle muss dem richtigen Dialekt für die zugrunde liegende Datenbank entsprechen. Wenn die Daten in eine andere Datenbank verschoben werden, muss die Definition der SQL-basierten abgeleiteten Tabelle geändert werden.

Aus diesen Gründen sind native abgeleitete Tabellen oft die bessere Wahl.

Native abgeleitete Tabellen

In Looker werden native abgeleitete Tabellen mit LookML definiert. Im Gegensatz zu SQL-basierten abgeleiteten Tabellen nutzen Sie Ihr LookML-Modell bei der Definition einer nativen abgeleiteten Tabelle auf folgende Weise:

  • Wenn sich der Tabellenname in der Datenbank ändert, müssen Sie ihn nur einmal im LookML-Modell aktualisieren. Da Ihre native abgeleitete Tabelle auf das vorhandene LookML-Objekt verweist, in dem die Datenbanktabelle definiert ist, wird automatisch auf die entsprechende Tabelle verwiesen.
  • Wenn Sie zu einem anderen Dialekt wechseln, ist das LookML für die native abgeleitete Tabelle weiterhin gültig, da Looker den SQL-Code generiert, der für Ihre Datenbankverbindung geeignet ist.

Sie können die LookML für die native abgeleitete Tabelle manuell definieren. Am einfachsten lässt sich eine native abgeleitete Tabelle jedoch erstellen, indem Sie Looker die abgeleitete Tabelle aus einer Explore-Abfrage erstellen lassen. Wählen Sie in Ihrem Explore die Felder aus, die Sie in der abgeleiteten Tabelle verwenden möchten, und rufen Sie die LookML über das Zahnradmenü des Explores mit der Option LookML abrufen > Abgeleitete Tabelle ab. Looker generiert die LookML, die Sie zum Erstellen einer abgeleiteten Tabelle aus Ihrer Explore-Abfrage benötigen, einschließlich der relevanten Felddefinitionen aus Ihrem LookML-Modell, die zum Erstellen der Spalten für Ihre abgeleitete Tabelle erforderlich sind. Sie können den LookML-Code in eine Ansichtsdatei in Ihrem Projekt kopieren, um die abgeleitete Tabelle zu erstellen.

Im folgenden Beispiel sehen Sie eine abgeleitete Tabelle, in der die 100 Nutzer mit den höchsten Gesamtausgaben für alle ihre Bestellungen aufgeführt sind:

view: top_100_users {
  derived_table: {
    explore_source:  orders {
      column: id {
        field: orders.customer_id
      }
      sorts: [order_items.sum_total_sale_price desc]
      limit: 100
    }
  }
}

Wenn Sie eine native abgeleitete Tabelle in einem Explore abfragen, werden alle Details der SQL-Abfrage für die native abgeleitete Tabelle automatisch generiert. Die Definitionen der Spalten einer LookML-Ansicht mit einer nativen abgeleiteten Tabelle werden aus den Definitionen des zugrunde liegenden Explores abgeleitet. Dadurch müssen keine Definitionen wiederholt werden.

Persistente abgeleitete Tabellen (PDTs)

In Looker können Sie sowohl temporäre abgeleitete Tabellen als auch persistente abgeleitete Tabellen erstellen. Nachdem Sie eine SQL-basierte abgeleitete Tabelle oder eine native abgeleitete Tabelle erstellt haben, können Sie Persistenz hinzufügen, damit Looker die Tabelle in ein Scratch-Schema in Ihrer Datenbank schreibt und sie nach dem von Ihnen angegebenen Zeitplan neu generiert. Weitere Informationen finden Sie unter Abgeleitete Tabellen in Looker.