Descripción general

En este lab, analizarás 2 conjuntos de datos públicos diferentes y ejecutarás consultas en ellos (por separado y, luego, en combinación) para obtener estadísticas interesantes.

Qué aprenderás

En este lab, aprenderás a realizar las siguientes actividades:

• Realizar consultas interactivas en la consola de BigQuery
• Combinar y ejecutar análisis en diversos conjuntos de datos

Requisitos previos

Este lab es de nivel básico y presupone que tienes cierta experiencia con BigQuery y SQL.

Introducción

En este lab, se usan dos conjuntos de datos públicos en BigQuery: los datos meteorológicos de la Administración Nacional Oceánica y Atmosférica (NOAA) de EE.UU. y los datos de alquiler de bicicletas de la ciudad de Nueva York.

Verás, por primera vez, varios aspectos de Google Cloud que benefician en gran medida a los científicos:

1. Tecnología sin servidores. No es necesario descargar los datos en tu máquina para trabajar con ellos; el conjunto de datos permanecerá en la nube.
2. Facilidad de uso. Permite ejecutar consultas en SQL ad hoc sobre tu conjunto de datos sin necesidad de prepararlos (p. ej., indexarlos) con anterioridad. Este es un aspecto invaluable para la exploración de datos.
3. Escala. Permite realizar una exploración interactiva de conjuntos de datos muy grandes. No hace falta tomar muestras para trabajar de manera oportuna.
4. Facilidad para compartir. Podrás ejecutar consultas en datos de distintos conjuntos sin problemas. BigQuery es una forma conveniente de compartir conjuntos de datos. Por supuesto que también puede mantener datos privados o compartirlos únicamente con ciertas personas; no todos los datos deben ser públicos.

Como resultado, podrás determinar si el alquiler de bicicletas disminuye en días de lluvia.

Configuración y requisitos

Configuración del lab

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

1. Haz clic en el botón Comenzar lab. Si debes pagar por el lab, se abrirá una ventana emergente para que selecciones tu forma de pago. A la izquierda, se encuentra el panel Detalles del lab, que tiene estos elementos:

   • El botón Abrir la consola de Google Cloud
   • El tiempo restante
   • Las credenciales temporales que debes usar para el lab
   • Otra información para completar el lab, si es necesaria

2. Haz clic en Abrir la consola de Google Cloud (o haz clic con el botón derecho y selecciona Abrir el vínculo en una ventana de incógnito si ejecutas el navegador Chrome).

   El lab inicia recursos y abre otra pestaña en la que se muestra la página de acceso.

   Sugerencia: Ordena las pestañas en ventanas separadas, una junto a la otra.

   Nota: Si ves el diálogo Elegir una cuenta, haz clic en Usar otra cuenta.

3. De ser necesario, copia el nombre de usuario a continuación y pégalo en el diálogo Acceder.

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

   También puedes encontrar el nombre de usuario en el panel Detalles del lab.

4. Haz clic en Siguiente.

5. Copia la contraseña que aparece a continuación y pégala en el diálogo Te damos la bienvenida.

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

   También puedes encontrar la contraseña en el panel Detalles del lab.

6. Haz clic en Siguiente.

   Importante: Debes usar las credenciales que te proporciona el lab. No uses las credenciales de tu cuenta de Google Cloud. Nota: Usar tu propia cuenta de Google Cloud para este lab podría generar cargos adicionales.

7. Haga clic para avanzar por las páginas siguientes:

   • Acepta los Términos y Condiciones.
   • No agregues opciones de recuperación o autenticación de dos factores (esta es una cuenta temporal).
   • No te registres para obtener pruebas gratuitas.

Después de un momento, se abrirá la consola de Google Cloud en esta pestaña.

Nota: Para ver un menú con una lista de productos y servicios de Google Cloud, haz clic en el menú de navegación que se encuentra en la parte superior izquierda o escribe el nombre del servicio o producto en el campo Búsqueda.

Tarea 1: Explora los datos de alquiler de bicicletas

En esta tarea, explorarás el conjunto de datos públicos bigquery-public-data, específicamente el conjunto de datos new_york_citibike, examinando el esquema de la tabla citibike_trips y ejecutando consultas para analizar la duración de los viajes y calcular la distancia total recorrida por cada bicicleta.

