Descripción general

En este lab, aprenderás a importar datos desde archivos de texto CSV a Cloud SQL y, luego, llevarás a cabo algunos análisis de datos básicos mediante consultas simples.

El conjunto de datos que se utiliza en este lab lo recopila la Comisión de Taxis y Limusinas de Nueva York y abarca los registros de todos los viajes completados en taxis amarillos y verdes en la Ciudad de Nueva York desde 2009 hasta la actualidad, así como todos los viajes en vehículos de alquiler (FHV) desde 2015 hasta la actualidad. Los registros incluyen campos que capturan la fecha y hora en que se recogieron y dejaron pasajeros, ambas ubicaciones, las distancias de los viajes, las tarifas detalladas, los tipos de tarifas, los tipos de pago y el recuento de pasajeros informado por los choferes.

Este conjunto de datos puede usarse para demostrar una amplia variedad de conceptos y técnicas sobre la ciencia de datos, y se utilizará en varios labs del programa de Data Engineering.

Objetivos

• Crear una instancia de Cloud SQL
• Crear una base de datos de Cloud SQL
• Importar datos de texto a Cloud SQL
• Verificar la integridad de los datos

Configuración y requisitos

En cada lab, recibirá un proyecto de Google Cloud y un conjunto de recursos nuevos por tiempo limitado y sin costo adicional.

1. Accede a Qwiklabs desde una ventana de incógnito.

2. Ten en cuenta el tiempo de acceso del lab (por ejemplo, 1:15:00) y asegúrate de finalizarlo en el plazo asignado.
   No existe una función de pausa. Si lo necesita, puede reiniciar el lab, pero deberá hacerlo desde el comienzo.

3. Cuando esté listo, haga clic en Comenzar lab.

4. Anote las credenciales del lab (el nombre de usuario y la contraseña). Las usarás para acceder a la consola de Google Cloud.

5. Haga clic en Abrir Google Console.

6. Haga clic en Usar otra cuenta, copie las credenciales para este lab y péguelas en el mensaje emergente que aparece.
   Si usa otras credenciales, se generarán errores o incurrirá en cargos.

7. Acepta las condiciones y omite la página de recursos de recuperación.

Activa Google Cloud Shell

Google Cloud Shell es una máquina virtual que cuenta con herramientas para desarrolladores. Ofrece un directorio principal persistente de 5 GB y se ejecuta en Google Cloud.

Google Cloud Shell proporciona acceso de línea de comandos a tus recursos de Google Cloud.

1. En la consola de Cloud, en la barra de herramientas superior derecha, haz clic en el botón Abrir Cloud Shell.

   

2. Haz clic en Continuar.

El aprovisionamiento y la conexión al entorno demorarán unos minutos. Cuando te conectes, habrás completado la autenticación, y el proyecto estará configurado con tu PROJECT_ID. Por ejemplo:

gcloud es la herramienta de línea de comandos de Google Cloud. Viene preinstalada en Cloud Shell y es compatible con el completado de línea de comando.

• Puedes solicitar el nombre de la cuenta activa con este comando:

gcloud auth list

Resultado:

Credentialed accounts: - @.com (active)

Resultado de ejemplo:

Credentialed accounts: - google1623327_student@qwiklabs.net

• Puedes solicitar el ID del proyecto con este comando:

gcloud config list project

Resultado:

[core] project =

Resultado de ejemplo:

[core] project = qwiklabs-gcp-44776a13dea667a6 Nota: La documentación completa de gcloud está disponible en la guía de descripción general de gcloud CLI .

Tarea 1: Prepara tu entorno

• Crea las variables de entorno que se usarán más adelante en el lab para el ID de tu proyecto y el bucket de almacenamiento que contendrá tus datos:

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

Tarea 2: Crea una instancia de Cloud SQL

1. Ingresa los siguientes comandos para crear una instancia de Cloud SQL:

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

Este proceso tardará unos minutos en completarse.

Prueba la tarea completada

Haz clic en Revisar mi progreso para verificar la tarea realizada. Si la completaste correctamente, se te otorgará una puntuación de evaluación.

Crear una instancia de Cloud SQL

2. Establece una contraseña raíz para la instancia de Cloud SQL:

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

3. Cuando se te solicite la contraseña, escribe Passw0rd y presiona Intro para actualizar la contraseña raíz.

4. Ahora, crea una variable de entorno con la dirección IP de Cloud Shell:

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

5. Incluye la instancia de Cloud Shell en la lista de entidades permitidas para obtener acceso de administración a tu instancia de SQL.

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

6. Cuando se te solicite, presiona Y para aceptar el cambio.

Prueba la tarea completada

Haz clic en Revisar mi progreso para verificar la tarea realizada. Si la completaste correctamente, se te otorgará una puntuación de evaluación.

Incluir la instancia de Cloud Shell en la lista de entidades permitidas para acceder a tu instancia de SQL

