Offene SQL-Schnittstelle

Mit der semantischen Modellierungsebene LookML von Looker können Fachkräfte für Datenanalyse Dimensionen, Zusammenfassungen, Berechnungen und Datenbeziehungen in einer SQL-Datenbank definieren. LookML-Modelle bieten Wiederverwendbarkeit von Code und Git-Integration. Ein gut strukturiertes LookML-Modell ermöglicht es den Benutzern, ihre eigene Self-Service-Datenexploration und -Berichterstellung durchzuführen.

Das LookML-Modell ist die Grundlage aller von Looker angeforderten Daten, unabhängig davon, ob diese Anfrage über die Explore-Schnittstelle von Looker in der Looker-Benutzeroberfläche, eine eingebettete Visualisierung in Ihrem Unternehmensportal oder eine andere Drittanbieteranwendung oder eine benutzerdefinierte Anwendung kommt, die mit der Looker-API entwickelt wurde. Die Open SQL-Schnittstelle bietet Zugriff auf die LookML-Modelle für jede Drittanbieteranwendung, die Java Database Connectivity (JDBC) unterstützt. Anwendungen können wie eine Datenbank eine Verbindung zu einem LookML-Modell herstellen. So können Benutzer die gesamte Arbeit ihrer Datenanalysten in das LookML-Modell nutzen, während sie die Tools verwenden, mit denen sie am besten vertraut sind.

So werden LookML-Projektelemente in der Open SQL-Benutzeroberfläche angezeigt

Um zu verstehen, wie die Elemente eines LookML-Projekts in der Open SQL-Schnittstelle angezeigt werden, ist es wichtig zu verstehen, wie LookML-Projekte strukturiert sind.

Ein LookML-Projekt ist eine Sammlung von Dateien, die die Objekte, Datenbankverbindungen und Benutzeroberflächenelemente beschreiben, die zum Ausführen von SQL-Abfragen in Looker verwendet werden. Weitere Informationen finden Sie unter LookML – Begriffe und Konzepte. Die folgenden LookML-Projektkonzepte beziehen sich auf die Open SQL-Schnittstelle:

  • Ein LookML-model gibt eine Datenbankverbindung und einen oder mehrere Explores an. In der Open SQL-Oberfläche werden Modelle als Datenbankschemas angezeigt.
  • Ein Explore ist eine logische Gruppierung aus einer oder mehreren Ansichten und den Join-Beziehungen zwischen diesen Ansichten. In der Open SQL-Oberfläche werden Explores als Datenbanktabellen angezeigt.
  • In einer Ansicht wird eine Sammlung von Feldern (Dimensionen und Messwerte) definiert. Eine Ansicht basiert im Allgemeinen auf einer Tabelle in Ihrer Datenbank oder einer abgeleiteten Tabelle. Ansichten können die Spalten aus der zugrunde liegenden Datenbanktabelle sowie alle benutzerdefinierten Dimensionen oder Messwerte enthalten, die Ihre Endnutzer möglicherweise benötigen. In der Open SQL-Oberfläche wird die Kombination aus einem Ansichtsnamen und einem Feldnamen als Name der Datenbankspalte angezeigt. Beispielsweise wird die Dimension id in der Ansicht order_items in der Open SQL-Schnittstelle als Datenbankspalte mit dem Namen order_items.id dargestellt.

In einem Looker-Explore können Join-Beziehungen zwischen mehreren Ansichten definiert werden. Da es möglich ist, dass eine Ansicht ein Feld mit demselben Namen wie ein Feld in einer anderen Ansicht hat, enthält die Open SQL-Schnittstelle beim Verweis auf eine Spalte sowohl den Ansichtsnamen als auch den Feldnamen. Verwenden Sie daher dieses Format, um auf einen Spaltennamen zu verweisen, wenn Sie Abfragen an die Open SQL-Schnittstelle senden:

`<view_name>.<field_name>`