1. En el menú de navegación () de la consola de Google Cloud, haz clic en BigQuery.

Se abrirá el cuadro de mensaje “Te damos la bienvenida a BigQuery en la consola de Cloud”. Contiene un vínculo a la guía de inicio rápido y enumera las actualizaciones de la IU.

2. Haz clic en Listo.

3. En el panel izquierdo, haz clic en + Agregar y, luego, haz clic en Destacar un proyecto por nombre. A continuación, en la ventana emergente, escribe bigquery-public-data y, por último, haz clic en Destacar.

4. En la consola de BigQuery, verás dos proyectos en el panel izquierdo: uno con el ID de tu proyecto de Qwiklabs y otro con el nombre bigquery-public-data.

5. En el panel izquierdo de la consola de BigQuery, selecciona bigquery-public-data > new_york_citibike > tabla citibike_trips.

6. En la ventana Tabla (citibike_trips), haz clic en la pestaña Esquema.

7. Examina los nombres de las columnas y los tipos de datos.

8. Haz clic en el botón + de color azul para crear una consulta nueva.

Ingresa la siguiente consulta:

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. Haz clic en Ejecutar. Examina los resultados y trata de determinar qué hace esta consulta.

   Pista: La duración típica de los 10 alquileres más comunes que son de ida solo.

10. Luego, ejecuta la siguiente consulta para descubrir otro dato interesante: la distancia total recorrida por cada bicicleta en el conjunto de datos. Ten en cuenta que la consulta limita los resultados a solo los 5 principales.

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: En esta consulta, también usamos la otra tabla del conjunto de datos, llamada citibike_stations, para obtener información sobre las estaciones de bicicletas.

Tarea 2: Explora el conjunto de datos meteorológicos

En esta tarea, explorarás el conjunto de datos ghcn_d. Para ello, obtendrás una vista previa de la tabla ghcnd_2015 y ejecutarás una consulta para recuperar datos de precipitaciones de una estación meteorológica de Nueva York.

1. En el panel izquierdo de la consola de BigQuery, selecciona el proyecto bigquery-public-data que acabas de agregar y, luego, ghcn_d > ghcnd_2015.

2. Luego, haz clic en la pestaña Vista previa. La consola debería verse de la siguiente manera:

Examina las columnas y algunos valores de datos.

3. Haz clic en el botón + de color azul para crear una consulta nueva y escribe lo siguiente:

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. Haz clic en Ejecutar.

Esta consulta devolverá la cantidad de precipitaciones (en mm) para todo 2015 según los datos de una estación meteorológica de Nueva York cuyo ID se proporcionó en la consulta (la estación corresponde a NEW YORK CNTRL PK TWR).

Tarea 3: Encuentra una correlación entre las precipitaciones y los alquileres de bicicletas

En esta tarea, analizarás la correlación entre las precipitaciones y los alquileres de bicicletas uniendo el conjunto de datos new_york_citibike.citibike_trips con el conjunto de datos meteorológicos ghcn_d.ghcnd_2015 para determinar la cantidad promedio de viajes en bicicleta en días lluviosos y no lluviosos.

Te recomendamos unir los datos meteorológicos y de alquileres de bicicletas para ver si hay menos alquileres en días de lluvia.

1. Haz clic en el botón + de color azul para crear una consulta nueva y escribe lo siguiente:

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. Haz clic en Ejecutar.

Ahora puedes ver los resultados de unir el conjunto de datos de alquiler de bicicletas con el conjunto de datos meteorológicos, el cual proviene de una fuente completamente distinta:

La ejecución de esta consulta arroja el resultado previsto: cuando llueve, los viajes en bicicleta en la ciudad de Nueva York disminuyen en un 47%.

Resumen

En este lab, realizaste consultas ad hoc en dos conjuntos de datos. No solo pudiste consultar los datos sin configurar clústeres, crear índices, etc., sino que también pudiste combinar los dos conjuntos de datos y obtener estadísticas interesantes sin siquiera salir de tu navegador.

¡Felicitaciones!

Aprendiste a ejecutar algunas consultas muy interesantes en BigQuery.

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.