SQL-Oberfläche öffnen

Mit der semantischen Modellierungsebene LookML von Looker kann eine Fachkraft für Datenanalyse Dimensionen, Aggregatfunktionen, Berechnungen und Datenbeziehungen in einer SQL-Datenbank definieren. LookML-Modelle bieten die Wiederverwendbarkeit von Code und die Git-Integration. Ein gut strukturiertes LookML-Modell ermöglicht es Benutzern, ihre eigene Self-Service-Datenerkundung und -Berichterstellung durchzuführen.

Das LookML-Modell bildet die Grundlage aller Daten, die von Looker angefordert werden. Dabei spielt es keine Rolle, ob die Anfrage von der Explore-Oberfläche von Looker in der Looker-Benutzeroberfläche, einer eingebetteten Visualisierung in Ihrem Unternehmensportal, einer anderen Drittanbieteranwendung oder einer benutzerdefinierten Anwendung stammt, die mit der API entwickelt wurde. Die Open SQL-Schnittstelle bietet Zugriff auf die LookML-Modelle für alle Drittanbieteranwendungen, die Java Database Connectivity (JDBC) unterstützen. Anwendungen können wie eine Datenbank eine Verbindung zu einem LookML-Modell herstellen, sodass die Benutzer die gesamte Arbeit nutzen können, die ihre Datenanalysten im LookML-Modell erledigen, während sie gleichzeitig die Tools verwenden, mit denen sie am besten vertraut sind.

Wie die Open SQL-Schnittstelle LookML-Projektelemente anzeigt

Um zu verstehen, wie die Elemente eines LookML-Projekts in der Open SQL-Oberfläche 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 Elemente der Benutzeroberfläche 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-Oberfläche:

  • Ein LookML-model gibt eine Datenbankverbindung und ein oder mehrere Explores an. Die Open SQL-Oberfläche stellt Modelle als Datenbankschemas bereit.
  • Ein Explore ist eine logische Gruppierung einer oder mehrerer Ansichten und der Join-Beziehungen zwischen diesen Ansichten. In der Open SQL-Oberfläche werden Explores als Datenbanktabellen angezeigt.
  • Eine Ansicht definiert eine Sammlung von Feldern (Dimensionen und Kennzahlen). Eine Ansicht basiert in der Regel auf einer Tabelle in der Datenbank oder einer abgeleiteten Tabelle. Ansichten können die Spalten aus der zugrunde liegenden Datenbanktabelle sowie beliebige benutzerdefinierte 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 Datenbankspaltenname angezeigt. Beispielsweise wird die Dimension id in der Ansicht order_items von der Open SQL Interface als Datenbankspalte mit dem Namen order_items.id angezeigt.

Mit 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 enthält, enthält die Open SQL-Schnittstelle beim Verweis auf eine Spalte sowohl den Ansichtsnamen als auch den Feldnamen. Verwenden Sie daher dieses Format, um beim Senden von Abfragen an die Open SQL-Schnittstelle auf einen Spaltennamen zu verweisen:

`<view_name>.<field_name>`

Beispiel: Wenn es ein Explore namens order_items gibt, das eine Ansicht namens customer mit einer Ansicht namens product verbindet und beide Datenansichten eine id-Dimension haben, würden Sie die beiden id-Felder mit `customer.id` und `product.id` bezeichnen. Wenn Sie den voll qualifizierten Namen auch mit dem Explore-Namen verwenden möchten, nennen Sie die beiden Felder als `order_items`.`customer.id` und `order_items`.`product.id`. Informationen darüber, wo beim Verweisen auf Datenbankkennungen die Graviszeichen einzufügen sind, finden Sie unter Gravis um Datenbankkennungen verwenden.

Open 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 der Open SQL-Schnittstelle herunter.

In den folgenden Abschnitten werden diese Schritte beschrieben.

Voraussetzungen

Die folgenden Komponenten sind zur Verwendung der Open SQL-Schnittstelle erforderlich:

Open SQL-Schnittstelle in der Looker-Instanz aktivieren

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

JDBC-Treiber für Open SQL Interface herunterladen

Der JDBC-Treiber für die Open SQL-Schnittstelle von Looker 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 ist:

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 so konfiguriert werden, dass sie den OAuth-Server einer Looker-Instanz verwenden. So konfigurieren Sie die OAuth-Authentifizierung:

  1. Registrieren Sie den JDBC-OAuth-Client mit der API Explorer-Erweiterung bei Ihrer Looker-Instanz, 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 die JDBC-Verbindung zur Open SQL-Schnittstelle geöffnet wird.

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 standardmäßigen 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 der 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 API-Endpunkt login 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, mit dem Sie diese Anfrage stellen:

      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 beim Öffnen der JDBC-Verbindung zur Open SQL Interface die OAuth-Anmeldedaten zu übergeben.

