Panoramica

In questo lab analizzerai due diversi set di dati pubblici ed eseguirai query su questi set di dati, prima separatamente e poi combinandoli per ottenere insight interessanti.

Cosa imparerai a fare

In questo lab imparerai a:

• Eseguire query interattive nella console BigQuery.
• Combinare ed eseguire l'analisi su più set di dati.

Prerequisiti

Questo è un lab di livello base e presuppone una certa conoscenza di BigQuery e SQL.

Introduzione

In questo lab sono utilizzati due set di dati pubblici di BigQuery: dati meteorologici della National Oceanic and Atmospheric Administration (NOAA) degli Stati Uniti e dati sul noleggio di biciclette nella città di New York.

Scoprirai per la prima volta diversi aspetti della piattaforma Google Cloud che offrono vantaggi significativi ai data scientist:

1. Serverless. Non è necessario scaricare i dati sul computer per lavorarci: il set di dati rimarrà nel cloud.
2. Facilità di utilizzo. Esegui query SQL ad hoc sul set di dati senza dover preparare in anticipo i dati, ad esempio gli indici. È un aspetto di valore inestimabile per l'esplorazione dei dati.
3. Scala. Esegui l'esplorazione su set di dati molto grandi in modo interattivo. Non è necessario campionare i dati per poterli utilizzare in modo tempestivo.
4. Condivisibilità. Potrai eseguire senza problemi query su dati provenienti da diversi set. BigQuery offre un modo conveniente per condividere i set di dati. Naturalmente, puoi anche mantenere privati i dati o condividerli solo con persone specifiche: non è necessario che tutti i dati siano pubblici.

Il risultato finale è che scoprirai se il noleggio di biciclette è minore nei giorni di pioggia.

Configurazione e requisiti

Configurazione del lab

Per ciascun lab, riceverai un nuovo progetto Google Cloud e un insieme di risorse per un periodo di tempo limitato senza alcun costo aggiuntivo.

1. Fai clic sul pulsante Avvia lab. Se devi effettuare il pagamento per il lab, si apre una finestra popup per permetterti di selezionare il metodo di pagamento. A sinistra, trovi il riquadro Dettagli lab con le seguenti informazioni:

   • Il pulsante Apri console Google Cloud
   • Tempo rimanente
   • Credenziali temporanee da utilizzare per il lab
   • Altre informazioni per seguire questo lab, se necessario

2. Fai clic su Apri console Google Cloud (o fai clic con il tasto destro del mouse e seleziona Apri link in finestra di navigazione in incognito se utilizzi il browser Chrome).

   Il lab avvia le risorse e apre un'altra scheda con la pagina di accesso.

   Suggerimento: disponi le schede in finestre separate posizionate fianco a fianco.

   Nota: se visualizzi la finestra di dialogo Scegli un account, fai clic su Usa un altro account.

3. Se necessario, copia il Nome utente di seguito e incollalo nella finestra di dialogo di accesso.

   {{{user_0.username | "Username"}}}

   Puoi trovare il Nome utente anche nel riquadro Dettagli lab.

4. Fai clic su Avanti.

5. Copia la Password di seguito e incollala nella finestra di dialogo di benvenuto.

   {{{user_0.password | "Password"}}}

   Puoi trovare la Password anche nel riquadro Dettagli lab.

6. Fai clic su Avanti.

   Importante: devi utilizzare le credenziali fornite dal lab. Non utilizzare le credenziali del tuo account Google Cloud. Nota: utilizzare il tuo account Google Cloud per questo lab potrebbe comportare addebiti aggiuntivi.

7. Fai clic nelle pagine successive:

   • Accetta i termini e le condizioni.
   • Non inserire opzioni di recupero o l'autenticazione a due fattori, perché si tratta di un account temporaneo.
   • Non registrarti per le prove gratuite.

Dopo qualche istante, la console Google Cloud si apre in questa scheda.

Nota: per visualizzare un menu con un elenco di prodotti e servizi Google Cloud, fai clic sul menu di navigazione in alto a sinistra oppure digita il nome del servizio o del prodotto nel campo di ricerca.

Attività 1: esplora i dati sul noleggio di biciclette

In questa attività esplorerai il set di dati pubblico bigquery-public-data, in particolare il set di dati new_york_citibike, esaminando lo schema della tabella citibike_trips ed eseguendo query per analizzare la durata dei viaggi e calcolare la distanza totale percorsa da ogni bicicletta.

1. Nella console Google Cloud, nel menu di navigazione (), fai clic su BigQuery.

Si aprirà una finestra con il messaggio Ti diamo il benvenuto in BigQuery sulla console Cloud. Questa finestra fornisce un link alla guida rapida ed elenca gli aggiornamenti dell'interfaccia utente.

2. Fai clic su Fine.

3. Nel riquadro di sinistra, fai clic su + Aggiungi, poi su Aggiungi un progetto a Speciali per nome, digita bigquery-public-data nella finestra popup e, infine, fai clic su Aggiungi a Speciali.

4. Nel riquadro a sinistra della console BigQuery sono presenti due progetti: uno denominato con l'ID progetto Qwiklabs e l'altro denominatobigquery-public-data.

5. Nel riquadro a sinistra della console BigQuery seleziona la tabella bigquery-public-data > new_york_citibike > citibike_trips.

6. Nella finestra della tabella (citibike_trips), fai clic sulla scheda Schema.

7. Esamina i nomi delle colonne e i tipi di dati.

8. Fai clic sul pulsante blu + per creare una nuova query.

Inserisci la seguente query:

SELECT MIN(start_station_name) AS start_station_name, MIN(end_station_name) AS end_station_name, APPROX_QUANTILES(tripduration, 10)[OFFSET (5)] AS typical_duration, COUNT(tripduration) AS num_trips FROM `bigquery-public-data.new_york_citibike.citibike_trips` WHERE start_station_id != end_station_id GROUP BY start_station_id, end_station_id ORDER BY num_trips DESC LIMIT 10

