GSP281

Panoramica

SQL (Structured Query Language) è un linguaggio standard per le operazioni sui dati che consente di porre domande e di ottenere informazioni approfondite da set di dati strutturati. È comunemente usato nella gestione di database e consente di eseguire attività come la scrittura di record di transazioni nei database relazionali e l'analisi di dati sulla scala dei petabyte.

Questo lab è diviso in due parti: nella prima imparerai le parole chiave fondamentali delle query SQL, che eseguirai in BigQuery su un set di dati pubblico che contiene informazioni sui bike sharing di Londra.

Nella seconda metà imparerai a esportare i sottoinsiemi del set di dati sui bike sharing di Londra in file CSV, che poi caricherai su Cloud SQL. Da lì vedremo come utilizzare Cloud SQL per creare e gestire database e tabelle. Verso la fine, farai pratica con parole chiave SQL aggiuntive che manipolano e modificano i dati.

Obiettivi didattici

In questo lab, imparerai a:

• Leggere dati in BigQuery.
• Eseguire query semplici in BigQuery per esplorare i dati.
• Esportare un sottoinsieme di dati in un file CSV e archiviare il file in un nuovo bucket Cloud Storage.
• Creare una nuova istanza Cloud SQL e caricare il file CSV esportato come nuova tabella.

Prerequisiti

Avviso importante: prima di iniziare questo lab, esci dal tuo account Gmail personale o aziendale.

Si tratta di un lab di livello base. È adatto a chi ha poca o nessuna esperienza con SQL. Suggeriamo, ma non è obbligatoria, esperienza con Cloud Storage e Cloud Shell. Questo lab ti insegnerà le basi della lettura e della scrittura di query in SQL, che applicherai utilizzando BigQuery e Cloud SQL.

Prima di seguire questo lab, valuta le tue competenze di SQL. Di seguito troverai lab più complessi che ti consentiranno di applicare le conoscenze acquisite a casi d'uso più avanzati:

• Dati meteorologici in BigQuery
• Analisi dei dati di natalità utilizzando Vertex AI e BigQuery

Quando è tutto pronto, scorri verso il basso e segui i passaggi indicati di seguito per configurare l'ambiente del lab.

Configurazione e requisiti

Prima di fare clic sul pulsante Avvia lab

Leggi le seguenti istruzioni. I lab sono a tempo e non possono essere messi in pausa. Il timer si avvia quando fai clic su Avvia lab e ti mostra per quanto tempo avrai a disposizione le risorse Google Cloud.

Con questo lab pratico avrai la possibilità di completare le attività in prima persona, in un ambiente cloud reale e non di simulazione o demo. Riceverai delle nuove credenziali temporanee che potrai utilizzare per accedere a Google Cloud per la durata del lab.

Per completare il lab, avrai bisogno di:

• Accesso a un browser internet standard (Chrome è il browser consigliato).

Nota: utilizza una finestra del browser in incognito o privata per eseguire questo lab. Ciò evita eventuali conflitti tra il tuo account personale e l'account Studente, che potrebbero causare addebiti aggiuntivi sul tuo account personale.

• È ora di completare il lab: ricorda che, una volta iniziato, non puoi metterlo in pausa.

Nota: se hai già un account o un progetto Google Cloud personale, non utilizzarlo per questo lab per evitare addebiti aggiuntivi al tuo account.

Come avviare il lab e accedere alla console Google Cloud

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.

Attività 1: nozioni di base su SQL

Database e tabelle

Come detto in precedenza, SQL consente di ottenere informazioni da "set di dati strutturati". I set di dati strutturati hanno regole e formattazioni chiare e spesso sono organizzati in tabelle o dati formattati in righe e colonne.

Un esempio di dati non strutturati potrebbe essere un file immagine. I dati non strutturati non sono utilizzabili con SQL e non possono essere archiviati in set di dati o tabelle BigQuery (almeno in modo nativo). Ad esempio, per lavorare con i dati di immagine dovrai usare un servizio come Cloud Vision, magari direttamente tramite la sua API.

