Panoramica

In questo lab imparerai a importare i dati da file di testo CSV in Cloud SQL ed eseguire quindi analisi di base dei dati utilizzando semplici query.

Il set di dati utilizzato in questo lab è stato raccolto dalla NYC Taxi and Limousine Commission e include i registri di tutte le corse effettuate sui taxi gialli e verdi a New York dal 2009 a oggi e di tutte le corse su veicoli a noleggio dal 2015 a oggi. I record includono campi che contengono date/orari di partenza e arrivo, luoghi di partenza e arrivo, distanze delle corse, tariffe dettagliate, tipi di tariffa, tipi di pagamento e numero di passeggeri dichiarati dal conducente.

Questo set di dati consente di spiegare un'ampia gamma di concetti e tecniche di data science ed è utilizzato in vari lab della serie sul data engineering.

Obiettivi

• Creare un'istanza di Cloud SQL
• Creare un database Cloud SQL
• Importare dati di testo in Cloud SQL
• Verificare l'integrità dei dati

Configurazione e requisiti

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. Accedi a Qwiklabs utilizzando una finestra di navigazione in incognito.

2. Tieni presente la durata dell'accesso al lab (ad esempio, 1:15:00) e assicurati di finire entro quell'intervallo di tempo.
   Non è disponibile una funzionalità di pausa. Se necessario, puoi riavviare il lab ma dovrai ricominciare dall'inizio.

3. Quando è tutto pronto, fai clic su Inizia lab.

4. Annota le tue credenziali del lab (Nome utente e Password). Le userai per accedere a Google Cloud Console.

5. Fai clic su Apri console Google.

6. Fai clic su Utilizza un altro account e copia/incolla le credenziali per questo lab nei prompt.
   Se utilizzi altre credenziali, compariranno errori oppure ti verranno addebitati dei costi.

7. Accetta i termini e salta la pagina di ripristino delle risorse.

Attiva Google Cloud Shell

Google Cloud Shell è una macchina virtuale in cui sono caricati strumenti per sviluppatori. Offre una home directory permanente da 5 GB e viene eseguita su Google Cloud.

Google Cloud Shell fornisce l'accesso da riga di comando alle risorse Google Cloud.

1. Nella barra degli strumenti in alto a destra della console Cloud, fai clic sul pulsante Apri Cloud Shell.

   

2. Fai clic su Continua.

Bastano pochi istanti per eseguire il provisioning e connettersi all'ambiente. Quando la connessione è attiva, l'autenticazione è già avvenuta e il progetto è impostato sul tuo PROJECT_ID. Ad esempio:

gcloud è lo strumento a riga di comando di Google Cloud. È preinstallato su Cloud Shell e supporta il completamento.

• Puoi visualizzare il nome dell'account attivo con questo comando:

gcloud auth list

Output:

Credentialed accounts: - @.com (active)

Output di esempio:

Credentialed accounts: - google1623327_student@qwiklabs.net

• Puoi elencare l'ID progetto con questo comando:

gcloud config list project

Output:

[core] project =

Output di esempio:

[core] project = qwiklabs-gcp-44776a13dea667a6 Nota: la documentazione completa di gcloud è disponibile nella guida Panoramica dell'interfaccia a riga di comando gcloud .

Attività 1: preparazione dell'ambiente

• Crea le variabili di ambiente che saranno utilizzate più avanti nel lab per il tuo ID progetto e il bucket di archiviazione che conterrà i dati:

export PROJECT_ID=$(gcloud info --format='value(config.project)') export BUCKET=${PROJECT_ID}-ml

Attività 2: crea un'istanza di Cloud SQL

1. Inserisci i comandi seguenti per creare un'istanza di Cloud SQL:

gcloud sql instances create taxi \ --tier=db-n1-standard-1 --activation-policy=ALWAYS

Il completamento dell'operazione richiede alcuni minuti.

Verifica l'attività completata

Fai clic su Controlla i miei progressi per verificare l'attività eseguita. Se hai completato correttamente l'attività, riceverai un punteggio di valutazione.

Creare un'istanza di Cloud SQL.

2. Crea una password root per l'istanza di Cloud SQL:

gcloud sql users set-password root --host % --instance taxi \ --password Passw0rd

3. Quando viene richiesta la password, digita Passw0rd e premi Invio per aggiornare la password root.

4. Ora crea una variabile di ambiente con l'Indirizzo IP di Cloud Shell:

