Sintassi SQL di Google BigQuery: Guida completa

Che cos'è Google BigQuery? Cosa può fare per la vostra organizzazione? L'SQL BigQuery è un'abilità preziosa? Dove si può imparare e dove si può fare pratica?

Oggi sono poche le organizzazioni che non sono informatizzate. Molti processi si avvalgono dell'IoT (Internet of Things), in cui tutti i tipi di dispositivi sono collegati in rete e forniscono continuamente dati in tempo reale ai sistemi informatici. Il risultato è un'enorme quantità di dati disponibili per il processo decisionale. Spesso si parla di data lake.

Le sfide principali di questo flusso di dati sono come e dove archiviarli e come renderli disponibili ai decisori. BigQuery di Google è probabilmente una delle migliori risposte a queste sfide.

Le competenze SQL di BigQuery sono un'aggiunta importante al vostro portfolio. Se volete imparare BigQuery, da dove dovreste iniziare? BigQuery supporta gli standard ANSI SQL, quindi il primo passo è acquisire le competenze SQL di base.

Se non conoscete già l'SQL, il corso LearnSQL.it di SQL Basics vi permetterà di acquisire rapidamente le competenze necessarie. Imparerete importanti concetti di database; alla fine del corso sarete in grado di estrarre, aggregare e analizzare dati da una o più tabelle per ottenere informazioni significative. Il corso dura circa 10 ore e comprende 129 esercizi interattivi che insegnano a risolvere problemi reali con SQL.

Cos'è Google BigQuery?

Google BigQuery è una soluzione di data warehousing senza server e altamente scalabile. Può analizzare terabyte di dati in pochi secondi e petabyte di dati in pochi minuti. Ma cosa significa in realtà?

Magazzino dati

I dati vengono solitamente archiviati per uno dei due scopi:

• Supportare le operazioni quotidiane di un'azienda
• Supportare il processo decisionale.

Il primo di questi scopi è noto come dati operativi ed è spesso memorizzato da diversi sistemi informatici che svolgono le varie attività necessarie all'organizzazione. Il secondo è noto come dati analitici. Idealmente, questi dati dovrebbero essere archiviati in un luogo centrale, accessibile a chiunque abbia bisogno di informazioni.

Questo luogo centrale è noto come data warehouse e deve essere ottimizzato per cercare e analizzare rapidamente grandi volumi di dati. Per trasferire i dati operativi nel magazzino si utilizza un processo noto come ETL (Extract, Transform, Load).

Come data warehouse, BigQuery presenta diversi vantaggi:

• Dispone di eccellenti strumenti ETL.
• Utilizza il file system Colossus, progettato per le "grandi dimensioni"; è possibile aggiungere facilmente altro spazio quando necessario.
• Utilizza SQL come linguaggio di interrogazione, quindi è compatibile con i migliori strumenti di business intelligence (BI), come ad esempio
• Google Data Studio consente di presentare i dati in molti modi diversi.

Ricerche veloci

BigQuery utilizza il motore di ricerca Dremel per elaborare rapidamente grandi volumi di dati. Come ho già detto, BigQuery è in grado di analizzare petabyte (un petabyte equivale a un milione di gigabyte) in pochi minuti. Come fa a farlo?

Ogni volta che si esegue una ricerca su BigQuery, migliaia di thread di lavoratori si dividono il compito; ciò consente di analizzare simultaneamente enormi quantità di dati. I risultati vengono poi riuniti e restituiti all'utente.

Architettura serverless

Chi utilizza BigQuery non ha bisogno di un server. Ciò significa che non dovete gestire l'infrastruttura, preoccuparvi dei backup e della sicurezza o capire se avete bisogno di altri dischi rigidi. I vostri dati diventano parte del grande lago di dati, o oceano!

Questo oceano di dati digitali è suddiviso in innumerevoli server in tutto il mondo. Se è necessario più spazio, Google lo assegna e lo gestisce. L'utente paga solo per quello che utilizza.

E la potenza di elaborazione necessaria per eseguire i calcoli? Google assegna la potenza di elaborazione quando è necessaria. Il software di rete ad alta velocità di Google, Jupiter, è appositamente progettato per una comunicazione veloce tra i thread; si paga solo per gli slot di elaborazione utilizzati.

