GSP413

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.

El conjunto de datos que usarás es un conjunto de datos de comercio electrónico que tiene millones de registros de Google Analytics provenientes de Google Merchandise Store. Explorarás los campos y las filas disponibles para obtener estadísticas.

En este lab, se describe cómo crear nuevas tablas de informes con uniones (JOIN) y fusiones (UNION) de SQL.

Situación: Tu equipo de marketing te proporcionó a ti y a tu equipo de ciencia de datos todas las opiniones sobre productos de tu sitio web de comercio electrónico. Te asociaste con ellos para crear un almacén de datos en BigQuery que reúna datos de tres fuentes:

• Datos de comercio electrónico del sitio web
• Plazos de entrega y niveles del inventario de productos
• Análisis de opiniones sobre productos

Actividades

En este lab, aprenderás a realizar estas tareas:

• Explorar nuevos datos de comercio electrónico sobre análisis de opiniones
• Combinar conjuntos de datos y crear tablas nuevas
• Adjuntar datos históricos con fusiones y comodines de tablas

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 nuevo conjunto de datos para almacenar tus tablas

Para comenzar, crea un conjunto de datos nuevo con el nombre ecommerce en BigQuery para almacenar tus tablas.

1. En el panel de la izquierda, haz clic en el nombre de tu proyecto de BigQuery (qwiklabs-gcp-xxxx).

2. Haz clic en los tres puntos al lado del nombre del proyecto y, luego, selecciona Crear conjunto de datos.

Se abrirá el diálogo Crear un conjunto de datos.

3. Configura el ID de conjunto de datos en ecommerce y deja las demás opciones con sus valores predeterminados.

4. Haz clic en Crear conjunto de datos.

Haz clic en Revisar mi progreso para verificar el objetivo. Crear un nuevo conjunto de datos para almacenar las tablas

Tarea 2. Explora el conjunto de datos de opiniones de productos

Tu equipo de ciencia de datos analizó todas las opiniones sobre productos con la API y te proporcionó la puntuación y la magnitud promedio de las opiniones para cada uno de tus productos.

El proyecto que contiene el conjunto de datos de tu equipo de marketing es data-to-insights. Los conjuntos de datos públicos de BigQuery no se muestran de forma predeterminada en BigQuery. Las consultas de este lab usarán el conjunto de datos data-to-insights, aunque no puedas verlo.

1. Primero, crea una copia de la tabla que hizo el equipo de ciencia de datos para poder leerla de la siguiente manera:

create or replace TABLE ecommerce.products AS SELECT * FROM `data-to-insights.ecommerce.products` Nota: Esto es solo para tu revisión. Las consultas de este lab usarán el proyecto data-to-insights.

2. Haz clic en el conjunto de datos ecommerce para que se muestre la tabla products.

Examina los datos con las pestañas Vista previa y Esquema

1. Navega hasta ecommerce > Conjunto de datos products y haz clic en la pestaña Vista previa para ver los datos.

2. Haz clic en la pestaña Esquema.

Crea una consulta que muestre los 5 productos principales con la opinión más positiva

1. En el Editor de consultas, escribe tu consulta en SQL.

Solución posible:

SELECT SKU, name, sentimentScore, sentimentMagnitude FROM `data-to-insights.ecommerce.products` ORDER BY sentimentScore DESC LIMIT 5

2. Revisa tu consulta para mostrar los 5 productos principales con la opinión más negativa y filtrar los valores nulos (NULL).

Solución posible:

SELECT SKU, name, sentimentScore, sentimentMagnitude FROM `data-to-insights.ecommerce.products` WHERE sentimentScore IS NOT NULL ORDER BY sentimentScore LIMIT 5

¿Qué producto tiene la opinión más negativa?

Haz clic en Revisar mi progreso para verificar el objetivo. Explorar el conjunto de datos de opiniones de productos

Tarea 3. Une conjuntos de datos para buscar estadísticas

Situación: Es el primer día del mes y tu equipo de inventario te informó que el campo orderedQuantity del conjunto de datos del inventario de productos está desactualizado. Necesita tu ayuda porque debe consultar las ventas totales por producto para la fecha 1/8/2017 para compararlas con los niveles de inventario actuales y determinar qué productos deben reabastecer primero.

Calcula el volumen de ventas diarias por productSKU

1. En el conjunto de datos ecommerce, crea una nueva tabla que cumpla con los siguientes requisitos:

• Nómbrala sales_by_sku_20170801.
• Extrae los datos de data-to-insights.ecommerce.all_sessions_raw.
• Incluye solo resultados diferentes.
• Devuelve productSKU.
• Devuelve la cantidad total pedida (productQuantity). Sugerencia: Usa una condición SUM() with a IFNULL
• Filtra los datos para devolver solo las ventas de 20170801.
• Usa ORDER BY para ordenar los datos según los SKU con más pedidos primero.

Solución posible:

# pull what sold on 08/01/2017 CREATE OR REPLACE TABLE ecommerce.sales_by_sku_20170801 AS SELECT productSKU, SUM(IFNULL(productQuantity,0)) AS total_ordered FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20170801' GROUP BY productSKU ORDER BY total_ordered DESC #462 skus sold

2. Haz clic en la tabla sales_by_sku y, luego, haz clic en la pestaña Vista previa.

¿Cuántos SKU de productos distintos se vendieron?

Respuesta: 462

Ahora, enriquece los datos de tus ventas con información del inventario de productos. Para ello, une los dos conjuntos de datos.

Fusiona los datos de ventas y los de inventario

1. Usa una fusión (JOIN) para enriquecer los datos de comercio electrónico del sitio web con los siguientes campos del conjunto de datos de inventario de productos:

• name
• stockLevel
• restockingLeadTime
• sentimentScore
• sentimentMagnitude

2. Completa la consulta escrita parcialmente:

# join against product inventory to get name SELECT DISTINCT website.productSKU, website.total_ordered, inventory.name, inventory.stockLevel, inventory.restockingLeadTime, inventory.sentimentScore, inventory.sentimentMagnitude FROM ecommerce.sales_by_sku_20170801 AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ORDER BY total_ordered DESC

Solución posible:

# join against product inventory to get name SELECT DISTINCT website.productSKU, website.total_ordered, inventory.name, inventory.stockLevel, inventory.restockingLeadTime, inventory.sentimentScore, inventory.sentimentMagnitude FROM ecommerce.sales_by_sku_20170801 AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU ORDER BY total_ordered DESC

3. Modifica la consulta que escribiste de modo que ahora incluya lo siguiente:

• Un campo calculado a partir de (total_ordered / stockLevel) y que tenga como alias “ratio”. Sugerencia: Usa SAFE_DIVIDE(field1,field2) para evitar errores de división por 0 cuando el nivel de inventario sea 0.
• Filtra los resultados para incluir únicamente los productos cuyo inventario haya disminuido un 50% o más al comienzo del mes.

Solución posible:

# calculate ratio and filter SELECT DISTINCT website.productSKU, website.total_ordered, inventory.name, inventory.stockLevel, inventory.restockingLeadTime, inventory.sentimentScore, inventory.sentimentMagnitude, SAFE_DIVIDE(website.total_ordered, inventory.stockLevel) AS ratio FROM ecommerce.sales_by_sku_20170801 AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU # gone through more than 50% of inventory for the month WHERE SAFE_DIVIDE(website.total_ordered,inventory.stockLevel) >= .50 ORDER BY total_ordered DESC

Haz clic en Revisar mi progreso para verificar el objetivo. Unir conjuntos de datos para buscar estadísticas

Tarea 4. Adjunta registros adicionales

Tu equipo internacional ya realizó ventas en la tienda el 2/8/2017, y deseas registrarlas en tus tablas de ventas diarias.

Crea una nueva tabla vacía para almacenar ventas por productSKU para la fecha 2/8/2017

1. En el esquema, especifica los siguientes campos:

• el nombre de la tabla debe ser ecommerce.sales_by_sku_20170802
• productSKU STRING
• total_ordered como un campo INT64

Solución posible:

CREATE OR REPLACE TABLE ecommerce.sales_by_sku_20170802 ( productSKU STRING, total_ordered INT64 );

2. Confirma que ahora tienes dos tablas de ventas con fechas compartidas. Usa el menú desplegable junto al nombre de la tabla Sales_by_sku en los resultados o actualiza la pestaña del navegador para verla en el menú de la izquierda:

3. Inserta el registro de ventas que te proporcionó el equipo de ventas:

INSERT INTO ecommerce.sales_by_sku_20170802 (productSKU, total_ordered) VALUES('GGOEGHPA002910', 101)

4. Confirma que el registro aparece en la vista previa de la tabla. Haz clic en el nombre de la tabla para ver los resultados.

Adjunta datos históricos

Existen muchas maneras de adjuntar datos que tienen el mismo esquema. Por ejemplo, dos de estas consisten en usar fusiones (UNION) y usar comodines de tablas.

• Una fusión es un operador SQL que adjunta filas de conjuntos de resultados diferentes.
• Los comodines de tablas te permiten consultar varias tablas con instrucciones de SQL concisas. Las tablas comodín solo están disponibles en SQL estándar.

1. Escribe una consulta de fusión (UNION) que muestre todos los registros de las dos tablas a continuación:

• ecommerce.sales_by_sku_20170801
• ecommerce.sales_by_sku_20170802

SELECT * FROM ecommerce.sales_by_sku_20170801 UNION ALL SELECT * FROM ecommerce.sales_by_sku_20170802 Nota: La diferencia entre UNION y UNION ALL es que UNION no incluirá registros duplicados.

¿Cuál es la desventaja de tener muchas tablas de ventas diarias? Deberás escribir muchas sentencias UNION encadenadas.

Una mejor solución es usar el filtro de comodín de tablas y el filtro _TABLE_SUFFIX.

2. Escribe una consulta en la que se use el comodín de tablas (*) para seleccionar todos los registros de ecommerce.sales_by_sku_ del año 2017.

Solución posible:

SELECT * FROM `ecommerce.sales_by_sku_2017*`

3. Modifica la consulta anterior para agregar un filtro que limite los resultados a la fecha 2/8/2017.

Solución posible:

SELECT * FROM `ecommerce.sales_by_sku_2017*` WHERE _TABLE_SUFFIX = '0802' Nota: Otra opción que se debe considerar es crear una tabla particionada que pueda transferir datos de ventas diarias a la partición correcta automáticamente.

Haz clic en Revisar mi progreso para verificar el objetivo. Adjuntar registros adicionales

¡Felicitaciones!

Creaste tablas de informes y, luego, manipulaste vistas con uniones (UNION) y fusiones (JOIN) de SQL para explorar datos de comercio electrónico de muestra.

Próximos pasos y más información

• ¿Tienes una cuenta de Google Analytics y deseas consultar tus propios conjuntos de datos en BigQuery? Sigue esta guía de exportación.
• Si quieres obtener más información sobre la sintaxis de SQL para uniones (UNION), consulta la documentación sobre uniones de BigQuery.
• Prueba Google Búsqueda de Datasets como recurso.
• Consulta estos labs:
  • Cómo explorar datos de la NCAA con BigQuery
  • Cloud Composer: Cómo copiar tablas de BigQuery en diferentes ubicaciones

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: 31 de octubre de 2023

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.