Wenn beispielsweise ein Explore namens order_items eine Ansicht namens customer mit einer Ansicht namens product verbindet und beide Ansichten eine id-Dimension haben, würden Sie die beiden id-Felder als `customer.id` bzw. `product.id` bezeichnen. Um den voll qualifizierten Namen zusammen mit dem Explore-Namen zu verwenden, sollten Sie die beiden Felder als `order_items`.`customer.id` und `order_items`.`product.id` bezeichnen. Weitere Informationen dazu, wo die Backticks gesetzt werden, wenn Sie auf Datenbankkennungen verweisen, finden Sie unter Gravis um Datenbankkennungen verwenden.

Die offene SQL-Schnittstelle einrichten

Führen Sie die folgenden Schritte aus, um die Open SQL-Schnittstelle zu verwenden:

  1. Prüfen Sie, ob die Anforderungen erfüllt sind.
  2. Aktivieren Sie die Open SQL-Schnittstelle auf Ihrer Looker-Instanz.
  3. Laden Sie die JDBC-Treiberdatei für die Open SQL-Schnittstelle herunter.

In den folgenden Abschnitten werden diese Schritte beschrieben.

Voraussetzungen

Die folgenden Komponenten sind für die Verwendung der Open SQL-Schnittstelle erforderlich:

Open SQL-Schnittstelle auf Ihrer Looker-Instanz aktivieren

Aktivieren Sie die Open SQL-Schnittstelle auf Ihrer Instanz, indem Sie die folgenden Schritte ausführen:

JDBC-Treiber für die Open SQL-Schnittstelle herunterladen

Der JDBC-Treiber für die Looker Open SQL-Schnittstelle heißt avatica-<release_number>-looker.jar. Laden Sie die neueste Version von GitHub unter https://github.com/looker-open-source/calcite-avatica/releases herunter.

Der JDBC-Treiber erwartet das folgende URL-Format:

jdbc:looker:url=https://your Looker instance URL

Beispiel:

jdbc:looker:url=https://myInstance.cloud.looker.com

Die JDBC-Treiberklasse lautet:

org.apache.calcite.avatica.remote.looker.LookerDriver

Bei der offenen SQL-Schnittstelle authentifizieren

Die Open SQL-Schnittstelle unterstützt drei Authentifizierungsmethoden:

OAuth

JDBC-Clients, die OAuth unterstützen, können für die Verwendung des OAuth-Servers einer Looker-Instanz konfiguriert werden. So konfigurieren Sie die OAuth-Authentifizierung:

  1. Verwenden Sie die Erweiterung „API Explorer“, um den JDBC-OAuth-Client bei Ihrer Looker-Instanz zu registrieren, damit die Looker-Instanz OAuth-Anfragen erkennen kann. Eine Anleitung dazu finden Sie unter OAuth-Clientanwendung registrieren.
  2. Melden Sie sich mit OAuth bei Looker an, um ein Zugriffstoken anzufordern. Ein Beispiel finden Sie unter Nutzeranmeldung mit OAuth durchführen.
  3. Verwenden Sie ein Properties-Objekt, um die OAuth-Anmeldedaten zu übergeben, wenn Sie die JDBC-Verbindung zur Open SQL-Schnittstelle öffnen.

Im Folgenden finden Sie ein Beispiel mit DriverManager#getConnection(<String>, <Properties>`):

String access_token = getAccessToken() //uses the Looker OAuth flow to get a token
String URL = "jdbc:looker:url=https://myInstance.cloud.looker.com"
Properties info = new Properties( );
info.put("token", access_token);
Connection conn = DriverManager.getConnection(URL, info);

Zugriffstoken mithilfe von API-Schlüsseln generieren

