Apri interfaccia SQL

Il livello di modellazione semantica LookML di Looker consente a un data analyst di definire dimensioni, aggregazioni, calcoli e relazioni tra i dati in un database SQL. I modelli LookML offrono la riusabilità del codice e l'integrazione Git. Un modello LookML ben strutturato consente agli utenti di eseguire esplorazioni e report dei dati in modalità self-service.

Il modello LookML è la base di tutti i dati richiesti a Looker, indipendentemente dal fatto che la richiesta provenga dall'interfaccia di esplorazione di Looker nell'interfaccia utente di Looker, da una visualizzazione incorporata nel portale aziendale o in un'altra applicazione di terze parti oppure da un'applicazione personalizzata sviluppata con l'API di Looker. Open SQL Interface consente di accedere ai modelli LookML a qualsiasi applicazione di terze parti che supporti Java Database Connectivity (JDBC). Le applicazioni possono connettersi a un modello LookML come se fosse un database, consentendo agli utenti di sfruttare tutto il lavoro svolto dai propri analisti di dati nel modello LookML, utilizzando gli strumenti con cui si sentono più a proprio agio.

In che modo Open SQL Interface mostra gli elementi del progetto LookML

Per capire in che modo l'interfaccia Open SQL evidenzia gli elementi di un progetto LookML, è importante comprendere come sono strutturati i progetti LookML.

Un progetto LookML è una raccolta di file che descrivono gli oggetti, le connessioni ai database e gli elementi dell'interfaccia utente utilizzati per eseguire query SQL in Looker (per saperne di più, consulta termini e concetti di LookML). I seguenti concetti di progetto LookML sono correlati all'interfaccia Open SQL:

  • Un modello LookML specifica una connessione di database e una o più esplorazioni. L'interfaccia Open SQL mostra i modelli come schemi di database.
  • Un'esplorazione è un raggruppamento logico di una o più viste e le relazioni di join tra queste viste. L'interfaccia SQL aperta mostra le esplorazioni come tabelle di database.
  • Una visualizzazione definisce una raccolta di campi (dimensioni e misure). Una vista è generalmente basata su una tabella nel database o su una tabella derivata. Le visualizzazioni possono contenere le colonne della tabella di database sottostante, nonché eventuali dimensioni o misure personalizzate richieste dagli utenti finali. L'interfaccia Open SQL mostra la combinazione di un nome di visualizzazione e un nome di campo come nome della colonna del database. Ad esempio, la dimensione id nella visualizzazione order_items viene visualizzata dall'interfaccia Open SQL come colonna di database denominata order_items.id.

Un'esplorazione di Looker può definire relazioni di join tra più viste. Poiché è possibile che una visualizzazione abbia un campo con lo stesso nome di un campo in un'altra visualizzazione, l'interfaccia Open SQL include sia il nome della visualizzazione sia il nome del campo quando fa riferimento a una colonna. Pertanto, utilizza questo formato per fare riferimento al nome di una colonna quando invii query all'interfaccia Open SQL:

`<view_name>.<field_name>`

Ad esempio, se esistesse un'esplorazione denominata order_items che unisce una visualizzazione denominata customer a una visualizzazione denominata product e entrambe le visualizzazioni avessero una dimensione id, faresti riferimento ai due campi id come `customer.id` e `product.id`, rispettivamente. Per utilizzare il nome completo anche con il nome Esplora, devi fare riferimento ai due campi come `order_items`.`customer.id` e `order_items`.`product.id`. (Per informazioni su dove inserire gli apici inversi per gli identificatori di database, consulta Utilizzare apici inversi per gli identificatori di database.)

configurazione dell'interfaccia open SQL

Per utilizzare l'interfaccia Open SQL, segui questi passaggi:

  1. Verifica che i requisiti siano soddisfatti.
  2. Scarica il file del driver JDBC Open SQL Interface.

Questi passaggi sono descritti nelle sezioni seguenti.

Requisiti

Per utilizzare l'interfaccia Open SQL sono necessari i seguenti componenti:

Scarica il driver JDBC Open SQL Interface

Il driver JDBC di Looker Open SQL Interface si chiama avatica-<release_number>-looker.jar. Scarica la versione più recente da GitHub all'indirizzo https://github.com/looker-open-source/calcite-avatica/releases.