export ADDRESS=$(wget -qO - http://ipecho.net/plain)/32

5. Autorizza l'istanza di Cloud Shell ad accedere all'istanza di SQL per gestirla:

gcloud sql instances patch taxi --authorized-networks $ADDRESS

6. Quando viene richiesto, premi Y per accettare la modifica.

Verifica l'attività completata

Fai clic su Controlla i miei progressi per verificare l'attività eseguita. Se hai completato correttamente l'attività, riceverai un punteggio di valutazione.

Autorizzare l'istanza di Cloud Shell ad accedere all'istanza di SQL.

7. Esegui questo comando per ricevere l'indirizzo IP dell'istanza di Cloud SQL:

MYSQLIP=$(gcloud sql instances describe \ taxi --format="value(ipAddresses.ipAddress)")

8. Verifica la variabile MYSQLIP:

echo $MYSQLIP

L'output visualizzato dovrebbe essere un indirizzo IP.

9. Per creare la tabella delle corse in taxi, accedi all'interfaccia a riga di comando mysql:

mysql --host=$MYSQLIP --user=root \ --password --verbose

10. Quando viene richiesta la password, inserisci Passw0rd.

11. Incolla i contenuti seguenti nella riga di comando per creare lo schema della tabella trips:

create database if not exists bts; use bts; drop table if exists trips; create table trips ( vendor_id VARCHAR(16), pickup_datetime DATETIME, dropoff_datetime DATETIME, passenger_count INT, trip_distance FLOAT, rate_code VARCHAR(16), store_and_fwd_flag VARCHAR(16), payment_type VARCHAR(16), fare_amount FLOAT, extra FLOAT, mta_tax FLOAT, tip_amount FLOAT, tolls_amount FLOAT, imp_surcharge FLOAT, total_amount FLOAT, pickup_location_id VARCHAR(16), dropoff_location_id VARCHAR(16) );

Verifica l'attività completata

Fai clic su Controlla i miei progressi per verificare l'attività eseguita. Se hai completato correttamente l'attività, riceverai un punteggio di valutazione.

Creare il database bts e la tabella trips.

12. Inserisci i comandi seguenti nell'interfaccia a riga di comando mysql per verificare l'importazione:

describe trips;

13. Esegui la query sulla tabella trips:

select distinct(pickup_location_id) from trips;

La query restituisce un set vuoto in quanto non sono ancora stati inseriti dati nel database.

14. Esci dalla console interattiva mysql:

exit

Attività 3: aggiungi dati all'istanza Cloud SQL

Adesso copia in locale i file CSV delle corse in taxi a New York memorizzati in Cloud Storage. Per contenere l'utilizzo delle risorse, lavorerai solo con un subset di dati (~ 20.000 righe).

1. Nella riga di comando, inserisci quanto segue:

gcloud storage cp gs://cloud-training/OCBL013/nyc_tlc_yellow_trips_2018_subset_1.csv trips.csv-1 gcloud storage cp gs://cloud-training/OCBL013/nyc_tlc_yellow_trips_2018_subset_2.csv trips.csv-2

2. Connettiti alla console interattiva mysql per caricare i dati infile locali:

mysql --host=$MYSQLIP --user=root --password --local-infile

3. Quando viene richiesta la password, inserisci Passw0rd.

4. Nella console interattiva mysql seleziona il database:

use bts;

5. Carica i dati del file CSV locale utilizzando local-infile:

LOAD DATA LOCAL INFILE 'trips.csv-1' INTO TABLE trips FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (vendor_id,pickup_datetime,dropoff_datetime,passenger_count,trip_distance,rate_code,store_and_fwd_flag,payment_type,fare_amount,extra,mta_tax,tip_amount,tolls_amount,imp_surcharge,total_amount,pickup_location_id,dropoff_location_id); LOAD DATA LOCAL INFILE 'trips.csv-2' INTO TABLE trips FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (vendor_id,pickup_datetime,dropoff_datetime,passenger_count,trip_distance,rate_code,store_and_fwd_flag,payment_type,fare_amount,extra,mta_tax,tip_amount,tolls_amount,imp_surcharge,total_amount,pickup_location_id,dropoff_location_id);

Attività 4: verifica l'integrità dei dati

Ogni volta che i dati vengono importati da un'origine, è sempre importante verificarne l'integrità. In parole povere, devi assicurarti che i dati corrispondano alle tue aspettative.

1. Esegui la query sulla tabella trips per trovare aree con posizioni specifiche di partenza delle corse:

select distinct(pickup_location_id) from trips;

Dovrebbero venire restituiti 159 ID univoci.

2. Analizziamo per prima la colonna trip_distance. Inserisci la seguente query nella console:

select max(trip_distance), min(trip_distance) from trips;

Ci si potrebbe aspettare che la distanza del viaggio sia maggiore di 0 e minore, diciamo, di 1000 miglia. La distanza massima percorsa restituita di 85 miglia sembra ragionevole, ma la distanza minima percorsa pari a 0 sembra errata.

3. Quante corse nel set di dati hanno una distanza percorsa pari a 0?

select count(*) from trips where trip_distance = 0;

Nel database sono presenti 155 corse di questo tipo e meritano un'analisi più approfondita. Scoprirai che a queste corse sono associati pagamenti con un importo diverso da zero. Forse si tratta di transazioni fraudolente?

4. Vediamo se riusciamo a trovare altri dati che non corrispondono alle nostre aspettative. Ci aspettiamo che la colonna fare_amount abbia valori positivi. Inserisci la seguente query per vedere se è vero nel database:

select count(*) from trips where fare_amount < 0;

Dovrebbero essere restituite 14 corse di questo tipo che, anche in questo caso, devono essere analizzate più a fondo. Potrebbe esserci una spiegazione plausibile per cui le tariffe hanno valori negativi. Tuttavia, spetta al data engineer verificare che questo risultato non sia causato da eventuali bug presenti nella pipeline dei dati.

5. Infine, analizziamo la colonna payment_type.

select payment_type, count(*) from trips group by payment_type;

I risultati della query indicano che esistono quattro diversi tipi di pagamento:

• Il tipo di pagamento 1 ha 13.863 righe
• Il tipo di pagamento 2 ha 6016 righe
• Il tipo di pagamento 3 ha 113 righe
• Il tipo di pagamento 4 ha 32 righe

La documentazione spiega che il tipo di pagamento 1 è quello con carta di credito, il tipo di pagamento 2 è quello in contanti e il tipo di pagamento 4 si riferisce a una contestazione. I dati sono plausibili.

6. Esci dalla console interattiva 'mysql':

exit

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.