7. Para obtener la dirección IP de tu instancia de Cloud SQL, ejecuta lo siguiente:

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

8. Verifica la variable MYSQLIP:

echo $MYSQLIP

Deberías obtener una dirección IP como resultado.

9. Para crear una tabla de viajes en taxi, accede a la interfaz de línea de comandos mysql:

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

10. Cuando se te solicite una contraseña, ingresa Passw0rd.

11. Pega lo siguiente en la línea de comandos para crear el esquema de la tabla 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) );

Prueba la tarea completada

Haz clic en Revisar mi progreso para verificar la tarea realizada. Si la completaste correctamente, se te otorgará una puntuación de evaluación.

Crear una base de datos bts y una tabla de viajes

12. En la interfaz de línea de comandos mysql, ingresa los siguientes comandos para verificar la importación:

describe trips;

13. Consulta la tabla trips:

select distinct(pickup_location_id) from trips;

Esto mostrará un conjunto vacío, ya que aún no hay nada en la base de datos.

14. Sal de la consola interactiva de mysql:

exit

Tarea 3. Agrega datos a una instancia de Cloud SQL

Ahora, copiarás los archivos CSV de los viajes en taxis de la Ciudad de Nueva York que están almacenados de manera local en Cloud Storage. Para mantener bajo el uso de recursos, solo trabajarás con un subconjunto de los datos (aproximadamente, 20,000 filas).

1. Ejecuta lo siguiente en la línea de comandos:

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. Conéctate a la consola interactiva mysql para cargar datos locales del archivo:

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

3. Cuando se te solicite una contraseña, ingresa Passw0rd.

4. En la consola interactiva mysql, selecciona la base de datos:

use bts;

5. Carga los datos del archivo CSV local con 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);

Tarea 4: Verifica la integridad de los datos

Cuando los datos se importan de una fuente, siempre es importante verificar su integridad. A grandes rasgos, esto significa asegurarte de que los datos cumplen con tus expectativas.

1. Consulta regiones de lugares de partida únicos en la tabla trips:

select distinct(pickup_location_id) from trips;

Se debería mostrar un resultado de 159 IDs únicos.

2. Comencemos por analizar en profundidad la columna trip_distance. Ingresa la siguiente consulta en la consola:

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

Se espera que la distancia del viaje sea mayor de 0 y menor de, digamos, 1,000 millas. El resultado de distancia máxima de 85 millas de un viaje parece razonable, pero la distancia mínima de 0 parece errónea.

3. ¿Cuántos viajes del conjunto de datos tienen una distancia igual a 0?

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

Hay 155 viajes de ese tipo en la base de datos. Estos viajes justifican una exploración más profunda. Descubrirás que estos viajes tienen importes de pago asociados que no equivalen a cero. ¿Puede que sean transacciones fraudulentas?

4. Veamos si podemos encontrar más datos que no cumplan con nuestras expectativas. Esperamos que la columna fare_amount sea positiva. Ingresa la siguiente consulta para ver si esto es cierto en la base de datos:

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

Debería haber un resultado de 14 viajes. Nuevamente, estos viajes justifican una exploración más profunda. Puede haber una explicación razonable sobre por qué las tarifas están en números negativos. Sin embargo, depende del ingeniero de datos asegurarse de que no haya errores en la canalización de datos que pudieran provocar ese resultado.

5. Por último, investiguemos la columna payment_type.

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

Los resultados de la consulta indican que hay cuatro tipos de pago diferentes, que se detallan a continuación:

• Tipo de pago = 1 tiene 13,863 filas
• Tipo de pago = 2 tiene 6,016 filas
• Tipo de pago = 3 tiene 113 filas
• Tipo de pago = 4 tiene 32 filas

Si analizamos la documentación en detalle, el tipo de pago igual a 1 se refiere al uso de tarjeta de crédito, el tipo de pago igual a 2 es efectivo y el tipo de pago igual a 4 se refiere a una disputa. Las cifras tienen sentido.

6. Sal de la consola interactiva de MySQL:

exit

Finalice su lab

Cuando haya completado el lab, haga clic en Finalizar lab. Google Cloud Skills Boost quitará los recursos que usó y limpiará la cuenta.

Tendrá la oportunidad de calificar su experiencia en el lab. Seleccione la cantidad de estrellas que corresponda, ingrese un comentario y haga clic en Enviar.

La cantidad de estrellas indica lo siguiente:

• 1 estrella = Muy insatisfecho
• 2 estrellas = Insatisfecho
• 3 estrellas = Neutral
• 4 estrellas = Satisfecho
• 5 estrellas = Muy satisfecho

Puede cerrar el cuadro de diálogo si no desea proporcionar comentarios.

Para enviar comentarios, sugerencias o correcciones, use la pestaña Asistencia.

Copyright 2020 Google LLC. All rights reserved. Google y el logotipo de Google son marcas de Google LLC. Los demás nombres de productos y empresas pueden ser marcas de las respectivas empresas a las que estén asociados.