Il driver JDBC si aspetta il seguente formato di URL:

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

Ad esempio:

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

La classe del driver JDBC è:

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

Autenticazione tramite un'interfaccia SQL aperta

Open SQL Interface supporta tre metodi di autenticazione:

OAuth

I client JDBC che supportano OAuth possono essere configurati per utilizzare il server OAuth di un'istanza di Looker. Segui i passaggi per configurare l'autenticazione OAuth:

  1. Utilizza l'estensione Explorer API per registrare il client OAuth JDBC con la tua istanza Looker in modo che l'istanza Looker possa riconoscere le richieste OAuth. Per le istruzioni, consulta l'argomento Registrazione di un'applicazione client OAuth.
  2. Accedi a Looker con OAuth per richiedere un token di accesso. Per un esempio, vedi Eseguire l'accesso utente utilizzando OAuth.
  3. Utilizza un oggetto Properties per passare le credenziali OAuth quando apri la connessione JDBC all'interfaccia Open SQL.

Di seguito è riportato un esempio che utilizza 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);

Generare un token di accesso utilizzando le chiavi API

Anziché utilizzare il flusso OAuth standard per generare un token di accesso, puoi seguire questa procedura per utilizzare l'API Looker per generare un token di accesso che può essere passato al driver JDBC Open SQL Interface:

  1. Genera le chiavi API per il tuo utente Looker come descritto nella pagina Impostazioni amministrazione - Utenti.

  2. Utilizza l'endpoint API login per la tua istanza di Looker. La risposta include un token di accesso nel formato Authorization: token <access_token>. Di seguito è riportato un esempio del comando curl che puoi utilizzare per effettuare questa richiesta:

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

  3. Trasmetti il valore <access_token> della risposta come token nell'oggetto Proprietà per passare le credenziali OAuth all'apertura della connessione JDBC a Open SQL Interface.

Chiavi API

Puoi anche utilizzare le chiavi API per l'autenticazione al posto di un nome utente e una password. Le chiavi API sono considerate meno sicure di OAuth e potrebbero essere disponibili solo durante l'anteprima di Open SQL Interface. Consulta la sezione Chiavi API per informazioni sulla creazione di chiavi API per la tua istanza di Looker.

Utilizza la porzione ID client della chiave API di Looker come nome utente. Utilizza la parte Client Secret per la password.

Esecuzione di query con l'interfaccia open SQL

Tieni presenti le seguenti linee guida quando esegui query con l'interfaccia open SQL:

Limitazioni di LookML

Tieni presente quanto segue quando invii query all'interfaccia Open SQL:

Limitazioni SQL

Tieni presente le seguenti limitazioni SQL quando invii query all'interfaccia Open SQL Interface:

Utilizzare apici inversi per gli identificatori di database

Quando invii query all'interfaccia Open SQL, utilizza i backtick intorno agli identificatori di schema, tabella e colonna. Ecco come specificare gli elementi del database utilizzando l'accento grave con i termini di Looker:

  • schema: `<model_name>`
  • table: `<explore_name>`

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

Ecco un esempio di formato dell'istruzione SELECT che utilizza questi elementi:

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

Specifica le misure LookML con AGGREGATE()

Le tabelle di database di solito contengono solo dimensioni, ovvero dati che descrivono un singolo attributo relativo a una riga della tabella. I progetti LookML, tuttavia, possono definire sia dimensioni che misure. Una misura è un'aggregazione di dati in più righe, ad esempio SUM, AVG, MIN o MAX. Sono supportati anche altri tipi di misure, consulta la pagina Tipi di misurazione per l'elenco completo dei tipi di misure LookML supportati.

Con l'interfaccia Open SQL, devi designare le misure LookML incluse in una query racchiudendole (incluse le barre graffe) nella funzione speciale AGGREGATE(). Ad esempio, utilizza questa istruzione per specificare la misura conteggio dalla visualizzazione ordini:

AGGREGATE(`orders.count`)

Devi includere le misure LookML nella funzione AGGREGATE() indipendentemente dal fatto che la misura sia in una clausola SELECT, HAVING o ORDER BY.