Di seguito è riportato un esempio di un set di dati strutturato, una semplice tabella:

User	Price	Shipped
Sean	$35	Yes
Rocky	$50	No

Se hai esperienza con Fogli Google, l'esempio riportato sopra dovrebbe esserti familiare. La tabella ha colonne con intestazione User, Price e Shipped, mentre i valori delle colonne compilati vanno a costituire le due righe della tabella.

Un database è essenzialmente una raccolta di una o più tabelle. SQL è uno strumento per la gestione di database strutturati, ma molto spesso (come in questo lab) eseguirai query su una o più tabelle unite, non su interi database.

SELECT e FROM

SQL è fonetico per natura e prima di eseguire una query è sempre utile capire con quale domanda intendi interrogare i dati (a meno che tu non voglia semplicemente passare un po' il tempo).

SQL presenta parole chiave predefinite che servono per tradurre la domanda nella sintassi SQL pseudo-inglese, in modo che il motore del database restituisca la risposta che vuoi.

Le parole chiave più importanti sono SELECT e FROM:

• Usa SELECT per specificare i campi da cui vuoi estrarre il set di dati.
• Usa FROM per specificare la tabella o le tabelle da cui vuoi estrarre i dati.

Un esempio può favorire la comprensione. Supponi di avere la seguente tabella example_table, con le colonne USER, PRICE e SHIPPED:

E supponiamo di voler semplicemente estrarre i dati che si trovano nella colonna USER. Puoi farlo eseguendo la seguente query che usa SELECT e FROM:

SELECT USER FROM example_table

Se eseguissi il comando riportato sopra, selezioneresti tutti i nomi della colonna USER che si trovano in example_table.

Puoi anche selezionare più colonne con la parola chiave SQL SELECT. Supponiamo di voler estrarre i dati che si trovano nelle colonne USER e SHIPPED. A questo scopo, modifica la query precedente aggiungendo un altro valore della colonna alla query SELECT (ricorda di separare i valori con la virgola):

SELECT USER, SHIPPED FROM example_table

La query riportata sopra recupera i dati USER e SHIPPED dalla memoria:

Con queste semplici operazioni hai imparato a usare due parole chiave SQL fondamentali. Ora rendiamo le cose un po' più interessanti.

WHERE

La parola chiave WHERE è un altro comando SQL che filtra le tabelle in base a valori specifici delle colonne. Supponiamo di voler estrarre da example_table i nomi degli utenti i cui pacchi sono stati spediti. Puoi aggiungere WHERE alla query, come in questo esempio:

SELECT USER FROM example_table WHERE SHIPPED='YES'

La query sopra riportata restituisce tutti i valori USER degli utenti i cui pacchi sono stati spediti (SHIPPED):

Ora che hai una conoscenza di base delle parole chiave fondamentali di SQL, applica quanto appreso eseguendo questi tipi di query nella console BigQuery.

Verifica le tue conoscenze

Di seguito sono riportate alcune domande a scelta multipla per consolidare la tua conoscenza dei concetti esposti fin qui. Rispondi alle domande al meglio delle tue capacità.

Attività 2: esplora la console BigQuery

Il paradigma BigQuery

BigQuery è un data warehouse completamente gestito con scalabilità nell'ordine dei petabyte in esecuzione su Google Cloud. Gli analisti di dati e i data scientist possono interrogare e filtrare rapidamente grandi set di dati, aggregare i risultati ed eseguire operazioni complesse senza doversi preoccupare dell'impostazione e della gestione dei server. Si presenta sotto forma di uno strumento a riga di comando (preinstallato in Cloud Shell) o di una console web e in entrambi i casi è pronto per la gestione e l'interrogazione dei dati ospitati nei progetti Google Cloud.

In questo lab utilizzerai la console web per eseguire query SQL.

Apri la console BigQuery

1. Nella console Google Cloud, seleziona il menu di navigazione > BigQuery:

Si aprirà la finestra con il messaggio Ti diamo il benvenuto in BigQuery su Cloud Console. Questa finestra fornisce un link alla guida rapida e alle note di rilascio.

2. Fai clic su Fine.

Si aprirà la console di BigQuery.

Prenditi un momento per considerare alcune importanti funzionalità della UI. Sul lato destro della console si trova l'"Editor query". Qui puoi scrivere ed eseguire comandi SQL come gli esempi esposti prima. Sotto si trova la "Cronologia query", che elenca le query eseguite in precedenza.

Il riquadro sinistro della console è il menu di navigazione. Oltre alla cronologia delle query (che non necessita di spiegazioni), alle query salvate e alla cronologia dei job, c'è la scheda Explorer.

Il livello più alto di risorse nella scheda Explorer contiene progetti Google Cloud, che hanno lo stesso aspetto dei progetti temporanei di Google Cloud a cui accedi e che utilizzi con ciascun lab Google Cloud Skills Boost. Come puoi vedere nella console e nell'ultimo screenshot, nella scheda Explorer c'è solo il tuo progetto. Se provi a fare clic sulla freccia accanto al nome del progetto, non visualizzerai nulla.

Questo perché il progetto non contiene set di dati o tabelle: non c'è nulla su cui eseguire query. In precedenza hai appreso che i set di dati contengono tabelle. Quando aggiungi dati al progetto, nota che in BigQuery i progetti contengono set di dati che, a loro volta, contengono tabelle. Ora che conosci meglio il paradigma progetto > set di dati > tabella e le complessità della console, puoi caricare alcuni dati interrogabili.

Carica dati interrogabili

In questa sezione estrarrai alcuni dati pubblici nel progetto in modo da poter fare pratica con i comandi SQL in BigQuery.

1. Fai clic su + AGGIUNGI.

2. Scegli Aggiungi un progetto a Speciali per nome.

3. Per il nome del progetto, inserisci bigquery-public-data

4. Fai clic su AGGIUNGI A SPECIALI.

È importante notare che stai ancora lavorando al tuo progetto del lab in questa nuova scheda. Tutto quello che hai fatto è stato estrarre un progetto accessibile al pubblico che contiene set di dati e tabelle in BigQuery per l'analisi: non hai effettuato il passaggio a quel progetto. Tutti i tuoi job e servizi sono ancora legati al tuo account Google Cloud Skills Boost. Puoi verificarlo controllando il campo del progetto vicino alla parte superiore della console:

5. Ora hai accesso ai seguenti dati:

• Progetto Google Cloud → bigquery-public-data
• Set di dati → london_bicycles

6. Fai clic sul set di dati london bicycles per mostrare le tabelle associate

• Tabella → cycle_hire
• Tabella → cycle_stations

In questo lab, utilizzerai i dati di cycle_hire. Apri la tabella cycle_hire, quindi fai clic sulla scheda Anteprima. La pagina dovrebbe essere simile alla seguente:

Controlla le colonne e i valori inseriti nelle righe. Ora tutto è pronto per eseguire alcune query SQL sulla tabella cycle_hire.

Esegui SELECT, FROM e WHERE in BigQuery

Ora hai alcune conoscenze base delle parole chiave di SQL per l'interrogazione e del paradigma dei dati BigQuery, nonché alcuni dati con cui lavorare. Esegui alcuni comandi SQL utilizzando questo servizio.

Nell'angolo in basso a destra della console, puoi notare che ci sono 83.434.866 righe di dati o singoli viaggi in bike sharing effettuati a Londra tra il 2015 e il 2017 (una quantità notevole!).

Ora prendi nota della chiave della settima colonna: end_station_name, che specifica la destinazione finale delle corse in bike sharing. Prima di andare in profondità, esegui una semplice query per isolare la colonna end_station_name.

1. Copia e incolla il seguente comando nell'Editor query:

SELECT end_station_name FROM `bigquery-public-data.london_bicycles.cycle_hire`;

2. Fai clic su Esegui.

Dopo circa 20 secondi, dovrebbero essere restituite 83.434.866 righe che contengono la singola colonna richiesta: end_station_name.

Perché non scoprire quanti viaggi in bicicletta hanno avuto una durata di almeno 20 minuti?

3. Cancella la query dall'editor, quindi esegui la seguente query che utilizza la parola chiave WHERE:

SELECT * FROM `bigquery-public-data.london_bicycles.cycle_hire` WHERE duration>=1200;

L'esecuzione di questa query richiede circa un minuto.

SELECT * restituisce tutti i valori delle colonne della tabella. La durata viene misurata in secondi, per questo motivo viene utilizzato il valore 1200 (60 * 20).

Nell'angolo in basso a destra, puoi vedere che sono state restituite 26.441.016 righe. Come frazione del totale (26.441.016/83.434.866), questo significa che circa il 30% dei viaggi in bike sharing a Londra ha avuto una durata di almeno 20 minuti (non si tratta di tragitti brevi).

Verifica le tue conoscenze

Di seguito sono riportate alcune domande a scelta multipla che servono a consolidare le tue conoscenze relative ai concetti trattati finora. Rispondi alle domande al meglio delle tue capacità.

Attività 3: altre parole chiave SQL: GROUP BY, COUNT, AS e ORDER BY

GROUP BY

La parola chiave GROUP BY aggrega le righe del set di risultati che condividono criteri comuni (ad esempio il valore della colonna) e restituisce tutte le voci univoche trovate per questi criteri.

Si tratta di una parola chiave utile per capire le informazioni relative alle categorie nelle tabelle.

1. Per avere un quadro migliore della funzione di questa parola chiave, cancella la query dall'editor, quindi copia e incolla questo comando:

SELECT start_station_name FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name;

2. Fai clic su Esegui.

I risultati sono un elenco di valori di colonna univoci (non duplicati).

Senza GROUP BY, la query avrebbe restituito tutte le 83.434.866 righe. GROUP BY restituisce come output i valori univoci della colonna nella tabella. Puoi verificarlo guardando nell'angolo in basso a destra. Vedrai 954 righe, il che significa che ci sono 954 punti di partenza distinti per il bike sharing a Londra.

COUNT

La funzione COUNT() restituisce il numero di righe che condividono gli stessi criteri (ad esempio il valore della colonna). Questo può essere molto utile in coppia con GROUP BY.

Aggiungi la funzione COUNT alla query precedente per sapere quante corse hanno inizio da ciascun punto di partenza.

• Cancella la query dall'editor, copia e incolla il seguente comando, quindi fai clic su Esegui:

SELECT start_station_name, COUNT(*) FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name;

L'output mostra quante corse di bike sharing hanno inizio da ciascun punto di partenza.

AS

SQL presenta anche una parola chiave AS, che crea un alias di una tabella o colonna. Un alias è un nuovo nome assegnato alla colonna o alla tabella restituita, qualunque cosa AS specifichi.

1. Per vedere un esempio pratico, aggiungi una parola chiave AS all'ultima query eseguita. Cancella la query dall'editor, quindi copia e incolla il comando seguente:

SELECT start_station_name, COUNT(*) AS num_starts FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name;

2. Fai clic su Esegui.

Per Risultati, il nome della colonna di destra è cambiato da COUNT(*) in num_starts.

Come puoi vedere, ora la colonna COUNT(*) nella tabella restituita è impostata con il nome alias num_starts. Questa è una parola chiave utile da usare, soprattutto se si ha a che fare con grandi set di dati: dimenticarsi a cosa faccia riferimento un nome poco chiaro di una tabella o di una colonna accade più spesso di quanto si pensi.

ORDER BY

La parola chiave ORDER BY ordina i dati restituiti da una query in ordine crescente o decrescente in base a un determinato criterio o valore della colonna. Aggiungi questa parola chiave alla nostra query precedente per fare quanto segue:

• Restituire una tabella che contiene il numero di corse in bike sharing che iniziano da ciascun punto di partenza, organizzate in ordine alfabetico in base al punto di partenza.
• Restituire una tabella che contiene il numero di corse in bike sharing che iniziano da ciascun punto di partenza, organizzate numericamente in ordine crescente.
• Restituire una tabella che contiene il numero di corse in bike sharing che iniziano da ciascun punto di partenza, organizzate numericamente in ordine decrescente.

Ciascuno dei comandi seguenti è una query separata. Per ciascun comando:

1. Cancella i dati nell'Editor query.
2. Copia e incolla il comando nell'Editor query.
3. Fai clic su Esegui. Esamina i risultati.

SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY start_station_name; SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY num; SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY num DESC;

I risultati delle ultime posizioni di partenza degli elenchi di query in base al numero di partenze da quella posizione.

Puoi notare che "Belgrove Street, King's Cross" mostra il numero maggiore di partenze. Tuttavia, come frazione del totale (234.458/83.434.866), puoi osservare che < 1% delle corse parte da questo punto.

Verifica le tue conoscenze

Di seguito sono riportate alcune domande a scelta multipla per consolidare la tua conoscenza dei concetti esposti fin qui. Rispondi alle domande al meglio delle tue capacità.

Attività 4: utilizza Cloud SQL

Esporta le query come file CSV

Cloud SQL è un servizio di database completamente gestito che semplifica la configurazione, la manutenzione, la gestione e l'amministrazione dei database relazionali PostgreSQL e MySQL nel cloud. Sono due i formati di dati accettati da Cloud SQL: file dump (.sql) o file CSV (.csv). Imparerai a esportare i sottoinsiemi della tabella cycle_hire in file CSV e caricarli su Cloud Storage come posizione intermedia.

Quando torni nella console BigQuery, questo dovrebbe essere l'ultimo comando che esegui:

SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY num DESC;

1. Nella sezione Risultati delle query, fai clic su SALVA RISULTATI > CSV (file locale). Questa azione avvia un download che salva la query come file CSV. Prendi nota della posizione e del nome del file scaricato: ti serviranno a breve.

2. Cancella i dati dall'editor di query, quindi copia ed esegui il comando seguente nell'editor di query:

SELECT end_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY end_station_name ORDER BY num DESC;

Questa azione restituisce una tabella che contiene il numero di corse in bike sharing che terminano in ciascun punto di arrivo, organizzate numericamente in ordine decrescente.

3. Nella sezione Risultati delle query, fai clic su SALVA RISULTATI > CSV (file locale). Questa azione avvia un download che salva la query come file CSV. Prendi nota della posizione e del nome del file scaricato: ti serviranno nella sezione seguente.

Carica i file CSV su Cloud Storage

1. Vai sulla console Cloud, dove potrai creare un bucket di archiviazione in cui caricare i file appena creati.

2. Seleziona Menu di navigazione > Cloud Storage > Bucket, quindi fai clic su CREA BUCKET.

Nota: se richiesto, fai clic su ESCI per Lavoro non salvato.

3. Inserisci un nome univoco per il tuo bucket, mantieni tutte le altre impostazioni come predefinite e fai clic su Crea.

4. Se ti viene richiesto, fai clic su Conferma per la finestra di dialogo L'accesso pubblico verrà vietato.

Verifica l'attività completata

Fai clic su Controlla i miei progressi qui sotto per verificare lo stato di avanzamento del lab. Se hai creato correttamente il bucket, visualizzerai un punteggio di valutazione.

Crea un bucket Cloud Storage.

A questo punto dovresti essere nella console Cloud e vedere il bucket Cloud Storage appena creato.

1. Fai clic su CARICA FILE e seleziona il file CSV che contiene i dati start_station_name.

2. Poi, fai clic su Apri. Ripeti questo passaggio per i dati end_station_name.

3. Rinomina il file start_station_name facendo clic sui tre puntini all'estremità del file, quindi su Rinomina. Rinomina il file come start_station_data.csv.

4. Rinomina il file end_station_name facendo clic sui tre puntini all'estremità del file, quindi su Rinomina. Rinomina il file come end_station_data.csv.

Ora dovresti vedere start_station_data.csv e end_station_data.csv nell'elenco Oggetti nella pagina Dettagli bucket.

Verifica l'attività completata

Fai clic su Controlla i miei progressi per verificare l'attività eseguita. Se hai caricato correttamente gli oggetti CSV nel bucket, visualizzerai un punteggio di valutazione.

Carica i file CSV su Cloud Storage.

Attività 5: crea un'istanza Cloud SQL

Nella console, seleziona Menu di navigazione > SQL.

1. Fai clic su CREA ISTANZA > Scegli MySQL.

2. Per l'ID istanza inserisci my-demo.

3. Inserisci una password sicura nel campo Password (non dimenticarla!).

4. Per la versione del database seleziona MySQL 8.

5. Per Scegli una versione di Cloud SQL, seleziona Enterprise.

6. Per Preimpostazione, seleziona Sviluppo (4 vCPU, 16 GB di RAM, 100 GB di spazio di archiviazione, Zona singola).

Avviso: se scegli una preimpostazione superiore allo Sviluppo, il tuo progetto verrà segnalato e il tuo lab verrà sospeso.

7. Imposta il campo Più zone (disponibilità elevata) su

8. Fai clic su CREA ISTANZA.

Nota: la creazione dell'istanza potrebbe richiedere qualche minuto. Al termine, visualizzerai un segno di spunta verde accanto al nome dell'istanza sulla pagina Istanze Cloud SQL.

9. Fai clic sull'istanza Cloud SQL. Si apre la pagina Panoramica SQL.

Verifica l'attività completata

Per verificare i progressi del lab, fai clic su Controlla i miei progressi di seguito. Se hai configurato correttamente un'istanza di Cloud SQL, visualizzerai un punteggio di valutazione.

Crea un'istanza Cloud SQL.

Attività 6: nuove query in Cloud SQL

Parola chiave CREATE (database e tabelle)

Ora che hai un'istanza Cloud SQL in funzione, crea un database al suo interno utilizzando la riga di comando di Cloud Shell.

1. Apri Cloud Shell facendo clic sull'icona nell'angolo in alto a destra della console.

2. Esegui questo comando per impostare l'ID progetto come variabile di ambiente:

export PROJECT_ID=$(gcloud config get-value project) gcloud config set project $PROJECT_ID

Crea un database in Cloud Shell

1. Esegui il comando riportato di seguito in Cloud Shell per configurare l'autenticazione senza aprire un browser.

gcloud auth login --no-launch-browser

Questo ti darà un link da aprire nel tuo browser. Apri il link nello stesso browser in cui hai effettuato l'accesso all'account qwiklabs. Una volta effettuato il login riceverai un codice di verifica da copiare. Incolla questo codice in Cloud Shell.

2. Esegui il seguente comando per connetterti alla tua istanza SQL, sostituendo my-demo se hai utilizzato un nome diverso per la tua istanza:

gcloud sql connect my-demo --user=root --quiet Nota: la connessione all'istanza potrebbe richiedere un minuto. Se visualizzi il messaggio "Operazione non riuscita perché è già in corso un'altra operazione", attendi finché l'istanza SQL non viene creata, quindi prova a connetterti di nuovo.

3. Quando richiesto, inserisci la password root che hai impostato per l'istanza.

Dovresti vedere un output simile:

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 494 Server version: 8.0.31-google (Google) Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

Un'istanza Cloud SQL è dotata di database preconfigurati, ma ne creerai uno tuo per memorizzare i dati del bike sharing di Londra.

4. Esegui il comando seguente alla richiesta del server MySQL di creare un database chiamato bike:

CREATE DATABASE bike;

Dovrebbe essere restituito questo output:

Query OK, 1 row affected (0.05 sec) mysql>

Verifica l'attività completata

Verifica i progressi facendo clic su Controlla i miei progressi per verificare l'attività eseguita. Se hai creato correttamente un database nell'istanza Cloud SQL, vedrai un punteggio di valutazione.

Crea un database.

Crea una tabella in Cloud Shell

1. Crea una tabella all'interno del database bike eseguendo il seguente comando:

USE bike; CREATE TABLE london1 (start_station_name VARCHAR(255), num INT);

Questa istruzione contiene la parola chiave CREATE, ma questa volta usa la clausola TABLE per specificare l'intenzione di creare una tabella invece di un database. La parola chiave USE specifica un database a cui intendi connetterti. Ora hai una tabella chiamata "london1" che contiene due colonne, "start_station_name" e "num". VARCHAR(255) specifica una colonna di tipo stringa di lunghezza variabile che può contenere fino a 255 caratteri, mentre INT è una colonna di tipo integer.

2. Crea un'altra tabella chiamata "london2" eseguendo il seguente comando:

USE bike; CREATE TABLE london2 (end_station_name VARCHAR(255), num INT);

3. Ora conferma di aver creato le tabelle vuote. Esegui i seguenti comandi alla richiesta del server MySQL:

SELECT * FROM london1; SELECT * FROM london2;

L'output che ricevi dovrebbe essere simile al seguente per entrambi i comandi:

Empty set (0.04 sec)

Viene visualizzato "empty set" perché non hai ancora caricato i dati.

Carica i file CSV nelle tabelle

Torna alla console di Cloud SQL. Ora caricherai i file CSV start_station_name ed end_station_name nelle tabelle london1 e london2 appena create.

1. Nella pagina dell'istanza Cloud SQL, fai clic su IMPORTA.
2. Nel campo File Cloud Storage, fai clic su Sfoglia, poi fai clic sulla freccia accanto al nome del bucket, quindi su start_station_data.csv. Fai clic su Seleziona.
3. Seleziona CSV nel campo Formato file.
4. Seleziona il database bike e inserisci london1 per la tabella.
5. Fai clic su Importa.

Esegui le stesse azioni per l'altro file CSV.

1. Nella pagina dell'istanza Cloud SQL, fai clic su IMPORTA.
2. Nel campo File Cloud Storage, fai clic su Sfoglia, poi sulla freccia accanto al nome del bucket e quindi su end_station_data.csv. Fai clic su Seleziona.
3. Seleziona CSV nel campo Formato file.
4. Seleziona il database bike e inserisci london2 per la tabella.
5. Fai clic su Importa.

Ora dovresti aver caricato entrambi i file CSV nelle tabelle nel database bike.

1. Torna alla sessione Cloud Shell ed esegui il seguente comando alla richiesta del server MySQL di ispezionare i contenuti di london1:

SELECT * FROM london1;

Dovrebbero essere restituite 955 righe di output, una per ciascun nome univoco delle stazioni.

2. Esegui il seguente comando per assicurarti che la tabella london2 sia stata compilata:

SELECT * FROM london2;

Dovrebbero essere restituite 959 righe di output, una in più rispetto ai nomi univoci delle stazioni.

Parola chiave DELETE

Ecco di seguito un paio di altre parole chiave SQL che aiutano nella gestione dei dati. La prima è la parola chiave DELETE.

• Esegui i seguenti comandi nella sessione MySQL per eliminare la prima riga di london1 e london2:

DELETE FROM london1 WHERE num=0; DELETE FROM london2 WHERE num=0;

Dopo l'esecuzione di entrambi i comandi dovresti visualizzare il seguente output:

Query OK, 1 row affected (0.04 sec)

Le righe eliminate erano le intestazioni di colonna dei file CSV. La parola chiave DELETE non elimina la prima riga del file in sé, ma tutte le righe della tabella in cui il nome della colonna (in questo caso "num") contiene un valore specifico (in questo caso "0"). Se esegui le query SELECT * FROM london1; e SELECT * FROM london2; e scorri fino alla parte superiore della tabella, vedrai che quelle righe non esistono più.

Parola chiave INSERT INTO

Puoi inserire i valori nelle tabelle con la parola chiave INSERT INTO.

• Esegui il seguente comando per inserire una nuova riga in london1, che imposta start_station_name su "test destination" e num su "1":

INSERT INTO london1 (start_station_name, num) VALUES ("test destination", 1);

La parola chiave INSERT INTO richiede una tabella (london1) e crea una nuova riga con colonne specificate dai termini nella prima parentesi (in questo caso "start_station_name" e "num"). Qualunque elemento segua la clausola "VALUES" verrà inserito come valore nella nuova riga.

Dovrebbe essere restituito questo output:

Query OK, 1 row affected (0.05 sec)

Se esegui la query SELECT * FROM london1; vedrai una riga supplementare aggiunta in fondo alla tabella "london1":

Parola chiave UNION

L'ultima parola chiave SQL che imparerai è UNION. Questa parola chiave combina l'output di due o più query SELECT in un set di risultati. Usa UNION per combinare i sottoinsiemi delle tabelle "london1" e "london2".

La seguente query concatenata estrae dati specifici da entrambe le tabelle e li combina con l'operatore UNION.

• Esegui il seguente comando alla richiesta del server MySQL:

SELECT start_station_name AS top_stations, num FROM london1 WHERE num>100000 UNION SELECT end_station_name, num FROM london2 WHERE num>100000 ORDER BY top_stations DESC;

La prima query SELECT seleziona due colonne della tabella "london1" e crea un alias per "start_station_name", che viene impostato su "top_stations". Usa la parola chiave WHERE unicamente per estrarre i nomi delle stazioni di bike sharing da cui iniziano la corsa oltre 100.000 biciclette.

La seconda query SELECT seleziona le due colonne dalla tabella "london2" e usa la parola chiave WHERE unicamente per estrarre i nomi delle stazioni di bike sharing in cui terminano la corsa oltre 100.000 biciclette.

La parola chiave UNION tra le due query ne combina l'output unendo i dati di "london2" e "london1". Dal momento che "london1" viene unita a "london2", i valori della colonna che hanno la precedenza sono "top_stations" e "num".

ORDER BY ordina la tabella finale unita e ne organizza i valori della colonna "top_stations" in ordine alfabetico A-Z.

Dovrebbe essere restituito questo output:

Come vedi, 13/14 stazioni si dividono i primi posti per punti di partenza e di arrivo delle corse di bike sharing. Con alcune parole chiave SQL di base è stato possibile interrogare un set di dati di notevoli dimensioni, che ha restituito punti dati e risposte a domande specifiche.

Complimenti!

In questo lab hai appreso le nozioni fondamentali di SQL e hai imparato ad applicare parole chiave ed eseguire query in BigQuery e Cloud SQL. Ti sono stati spiegati i concetti fondamentali di progetti, database e tabelle. Hai fatto pratica con parole chiave che hanno manipolato e modificato i dati. Hai imparato a leggere i dati in BigQuery e ti sei esercitato a eseguire query sulle tabelle. Hai imparato a creare istanze in Cloud SQL e hai fatto pratica con il trasferimento di sottoinsiemi di dati nelle tabelle contenute nei database. Hai concatenato ed eseguito query in Cloud SQL per arrivare ad alcune interessanti conclusioni sulle stazioni di partenza e di arrivo dei bike sharing di Londra.

Prossimi passi/Scopri di più

Continua a imparare e ad esercitarti con Cloud SQL e BigQuery con questi lab Google Cloud Skills Boost:

• Dati meteorologici in BigQuery
• Esplorazione dei dati NCAA con BigQuery
• Caricamento di dati in Google Cloud SQL
• Cloud SQL con Terraform

Scopri di più sulla data science con Data Science on the Google Cloud Platform, 2nd Edition: O'Reilly Media, Inc..

Formazione e certificazione Google Cloud

… per utilizzare al meglio le tecnologie Google Cloud. I nostri corsi ti consentono di sviluppare competenze tecniche e best practice per aiutarti a metterti subito al passo e avanzare nel tuo percorso di apprendimento. Offriamo vari livelli di formazione, dal livello base a quello avanzato, con opzioni di corsi on demand, dal vivo e virtuali, in modo da poter scegliere il più adatto in base ai tuoi impegni. Le certificazioni ti permettono di confermare e dimostrare le tue abilità e competenze relative alle tecnologie Google Cloud.

Ultimo aggiornamento del manuale: 2 febbraio 2024

Ultimo test del lab: 2 febbraio 2024

Copyright 2024 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.