GSP414

Descripción general

BigQuery es la base de datos analítica de bajo costo, no-ops y completamente administrada de Google. Con BigQuery, puedes consultar muchos terabytes de datos sin tener que administrar infraestructuras y sin necesitar un administrador de base de datos. BigQuery usa SQL y puede aprovechar el modelo de pago por uso. BigQuery te permite enfocarte en el análisis de datos para encontrar estadísticas valiosas.

En este lab, aprenderás a consultar y crear tablas particionadas en BigQuery para mejorar el rendimiento de las consultas y reducir el uso de recursos. Los datos que se usan en este lab son un conjunto de datos de comercio electrónico que tiene millones de registros de Google Analytics sobre Google Merchandise Store cargados en BigQuery.

Actividades

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

• Consultar tablas particionadas
• Crear tus propias tablas particionadas

Configuración y requisitos

Antes de hacer clic en el botón Comenzar lab

Lee estas instrucciones. Los labs son cronometrados y no se pueden pausar. El cronómetro, que comienza a funcionar cuando haces clic en Comenzar lab, indica por cuánto tiempo tendrás a tu disposición los recursos de Google Cloud.

Este lab práctico te permitirá realizar las actividades correspondientes en un entorno de nube real, no en uno de simulación o demostración. Para ello, se te proporcionan credenciales temporales nuevas que utilizarás para acceder a Google Cloud durante todo el lab.

Para completar este lab, necesitarás lo siguiente:

• Acceso a un navegador de Internet estándar (se recomienda el navegador Chrome)

Nota: Usa una ventana de navegador privada o de Incógnito para ejecutar este lab. Así evitarás cualquier conflicto entre tu cuenta personal y la cuenta de estudiante, lo que podría generar cargos adicionales en tu cuenta personal.

• Tiempo para completar el lab: Recuerda que, una vez que comienzas un lab, no puedes pausarlo.

Nota: Si ya tienes un proyecto o una cuenta personal de Google Cloud, no los uses en este lab para evitar cargos adicionales en tu cuenta.

Cómo iniciar tu lab y acceder a la consola de Google Cloud

1. Haga clic en el botón Comenzar lab. Si debe pagar por el lab, se abrirá una ventana emergente para que seleccione su 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 debe 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: Ordene 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 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.

Cómo abrir la consola de BigQuery

1. En la consola de Google Cloud, seleccione elmenú de navegación > BigQuery.

Se abrirá el cuadro de mensaje Te damos la bienvenida a BigQuery en la consola de Cloud. Este cuadro de mensaje contiene un vínculo a la guía de inicio rápido y las notas de la versión.

2. Haga clic en Listo.

Se abrirá la consola de BigQuery.

Tarea 1. Crea un conjunto de datos nuevo

1. Primero, crearás un conjunto de datos para almacenar tus tablas.

2. En el panel Explorador, junto al ID de tu proyecto, haz clic en Ver acciones y, luego, en Crear conjunto de datos.

3. Configura el ID del conjunto de datos como ecommerce.

Deja todas las otras opciones en los valores predeterminados (Ubicación de los datos y Vencimiento predeterminado de la tabla).

4. Haz clic en Crear conjunto de datos.

Haz clic en Revisar mi progreso para verificar el objetivo.

Crear un conjunto de datos llamado ecommerce

Tarea 2. Crea tablas con particiones por fechas

Una tabla particionada se divide en segmentos, denominados particiones, que facilitan la administración y la consulta de los datos. Dividir una tabla grande en particiones más pequeñas puede mejorar el rendimiento de las consultas y ayudarte a controlar tus costos, ya que se reduce el número de bytes que lee una consulta.

Ahora, crea una nueva tabla y vincula una columna de fecha o marca de tiempo como partición. Pero antes, exploremos los datos en la tabla no particionada.

Consulta los análisis de la página web para obtener una muestra de los visitantes de 2017

1. Haz clic en + Redactar consulta nueva y agrega lo siguiente:

#standardSQL SELECT DISTINCT fullVisitorId, date, city, pageTitle FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20170708' LIMIT 5

Antes de ejecutarla, observa la cantidad total de datos que procesará, tal como se indica junto al ícono del validador de consultas: “Esta consulta procesará 1.74 GB cuando se ejecute”.

2. Haz clic en Ejecutar.

La consulta devuelve 5 resultados.

Consulta los análisis de la página web para obtener una muestra de los visitantes de 2018

Ahora modifica la consulta para analizar los visitantes que se registraron en 2018.

1. Haz clic en REDACTAR CONSULTA NUEVA para borrar los datos del Editor de consultas y, luego, agrega esta consulta nueva. Observa que el parámetro WHERE date cambió a 20180708:

#standardSQL SELECT DISTINCT fullVisitorId, date, city, pageTitle FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20180708' LIMIT 5

El Validador de consultas te informará la cantidad de datos que procesará esta consulta.

2. Haz clic en Ejecutar.

Observa que la consulta aún procesa 1.74 GB a pesar de que muestra 0 resultados. ¿Por qué? El motor de consultas debe analizar todos los registros del conjunto de datos para determinar si cumplen con la condición de concordancia de fechas en la cláusula WHERE. Es decir, debe buscar en todos los registros para comparar la fecha con la condición de ‘20180708'.

