SQL-Konzepte für abgeleitete Tabellen

LookML-Ansichten basieren in der Regel auf vorhandenen Tabellen in der 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 die Ergebnisse tatsächliche Tabellen in der Datenbank.

In diesem Leitfaden erfahren Sie Folgendes:

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

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

Das folgende Beispiel zeigt, wie Looker eine SQL-Abfrage generieren könnte, die einen CTE zum Abfragen einer abgeleiteten Tabelle verwendet:

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

Das folgende Beispiel zeigt, wie Looker eine SQL-Abfrage generieren könnte, die eine Inline-Ansicht zur Abfrage einer abgeleiteten Tabelle verwendet:

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 im Parameter derived_table einer Ansicht. Auf diese Weise können Sie die Spalten der abgeleiteten Tabelle mithilfe von SQL definieren.

In der folgenden Beispiel-LookML-Datei wird beispielsweise 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-SELECT-Anweisung im generierten SQL je nach Dialekt entweder als CTE- oder Inline-Ansicht.

Die Verwendung von SQL zum Definieren abgeleiteter Tabellen kann einige Einschränkungen haben. Im Beispiel aus dem Abschnitt SQL-basierte abgeleitete Tabellen gelten beispielsweise folgende Ü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, einschließlich der Definition Ihrer SQL-basierten abgeleiteten Tabelle.
  • Die SQL in der Definition der abgeleiteten Tabelle muss im richtigen Dialekt für die zugrunde liegende Datenbank angegeben werden. Wenn die Daten in eine andere Datenbank verschoben werden, muss die Definition der SQL-basierten abgeleiteten Tabelle geändert werden.

Daher sind native abgeleitete Tabellen oft die bessere Wahl.

Native abgeleitete Tabellen

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

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

Sie können die LookML für die native abgeleitete Tabelle manuell definieren. Die einfachste Methode zum Erstellen einer nativen abgeleiteten Tabelle besteht jedoch darin, die abgeleitete Tabelle in Looker anhand einer Explore-Abfrage erstellen zu lassen. Wählen Sie in Ihrem Explore die Felder aus, die Sie in der abgeleiteten Tabelle verwenden möchten, und klicken Sie im Dreipunkt-Menü des Explores auf LookML abrufen > Abgeleitete Tabelle, um die LookML abzurufen. 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 die abgeleitete Tabelle erforderlich sind. Sie können die LookML in eine Ansichtsdatei in Ihrem Projekt kopieren, um die abgeleitete Tabelle zu erstellen.

Im folgenden Beispiel sehen Sie eine Tabelle mit nativ abgeleiteten Daten, 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, sodass Definitionen nicht wiederholt werden müssen.

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 oder 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 gemäß dem von Ihnen angegebenen Zeitplan neu generiert. Weitere Informationen finden Sie unter Abgeleitete Tabellen in Looker.