Anstatt den Standard-OAuth-Ablauf zum Generieren eines Zugriffstokens zu verwenden, können Sie die folgenden Schritte ausführen, um mit der Looker API ein Zugriffstoken zu generieren, das an den JDBC-Treiber für die Open SQL-Schnittstelle übergeben werden kann:

  1. Generieren Sie API-Schlüssel für Ihren Looker-Nutzer, wie auf der Seite Administratoreinstellungen – Nutzer beschrieben.

  2. Verwenden Sie den login API-Endpunkt für Ihre Looker-Instanz. Die Antwort enthält ein Zugriffstoken im Format Authorization: token <access_token>. Hier ist ein Beispiel für einen curl-Befehl für diese Anfrage:

      curl -k -d "client_id=<client_id>&client_secret=<client_secret>" https://<looker_host>/login\

  3. Übergeben Sie den <access_token>-Wert der Antwort als Token im Properties-Objekt, um die OAuth-Anmeldedaten zu übergeben, wenn die JDBC-Verbindung für die Open SQL-Schnittstelle geöffnet wird.

API-Schlüssel

Sie können sich anstelle eines Nutzernamens und Passworts auch mit API-Schlüsseln authentifizieren. API-Schlüssel gelten als weniger sicher als OAuth und sind möglicherweise nur während der Vorabversion der offenen SQL-Schnittstelle verfügbar. Informationen zum Erstellen von API-Schlüsseln für Ihre Looker-Instanz finden Sie unter API-Schlüssel.

Verwenden Sie den Teil Client-ID des Looker API-Schlüssels als Nutzernamen. Verwenden Sie den Teil Client Secret (Clientschlüssel) für das Passwort.

Abfragen mit der offenen SQL-Schnittstelle ausführen