Además, LIMIT 5 no reduce la cantidad total de datos procesados, lo cual es un error común.

Casos prácticos comunes para tablas particionadas por fecha

Analizar todo el conjunto de datos cada vez que deseas comparar filas en función de una condición WHERE es una gran pérdida de recursos. Esto es así especialmente si solo te interesan los registros de un período específico; por ejemplo:

• Todas las transacciones del último año
• Todas las interacciones de los visitantes en los últimos 7 días
• Todos los productos que se vendieron el último mes

En vez de analizar todo el conjunto de datos y filtrar por el campo de fecha como hicimos en las consultas anteriores, configura ahora una tabla particionada por fecha, lo que nos permitirá omitir completamente el análisis de registros en ciertas particiones que no son pertinentes para nuestra consulta.

Crea una tabla particionada nueva en función de la fecha

1. Haz clic en REDACTAR CONSULTA NUEVA, agrega la siguiente consulta y haz clic en Ejecutar:

#standardSQL CREATE OR REPLACE TABLE ecommerce.partition_by_day PARTITION BY date_formatted OPTIONS( description="a table partitioned by date" ) AS SELECT DISTINCT PARSE_DATE("%Y%m%d", date) AS date_formatted, fullvisitorId FROM `data-to-insights.ecommerce.all_sessions_raw`

En esta consulta, observa la nueva opción para particionar por campo, PARTITION BY. Las dos opciones disponibles para particionar son DATE y TIMESTAMP. La función PARSE_DATE se usa en el campo de fecha (almacenado como una cadena) para cambiarlo al tipo de DATE correcto de la partición.

2. Haz clic en el conjunto de datos ecommerce y, luego, selecciona la nueva tabla partition_by_day:

3. Haz clic en la pestaña Detalles.

Verifica si ves lo siguiente:

• Particionada por: día (Partitioned by: Day)
• Particionada en: fecha_con_formato (Partitioning on: date_formatted)

Nota: Las particiones dentro de tablas particionadas en tu cuenta del lab vencerán automáticamente cuando transcurran 60 días del valor que aparece en la columna de fecha. Tu cuenta personal de Google Cloud con facturación habilitada te permitirá tener tablas particionadas que no vencen. A los fines de este lab, se ejecutarán las consultas restantes en relación con tablas particionadas que ya se crearon.

Haz clic en Revisar mi progreso para verificar el objetivo.

Crear una tabla particionada nueva en función de la fecha

Tarea 3. Revisa los resultados de las consultas en una tabla particionada

1. Ejecuta la siguiente consulta y observa la cantidad total de bytes que se procesarán:

#standardSQL SELECT * FROM `data-to-insights.ecommerce.partition_by_day` WHERE date_formatted = '2016-08-01'

Esta vez se procesan 25 KB o 0.025 MB, que es una fracción de lo que consultaste.

2. Ahora ejecuta la siguiente consulta y observa el total de bytes que se procesarán:

#standardSQL SELECT * FROM `data-to-insights.ecommerce.partition_by_day` WHERE date_formatted = '2018-07-08'

Deberías ver lo siguiente: Esta consulta procesará 0 B cuando se ejecute.

Tarea 4. Crea una tabla particionada que venza automáticamente

Las tablas particionadas que vencen automáticamente se usan para cumplir con las leyes de privacidad de los datos y se pueden utilizar para evitar el almacenamiento innecesario (por el cual se te cobrará si se trata de un entorno de producción). Si deseas crear una ventana móvil de datos, agrega una fecha de vencimiento para que la partición desaparezca cuando hayas terminado de usarla.

Explora las tablas de datos del tiempo disponibles de la NOAA

1. En el menú de la izquierda, en Explorador, haz clic en + Agregar y selecciona Conjuntos de datos públicos.

2. Busca GSOD NOAA y, luego, selecciona el conjunto de datos.

3. Haz clic en Ver conjunto de datos.

4. Desplázate por las tablas del conjunto de datos noaa_gsod (que se fragmentaron manualmente, pero no están particionadas):

Tu objetivo es crear una tabla que te permita hacer lo siguiente:

• Consultar datos del clima desde 2018 en adelante
• Filtrar los datos y mostrar solo los días con precipitaciones (lluvia, nieve, etcétera)
• Almacenar cada partición de datos únicamente por 90 días desde la fecha de esa partición (ventana móvil)

1. Primero, copia y pega la siguiente consulta:

#standardSQL SELECT DATE(CAST(year AS INT64), CAST(mo AS INT64), CAST(da AS INT64)) AS date, (SELECT ANY_VALUE(name) FROM `bigquery-public-data.noaa_gsod.stations` AS stations WHERE stations.usaf = stn) AS station_name, -- Stations may have multiple names prcp FROM `bigquery-public-data.noaa_gsod.gsod*` AS weather WHERE prcp < 99.9 -- Filter unknown values AND prcp > 0 -- Filter stations/days with no precipitation AND _TABLE_SUFFIX >= '2018' ORDER BY date DESC -- Where has it rained/snowed recently LIMIT 10 Nota: El comodín de tablas * se usa en la cláusula FROM para limitar la cantidad de tablas a las que se hace referencia en el filtro TABLE_SUFFIX. Nota: Aunque se agregó LIMIT 10, esto no reduce la cantidad total de datos analizados (aproximadamente 1.83 GB), ya que todavía no hay particiones.

