BigQuery è il database di Google che permette un’analisi interattiva di grandi moli di dati in tempi ridotti e a basso costo. Con BigQuery è possibile interrogare terabyte di dati senza la necessità di dover gestire alcuna infrastruttura o le impostazioni del database. BigQuery utilizza SQL e può usufruire del modello pay-as-you-go. Pertanto, questo strumento permette di concentrarsi sull’analisi dei dati al fine di trovare le informazioni significative.
In questa serie di articoli analizzeremo alcune funzionalità avanzate di BigQuery. In particolare, esploreremo la clausola WITH rispetto alle tabelle permanenti a livello di prestazioni e leggibilità, e successivamente presenteremo le funzioni GIS che sono supportate nativamente. Dopo di che presenteremo l’uso delle funzioni di finestra analitica, che permettono di suddividere i dati in sottoinsiemi su cui eseguire l’SQL. Discuteremo anche le funzioni per la creazione di modelli per la classificazione dei dati che possono essere generati e salvati direttamente in BigQuery. Infine, concluderemo con le migliori strategie per ottimizzare le performance delle query.
La clausola WITH
La clausola WITH contiene una o più common table expression (CTE). Ogni CTE lega i risultati di una sottoquery ad un nome di tabella, che può essere usato altrove nella stessa espressione di query.
La sintassi è la seguente:
WITH cte[, ...] BigQuery non materializza i risultati delle CTE non ricorsive all’interno della clausola WITH. Se una CTE non ricorsiva è referenziata in più punti in una query, allora la CTE viene eseguita una volta per ogni riferimento. Usare una clausola WITH è un ottimo modo per aiutare a spezzare una query complessa.
Vediamo alcuni esempi per capire il suo funzionamento e i vantaggi che introduce.
Ecommerce log
Supponiamo che il team di analisti ha esportato i log di Google Analytics per un sito di e-commerce in BigQuery e ha creato una nuova tabella con tutti i dati grezzi delle sessioni dei visitatori dell’e-commerce. Un esempio di dataset è disponibile in Google Cloud qui.
Vogliamo individuare i cinque prodotti con il maggior numero di visualizzazioni (product_views) da parte di tutti i visitatori, includendo le persone che hanno visualizzato lo stesso prodotto più di una volta. In Google Analytics, un visitatore può visualizzare un prodotto durante i seguenti tipi di interazione: ‘page’, ‘screenview’, ‘event’, ‘transaction’, ‘item’, ‘social’, ‘exception’, ‘timing’. Per semplificare l’analisi, consideriamo solo le interazioni di tipo ‘page’.
La query, riportata di seguito, conta il numero di volte che un prodotto (v2ProductName) è stato visto (product_views), ordina il risultato in base ai valori decrescenti di visualizzazioni e limita la visualizzazione ai primi 5 risultati.
SELECT
COUNT(*) AS product_views,
(v2ProductName) AS ProductName
FROM `data-to-insights.ecommerce.all_sessions`
WHERE type = 'PAGE'
GROUP BY v2ProductName
ORDER BY product_views DESC
LIMIT 5; Questa query non utilizza il costrutto WITH poiché il calcolo richiesto può essere risolto mediante una semplice funzione di aggregazione (COUNT) applicata a ciascun gruppo definito dalla clausola GROUP BY. Il risultato però così ottenuto non è del tutto veritiero. Infatti, considera visualizzazioni distinte quelle effettuate dallo stesso utente. Per eliminare questo errore di valutazione è necessario riscrivere la query precedente come segue.
WITH unique_product_views_by_person AS (
-- find each unique product viewed by each visitor
SELECT
fullVisitorId,
(v2ProductName) AS ProductName
FROM `data-to-insights.ecommerce.all_sessions`
WHERE type = 'PAGE'
GROUP BY fullVisitorId, v2ProductName )
-- aggregate the top viewed products and sort them
SELECT
COUNT(*) AS unique_view_count,
ProductName
FROM unique_product_views_by_person
GROUP BY ProductName
ORDER BY unique_view_count DESC
LIMIT 5; In questo caso, andiamo a definire una CTE unique_product_views_by_person che crea una tabella temporanea contente solo la coppia di dati fullVisitorId e v2ProductName (rinominato ProductName mediante la clausola AS). Il risultato della CTE viene utilizzato per calcolare il numero di visite univoche di ciascun prodotto.
La clausola WITH, pertanto, è suddividere il problema in sotto-problemi più semplici evitando anche errori di calcolo.
Pandemia
La pandemia del COVID-19 è ormai oggetto quotidiano di svariate analisi grazie anche all’enorme quantità di dati resi pubblicamente disponibili. Usando il dataset bigquery-public-data.covid19_open_data.covid19_open_data vogliamo semplicemente rispondere alla seguente domanda: “Quanti stati negli USA hanno avuto più di 100 morti il 10 aprile 2020?”
Per rispondere a questa domanda possiamo usare la query seguente.
WITH per_state AS (
SELECT subregion1_name as state,
SUM(cumulative_deceased) as total_cases
FROM `bigquery-public-data.covid19_open_data.covid19_open_data`
WHERE date='2020-04-10'
and country_name="United States of America"
and subregion1_name IS NOT NULL
GROUP BY subregion1_name )
SELECT count(*) as count_of_states
FROM per_state
WHERE total_cases > 100; La CTE per_state calcola il numero totali di decessi (total_cases) per ogni subregion appartenente agli Stati Uniti d’America e il cui valore non sia NULL. Il suo risultato viene usato per imporre il vincola sulla soglia minima di 100 decessi e contare il numero di stati che hanno superato questa soglia.
Considerazioni
Come visto in precedenza la clausola WITH viene utilizzata per calcolare un risultato intermedio utilizzato in una delle query successive per semplificare la risoluzione del problema. Diversamente, si potrebbe utilizzare una tabella per salvare il risultato della query. Discutiamo brevemente i pro e contro di ogni approccio.
Potenzialmente, le query incluse nella clausola WITH potrebbero essere incollare all’interno della clausola FROM di un’altra query, come avviene in Oracle per la definizione delle table functions. Questo approccio però produce una query molto difficile da leggere, anche se il risultato è il medesimo. Con la clausola WITH è possibile anche avere più di una sottoquery che possono essere concatenate tra di loro. In questo modo si producono tanti risultati intermedi quanti sono necessari per rispondere all’interrogazione.
Ma perché tenere la query di pre-elaborazione nella clausola WITH e non memorizzare il suo risultato in una nuova tabella permanente? Ottima domanda!
Ovviamente è assolutamente possibile memorizzare i risultati della query di pre-elaborazione in una tabella. Saranno molto più veloci da interrogare in seguito, perché la pre-elaborazione e gli eventuali join coinvolti sono già stati effettuatati. Inoltre, è possibile potenzialmente condividere quella tabella con altri utenti.
Lo svantaggio di questo approccio è che di solito gli utenti hanno bisogno solo di un sottoinsieme dei dati aggiungendo continuamente clausole WHERE che andrebbero ad inficiare sulle performance. Pertanto, potrebbe essere molto più veloce usare la clausola WITH. Ma come è possibile che sia più efficiente l’approcio con la clausola WITH rispetto ad un risultato salvato in una tabella indicizzata? La risposta è che BigQuery è abbastanza intelligente da fare quello che viene chiamato push-down. Anche se la query con la clausola WITH non ha un filtro WHERE, BigQuery applica il filtro della clausola WHERE della query successiva come parte della dichiarazione WITH. In questo modo, l’esecuzione risulta ottimizzata.
Infine, con la tabella permanente si perdono i vari calcoli effettuati nei vari passaggi. Ciò comporta che è necessario distruggerla e ricrearla ogni qualvolta che bisogna fare una modifica alla query di partenza.