E per quanto riguarda la sicurezza? È possibile creare viste per i dati e specificare esattamente chi può vedere cosa. Poiché i dati sono crittografati, sia quando vengono archiviati sia quando sono in transito, sono al sicuro da eventuali intrusi.

Fare pratica con BigQuery

Se conoscete già l'SQL, potete provare subito BigQuery. Se non lo conoscete, potete comunque provare alcuni esempi nella prossima sezione per farvi un'idea di ciò che può fare. Ma per ottenere tutti i vantaggi di BigQuery, vale la pena di migliorare le proprie competenze SQL iscrivendosi a un corso SQL completo. LearnSQL.it Il percorso di apprendimento di SQL from A to Z comprende 7 corsi che vi porteranno dal principiante all'esperto.

Dove e come si possono mettere in pratica queste competenze in BigQuery? Chiunque può creare un progetto su BigQuery gratuitamente. Il costo viene addebitato solo quando si desidera caricare ed elaborare grandi quantità di dati. La sandbox di BigQuery consente di inserire i propri dati di prova. Oppure si può accedere a un'ampia gamma di database pubblici con cui giocare.

Per iniziare, visitate la pagina BigQuery di Google Cloud Console. Se non avete ancora effettuato l'accesso a un account Google, vi verrà chiesto di farlo. La prima volta che si accede a questa console, si vedrà un avviso come questo:

Fate clic su CREA PROGETTO e date un nome al vostro progetto oppure accettate il suggerimento di Google.

Ora avrete bisogno di alcuni dati su cui esercitarvi. Fortunatamente, BigQuery mette a disposizione molti dati pubblici. Per accedervi, è necessario aggiungere i set di dati pubblici all'area di lavoro SQL. Nella parte superiore sinistra della pagina, vedrete questo:

Fare clic su AGGIUNGI. Verrà visualizzata una schermata a comparsa con diverse opzioni. Fare clic su questa:

Verrà richiesto il nome del progetto. Immettere bigquery-public-data e fare clic su 'STAR'. Il progetto dovrebbe essere elencato nell'esploratore di progetti sulla sinistra dello schermo. Ora siete pronti per iniziare l'esplorazione. Se fate clic sulla freccia accanto a bigquery-public-data, vedrete un elenco di tutti i database di questo progetto. Se si fa clic su un database, viene visualizzato un elenco delle sue tabelle. Gli esempi riportati nella prossima sezione di questo articolo sono tratti dal database census_bureau_international.

Se avete già esperienza con SQL, questo è tutto ciò che vi serve per iniziare. Tuttavia, se siete alle prime armi con SQL, potreste voler fare un po' di pratica in un ambiente in cui l'aiuto sia disponibile e in cui siate guidati nella ricerca di soluzioni ai problemi. In questo caso, vi consiglio la traccia pratica di LearnSQL.it, dove avrete la possibilità di risolvere oltre 80 sfide del mondo reale.

Sintassi di BigQuery

Ora che sapete come configurare BigQuery, diamo un'occhiata alla sintassi di BigQuery. Ma prima, come forse già saprete, l'SQL ha molti dialetti diversi, unici per i vari prodotti di database. Per saperne di più sui diversi dialetti dell'SQL, consultate il sito.

L'American National Standards Institute (ANSI) definisce gli standard raccomandati per SQL; ogni dialetto può implementare o meno tutti questi standard. Molti di essi hanno aggiunto al linguaggio le proprie funzionalità.

BigQuery aveva originariamente una versione non standard di SQL che era unica per le sue esigenze. Questa versione è ora chiamata "Legacy SQL" e va imparata solo se si vuole lavorare per un'azienda che la utilizza ancora. BigQuery utilizza ora Google SQL, che si attiene strettamente allo standard ANSI. Se avete già familiarità con un altro dialetto (come MS SQL Server o MySQL), troverete pochissime differenze nell'utilizzo di BigQuery.

La differenza principale è che BigQuery ha alcune funzioni statistiche aggiuntive e supporta strutture di dati complesse come JSON e array.

Vediamo alcuni esempi di esecuzione di query nella Cloud Console. Se non avete mai usato SQL prima d'ora e trovate gli esempi difficili da seguire, potreste trovare utile questo articolo sui comandi SQL più importanti.

La sintassi completa di tutti i comandi SQL di Googleè disponibile qui.

Esecuzione dei comandi nella BigQuery Cloud Console

