SQL-Einbindung und Verweise auf LookML-Objekte

Um leistungsstarken LookML-Code zu schreiben, müssen Sie auch dann auf vorhandene Dimensionen, Messwerte, Ansichten oder abgeleitete Tabellen verweisen können, wenn sie nicht zum aktuellen Scope gehören. Darüber hinaus müssen Sie auf Spalten in der zugrunde liegenden Tabelle verweisen sowie Funktionsaufrufe Ihres Datenbankdialekts nutzen, um diese Werte zu bearbeiten.

Substitutionsoperator ($)

Durch den Substitutionsoperator $ ist LookML-Code leichter wiederverwendbar und modularer. So können Sie auf andere Ansichten und abgeleitete Tabellen, Spalten in einer SQL-Tabelle oder LookML-Dimensionen und -Messwerte verweisen. Dies ist aus zwei Gründen sinnvoll. Erstens haben Sie möglicherweise bereits eine sehr komplizierte Dimension oder einen sehr komplizierten Messwert erstellt und müssen die Komplexität nicht noch einmal neu entwickeln. Zweitens können Änderungen, die Sie an einer Dimension oder einem Messwert vornehmen, auf alles andere angewendet werden, das darauf zurückgreift.

Es gibt mehrere Möglichkeiten, den Substitutionsoperator zu verwenden:

${TABLE}.column_name verweist auf eine Spalte in der Tabelle, die mit der Ansicht verbunden ist, an der Sie gerade arbeiten. Beispiel:

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

${field_name} verweist auf eine Dimension oder einen Messwert in der Ansicht, an der Sie gerade arbeiten. Beispiel:

measure: total_population {
  type: sum
  sql: ${population} ;;
}

${view_name.field_name} verweist auf eine Dimension oder einen Messwert aus einer anderen Ansicht. Beispiel:

dimension: lifetime_orders {
  type: number
  sql: ${user_order_facts.lifetime_orders} ;;
}

${view_name.SQL_TABLE_NAME} verweist auf eine andere Ansicht oder abgeleitete Tabelle. In diesem Verweis ist SQL_TABLE_NAME eine literale Zeichenfolge. Sie muss nicht ersetzt werden. Beispiel:

explore: trips {
  view_label: "Long Trips"
  # This will ensure that we only see trips that are longer than average!
  sql_always_where: ${trips.trip_duration}>=(SELECT tripduration FROM ${average_trip_duration.SQL_TABLE_NAME});;
}

${view_name.SQL_TABLE_NAME} funktioniert nicht mit dem Parameter sql_trigger, der mit Datengruppen verwendet wird.

Scoping und Benennung

Sie können Explores, Ansichten, Feldern und Sätzen Namen geben. Diese Looker-Kennungen werden ohne Anführungszeichen geschrieben.

LookML-Felder und -Sätze verfügen über vollständige Namen und Kurznamen:

• Vollständige Namen haben folgende Form: <view>.<field-name | set-name>. Die linke Seite vor dem Punkt entspricht dem Scope, also der Ansicht, die das Feld bzw. den Satz enthält. Auf der rechten Seite wird der jeweilige Feld- oder Satzname angegeben.
• Kurznamen haben die Form <field-name | set-name> ohne trennenden Punkt. Looker erweitert Kurznamen mithilfe des Scopes, in dem sie verwendet werden, zu vollständigen Namen.

Im folgenden Beispiel werden diverse Formen von Namen und Scopes dargestellt. Diese Gruppe von Feldern ist kein realistisches Beispiel, sondern soll lediglich veranschaulichen, wie viele verschiedene Möglichkeiten es für Scoping-Ausdrücke gibt.

view: orders {                   # "orders" becomes the containing scope
  measure: count {               # short name, equivalent to orders.count
    type: count
  }
  dimension: customer_id {       # short name, equivalent to orders.customer_id
    type: number
    sql: ${TABLE}.customer_id ;;
  }
  dimension: customer_address {  # short name, equivalent to orders.customer_address
    sql: ${customer.address} ;;  # full name, references a field defined in the "customer" view
  }
  set: drill_fields {            # short name, equivalent to orders.drill_fields
    fields: [
      count,                     # short name, equivalent to orders.count
      customer.id                # full name, references a field defined in the "customer" view
    ]
  }
}