Se non sai con certezza se un campo è una misura LookML, puoi utilizzare il metodo DatabaseMetaData.getColumns per accedere ai metadati del progetto LookML. La colonna IS_GENERATEDCOLUMN indicherà YES per tutte le misure LookML e NO per le dimensioni LookML. Per ulteriori informazioni, consulta la sezione Accesso ai metadati del database.

Esempio

Ecco un esempio di query che utilizza sia le dimensioni sia le misure. Questa query recupera le dimensioni state e city dalla visualizzazione customers e la misura dell'importo totale dalla visualizzazione orders. Entrambe queste visualizzazioni vengono unite nell'esplorazione ordini nel modello e-commerce. Per le città con più di 10 ordini, questa risposta alla query mostra le 5 città principali in base all'importo dell'ordine:

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;

Specificare campi e parametri con solo filtri con JSON_OBJECT

Open SQL Interface supporta parametri e campi con solo filtri.

Quando esegui query con un'interfaccia SQL aperta, puoi applicare alla query parametri e campi con solo filtri includendo una chiamata al costruttore JSON_OBJECT con il seguente formato:

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

L'oggetto JSON può contenere zero o più coppie chiave-valore di filtro e zero o più coppie chiave-valore di filtro.

  • La chiave nel costruttore JSON_OBJECT deve essere il nome di un campo o di un parametro con solo filtri.
  • Per i campi solo con filtri, il valore di ogni chiave deve essere un'espressione di filtro di stringa di Looker.
  • Per i parametri, il valore di ogni chiave deve essere un valore normale definito nella definizione parameter.

Consulta le sezioni seguenti per esempi di utilizzo di parametri e campi con solo filtri con l'interfaccia Open SQL.

Esempio di parametro

Come esempio per l'utilizzo di un parameter con interfaccia SQL aperta, se la vista customers aveva un parametro definito in Looker come segue:

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"
  }
}

Puoi inviare questa query all'interfaccia Open SQL per applicare il valore del parametro segment di medium_customers alla query:

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;

L'interfaccia Open SQL trasmette questo valore del parametro alla query in Looker, che applica il valore medium_customers a tutti i campi dell'esplorazione configurati per utilizzare il parametro segment. Per informazioni sul funzionamento dei parametri in Looker, consulta la documentazione di parameter.

Esempio di campo solo con filtri

Puoi utilizzare un campo filter con Open SQL Interface. Ad esempio, se una vista products aveva una dimensione e un campo solo filtro definiti in Looker come segue:

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 ;;
    }

Puoi utilizzare il filtro brand_select con Open SQL Interface inviando una query come la seguente:

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;

Apri l'interfaccia SQL applicherà l'espressione di filtro stringa di Looker %Santa Cruz% alla query in Looker. Per informazioni sul funzionamento dei campi con solo filtri in Looker, consulta la documentazione di filter.

Accesso ai metadati del database

L'interfaccia Open SQL supporta un sottoinsieme dell'interfaccia DatabaseMetaData JDBC standard, che viene utilizzata per ottenere informazioni sul database sottostante. Puoi utilizzare i seguenti metodi dell'interfaccia DatabaseMetaData per ottenere informazioni sul tuo modello LookML:

DatabaseMetadata.getSchemas

La seguente tabella descrive la relazione tra un modello LookML e le strutture di database standard nella risposta del metodo di interfaccia DatabaseMetadata.getSchemas.

getSchemas colonna di risposta Descrizione
TABLE_SCHEM Nome del modello LookML
TABLE_CATALOG (nullo)

DatabaseMetadata.getTables

La seguente tabella descrive la relazione tra un modello LookML e le strutture di database nella risposta del metodo di interfaccia DatabaseMetaData.getTables. La risposta include i metadati JDBC standard e i metadati specifici di Looker:

getTables colonna di risposta Descrizione
Metadati standard JDBC
TABLE_CAT (nullo)
TABLE_SCHEM Nome del modello LookML
TABLE_NAME Nome dell'esplorazione LookML
TABLE_TYPE Restituisce sempre il valore TABLE_TYPE
Metadati specifici di Looker
DESCRIPTION Descrizione di Esplora
LABEL Esplora etichetta
TAGS Esplorare i tag

DatabaseMetadata.getColumns