A destra dell'esploratore di progetti nella BigQuery Cloud Console, si trova una finestra in cui è possibile eseguire i comandi di Google SQL. L'aspetto è questo:

Si digita la query nella finestra della query, quindi si fa clic su 'RUN' nella barra delle azioni in alto. Se è stato digitato un comando SQL valido, nella finestra dei risultati verranno visualizzati i dati richiesti. In caso contrario, verrà visualizzato un messaggio di errore.

Semplici comandi SQL

Per l'estrazione e l'analisi dei dati si utilizza un solo comando SQL: l'istruzione SELECT. Nella sua forma più semplice, il comando è SELECT * FROM tablename, dove tablename è il nome di una delle tabelle del database. L'asterisco (*) indica di restituire tutte le colonne della tabella.

In Google SQL, a meno che i dati non provengano dal proprio progetto, il nome della tabella viene preceduto dal nome del progetto e del database. Negli esempi, accederò ai dati del database census_bureau_international nel progetto bigquery-public-data. Una delle tabelle che utilizzerò si chiama midyear_population. Dovrò fare riferimento a questa tabella come:

bigquery-public-data.census_bureau_international.midyear_population

Per comodità, si può dare a questo nome piuttosto lungo un alias, in modo da non doverlo digitare continuamente. Nell'esempio che segue, gli darò l'alias midyear.

bigquery-public-data.census_bureau_international.midyear_population AS midyear

Quando si eseguono test su tabelle che possono essere molto grandi, è una buona idea porre un limite al numero di righe che verranno restituite. Altrimenti si potrebbero superare i limiti della versione gratuita di BigQuery.

Per vedere tutto ciò che è contenuto nelle prime 10 righe di questa tabella, si deve digitare questo comando nella finestra della query, quindi fare clic su RUN:

SELECT * FROM 
bigquery-public-data.census_bureau_international.midyear_population 
AS midyear
LIMIT 10

Provate voi stessi. A meno che non si sia commesso un errore di battitura, la schermata dovrebbe apparire come questa:

È possibile utilizzare la barra di scorrimento per scorrere i risultati.

Vediamo ora alcune delle funzioni opzionali dell'istruzione SELECT.

