Il livello di modellazione semantica LookML di Looker consente a un analista di dati di definire dimensioni, aggregazioni, calcoli e relazioni tra i dati in un database SQL. I modelli LookML consentono la riutilizzabilità del codice e l'integrazione Git. Un modello LookML ben strutturato consente agli utenti di eseguire autonomamente l'esplorazione dei dati e il reporting.
Il modello LookML è la base di qualsiasi dato richiesto a Looker, indipendentemente dal fatto che la richiesta provenga dall'interfaccia Esplora di Looker nell'interfaccia utente di Looker, da una visualizzazione incorporata nel portale aziendale o da un'altra applicazione di terze parti oppure da un'applicazione personalizzata sviluppata con l'API Looker. L'interfaccia Open SQL fornisce l'accesso 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 trarre vantaggio da tutto il lavoro svolto dai loro analisti di dati nel modello LookML, utilizzando gli strumenti a cui sono più a proprio agio.
In che modo 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 capire 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 del progetto LookML sono correlati all'interfaccia Open SQL:
- Un model 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 le relazioni di join tra queste viste. L'interfaccia Open SQL mostra le esplorazioni come tabelle di database.
- Una vista definisce una raccolta di campi (dimensioni e misure). Una vista in genere si basa su una tabella del database o su una tabella derivata. Le viste 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 vista e di un nome campo come nome colonna del database. Ad esempio, la dimensione
id
nella visualizzazioneorder_items
viene indicata da Open SQL Interface come colonna di database denominataorder_items.id
.
Un'esplorazione di Looker può definire le relazioni di unione tra più viste. Poiché è possibile che una vista abbia un campo con lo stesso nome di un campo in una vista diversa, l'interfaccia Open SQL include sia il nome della vista sia il nome del campo quando fai riferimento a una colonna. Utilizza quindi questo formato per fare riferimento al nome di una colonna quando invii query all'interfaccia Open SQL:
`<view_name>.<field_name>`
Ad esempio, se esiste un'esplorazione denominata order_items
che unisce una vista denominata customer
a una chiamata product
ed entrambe le viste avevano una dimensione id
, fare riferimento ai due campi id
rispettivamente come `customer.id`
e `product.id`
. 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`
. (Vedi Utilizzare l'accento grave sugli identificatori di database per informazioni su dove posizionare l'accento grave quando si fa riferimento agli identificatori di database.)
Configurazione dell'interfaccia Open SQL
Per utilizzare l'interfaccia Open SQL, segui questi passaggi:
- Verifica che i requisiti siano soddisfatti.
- Abilita l'interfaccia Open SQL sulla tua istanza Looker.
- Scarica il file del driver JDBC dell'interfaccia Open SQL.
Nelle sezioni seguenti vengono descritti i passaggi.
Requisiti
Per utilizzare l'interfaccia Open SQL sono necessari i seguenti componenti:
- Un'istanza di Looker ospitata per Looker e che esegue 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 Google BigQuery nel parametro
connection
. - Un ruolo utente Looker che include l'autorizzazione
explore
sul modello LookML a cui vuoi accedere con l'interfaccia Open SQL.
Abilita l'interfaccia Open SQL sulla tua istanza Looker
Per abilitare l'interfaccia Open SQL sulla tua istanza, segui questi passaggi:
- Per le istanze Looker (originale), abilita la funzionalità del lab sperimentale interfaccia SQL sull'istanza di Looker.
- Per le istanze Looker (Google Cloud core), compila il modulo di interesse per il contratto pre-GA dell'interfaccia SQL di Looker. Il team di Google abiliterà la funzionalità interfaccia Open SQL per la tua istanza.
Scarica il driver JDBC dell'interfaccia Open SQL
Il driver JDBC dell'interfaccia aperta SQL di Looker si chiama avatica-<release_number>-looker.jar
. Scarica la versione più recente da GitHub all'indirizzo http://github.com/looker-open-source/calcite-avatica/releases.
Il driver JDBC prevede il seguente formato di URL:
jdbc:looker:url=http://your Looker instance URL
Ad esempio:
jdbc:looker:url=http://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 per l'autenticazione:
OAuth
I client JDBC che supportano OAuth possono essere configurati per l'utilizzo del server OAuth di un'istanza di Looker. Segui questi 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 quest'ultima possa riconoscere le richieste OAuth. Per ulteriori istruzioni, consulta Registrazione di un'applicazione client OAuth.
- Accedi a Looker con OAuth per richiedere un token di accesso. Per un esempio, consulta Esecuzione dell'accesso utente tramite OAuth.
- Utilizza un oggetto Proprietà per passare le credenziali OAuth quando apri la connessione JDBC all'interfaccia Open SQL.
Di seguito è riportato un esempio in cui viene utilizzato DriverManager#getConnection(<String>, <Properties>
`):
String access_token = getAccessToken() //uses the Looker OAuth flow to get a token
String URL = "jdbc:looker:url=http://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 mediante 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 possa essere trasmesso al driver JDBC dell'interfaccia Open SQL:
- Genera chiavi API per l'utente Looker come descritto nella pagina Impostazioni amministratore - Utenti.
Utilizza l'endpoint API
login
per la tua istanza Looker. La risposta include un token di accesso nel formatoAuthorization: token <access_token>
. Ecco un esempio di comando curl per effettuare questa richiesta:curl -k -d "client_id=<client_id>&client_secret=<client_secret>" http://<looker_host>/login\
Passa il valore
<access_token>
della risposta come token nell'oggetto Proprietà per passare le credenziali OAuth quando viene aperta la connessione JDBC a Open SQL Interface.
Chiavi API
Per l'autenticazione puoi anche utilizzare le chiavi API al posto di nome utente e password. Le chiavi API sono considerate meno sicure di OAuth e potrebbero essere disponibili solo durante l'anteprima dell'interfaccia Open SQL. Per informazioni sulla creazione di chiavi API per l'istanza di Looker, consulta Chiavi API.
Utilizza la parte Client ID della chiave API Looker come nome utente. Utilizza la parte del client secret per la password.
Esecuzione di 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 le query SQL che rispettano la sintassi GoogleSQL.
- L'interfaccia Open SQL richiede un accento grave (`) intorno al modello, all'esplorazione e agli identificatori dei campi. Per ulteriori informazioni ed esempi, consulta Usare l'accento grave per gli identificatori del database.
- L'interfaccia Open SQL supporta la maggior parte degli operatori BigQuery. Se hai bisogno di un operatore non supportato, invia una richiesta via email a [email protected].
- Con l'interfaccia Open SQL, devi designare tutte le misure LookML incluse in una query inserendo la misura (inclusi gli apici inversi) nella funzione speciale
AGGREGATE()
. Consulta la sezione Specificare le misure LookML conAGGREGATE()
.
Limitazioni di LookML
Tieni presente le seguenti limitazioni di LookML durante l'invio di query all'interfaccia Open SQL:
- L'interfaccia Open SQL supporta solo dimensioni e misure Looker. L'interfaccia Open SQL non supporta i parametri LookML
filter
oparameter
. - Non è possibile utilizzare l'interfaccia Open SQL per sostituire i valori di
always_filter
econditionally_filter
definiti nel modello LookML.
Limitazioni SQL
Tieni presente le seguenti limitazioni SQL per l'invio di query all'interfaccia Open SQL:
- L'interfaccia Open SQL supporta solo le query
SELECT
. L'interfaccia Open SQL non supporta le istruzioniUPDATE
eDELETE
, né qualsiasi altro tipo di 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 join all'interno della stessa esplorazione o in due esplorazioni diverse. - Se vuoi creare un join tra due tabelle nel database, puoi farlo nel modello LookML creando join per una o più viste in una definizione di Esplora 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 funzione finestra.
- L'interfaccia Open SQL non supporta le sottoquery.
- L'interfaccia Open SQL non supporta la conversione del fuso orario. Le date e gli orari 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 area geografica, JSON e tempo.
Usa l'accento grave per gli identificatori di database
Quando invii query all'interfaccia Open SQL, utilizza l'accento grave per gli 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>`
- tabella:
`<explore_name>`
colonna:
`<view_name>.<field_name>`
Ecco un esempio di formato di istruzione SELECT
che utilizza i seguenti elementi:
SELECT `view.field`
FROM `model`.`explore`
LIMIT 10;
Specifica le misure LookML con AGGREGATE()
Le tabelle di database in genere contengono solo dimensioni, ossia dati che descrivono un singolo attributo relativo a una riga della tabella. I progetti LookML, tuttavia, possono definire sia dimensioni che misure. Una misurazione è un'aggregazione di dati su più righe, ad esempio SUM
, AVG
, MIN
o MAX
. Sono supportati anche altri tipi di misure. Per l'elenco completo dei tipi di misure LookML supportati, consulta la pagina Tipi di misure.
Con l'interfaccia Open SQL, devi designare tutte le misure LookML incluse in una query inserendo la misura (inclusi gli apici inversi) nella funzione speciale AGGREGATE()
. Ad esempio, utilizza questo valore per specificare la misura count dalla visualizzazione orders:
AGGREGATE(`orders.count`)
Devi eseguire il wrapping delle misure LookML nella funzione AGGREGATE()
se la misura si trova 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 per il 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 una query di esempio che utilizza sia dimensioni che misure. Questa query recupera le dimensioni state e city dalla visualizzazione customers e la misura dell'importo totale dalla visualizzazione orders. Entrambe le viste sono unite all'esplorazione degli ordini nel modello di e-commerce. Per le città con più di 10 ordini, questa risposta alla query mostra le prime 5 città per 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;
Specifica i campi e i parametri solo con filtro con JSON_OBJECT
L'interfaccia Open SQL supporta i parametri e i campi solo con filtro.
Quando esegui query con l'interfaccia Open SQL, puoi applicare parametri e campi solo con filtro alla query includendo una chiamata del 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 parametri.
- La chiave nel costruttore
JSON_OBJECT
deve essere il nome di un parametro o campo con solo filtro. - Per i campi solo con filtro, il valore di ogni chiave deve essere un'espressione di filtro stringa Looker.
- Per i parametri, il valore di ogni chiave deve essere un valore normale definito nella definizione di
parameter
.
Consulta le sezioni seguenti per alcuni esempi di utilizzo dei parametri e dei campi solo con filtro con l'interfaccia Open SQL.
Esempio di parametro
Ecco un esempio di come utilizzare parameter
con interfaccia Open SQL, 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"
}
}
Potresti 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;
Open SQL Interface passerà questo valore parametro alla query in Looker e Looker applicherà il valore medium_customers
a tutti i campi dell'esplorazione configurati per l'utilizzo del parametro segment
. Per informazioni su come funzionano i parametri in Looker, consulta la documentazione di parameter
.
Esempio di campo solo filtro
Puoi utilizzare un campo filter
con l'interfaccia Open SQL. Ad esempio, se una vista products
aveva una dimensione e un campo con 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 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'apertura dell'interfaccia SQL applicherà l'espressione di filtro per stringa Looker %Santa Cruz%
alla query in Looker. Per informazioni su come funzionano i campi solo con filtro in Looker, consulta la documentazione di filter
.
Accesso ai metadati del database
L'interfaccia Open SQL supporta un sottoinsieme dell'interfaccia DatabaseMetaData JDBC standard, utilizzata per ottenere informazioni sul database sottostante. Puoi usare i seguenti metodi dell'interfaccia DatabaseMetaData per ottenere informazioni sul tuo modello LookML:
DatabaseMetadata.getSchemas
La seguente tabella descrive in che modo un modello LookML è correlato alle strutture di database standard nella risposta del metodo dell'interfaccia DatabaseMetadata.getSchemas
.
getSchemas colonna risposta |
Descrizione |
---|---|
TABLE_SCHEM |
Nome modello LookML |
TABLE_CATALOG |
(nullo) |
DatabaseMetadata.getTables
La tabella seguente descrive la relazione tra un modello LookML e le strutture di database nella risposta del metodo dell'interfaccia DatabaseMetaData.getTables
. La risposta include metadati JDBC standard e metadati specifici di Looker:
getTables colonna risposta |
Descrizione |
---|---|
Metadati JDBC standard | |
TABLE_CAT |
(nullo) |
TABLE_SCHEM |
Nome modello LookML |
TABLE_NAME |
Nome esplorazione LookML |
TABLE_TYPE |
Restituisce sempre il valore TABLE_TYPE |
Metadati specifici di Looker | |
DESCRIPTION |
Esplora description |
LABEL |
Esplora etichetta |
TAGS |
Esplora i tag |
DatabaseMetadata.getColumns
La tabella seguente descrive la relazione tra un modello LookML e le strutture di database nella risposta del metodo dell'interfaccia DatabaseMetaData.getColumns
. La risposta include metadati JDBC standard e metadati specifici di Looker:
getColumns colonna risposta |
Descrizione |
---|---|
Metadati JDBC standard | |
TABLE_CAT |
(nullo) |
TABLE_SCHEM |
Nome 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 il codice di tipo SQL 16 (BOOLEAN). |
ORDINAL_POSITION |
L'ordinale in base 1 del campo all'interno dell'esplorazione (combinazione di dimensioni e misure in ordine alfabetico per nome visualizzazione, poi nome 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 campo sarà nullo. |
DRILL_FIELDS |
Elenco di campi di esplorazione 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 viene presentato sotto un campo etichetta gruppo, FIELD_GROUP_VARIANT specifica il nome più breve del campo visualizzato sotto l'etichetta del gruppo. |
FIELD_LABEL |
Campo label |
FIELD_NAME |
Nome della dimensione o misura |
HIDDEN |
Indica se il campo è nascosto dal selettore campi nelle esplorazioni (TRUE ) o se è visibile nel selettore 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 poter riordinare i valori del campo (TRUE ) o se i valori del campo possono essere riordinati senza richiedere un aggiornamento della query SQL (FALSE ). |
SORTABLE |
Indica se il campo può essere ordinato (TRUE ) o non può essere ordinato (FALSE ). |
TAGS |
Tag di campo |
USE_STRICT_VALUE_FORMAT |
Indica se il campo utilizza il formato valore massimo (TRUE ) o meno (FALSE ) |
VALUE_FORMAT |
Stringa di formato valore per il campo |
VIEW_LABEL |
Visualizza etichetta per il campo. |
VIEW_NAME |
Nome della vista 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 che hanno avuto origine dall'interfaccia Open SQL:
- Nella pagina di amministrazione Query, le query dell'interfaccia Open SQL hanno il valore Origine di "Interfaccia SQL". Il valore Utente mostra il nome dell'utente di Looker che ha eseguito la query.
Nell'esplorazione della cronologia delle attività di sistema, le query dell'interfaccia Open SQL hanno il valore Source di "sql_interface". Il valore Email dell'utente mostra l'indirizzo email dell'utente di Looker che ha eseguito la query. Puoi andare direttamente all'esplorazione Cronologia filtrata su "sql_interface" inserendo l'indirizzo dell'istanza di Looker all'inizio di questo URL:
http://your Looker instance URL/explore/system__activity/history?fields=history.source,history.completed_date&f[history.source]=sql_interface
Feedback per l'interfaccia Open SQL
Per eventuali domande o richieste di funzionalità per l'interfaccia SQL aperta, scrivi all'indirizzo [email protected].