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 erfahren Sie Folgendes:

So generiert Looker SQL für abgeleitete Tabellen

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.

Im folgenden Beispiel wird gezeigt, wie Looker eine SQL-Abfrage generieren kann, bei der eine abgeleitete Tabelle mithilfe eines CTE abgefragt wird:

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, bei der eine abgeleitete Tabelle mithilfe einer Inline-Ansicht abgefragt wird:

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. So können Sie die Spalten der abgeleiteten Tabelle mit 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 als Inline-Ansicht.

Die Verwendung von SQL zum Definieren abgeleiteter Tabellen kann einige Einschränkungen haben. Für das Beispiel aus dem Abschnitt zu SQL-basierten abgeleiteten 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, einschließlich der Definition der SQL-basierten abgeleiteten Tabelle.
  • Der SQL-Code in der Definition der abgeleiteten Tabelle muss dem richtigen Dialekt der zugrunde liegenden 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 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, ist die LookML für die native abgeleitete Tabelle weiterhin 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. Am einfachsten lassen sich native abgeleitete Tabellen 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 klicken Sie im Dreipunkt-Menü des Explores auf LookML abrufen > Abgeleitete Tabelle, um die LookML zu erhalten. 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 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.