API-Schlüssel

Sie können statt Nutzername und Passwort auch API-Schlüssel verwenden, um sich zu authentifizieren. API-Schlüssel gelten als weniger sicher als OAuth und sind möglicherweise nur in der Vorschau der Open 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 Client-ID-Teil des Looker API-Schlüssels als Nutzernamen. Verwenden Sie den Abschnitt Client Secret (Client-Secret) als Passwort.

Abfragen mit der Open SQL-Schnittstelle ausführen

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

  • Über die Open SQL-Schnittstelle werden SQL-Abfragen akzeptiert, die der GoogleSQL-Syntax entsprechen.
  • Die Open SQL-Schnittstelle erfordert Graviszeichen (`) um Modell-, Explore- und Feldkennungen. Weitere Informationen und Beispiele finden Sie unter Gravis um Datenbankkennungen 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. Dazu setzen Sie den Messwert (einschließlich Gravis) in die spezielle Funktion AGGREGATE(). Weitere Informationen finden Sie im Abschnitt LookML-Messwerte mit AGGREGATE() angeben.

LookML-Einschränkungen

Beachten Sie beim Senden von Abfragen an die Open SQL-Schnittstelle die folgenden LookML-Einschränkungen:

SQL-Einschränkungen

Beachten Sie die folgenden SQL-Einschränkungen beim Senden von Abfragen an die Open SQL-Schnittstelle:

Backticks 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 Graviszeichen mit Looker-Begriffen an:

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

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

Hier ist ein Beispiel für ein SELECT-Anweisungsformat 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 Aggregation von Daten über mehrere Zeilen hinweg, wie z. B. SUM, AVG, MIN oder MAX. (Andere Arten von Messungen werden ebenfalls unterstützt. Eine vollständige Liste der unterstützten LookML-Messwerttypen finden Sie auf der Seite Messtypen.)

Mit der Open SQL-Schnittstelle müssen Sie alle LookML-Messwerte festlegen, die in einer Abfrage enthalten sind. Dazu setzen Sie den Messwert (einschließlich Gravis) in die spezielle Funktion AGGREGATE(). Verwenden Sie dies beispielsweise, um den Messwert count in der Ansicht orders anzugeben:

AGGREGATE(`orders.count`)

Sie müssen LookML-Messwerte in der Funktion AGGREGATE() zusammenfassen, unabhängig davon, ob sich der Messwert in einer SELECT-, einer HAVING- oder einer 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 für alle LookML-Messwerte YES und für LookML-Dimensionen NO angezeigt. Weitere Informationen finden Sie im Abschnitt Auf Datenbankmetadaten zugreifen.

Beispiel

Hier sehen Sie 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. Beide Ansichten sind im ecommerce-Modell mit dem Explore orders verknüpft. Für die Städte mit mehr als 10 Bestellungen zeigt diese Abfrageantwort die 5 wichtigsten Städte 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;

Nur-Filter-Felder und Parameter mit JSON_OBJECT angeben

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

Wenn Sie Abfragen über die Open SQL-Schnittstelle ausführen, können Sie Parameter und Nur-Filter-Felder auf die Abfrage anwenden, indem Sie einen JSON_OBJECT-Konstruktoraufruf im folgenden Format einfügen:

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 reinen Filterfelds oder -parameters sein.
  • Bei Nur-Filter-Feldern muss der Wert für jeden Schlüssel ein Looker-String-Filterausdruck sein.
  • Bei Parametern muss der Wert für jeden Schlüssel ein einfacher Wert sein, der in der parameter-Definition definiert ist.

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

Parameterbeispiel

Hier ein Beispiel für die Verwendung eines parameter mit Open SQL-Schnittstelle, wenn für die Ansicht customers ein in Looker definierter Parameter wie folgt definiert ist:

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 Parameterwert segment 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 Open SQL-Oberfläche übergibt diesen Parameterwert an die Abfrage in Looker. 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 Dokumentation zu parameter.

Beispiel für ein reines Filterfeld

Sie können das Feld filter mit der Open SQL-Schnittstelle verwenden. Angenommen, die Ansicht products enthält in Looker so eine Dimension und ein reines Filterfeld:

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önnen den Filter brand_select 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;

In der offenen SQL-Oberfläche wird der Looker-Stringfilter %Santa Cruz% auf die Abfrage in Looker angewendet. Informationen zur Funktionsweise reiner Filterfelder in Looker finden Sie in der filter-Dokumentation.

Auf Datenbankmetadaten zugreifen

Die Open SQL-Schnittstelle unterstützt einen Teil der JDBC-Standardschnittstelle DatabaseMetaData, die zum Abrufen von Informationen zur zugrunde liegenden Datenbank verwendet wird. Sie können die folgenden Methoden der DatabaseMetaData-Schnittstelle verwenden, um Informationen zu Ihrem LookML-Modell abzurufen:

DatabaseMetadata.getSchemas

In der folgenden Tabelle wird beschrieben, wie sich ein LookML-Modell mit den Standarddatenbankstrukturen in der Antwort der DatabaseMetadata.getSchemas-Schnittstellenmethode zusammensetzt.

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

DatabaseMetadata.getTables

In der folgenden Tabelle wird beschrieben, wie sich ein LookML-Modell mit den Datenbankstrukturen in der Antwort der DatabaseMetaData.getTables-Schnittstellenmethode verhält. Die Antwort enthält JDBC-Standardmetadaten sowie Looker-spezifische Metadaten:

getTables Antwortspalte Beschreibung
JDBC-Standardmetadaten
TABLE_CAT (Null)
TABLE_SCHEM Name des LookML-Modells
TABLE_NAME LookML-Explore Name
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 sich ein LookML-Modell mit den Datenbankstrukturen in der Antwort der DatabaseMetaData.getColumns-Schnittstellenmethode verhält. Die Antwort enthält JDBC-Standardmetadaten 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. Die Looker-yesno-Felder haben beispielsweise den SQL-Typcode 16 (BOOLEAN).
ORDINAL_POSITION Die 1-basierte Ordinalzahl des Felds innerhalb des Explores (das Kombinieren von Dimensionen und Messungen alphabetisch nach Ansichtsname und dann Feldname)
IS_NULLABLE Gibt immer den Wert YES zurück
IS_GENERATEDCOLUMN YES für Messwerte, NO für Dimensionen
Looker-spezifische Metadaten
DIMENSION_GROUP Name der Dimensionsgruppe, wenn das Feld Teil einer Dimensionsgruppe ist. Wenn das Feld nicht zu einer Dimensionsgruppe gehört, hat dieser Wert den Wert „null“.
DRILL_FIELDS Liste der für die Dimension oder den Messwert festgelegten Drilldown-Felder, falls vorhanden
FIELD_ALIAS Alias für das Feld, falls vorhanden
FIELD_CATEGORY Ob das Feld ein dimension- oder measure-Element ist
FIELD_DESCRIPTION Feld description
FIELD_GROUP_VARIANT Wenn das Feld unter einer Gruppenbezeichnung angezeigt wird, gibt FIELD_GROUP_VARIANT den kürzeren Namen des Felds an, das unter dem Gruppenlabel angezeigt wird.
FIELD_LABEL Feld label
FIELD_NAME Name der Dimension oder des Messwerts
HIDDEN Ob das Feld im Field Picker in Explores ausgeblendet ist (TRUE) oder ob es im Field Picker in Explores sichtbar ist (FALSE).
LOOKER_TYPE LookML-Feldtyp für die Dimension oder den Messwert
REQUIRES_REFRESH_ON_SORT Gibt an, ob die SQL-Abfrage aktualisiert werden muss, um die Werte des Felds neu zu sortieren (TRUE), oder ob die Werte des Felds 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 Ob für das Feld ein striktes Wertformat (TRUE) verwendet wird oder nicht (FALSE)
VALUE_FORMAT Wertformat-String für das Feld
VIEW_LABEL Label für das Feld ansehen
VIEW_NAME Name der Ansicht, in der das Feld im LookML-Projekt definiert ist

Abfragen der Open SQL-Oberfläche in der Looker-Benutzeroberfläche identifizieren

Looker-Administratoren können mithilfe der Looker-Benutzeroberfläche feststellen, welche Abfragen von der Open SQL-Oberfläche stammen:

  • Auf der Verwaltungsseite Abfragen haben Abfragen der Open SQL-Schnittstelle den Wert „Sql Interface“ als Source. Der Wert Nutzer gibt den Namen des Looker-Nutzers an, der die Abfrage ausgeführt hat.

  • Im Erkunden des Systemaktivitätsverlaufs haben Abfragen der Open SQL-Benutzeroberfläche den Wert Source „sql_interface“. Der Wert E-Mail-Adresse des Nutzers enthält die E-Mail-Adresse des Looker-Nutzers, der die Abfrage ausgeführt hat. Sie können direkt zum Explore Verlauf wechseln, gefiltert nach „sql_interface“, indem Sie Ihre Looker-Instanzadresse 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 Open SQL-Schnittstelle

Wenden Sie sich an looker-sql-interface@google.com, wenn Sie Fragen oder Funktionsanfragen für Open SQL Interface haben.