In questo lab imparerai a importare i dati da file di testo CSV in Cloud SQL e quindi eseguire 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 le registrazioni 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. Ogni record include 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 del percorso dedicato al data engineering.
Obiettivi
Creare un'istanza 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.
Accedi a Qwiklabs utilizzando una finestra di navigazione in incognito.
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.
Quando è tutto pronto, fai clic su Inizia lab.
Annota le tue credenziali del lab (Nome utente e Password). Le userai per accedere a Google Cloud Console.
Fai clic su Apri console Google.
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.
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.
Nella barra degli strumenti in alto a destra della console Cloud, fai clic sul pulsante Apri Cloud Shell.
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:
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.
Quando 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.
Consenti all'istanza di Cloud Shell di accedere all'istanza di SQL.
Esegui questo comando per ricevere l'indirizzo IP dell'istanza Cloud SQL:
Per creare la tabella delle corse in taxi, accedi all'interfaccia a riga di comando mysql:
mysql --host=$MYSQLIP --user=root \
--password --verbose
Quando viene richiesta la password, inserisci Passw0rd.
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.
Crea il database bts e la tabella trips.
Verifica l'importazione inserendo nell'interfaccia a riga di comando mysql i comandi seguenti:
describe trips;
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.
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).
Connettiti alla console interattiva mysql per caricare i dati infile locali:
mysql --host=$MYSQLIP --user=root --password --local-infile
Quando viene richiesta la password, inserisci Passw0rd.
Nella console interattiva mysql seleziona il database:
use bts;
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.
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.
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.
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?
Vediamo se riusciamo a trovare altri dati che non corrispondono alle nostre aspettative. Ci aspettiamo che la colonna fare_amount abbia valori positivi. Per vedere se è vero, inserisci nel database la seguente query:
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.
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.
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.
I lab creano un progetto e risorse Google Cloud per un periodo di tempo prestabilito
I lab hanno un limite di tempo e non possono essere messi in pausa. Se termini il lab, dovrai ricominciare dall'inizio.
In alto a sinistra dello schermo, fai clic su Inizia il lab per iniziare
Utilizza la navigazione privata
Copia il nome utente e la password forniti per il lab
Fai clic su Apri console in modalità privata
Accedi alla console
Accedi utilizzando le tue credenziali del lab. L'utilizzo di altre credenziali potrebbe causare errori oppure l'addebito di costi.
Accetta i termini e salta la pagina di ripristino delle risorse
Non fare clic su Termina lab a meno che tu non abbia terminato il lab o non voglia riavviarlo, perché il tuo lavoro verrà eliminato e il progetto verrà rimosso
Questi contenuti non sono al momento disponibili
Ti invieremo una notifica via email quando sarà disponibile
Bene.
Ti contatteremo via email non appena sarà disponibile
Un lab alla volta
Conferma per terminare tutti i lab esistenti e iniziare questo
Utilizza la navigazione privata per eseguire il lab
Utilizza una finestra del browser in incognito o privata per eseguire questo lab. In questo modo eviterai eventuali conflitti tra il tuo account personale e l'account Studente, che potrebbero causare addebiti aggiuntivi sul tuo account personale.
In questo lab importerai i dati da file di testo CSV in Cloud SQL e quindi eseguirai analisi di base dei dati utilizzando semplici query.
Durata:
Configurazione in 1 m
·
Accesso da 60 m
·
Completamento in 60 m