Beachten Sie beim Ausführen von Abfragen mit der Open SQL-Schnittstelle die folgenden Richtlinien:

  • Die Open SQL-Schnittstelle akzeptiert SQL-Abfragen, die der GoogleSQL-Syntax entsprechen.
  • Die Open SQL-Schnittstelle erfordert Graviszeichen (`) um Modell-, Explore- und Feldkennungen. Weitere Informationen und Beispiele finden Sie unter Backticks um Datenbankkennungen herum verwenden.
  • Die Open SQL-Schnittstelle unterstützt die meisten BigQuery-Operatoren. Wenn Sie einen nicht unterstützten Operator benötigen, senden Sie eine E-Mail-Anfrage an looker-sql-interface@google.com.
  • Mit der Open SQL-Schnittstelle müssen Sie alle LookML-Messwerte festlegen, die in einer Abfrage enthalten sind, indem Sie den Messwert (einschließlich Backticks) in die Sonderfunktion AGGREGATE() setzen. Weitere Informationen finden Sie im Abschnitt LookML-Messwerte mit AGGREGATE() angeben.

LookML-Einschränkungen

Beachten Sie die folgenden LookML-Einschränkungen, wenn Sie Abfragen an die Open SQL-Schnittstelle senden:

SQL-Einschränkungen

Beachten Sie die folgenden SQL-Einschränkungen, wenn Sie Abfragen an die Open SQL-Schnittstelle senden:

Backticks (Gravis) um Datenbankkennungen verwenden

Verwenden Sie beim Senden von Abfragen an die Open SQL-Schnittstelle Graviszeichen um Schema-, Tabellen- und Spaltenkennungen. So geben Sie Datenbankelemente mithilfe von Backticks für Looker-Begriffe an:

  • Schema: `<model_name>`
  • Tabelle: `<explore_name>`

  • Spalte: `<view_name>.<field_name>`

Hier ist ein Beispiel für das Format einer SELECT-Anweisung mit diesen Elementen:

SELECT `view.field`
  FROM `model`.`explore`
  LIMIT 10;

LookML-Messwerte mit AGGREGATE() angeben

Datenbanktabellen enthalten in der Regel nur Dimensionen, also Daten, die ein einzelnes Attribut zu einer Zeile in der Tabelle beschreiben. LookML-Projekte können jedoch sowohl Dimensionen als auch Messwerte definieren. Ein Messwert ist eine Zusammenfassung von Daten über mehrere Zeilen hinweg, z. B. SUM, AVG, MIN oder MAX. (Andere Arten von Messungen werden ebenfalls unterstützt. Die vollständige Liste der unterstützten LookML-Messwerttypen finden Sie auf der Seite Messwerttypen.)

Mit der Open SQL-Schnittstelle müssen Sie alle LookML-Messwerte festlegen, die in einer Abfrage enthalten sind, indem Sie den Messwert (einschließlich Backticks) in die Sonderfunktion AGGREGATE() setzen. So können Sie beispielsweise den Messwert count aus der Ansicht orders angeben:

AGGREGATE(`orders.count`)

Sie müssen LookML-Messungen in der AGGREGATE()-Funktion umschließen, unabhängig davon, ob sich der Messwert in einer SELECT-, HAVING- oder ORDER BY-Klausel befindet.

Wenn Sie nicht sicher sind, ob ein Feld ein LookML-Messwert ist, können Sie mit der Methode DatabaseMetaData.getColumns auf Metadaten für das LookML-Projekt zugreifen. In der Spalte IS_GENERATEDCOLUMN wird YES für alle LookML-Messungen und NO für LookML-Dimensionen angezeigt. Weitere Informationen finden Sie im Abschnitt Auf Datenbankmetadaten zugreifen.

Beispiel

Hier ist eine Beispielabfrage mit Dimensionen und Messwerten. Diese Abfrage ruft die Dimensionen state und city aus der Ansicht customers und den Messwert total amount aus der Ansicht orders ab. Diese beiden Ansichten sind im E-Commerce-Modell in der explorativen Auftragsanalyse zusammengefasst. Für die Städte mit mehr als zehn Bestellungen zeigt diese Abfrageantwort die fünf Städte mit den meisten Bestellungen nach Bestellbetrag an:

SELECT `customers.state`, `customers.city`,
  AGGREGATE(`orders.total_amount`)
FROM `ecommerce`.`orders`
GROUP BY `customers.state`, `customers.city`
HAVING AGGREGATE(`orders.count`) > 10
ORDER BY 3 DESC LIMIT 5;

Geben Sie mit JSON_OBJECT reine Filterfelder und Parameter an

Die offene SQL-Schnittstelle unterstützt Parameter und Nur-Filter-Felder.

Wenn Sie Abfragen über die Open SQL-Schnittstelle ausführen, können Sie Parameter und reine Filterfelder auf die Abfrage anwenden, indem Sie einen JSON_OBJECT-Konstruktoraufruf im folgenden Format einschließen:

JSON_OBJECT(
    '<view>.<parameter name>', '<parameter value>',
    '<view>.<filter name>', '<Looker filter expression>'
)

Das JSON-Objekt kann null oder mehr Filter-Schlüssel/Wert-Paare und null oder mehr Parameter-Schlüssel/Wert-Paare enthalten.

  • Der Schlüssel im JSON_OBJECT-Konstruktor muss der Name eines Nur-Filter-Felds oder -Parameters sein.
  • Bei reinen Filterfeldern muss der Wert für jeden Schlüssel ein Looker-Stringfilterausdruck sein.
  • Bei Parametern muss der Wert für jeden Schlüssel ein einfacher Wert sein, der in der Definition von parameter definiert ist.

In den folgenden Abschnitten finden Sie Beispiele für die Verwendung von Parametern und Nur-Filter-Feldern mit der Open SQL-Schnittstelle.

Parameterbeispiel

Beispiel für die Verwendung eines parameter mit offener SQL-Schnittstelle, wenn für die Ansicht customers ein Parameter in Looker definiert wurde:

parameter: segment {
  type: string
  allowed_value: {
    label: "Small (less than 500)"
    value: "small_customers"
  }
  allowed_value: {
    label: "Larger (greater than 10,000)"
    value: "large_customers"
  }
  allowed_value: {
    label: "Medium customers (Between 500 and 10,000)"
    value: "medium_customers"
  }
}

Sie können diese Abfrage an die Open SQL-Schnittstelle senden, um den segment-Parameterwert von medium_customers auf die Abfrage anzuwenden:

SELECT `customers.segment_size`,
  AGGREGATE(`orders.total_amount`)
FROM `ecommerce`.`orders`(JSON_OBJECT(
    'customers.segment', 'medium_customers'
))
GROUP BY `customers.state`, `customers.city`
HAVING AGGREGATE(`orders.count`) > 10
ORDER BY 3 DESC LIMIT 5;

Die offene SQL-Schnittstelle übergibt diesen Parameterwert an die Abfrage in Looker und Looker wendet den Wert medium_customers auf alle Felder im Explore an, die für die Verwendung des Parameters segment konfiguriert sind. Informationen zur Funktionsweise von Parametern in Looker finden Sie in der parameter-Dokumentation.

Beispiel für ein reines Filterfeld

Sie können das Feld filter mit der Open SQL-Schnittstelle verwenden. Beispiel: Eine products-Ansicht mit einer Dimension und einem reinen Filterfeld, die in Looker so definiert sind:

filter: brand_select {
  type: string
  }

dimension: brand_comparitor {
  sql:
    CASE
      WHEN {% condition brand_select %} ${products.brand_name} {% endcondition %}
      THEN ${products.brand_name}
      ELSE "All Other Brands"
    END ;;
    }

Sie könnten den brand_select-Filter mit der Open SQL-Schnittstelle verwenden, indem Sie eine Abfrage wie die folgende senden:

SELECT `products.brand_comparator`, `products.number_of_brands`,
  AGGREGATE(`products.total_revenue`)
FROM `ecommerce`.`orders`(JSON_OBJECT(
    'products.brand_select', '%Santa Cruz%'
))
GROUP BY `products.brand_comparator`
ORDER BY 3 DESC LIMIT 5;

Über die offene SQL-Schnittstelle wird der Looker-Stringfilterausdruck %Santa Cruz% auf die Abfrage in Looker angewendet. Informationen dazu, wie Nur-Filter-Felder in Looker funktionieren, finden Sie in der Dokumentation zu filter.

Auf Datenbankmetadaten zugreifen

Die Open SQL-Schnittstelle unterstützt einen Teil der JDBC-Standardschnittstelle DatabaseMetaData, über die Informationen zur zugrunde liegenden Datenbank abgerufen werden. Mit den folgenden Methoden der Schnittstelle DatabaseMetaData können Sie Informationen zu Ihrem LookML-Modell abrufen:

DatabaseMetadata.getSchemas

In der folgenden Tabelle wird beschrieben, wie ein LookML-Modell mit den Standard-Datenbankstrukturen in der Antwort der DatabaseMetadata.getSchemas-Schnittstellenmethode verknüpft ist.

getSchemas Antwortspalte Beschreibung
TABLE_SCHEM Name des LookML-Modells
TABLE_CATALOG (Null)

DatabaseMetadata.getTables

In der folgenden Tabelle wird beschrieben, wie ein LookML-Modell mit den Datenbankstrukturen in der Antwort der DatabaseMetaData.getTables-Schnittstellenmethode verknüpft ist. Die Antwort enthält Standard-JDBC-Metadaten sowie Looker-spezifische Metadaten:

getTables Antwortspalte Beschreibung
JDBC-Standardmetadaten
TABLE_CAT (Null)
TABLE_SCHEM Name des LookML-Modells
TABLE_NAME Name des LookML-Explores
TABLE_TYPE Gibt immer den Wert TABLE_TYPE zurück
Looker-spezifische Metadaten
DESCRIPTION Beschreibung ansehen
LABEL Label ansehen
TAGS Tags ansehen

DatabaseMetadata.getColumns

In der folgenden Tabelle wird beschrieben, wie ein LookML-Modell mit den Datenbankstrukturen in der Antwort der DatabaseMetaData.getColumns-Schnittstellenmethode verknüpft ist. Die Antwort enthält Standard-JDBC-Metadaten sowie Looker-spezifische Metadaten:

getColumns Antwortspalte Beschreibung
JDBC-Standardmetadaten
TABLE_CAT (Null)
TABLE_SCHEM Name des LookML-Modells
TABLE_NAME Name des LookML-Explores
COLUMN_NAME LookML-Feldname im Format `<view_name>.<field_name>`. Beispiel: `orders.amount`.
DATA_TYPE Der java.sql.Types-Code der Spalte. Looker-Felder yesno sind beispielsweise der SQL-Typcode 16 (BOOLEAN).
ORDINAL_POSITION Die auf 1 basierende Ordinale des Felds im Explore (Dimensionen und Messungen in alphabetischer Reihenfolge nach Ansichtsname und dann Feldname kombinieren)
IS_NULLABLE Gibt immer den Wert YES zurück
IS_GENERATEDCOLUMN YES für Messungen, NO für Dimensionen
Looker-spezifische Metadaten
DIMENSION_GROUP Name der Dimensionsgruppe, falls das Feld Teil einer Dimensionsgruppe ist. Wenn das Feld nicht Teil einer Dimensionsgruppe ist, ist der Wert null.
DRILL_FIELDS Liste der Aufschlüsselungsfelder, die für die Dimension oder den Messwert festgelegt wurden, falls vorhanden
FIELD_ALIAS Alias für das Feld, falls vorhanden
FIELD_CATEGORY Gibt an, ob das Feld dimension oder measure ist
FIELD_DESCRIPTION Feld description
FIELD_GROUP_VARIANT Wenn das Feld unter dem Feld group label angezeigt wird, gibt FIELD_GROUP_VARIANT den kürzeren Namen des Felds an, der unter der Gruppenbezeichnung angezeigt wird.
FIELD_LABEL Feld label
FIELD_NAME Name der Dimension oder des Messwerts
HIDDEN Gibt an, ob das Feld im Field Picker in Explores (TRUE) ausgeblendet oder im Field Picker in Explores sichtbar ist (FALSE).
LOOKER_TYPE LookML-Feldtyp für die Dimension oder Messwert
REQUIRES_REFRESH_ON_SORT Gibt an, ob die SQL-Abfrage aktualisiert werden muss, um die Feldwerte neu zu sortieren (TRUE), oder ob die Feldwerte neu sortiert werden können, ohne dass die SQL-Abfrage aktualisiert werden muss (FALSE).
SORTABLE Gibt an, ob das Feld sortiert (TRUE) oder nicht sortiert werden kann (FALSE)
TAGS Feld tags
USE_STRICT_VALUE_FORMAT Gibt an, ob für das Feld das strikte Werteformat (TRUE) verwendet wird oder nicht (FALSE)
VALUE_FORMAT Wertformat-String für das Feld
VIEW_LABEL Ansichtslabel für das Feld
VIEW_NAME Name der Ansicht, in der das Feld im LookML-Projekt definiert ist

Abfragen der offenen SQL-Schnittstelle in der Looker-Benutzeroberfläche identifizieren

Looker-Administratoren können über die Looker-Benutzeroberfläche herausfinden, welche Abfragen von der offenen SQL-Schnittstelle stammen:

  • Auf der Verwaltungsseite für Abfragen haben Abfragen der Open SQL-Schnittstelle den Source-Wert „Sql Interface“. Der Wert Nutzer enthält den Namen des Looker-Nutzers, der die Abfrage ausgeführt hat.

  • Im Systemaktivitätsverlauf-Explore haben Abfragen der Open SQL-Schnittstelle den Source-Wert „sql_interface“. Der Wert Nutzer-E-Mail enthält die E-Mail-Adresse des Looker-Nutzers, der die Abfrage ausgeführt hat. Sie können direkt zu dem nach „sql_interface“ gefilterten Explore Verlauf wechseln, indem Sie die Adresse Ihrer Looker-Instanz am Anfang dieser URL einfügen:

    https://your Looker instance URL/explore/system__activity/history?fields=history.source,history.completed_date&f[history.source]=sql_interface

Feedback zur offenen SQL-Schnittstelle

Wenden Sie sich an looker-sql-interface@google.com, wenn Sie Fragen oder Funktionsanfragen zur Open SQL-Schnittstelle haben.