La tabella seguente descrive la correlazione tra un modello LookML e le strutture di database nella risposta del metodo di interfaccia DatabaseMetaData.getColumns. La risposta include i metadati JDBC standard e i metadati specifici di Looker:

getColumns colonna di risposta Descrizione
Metadati standard JDBC
TABLE_CAT (nullo)
TABLE_SCHEM Nome del modello LookML
TABLE_NAME Nome esplorazione LookML
COLUMN_NAME Nome del campo LookML in formato `<view_name>.<field_name>`. Ad esempio, `orders.amount`.
DATA_TYPE Il codice java.sql.Types della colonna. Ad esempio, i campi yesno di Looker sono con codice di tipo SQL 16 (BOOLEAN).
ORDINAL_POSITION Il valore ordinale basato su 1 del campo all'interno dell'esplorazione (mescolando le dimensioni e le misure in ordine alfabetico in base al nome della visualizzazione e poi al nome del campo)
IS_NULLABLE Restituisce sempre il valore YES
IS_GENERATEDCOLUMN YES per le misure, NO per le dimensioni
Metadati specifici di Looker
DIMENSION_GROUP Nome del gruppo di dimensioni se il campo fa parte di un gruppo di dimensioni. Se il campo non fa parte di un gruppo di dimensioni, il valore sarà nullo.
DRILL_FIELDS Elenco di campi di visualizzazione dettagliata impostati per la dimensione o la misura, se presenti
FIELD_ALIAS Alias per il campo, se presente
FIELD_CATEGORY Se il campo è dimension o measure
FIELD_DESCRIPTION Descrizione del campo
FIELD_GROUP_VARIANT Se il campo viene presentato sotto l'etichetta gruppo di un campo, FIELD_GROUP_VARIANT specificherà il nome più breve del campo visualizzato sotto l'etichetta del gruppo.
FIELD_LABEL Etichetta del campo
FIELD_NAME Nome della dimensione o della misura
HIDDEN Indica se il campo è nascosto nel selettore campi in Esplora (TRUE) o nel selettore campi in Esplora (FALSE).
LOOKER_TYPE Tipo di campo LookML per la dimensione o la misura
REQUIRES_REFRESH_ON_SORT Indica se la query SQL deve essere aggiornata per riordinare i valori del campo (TRUE) o se i valori del campo possono essere riordinati senza richiedere l'aggiornamento della query SQL (FALSE).
SORTABLE Indica se il campo può essere ordinato (TRUE) o non può essere ordinato (FALSE)
TAGS Tag dei campi
USE_STRICT_VALUE_FORMAT Indica se il campo utilizza il formato con valori massimi (TRUE) o meno (FALSE)
VALUE_FORMAT Stringa Formato valore per il campo
VIEW_LABEL Visualizza etichetta per il campo
VIEW_NAME Nome della visualizzazione in cui è definito il campo nel progetto LookML

Identificare le query dell'interfaccia Open SQL nell'interfaccia utente di Looker

Gli amministratori di Looker possono utilizzare l'interfaccia utente di Looker per identificare le query provenienti dall'interfaccia open SQL:

  • Nella pagina di amministrazione Query, le query dall'interfaccia Open SQL hanno il valore Origine "Interfaccia SQL". Il valore Utente mostra il nome dell'utente di Looker che ha eseguito la query. Puoi fare clic sul pulsante Dettagli per una query per visualizzare ulteriori informazioni sulla query. Nella finestra di dialogo Dettagli, puoi fare clic su Query interfaccia SQL per visualizzare la query SQL inviata a Looker da Apri interfaccia SQL.

  • Nell'esplorazione della cronologia dell'attività di sistema, le query dall'interfaccia Open SQL hanno un valore Origine pari a "sql_interface". Il valore User Email (Email utente) mostra l'indirizzo email dell'utente Looker che ha eseguito la query. Puoi andare direttamente all'esplorazione History filtrata in base a "sql_interface" inserendo l'indirizzo dell'istanza di Looker all'inizio di questo URL:

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

Repository per le dipendenze di terze parti

Il seguente link fornisce l'accesso al repository ospitato da Google per le dipendenze di terze parti utilizzate dal driver JDBC di Looker:

https://third-party-mirror.googlesource.com/looker_sql_interface/+/refs/heads/master/third_party/