Beachten Sie in der Deklaration dimension: customer_address, dass sich die zugrunde liegende Ansicht für den SQL-Block (customer) vom umschließenden Ansichtsscope (orders) unterscheidet. Dies kann sinnvoll sein, wenn Sie Felder zwischen zwei verschiedenen Ansichten vergleichen müssen.

Wenn eine Ansicht (im Folgenden „Ansicht A“ genannt) auf ein Feld verweist, das in einer anderen Ansicht (im Folgenden „Ansicht B“ genannt) definiert ist, sind einige Punkte zu beachten:

1. Die Datei für Ansicht B muss mit dem include Parameter in dasselbe Modell wie Ansicht A aufgenommen werden.
2. Ansicht B muss in einem oder mehreren Explores mit Ansicht A verbunden sein. Weitere Informationen zu Joins finden Sie auf der Seite Mit Joins in LookML arbeiten.

SQL-Dialekt

Looker unterstützt viele Datenbanktypen wie MySQL, Postgres, Redshift und BigQuery. Jede Datenbank unterstützt ein etwas anderes Feature-Set mit unterschiedlichen Funktionsbezeichnungen. Dies wird als der SQL-Dialekt bezeichnet.

LookML ist so konstruiert, dass alle SQL-Dialekte verwendet werden können, und es wird kein Dialekt gegenüber einem anderen bevorzugt. In bestimmten LookML-Parametern müssen Sie jedoch SQL-Codeausdrücke verwenden (genannt SQL-Blöcke). Mit diesen Parametern gibt Looker den SQL-Ausdruck direkt an Ihre Datenbank weiter. Das heißt, Sie müssen den SQL-Dialekt verwenden, der Ihrer Datenbank entspricht. Beispiel: Wenn Sie eine SQL-Funktion verwenden, muss dies eine Funktion sein, die Ihre Datenbank unterstützt.

SQL-Blöcke

Einige LookML-Parameter verlangen rohe SQL-Ausdrücke, damit Looker nachvollziehen kann, wie Daten aus Ihrer Datenbank abgerufen werden sollen.

LookML-Parameter, die mit sql_ beginnen, erwarten irgendeine Art von SQL-Ausdruck. Beispiele sind: sql_always_where, sql_on und sql_table_name. Der gängigste LookML-Parameter für SQL-Blöcke ist sql. Er wird in Dimensions- und Messwert-Felddefinitionen verwendet, um den SQL-Ausdruck anzugeben, der die Dimension bzw. den Messwert definiert.

Der Code, den Sie in einem SQL-Block angeben, kann einfach sein, z. B. ein einzelner Feldname, oder komplex, z. B. eine korrelierte Unterauswahl. Der Inhalt kann recht komplex sein und fast jede denkbare Anforderung zum Ausdrücken benutzerdefinierter Abfragelogik in rohem SQL erfüllen. Hinweis: Der Code, den Sie in SQL-Blöcken verwenden, muss dem von der Datenbank verwendeten SQL-Dialekt entsprechen.

Beispiele für SQL-Blöcke für Dimensionen und Messwerte

Unten sind Beispiele für SQL-Blöcke für Dimensionen und Messwerte aufgeführt. Der LookML-Substitutionsoperator ($) kann bewirken, dass diese sql Deklarationen fälschlicherweise nicht wie SQL wirken. Doch nach einer Substitution ist die daraus resultierende Zeichenfolge reines SQL, das Looker in die SELECT-Klausel der Abfrage einfügt.

dimension: id {
  primary_key: yes
  sql: ${TABLE}.id ;;   # Specify the primary key, id
}
measure: average_cost {
  type: average
  value_format: "0.00"
  sql: ${order_items.cost} ;;   # Specify the field that you want to average
}
dimension: name {
  sql: CONCAT(${first_name}, ' ', ${last_name}) ;;
}
dimension: days_in_inventory {
  type: int
  sql: DATEDIFF(${sold_date}, ${created_date}) ;;
}