2. Haz clic en Ejecutar.

3. Confirma que la fecha tenga el formato correcto y que el campo de precipitaciones muestre valores distintos de cero.

Tarea 5. Tu turno: crea una tabla particionada

• Modifica la consulta anterior para crear una tabla con las siguientes especificaciones:

  • Nombre de la tabla: ecommerce.days_with_rain
  • Usa el campo de fecha (date) como tu cláusula PARTITION BY
  • En OPTIONS, especifica partition_expiration_days = 60
  • Agrega la descripción de la tabla: description="weather stations with precipitation, partitioned by day"

Tu consulta debería verse de la siguiente manera:

#standardSQL CREATE OR REPLACE TABLE ecommerce.days_with_rain PARTITION BY date OPTIONS ( partition_expiration_days=60, description="weather stations with precipitation, partitioned by day" ) AS SELECT DATE(CAST(year AS INT64), CAST(mo AS INT64), CAST(da AS INT64)) AS date, (SELECT ANY_VALUE(name) FROM `bigquery-public-data.noaa_gsod.stations` AS stations WHERE stations.usaf = stn) AS station_name, -- Stations may have multiple names prcp FROM `bigquery-public-data.noaa_gsod.gsod*` AS weather WHERE prcp < 99.9 -- Filter unknown values AND prcp > 0 -- Filter AND _TABLE_SUFFIX >= '2018'

Haz clic en Revisar mi progreso para verificar el objetivo.

Tu turno: crea una tabla particionada

Comprueba si funciona correctamente el vencimiento de la partición de datos

Para confirmar que solo almacenas datos de 60 días anteriores a la fecha actual, ejecuta la consulta DATE_DIFF. Así podrás conocer la antigüedad de tus particiones, cuyo vencimiento se configuró para después de 60 días.

A continuación, se muestra una consulta que hace un seguimiento de las lluvias para la estación meteorológica de la NOAA, en Wakayama, Japón, que registra muchas precipitaciones.

• Agrega esta consulta y ejecútala:

#standardSQL # avg monthly precipitation SELECT AVG(prcp) AS average, station_name, date, CURRENT_DATE() AS today, DATE_DIFF(CURRENT_DATE(), date, DAY) AS partition_age, EXTRACT(MONTH FROM date) AS month FROM ecommerce.days_with_rain WHERE station_name = 'WAKAYAMA' #Japan GROUP BY station_name, date, today, month, partition_age ORDER BY date DESC; # most recent days first

Tarea 6: Confirma que el valor más antiguo de partition_age sea 60 días o menos

Actualiza la cláusula ORDER BY para mostrar primero las particiones más antiguas.

• Agrega esta consulta y ejecútala:

#standardSQL # avg monthly precipitation SELECT AVG(prcp) AS average, station_name, date, CURRENT_DATE() AS today, DATE_DIFF(CURRENT_DATE(), date, DAY) AS partition_age, EXTRACT(MONTH FROM date) AS month FROM ecommerce.days_with_rain WHERE station_name = 'WAKAYAMA' #Japan GROUP BY station_name, date, today, month, partition_age ORDER BY partition_age DESC Nota: Tus resultados variarán si vuelves a ejecutar la consulta en el futuro, ya que los datos del clima y tus particiones se actualizan continuamente.

¡Felicitaciones!

Creaste y consultaste correctamente tablas particionadas en BigQuery.

Próximos pasos y más información

• Si quieres aprender a crear tablas particionadas por tiempo de transferencia que no estén vinculadas a una columna de fecha o marca de tiempo específica, consulta ejemplos y la documentación sobre particiones en BigQuery.
• ¿Tienes una cuenta de Google Analytics y deseas consultar tus propios conjuntos de datos en BigQuery? Sigue esta guía de exportación.
• Consulta estos labs de BigQuery:
  • Solución de problemas y resolución de errores en la unión de datos
  • Trabaja con JSON, arrays y structs en BigQuery
  • Predice las tarifas de taxis con un modelo de previsión del AA de BigQuery

Capacitación y certificación de Google Cloud

Recibe la formación que necesitas para aprovechar al máximo las tecnologías de Google Cloud. Nuestras clases incluyen habilidades técnicas y recomendaciones para ayudarte a avanzar rápidamente y a seguir aprendiendo. Para que puedas realizar nuestros cursos cuando más te convenga, ofrecemos distintos tipos de capacitación de nivel básico a avanzado: a pedido, presenciales y virtuales. Las certificaciones te ayudan a validar y demostrar tus habilidades y tu conocimiento técnico respecto a las tecnologías de Google Cloud.

Última actualización del manual: 3 de febrero de 2024

Prueba más reciente del lab: 1 de enero de 2024

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