9. Fai clic su Esegui. Controlla i risultati e cerca di capire che cosa fa questa query.

   Suggerimento: durata abituale per i 10 noleggi più comuni di una sola corsa)

10. Poi esegui la query in basso per trovare un altro aspetto interessante, ovvero la distanza totale percorsa da ogni bicicletta nel set di dati. Tieni presente che la query limita i risultati solo ai primi 5.

WITH trip_distance AS ( SELECT bikeid, ST_Distance(ST_GeogPoint(s.longitude, s.latitude), ST_GeogPoint(e.longitude, e.latitude)) AS distance FROM `bigquery-public-data.new_york_citibike.citibike_trips`, `bigquery-public-data.new_york_citibike.citibike_stations` as s, `bigquery-public-data.new_york_citibike.citibike_stations` as e WHERE start_station_name = s.name AND end_station_name = e.name) SELECT bikeid, SUM(distance)/1000 AS total_distance FROM trip_distance GROUP BY bikeid ORDER BY total_distance DESC LIMIT 5 Nota: in questa query abbiamo usato anche un'altra tabella del set di dati, denominata citibike_stations, per ottenere informazioni sulle stazioni di noleggio delle biciclette.

Attività 2: esplora il set di dati meteorologici

In questa attività esplorerai il set di dati ghcn_d, visualizzando l'anteprima della tabella ghcnd_2015 ed eseguendo una query per recuperare i dati sulle precipitazioni da una stazione meteorologica di New York.

1. Nel riquadro a sinistra della console BigQuery seleziona il nuovo progetto aggiunto bigquery-public-data e poi ghcn_d > ghcnd_2015.

2. Quindi fai clic sulla scheda Anteprima. I contenuti nella console dovrebbero essere simili ai seguenti:

Esamina le colonne e alcuni valori dei dati.

3. Fai clic sul pulsante blu + per creare una nuova query e inserisci quanto segue:

SELECT wx.date, wx.value/10.0 AS prcp FROM `bigquery-public-data.ghcn_d.ghcnd_2015` AS wx WHERE id = 'USW00094728' AND qflag IS NULL AND element = 'PRCP' ORDER BY wx.date

4. Fai clic su Esegui.

Questa query restituirà i dati relativi alle precipitazioni (in mm) per tutti i giorni del 2015, derivati da una stazione meteorologica di New York il cui ID è fornito nella query (la stazione corrisponde a NEW YORK CNTRL PK TWR ).

Attività 3: trova la relazione tra le precipitazioni e i noleggi di biciclette

In questa attività analizzerai la correlazione tra pioggia e noleggi di biciclette unendo il set di dati new_york_citibike.citibike_trips con il set di dati meteorologici ghcn_d.ghcnd_2015, determinando il numero medio di spostamenti in bicicletta nei giorni di pioggia rispetto a quelli senza pioggia.

Adesso uniamo i dati sui noleggi di biciclette con i dati meteorologici per scoprire se ci sono meno noleggi di biciclette nei giorni di pioggia.

1. Fai clic sul pulsante blu + per creare una nuova query e inserisci quanto segue:

WITH bicycle_rentals AS ( SELECT COUNT(starttime) as num_trips, EXTRACT(DATE from starttime) as trip_date FROM `bigquery-public-data.new_york_citibike.citibike_trips` GROUP BY trip_date ), rainy_days AS ( SELECT date, (MAX(prcp) > 5) AS rainy FROM ( SELECT wx.date AS date, IF (wx.element = 'PRCP', wx.value/10, NULL) AS prcp FROM `bigquery-public-data.ghcn_d.ghcnd_2015` AS wx WHERE wx.id = 'USW00094728' ) GROUP BY date ) SELECT ROUND(AVG(bk.num_trips)) AS num_trips, wx.rainy FROM bicycle_rentals AS bk JOIN rainy_days AS wx ON wx.date = bk.trip_date GROUP BY wx.rainy

2. Fai clic su Esegui.

Puoi vedere i risultati dell'unione del set di dati sul noleggio di biciclette con un set di dati meteorologici proveniente da una fonte completamente diversa:

Dall’esecuzione della query si può capire che, quando piove, l'utilizzo della bicicletta a New York diminuisce del 47%.

Riepilogo

In questo lab hai eseguito query ad hoc su due set di dati. Le hai eseguite senza impostare nessun cluster, creare indici e così via. Hai anche potuto unire i due set di dati e ottenere alcuni insight interessanti. Il tutto senza uscire dal browser.

Complimenti!

Hai imparato a eseguire query molto interessanti in BigQuery.

Termina il lab

Una volta completato il lab, fai clic su Termina lab. Google Cloud Skills Boost rimuove le risorse che hai utilizzato ed esegue la pulizia dell'account.

Avrai la possibilità di inserire una valutazione in merito alla tua esperienza. Seleziona il numero di stelle applicabile, inserisci un commento, quindi fai clic su Invia.

Il numero di stelle corrisponde alle seguenti valutazioni:

• 1 stella = molto insoddisfatto
• 2 stelle = insoddisfatto
• 3 stelle = esperienza neutra
• 4 stelle = soddisfatto
• 5 stelle = molto soddisfatto

Se non vuoi lasciare un feedback, chiudi la finestra di dialogo.

Per feedback, suggerimenti o correzioni, utilizza la scheda Assistenza.

Copyright 2020 Google LLC Tutti i diritti riservati. Google e il logo Google sono marchi di Google LLC. Tutti gli altri nomi di società e prodotti sono marchi delle rispettive società a cui sono associati.