• La clausola WHERE consente di scegliere solo le righe che soddisfano un determinato criterio.
• Invece di mostrare tutte le colonne (cioè utilizzando l'asterisco), è possibile elencare i nomi delle colonne che si desidera visualizzare. Nell'immagine precedente, i nomi delle colonne (country_code, country_name, year e midyear_population) sono visualizzati nella parte superiore di ogni colonna della finestra dei risultati.
• È possibile specificare l'ordine delle righe nel set di risultati utilizzando l'opzione ORDER BY

Provate a eseguire questa query per vedere solo i nomi dei paesi e le popolazioni del 1975, ordinati per nome del paese.

SELECT
midyear.country_name, midyear_population
FROM
bigquery-public-data.census_bureau_international.midyear_population
AS midyear
WHERE
year = 1975
ORDER BY country_name

Se avete fatto tutto correttamente, dovreste vedere questo:

SQL ha molte altre funzioni. Due delle più utili sono:

• L'uso di funzioni aggregate, come SUM() per trovare i totali, AVG() per trovare le medie e MIN() e MAX() per trovare i valori più piccoli e più grandi. Gli aggregati vengono solitamente utilizzati insieme alla clausola GROUP BY, che definisce i gruppi a cui si applicano le funzioni aggregate. Senza raggruppare i dati, si vedranno gli aggregati per l'insieme dei dati.
• Recuperare i dati da due o più tabelle, a condizione che abbiano una o più colonne in comune che possono essere utilizzate per unirle.

Proviamo alcune query di esempio. La prima:

SELECT
midyear.country_name, AVG(midyear.midyear_population)
FROM
bigquery-public-data.census_bureau_international.midyear_population
AS midyear
GROUP BY country_name
ORDER BY country_name

Questa query mostra la popolazione media di metà anno per ogni paese nell'intero periodo.

La prossima query prende i dati da due tabelle: la tabella midyear_population e la tabella birth_death_growth_rates. È quindi possibile vedere il tasso di crescita insieme alla popolazione.

È necessario unire i dati su due colonne corrispondenti: il paese e l'anno. Ecco la query e i suoi risultati:

SELECT
midyear.country_name, midyear.midyear_population, 
growth.growth_rate
FROM
bigquery-public-data.census_bureau_international.midyear_population
AS midyear
JOIN
bigquery-public-data.census_bureau_international.birth_death_growth_rates
AS growth
ON growth.country_name = midyear.country_name
AND growth.year = midyear.year
WHERE midyear.year = 1975
ORDER BY country_name

Per tutte queste query, BigQuery utilizza la sintassi standard ANSI; le query funzionano ugualmente bene in qualsiasi dialetto SQL.

Alcuni comandi di Advanced SQL

Approfondire la sintassi di istruzioni SQL complesse va oltre lo scopo di questo articolo, ma vorrei includere un paio di query avanzate che hanno la stessa sintassi in Google SQL e in altri dialetti (come MS SQL Server).

Le funzioni finestra sono utilizzate per visualizzare le singole righe rispetto agli aggregati dell'intero set di dati. Questo esempio classifica i Paesi in base alla popolazione: il Paese con la popolazione più numerosa è classificato come 1.

SELECT
midyear.country_name, midyear.midyear_population, RANK() OVER (ORDER BY midyear_population DESC)
FROM
bigquery-public-data.census_bureau_international.midyear_population
AS midyear
WHERE
year = 1975
ORDER BY country_name

I risultati dovrebbero apparire così:

Altre funzioni avanzate includono le estensioni GROUP BY, che consentono di mostrare aggregati a più livelli. Anche in questo caso, la sintassi di BigQuery segue gli standard ANSI; se avete imparato queste funzioni in MS SQL, sarete in grado di usarle in BigQuery.

Ecco un esempio. Questo mostra i totali a livello di età e di paese.

SELECT
m.country_name, m.age, SUM(m.population)
FROM
bigquery-public-data.census_bureau_international.midyear_population_agespecific
AS m
WHERE m.year = 1975
GROUP BY ROLLUP (m.country_name, m.age)
ORDER BY m.country_name, m.age

I risultati appaiono così. Gli spazi vuoti indicano che quella riga di output è un totale.

Caratteristiche aggiuntive di BigQuery

BigQuery ha molte caratteristiche che non si trovano nella maggior parte dei dialetti. Queste caratteristiche lo rendono particolarmente utile per gestire i big data provenienti da molte fonti diverse.

Ha diversi tipi di dati aggiuntivi:

• Geography - Memorizza le coordinate geografiche e può essere utilizzato per calcolare la distanza tra due località.
• Interval - Memorizza un intervallo di tempo.
• JSON - Un formato utilizzato da molte applicazioni.
• Struct - Un contenitore per contenere campi ordinati.

BigQuery consente anche di lavorare con gli array.

Quando si lavora con insiemi di dati molto grandi, è particolarmente utile poter estrarre campioni casuali di dati. Ecco un esempio di estrazione di un campione casuale dalla tabella midyear_population.

SELECT
AVG (m.life_expectancy)
FROM
bigquery-public-data.census_bureau_international.mortality_life_expectancy
AS m
TABLESAMPLE SYSTEM (10 PERCENT)

Invece di esaminare l'intera tabella, che è di grandi dimensioni, si prenderà un campione statistico del 10% della tabella. Poiché il campione è casuale, si potrebbero ottenere risultati leggermente diversi a ogni esecuzione. L'output potrebbe essere simile a questo:

Per ulteriori informazioni sulle funzioni aggiuntive di BigQuery SQL, consultare la documentazione del prodotto.

Lavorare con Google BigQuery: Il prossimo passo

L'oceano digitale si allarga ogni anno di più e sempre più datori di lavoro cercano competenze in BigQuery. Vale la pena diventare esperti in questo settore. Ma come?

Migliorate le vostre competenze in SQL seguendo alcuni corsi. LearnSQL.it offre un'ampia gamma di corsi che coprono tutti gli argomenti, dai principianti agli avanzati. È possibile cercare corsi specifici o semplicemente sfogliare l'offerta.

LearnSQL.itIl corso di pratica vi insegna ad essere agili nel risolvere tutti i tipi di problemi con SQL. Dovreste anche passare molto tempo a sperimentare con i dataset pubblici di Google Cloud Console. Se avete un'azienda e desiderate organizzare un programma di formazione SQL per il vostro personale, possiamo aiutarvi a farlo.

BigQuery è qui per restare, quindi iniziate subito a diventare esperti!