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 di 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 dell'azienda o da un'altra applicazione di terze parti o da un'applicazione personalizzata sviluppata con l'API di Looker. L'interfaccia Open SQL fornisce l'accesso ai modelli LookML a qualsiasi applicazione di terze parti che supporta 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 loro analisti dei dati nel modello LookML, utilizzando al contempo gli strumenti che preferiscono.
Come l'interfaccia Open SQL mostra gli elementi del progetto LookML
Per capire in che modo l'interfaccia Open SQL mostra gli elementi di un progetto LookML, è importante comprendere la struttura dei progetti LookML.
Un progetto LookML è una raccolta di file che descrivono gli oggetti, le connessioni al database e gli elementi dell'interfaccia utente utilizzati per eseguire query SQL in Looker (per ulteriori informazioni, consulta Termini e concetti di LookML). I seguenti concetti di progetto LookML sono correlati all'interfaccia Open SQL:
- Un modello LookML specifica una connessione al 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 delle relazioni di join tra queste viste. L'interfaccia Open SQL 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 di colonna del database. Ad esempio, la dimensione
id
nella visualizzazioneorder_items
viene visualizzata dall'interfaccia Open SQL come colonna di database denominataorder_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 dell'esplorazione, fai riferimento ai due campi come `order_items`.`customer.id`
e `order_items`.`product.id`
. (consulta Utilizzare le barre graffe intorno agli identificatori di database per informazioni su dove inserire le barre graffe quando fai riferimento agli identificatori di database).
Configurazione dell'interfaccia Open SQL
Per utilizzare l'interfaccia Open SQL, svolgi i seguenti passaggi:
- Verifica che i requisiti siano soddisfatti.
- Scarica il file del driver JDBC Open SQL Interface.
Le sezioni seguenti descrivono questi passaggi.
Requisiti
Per utilizzare l'interfaccia Open SQL sono necessari i seguenti componenti:
- Un'istanza di Looker in hosting su Looker e con Looker 23.18 o versioni successive.
- Un progetto LookML che utilizza i dati di una connessione Google BigQuery. Il progetto LookML deve avere un file model che specifichi una connessione a Google BigQuery nel parametro
connection
. - Un ruolo utente di Looker che include l'autorizzazione
explore
per il modello LookML a cui vuoi accedere con l'interfaccia Open SQL.
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 prevede il seguente formato dell'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 nell'interfaccia Open SQL
L'interfaccia Open SQL 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:
- 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 istruzioni, consulta Registrazione di un'applicazione client OAuth.
- Accedi a Looker con OAuth per richiedere un token di accesso. Per un esempio, consulta Eseguire l'accesso utente utilizzando OAuth.
- 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);
Generazione di un token di accesso utilizzando le chiavi API
Anziché utilizzare il flusso OAuth standard per generare un token di accesso, puoi seguire questi passaggi per utilizzare l'API Looker per generare un token di accesso che può essere passato al driver JDBC Open SQL Interface:
- Genera le chiavi API per il tuo utente Looker come descritto nella pagina Impostazioni amministrazione - Utenti.
Utilizza l'endpoint API
login
per la tua istanza di Looker. La risposta include un token di accesso nel formatoAuthorization: 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\
Passa il valore
<access_token>
della risposta come token nell'oggetto Properties per passare le credenziali OAuth quando apri la connessione JDBC all'interfaccia Open SQL.
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 dell'interfaccia Open SQL. Consulta la sezione Chiavi API per informazioni sulla creazione di chiavi API per la tua istanza di Looker.
Utilizza la parte ID client della chiave API di Looker come nome utente. Utilizza la parte Client Secret per la password.
Eseguire query con l'interfaccia Open SQL
Tieni presente le seguenti linee guida quando esegui query con l'interfaccia Open SQL:
- L'interfaccia Open SQL accetta query SQL conformi alla sintassi di GoogleSQL.
- L'interfaccia Open SQL richiede i simboli di apostrofo (`) intorno agli identificatori di modello, esplorazione e campo. Per ulteriori informazioni ed esempi, consulta Utilizzare i backtick intorno agli identificatori di database.
- L'interfaccia Open SQL supporta la maggior parte degli operatori BigQuery.
- Con l'interfaccia Open SQL, devi designare le misure LookML incluse in una query racchiudendole (incluse le barre graffe) nella funzione speciale
AGGREGATE()
. Consulta la sezione Specificare le misure LookML conAGGREGATE()
.
Limitazioni di LookML
Tieni presente quanto segue quando invii query all'interfaccia Open SQL:
- Puoi utilizzare una clausola
WHERE
in una query dell'interfaccia Open SQL per passare i valorialways_filter
econditionally_filter
al tuo modello LookML.
Limiti di SQL
Tieni presente le seguenti limitazioni di SQL quando invii query all'interfaccia Open SQL:
- L'interfaccia Open SQL supporta solo le query
SELECT
. L'interfaccia Open SQL non supporta le istruzioniUPDATE
eDELETE
o qualsiasi altra istruzione Data Definition Language (DDL), Data Manipulation Language (DML) o Data Control Language (DCL). - L'interfaccia Open SQL non supporta l'operatore
JOIN
.- Non puoi inviare una query con l'operatore
JOIN
all'interfaccia Open SQL per creare unioni all'interno della stessa esplorazione o in due esplorazioni diverse. - Se vuoi creare una unione tra due tabelle del tuo database, puoi farlo nel modello LookML creando unioni a una o più viste in una definizione di esplorazione all'interno di un file modello nel tuo progetto LookML.
- Non puoi inviare una query con l'operatore
- L'interfaccia Open SQL non supporta le chiamate di funzioni finestra.
- L'interfaccia Open SQL non supporta le sottoquery.
- L'interfaccia Open SQL non supporta la conversione del fuso orario. Le date e le ore nel modello LookML avranno il tipo
DATETIME
nel fuso orario definito nelle impostazioni (fuso orario dell'utente, fuso orario dell'applicazione o fuso orario del database). - L'interfaccia Open SQL non supporta i tipi di dati BigQuery geography, JSON e time.
Utilizza i backtick intorno agli identificatori del 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 i simboli di apostrofo 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;
Specificare le misure LookML con AGGREGATE()
In genere, le tabelle di database contengono solo dimensioni, ovvero dati che descrivono un singolo attributo di una riga della tabella. I progetti LookML, tuttavia, possono definire sia le dimensioni sia le misure. Una metrica è 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 misura per l'elenco completo dei tipi di misura 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 espressione per specificare la misura conteggio dalla visualizzazione ordini:
AGGREGATE(`orders.count`)
Devi racchiudere le misure LookML nella funzione AGGREGATE()
indipendentemente dal fatto che si trovino 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 le misure LookML e NO
per le dimensioni LookML. Per ulteriori informazioni, consulta la sezione Accedere ai metadati del database.
Esempio
Ecco un esempio di query che utilizza sia le dimensioni sia le misure. Questa query recupera le dimensioni provincia e città dalla visualizzazione Clienti e la misura Importo totale dalla visualizzazione Ordini. 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
L'interfaccia Open SQL supporta i parametri e i campi con solo filtri.
Quando esegui query con l'interfaccia Open SQL, puoi applicare parametri e campi solo per i filtri alla query 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 parametro.
- La chiave nel costruttore
JSON_OBJECT
deve essere il nome di un campo o 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 di utilizzo di un parameter
con l'interfaccia Open SQL, se la visualizzazione customers
avesse 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 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 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 l'interfaccia Open SQL. Ad esempio, se una vista products
avesse una dimensione e un campo solo con filtri definito 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 l'interfaccia Open SQL 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;
L'interfaccia SQL aperta applicherà l'espressione del filtro di stringa di Looker %Santa Cruz%
alla query in Looker. Consulta la documentazione di filter
per informazioni sul funzionamento dei campi solo con filtri in Looker.
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 del 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 seguente tabella descrive la relazione tra un modello LookML e le strutture del 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 dell'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 codici 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à null. |
DRILL_FIELDS |
Elenco di campi di visualizzazione 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 |
Campo description |
FIELD_GROUP_VARIANT |
Se il campo è presentato sotto un'etichetta gruppo di campo, FIELD_GROUP_VARIANT specificherà il nome più breve del campo visualizzato sotto l'etichetta gruppo. |
FIELD_LABEL |
Etichetta del campo |
FIELD_NAME |
Nome della dimensione o della misura |
HIDDEN |
Indica se il campo è nascosto nel selettore dei campi nelle esplorazioni (TRUE ) o se è visibile nel selettore dei campi nelle esplorazioni (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 ordinare nuovamente i valori del campo (TRUE ) o se i valori del campo possono essere ordinati nuovamente senza richiedere l'aggiornamento della query SQL (FALSE ). |
SORTABLE |
Indica se il campo può essere ordinato (TRUE ) o meno (FALSE ). |
TAGS |
Tag dei campi |
USE_STRICT_VALUE_FORMAT |
Indica se il campo utilizza il formato dei valori rigoroso (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 originate dall'interfaccia Open SQL:
- Nella pagina di amministrazione Query, le query dall'interfaccia Open SQL hanno un valore Origine pari a "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 dall'interfaccia Open 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 (Indirizzo email utente) mostra l'indirizzo email dell'utente di Looker che ha eseguito la query. Puoi andare direttamente all'esplorazione Cronologia filtrata per "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]=%22sql_interface%22
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/