Wie in den letzten beiden Dimensionen zu sehen, können SQL-Blöcke Funktionen verwenden, die von der zugrunde liegenden Datenbank unterstützt werden (z. B. die MySQL-Funktionen CONCAT und DATEDIFF in diesem Beispiel).

Beispiel für einen SQL-Block mit einer korrelierten Unterauswahl

Sie können jede beliebige SQL-Anweisung in den SQL-Block eines Feldes einsetzen, einschließlich einer korrelierten Unterauswahl. Beispiel:

view: customers {
  dimension: id {
    primary_key: yes
    sql: ${TABLE}.id ;;
  }
  dimension: first_order_id {
    sql: (SELECT MIN(id) FROM orders o WHERE o.customer_id=customers.id) ;;
         # correlated subselect to derive the value for "first_order_id"
  }
}

Beispiel für einen SQL-Block für abgeleitete Tabellen

Abgeleitete Tabellen verwenden den SQL-Block, um die Abfrage zur Ableitung der Tabelle anzugeben. Beispiel:

view: user_order_facts {
  derived_table: {
    sql:            # Get the number of orders for each user
      SELECT
        user_id
        , COUNT(*) as lifetime_orders
      FROM orders
      GROUP BY 1 ;;
  }
  # later, dimension declarations reference the derived column(s)

  dimension: lifetime_orders {
    type: number
  }
}

LookML-Feldtypreferenzen

Wenn Sie ein bestehendes LookML-Feld in einem anderen Feld referenzieren, können Sie Looker anweisen, das referenzierte Feld als einen bestimmten Datentyp zu behandeln, indem Sie einen Doppelpunkt (::) gefolgt vom ausgewählten Typ verwenden. Wenn Sie beispielsweise die orders.created_date Dimension in einem anderen Feld referenzieren, können Sie die Syntax ${orders.created_date::date} verwenden, um sicherzustellen, dass das Feld created_date im von Looker generierten SQL als Datumsfeld behandelt wird und nicht als Zeichenfolge ausgegeben wird.

Welchen Datentyp Sie in einem Feldbezug verwenden können, hängt vom Datentyp des ursprünglichen Feldes ab, auf das Sie sich beziehen. Wenn Sie beispielsweise ein Stringfeld referenzieren, können Sie nur ::string als Datentyp angeben. Im Folgenden sehen Sie eine vollständige Liste der zulässigen Feldbezüge, die Sie für die einzelnen Felder verwenden können:

• In einem Verweis auf ein Stringfeld können Sie ::string verwenden.
• In einem Verweis auf ein Zahlenfeld können Sie ::string und ::number verwenden.
• In einem Verweis auf ein Datums- oder Zeitfeld können Sie ::string, ::date und ::datetime verwenden.
  Verweise mit ::string und ::date geben Daten in der Zeitzone der Abfrage zurück, während Verweise mit ::datetime Daten in der Zeitzone der Datenbank zurückgeben.
• In einem Verweis auf ein Ja/Nein-Feld können Sie ::string, ::number und ::boolean verwenden.
  Feldbezüge unter Verwendung des Datentyps ::boolean sind für Datenbankdialekte, die den booleschen Datentyp nicht unterstützen, nicht verfügbar.
• In einem Verweis auf ein Standortfeld können Sie ::latitude und ::longitude verwenden.

LookML-Feldtypreferenzen mit Datumsfeldern verwenden

Angenommen, Sie haben eine Dimension enrollment_month und eine Dimension graduation_month, die beide in Dimensionsgruppen vom Typ type: time erstellt wurden. In diesem Beispiel wird die Dimension enrollment_month durch die folgende Dimensionsgruppe vom Typ type: time erstellt:

dimension_group: enrollment {
  type: time
  timeframes: [time, date, week, month, year, raw]
  sql: ${TABLE}.enrollment_date ;;
}

