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. Además, permite que te enfoques en el análisis de datos para buscar estadísticas valiosas.

Utilizarás un conjunto de datos de comercio electrónico que tiene millones de registros de Google Analytics para Google Merchandise Store cargados en BigQuery. Tienes una copia de ese conjunto de datos para este lab y explorarás los campos y las filas disponibles para obtener estadísticas.

En este lab, consultarás conjuntos de datos particionados y crearás tus propias particiones para mejorar el rendimiento de las consultas y reducir los costos.

Configuración

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.

Abra BigQuery en Console

1. En Google Cloud Console, seleccione el menú de navegación > BigQuery.

Se abrirá el cuadro de mensaje Te damos la bienvenida a BigQuery en Cloud Console, que contiene un vínculo a la guía de inicio rápido y enumera las actualizaciones de la IU.

2. Haga clic en Listo.

Tarea 1. Crea un conjunto de datos nuevo

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

1. Crea un nuevo conjunto de datos dentro de tu proyecto, para ello, haz clic en el ícono Ver acciones junto al ID de tu proyecto en la sección Explorador, luego, selecciona CREAR CONJUNTO DE DATOS.

2. Establece el ID de conjunto de datos en ecommerce. Deja todas las otras opciones en los valores predeterminados (Ubicación de los datos y Vencimiento predeterminado de la tabla).

3. Haz clic en CREAR CONJUNTO DE DATOS.

Tarea 2. Crea tablas con particiones por fecha

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 crearás una tabla nueva y vincularás una columna de fecha o marca de tiempo como una 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. En el editor de consultas, agrega la siguiente consulta. 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”.

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

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. En el Editor de consultas, agrega la siguiente consulta:

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

Los Resultados de la consulta te informarán 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 devuelve 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, ahora configuraremos 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 y agrega la siguiente consulta; luego, selecciona 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.

4. Confirma que ves la sección Información de la tabla:

• Particionada por: día
• Particionada en: fecha_con_formato

Nota: Las particiones dentro de tablas particionadas en tu cuenta de Qwiklabs vencerán automáticamente cuando transcurran 60 días del valor que figura 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.

Tarea 3. Visualiza datos procesados con 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.

¿Por qué se procesaron 0 bytes?

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 panel izquierdo, 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):

5. Luego, copia y pega la siguiente consulta en el editor de consultas:

#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 >= '2021' ORDER BY date DESC -- Where has it rained/snowed recently LIMIT 10

6. Observa el comodín * de la tabla que se usa en la cláusula FROM para limitar la cantidad de tablas a las que se hace referencia en el filtro TABLE_SUFFIX.

7. Observe que si bien se agregó LIMIT 10, eso no reduce la cantidad total de datos analizados (aproximadamente 141.6 MB), puesto que todavía no hay particiones.

8. Haz clic en Ejecutar.

9. 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 >= '2021'

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. La fecha que ves ahí.

• 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.

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.