Ebenso wird die Dimension graduation_month durch die folgende Dimensionsgruppe vom Typ type: time erstellt:

dimension_group: graduation {
  type: time
  timeframes: [time, date, week, month, year, raw]
  sql: ${TABLE}.graduation_date ;;
}

Mit den Dimensionen enrollment_month und graduation_month können Sie berechnen, wie viele Monate oder Jahre zwischen der Einschreibung und dem Abschluss eines Studenten vergangen sind, indem Sie eine Dimensionsgruppe vom Typ type: duration erstellen. Da jedoch einige Datumsfelder im vom Looker generierten SQL als Zeichenfolgen ausgegeben werden, kann es zu einem Fehler kommen, wenn Sie die Dimensionen enrollment_month und graduation_month als Werte für sql_start und sql_end festlegen.

Um einen Fehler zu vermeiden, der durch die Ausgabe dieser Zeitfelder als Zeichenfolgen verursacht wird, können Sie die Dimensionsgruppe vom Typ type: duration erstellen, die sich auf die raw-Zeiträume aus den Dimensionsgruppen enrollment und graduation in den Parametern sql_start und sql_end bezieht:

dimension_group: enrolled {
  type: duration
  intervals: [month, year]
  sql_start: ${enrollment_raw} ;;
  sql_end: ${graduation_raw} ;;
}

In der Explore-Benutzeroberfläche wird dadurch eine Dimensionsgruppe mit dem Namen Duration Enrolled mit den einzelnen Dimensionen Months Enrolled und Years Enrolled erstellt.

Eine einfachere Alternative zur Verwendung des raw Zeitraums in einer Dimensionsgruppe vom Typ type: duration besteht darin, den Referenztyp ::date oder ::datetime für die Felder anzugeben, die in den Parametern sql_start und sql_end referenziert werden.

dimension_group: enrolled {
  type: duration
  intervals: [month, year]
  sql_start: ${enrollment_month::date} ;;
  sql_end: ${graduation_month::date} ;;
}

Das LookML in diesem Beispiel erstellt auch eine Dimensionsgruppe Duration Enrolled. Durch die Verwendung des Verweistyps ::date können die Dimensionen enrollment_month und graduation_month jedoch verwendet werden, ohne einen raw-Zeitraum zu verwenden oder sie mit SQL als Zeichenfolgen auszugeben.

Ein weiteres Beispiel für die Verwendung von LookML-Feldbezügen zur Erstellung von benutzerdefinierten Dimensionsgruppen von type: duration finden Sie auf der Dokumentationsseite zum dimension_group Parameter.

Diese Syntax ist bei Messwerten vom Typ type: list nicht verfügbar, da sie ab Looker-Version 6.8 nicht mehr referenziert werden können.

LookML-Konstanten

Mit dem Parameter constant können Sie eine Konstante angeben, die Sie dann in einem LookML-Projekt verwenden können. Mit LookML-Konstanten können Sie einen Wert einmal definieren und dann in jedem beliebigen Teil Ihres Projekts referenzieren, in dem Zeichenfolgen akzeptiert werden. Dadurch wird die Anzahl der Wiederholungen in Ihrem LookML-Code verringert.

Konstanten müssen in einer Projekt-Manifestdatei deklariert werden und als Wert für Konstanten müssen Zeichenfolgen verwendet werden. Sie können beispielsweise eine Konstante city mit dem Wert "Okayama" wie folgt definieren:

constant: city {
  value: "Okayama"
}

Die Konstante city kann dann im gesamten Projekt mit der Syntax @{city} referenziert werden. Sie können die Konstante city beispielsweise mit dem Parameter label im Explore users verwenden:

explore: users {
  label: "@{city} Users"
}

Looker zeigt dann sowohl im Menü Explore als auch im Titel des Explores Okayama Users anstelle der Standardeinstellung Users an.

Weitere Informationen und Beispiele für die Verwendung von LookML-Konstanten zum Schreiben von wiederverwendbarem Code finden Sie auf der constant Parameterdokumentationsseite.