IMPORTANTE:

Asegúrate de completar este lab práctico únicamente en una computadora de escritorio o laptop.

Se permiten solo 5 intentos por lab.

A modo de recordatorio, es habitual no responder de forma correcta a todas las preguntas en el primer intento o incluso tener que volver a realizar una tarea; esto forma parte del proceso de aprendizaje.

Una vez comenzado el lab, no se puede detener el cronómetro. Después de 1 hora y 30 minutos, el lab terminará y tendrás que volver a empezar.

Para obtener más información, consulta la lectura Sugerencias técnicas para el lab.

Descripción general de la actividad

Una canalización de datos es una serie de procesos que transportan datos desde diferentes fuentes hacia un destino para su almacenamiento y análisis.

Las canalizaciones de datos aumentan el rendimiento y la eficiencia de la transformación de datos a través de la automatización del flujo de datos y la optimización de su manejo.

Una canalización de SQL puede ayudarte a mejorar el rendimiento eliminando pasos innecesarios. Esto es porque puede leer datos directamente desde el destino y escribirlos en él, sin tener que crear conjuntos de datos intermedios, lo que puede ahorrar tiempo y recursos, al igual que mejorar la exactitud de tus resultados.

Además, una canalización de SQL puede ayudarte a optimizar tu capacidad de almacenamiento de datos. Esto se debe a que puedes usar la canalización para borrar o archivar datos que ya no son necesarios. Esto puede liberar espacio en tus sistemas de almacenamiento y, también, puede ayudar a acelerar el rendimiento.

La creación de canalizaciones de SQL eficientes y flexibles puede ser un desafío, especialmente cuando las necesidades de las organizaciones cambian con rapidez. Sin embargo, con la planificación y el diseño detallados, puedes crear canalizaciones que pueden adaptarse incluso a las necesidades de datos más complejas.

En este lab, administrarás datos y usarás SQL para crear una canalización eficiente y flexible que aborde una necesidad comercial.

Situación

En años recientes, las ganancias del comercio electrónico TheLook aumentaron considerablemente gracias a las compras en línea. Pero los tiempos de entrega no estuvieron a la altura y la satisfacción del cliente disminuyó.

Como analista de datos en la nube para el comercio electrónico TheLook, se te solicitó que colabores con Kai, líder del equipo de Logística, para desarrollar una canalización de datos y, así, recopilar, borrar, transformar y cargar datos sobre las entregas a los clientes, incluidas las distancias recorridas desde el centro de distribución hasta el destino del cliente.

Esta información ayudará al equipo de Logística a determinar las formas en que pueden mejorar los tiempos de entrega y aumentar la satisfacción del cliente; por ejemplo, abrir nuevos centros de distribución, reubicar los existentes o invertir en nuevos métodos de transporte.

Aplicarás tus habilidades en BigQuery y SQL para diseñar una canalización flexible que proporcione al equipo de Logística datos confiables que le permitan supervisar mejor el rendimiento de las entregas y que se pueda actualizar con facilidad a medida que las necesidades de datos del equipo cambien.

Primero, deberás crear un conjunto de datos y definir esquemas de tablas para los datos que se transferirán. Luego, realizarás una serie de transformaciones y las explorarás. A continuación, aplicarás las transformaciones a los datos antes de cargar los datos transformados en nuevas tablas definidas. Finalmente, formalizarás esas consultas en un procedimiento almacenado.

Configuración

Antes de hacer clic en 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.

En este lab práctico, puedes realizar las actividades por tu cuenta en un entorno de nube real, en lugar de una simulación o un entorno de demostración. Para ello, se te proporcionan credenciales temporales nuevas que usará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 del 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. Haz clic en el botón Comenzar lab. A la izquierda, se encuentra el panel Detalles del lab, que tiene estos elementos:

   • Tiempo restante
   • El botón Abrir la consola de Google
   • Las credenciales temporales que debes usar para el lab
   • Otra información para completar el lab, si es necesaria
   Nota: Si debes pagar por el lab, se abrirá una ventana emergente para que selecciones tu forma de pago.

2. Haz clic en Abrir la consola de Google (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. La página Acceder se abre en una pestaña del navegador nueva.

   Sugerencia: Puedes organizar las pestañas de manera independiente (una ventana al lado de la otra) para alternar fácilmente entre ellas.

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

3. Si es necesario, copia el nombre de usuario de Google Cloud a continuación, y pégalo en el diálogo Ingresar. Haz clic en Siguiente.

{{{user_0.username | "nombre de usuario de Google Cloud"}}}

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

4. Copia la contraseña de Google Cloud a continuación y pégala en el diálogo te damos la bienvenida. Haz clic en Siguiente.

{{{user_0.password | "contraseña de Google Cloud"}}}

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

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 podría generar cargos adicionales.

5. Haz 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 las pruebas gratuitas.

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

Nota: Para ver el menú con una lista de los productos y servicios de Google Cloud, haz clic en el menú de navegación que se encuentra en la parte superior izquierda de la pantalla.

Tarea 1: Crea un conjunto de datos

En esta tarea, crearás un conjunto de datos en el proyecto existente y definirás el esquema y los tipos de datos.

1. En el menú de navegación () de la consola de Cloud, selecciona BigQuery.

Nota: Puede aparecer el mensaje Te damos la bienvenida a BigQuery en la consola de Cloud, que proporciona vínculos a la guía de inicio rápido y las notas de la versión de las actualizaciones de la IU. Haz clic en Listo para continuar.

Se mostrará el menú de navegación, el panel Explorer y el Editor de consultas.

2. Haz clic en el ícono de Ver acciones () junto al ID de tu proyecto y, luego, en Crear conjunto de datos.

Nota: Es probable que primero debas seleccionar un proyecto. Para hacerlo, haz clic en Selecciona un proyecto en la barra de título de la consola de Google Cloud y, luego, elige el vínculo del proyecto en el diálogo Selecciona un proyecto.

3. Establece el ID de conjunto de datos en thelook_ecommerce. Verifica que la ubicación esté establecida en EE.UU. y deja los campos restantes en sus valores predeterminados.

4. Haz clic en Crear conjunto de datos.

Se mostrará thelook_ecommerce bajo el nombre del proyecto.

5. En el editor de consultas, haz clic en ícono de redactar nueva consulta (+) para abrir una nueva pestaña Sin título y ejecutar la consulta.

Nota: Cada vez que ejecutes una nueva consulta en el editor de consultas, puedes copiar y pegar para reemplazar la antigua consulta por la nueva en la misma pestaña Sin título, o puedes hacer clic en el ícono de redactar una nueva consulta (+) y, así, abrir una nueva pestaña Sin título para ejecutar la consulta.

6. Copia y pega la siguiente consulta en la pestaña Sin título para crear la tabla product_order_fulfillment:

--Create empty product_orders_fulfillment table CREATE OR REPLACE TABLE `thelook_ecommerce.product_orders_fulfillment` ( order_id INT64, user_id INT64, status STRING, product_id INT64, created_at TIMESTAMP, returned_at TIMESTAMP, shipped_at TIMESTAMP, delivered_at TIMESTAMP, cost NUMERIC, sale_price NUMERIC, retail_price NUMERIC, category STRING, name STRING, brand STRING, department STRING, sku STRING, distribution_center_id INT64);

7. Haz clic en Ejecutar.

Nota: Esta consulta crea una nueva tabla denominada product_orders_fulfillment, si aún no existe, o reemplaza la existente por la nueva definición de tabla.

8. Haz clic en Ir a la tabla para explorar su esquema y los tipos de datos que se crearon.

Si bien este conjunto de datos está vacío actualmente, es la ubicación en la que se propagarán los datos una vez que se carguen.

Haz clic en Revisar mi progreso para verificar que completaste esta tarea correctamente.

Crear un conjunto de datos

Tarea 2: Crea una tabla a partir de los resultados de la consulta

Tu análisis se expandirá para incluir el análisis de la proximidad de los centros de distribución a los clientes que hacen pedidos. Para hacerlo, necesitarás calcular los valores en función de las ubicaciones geográficas.

En esta tarea, crearás puntos de geometría a partir de los valores de latitud y longitud proporcionados en las tablas original users y distribution_centers.

1. Copia la siguiente consulta en el editor de consultas: --Create empty customers table CREATE OR REPLACE TABLE `thelook_ecommerce.customers` ( id INT64, first_name STRING, last_name STRING, email STRING, age INT64, gender STRING, state STRING, street_address STRING, postal_code STRING, city STRING, country STRING, traffic_source STRING, created_at TIMESTAMP, latitude FLOAT64, longitude FLOAT64, point_location GEOGRAPHY); --Create empty centers table CREATE OR REPLACE TABLE `thelook_ecommerce.centers` ( id INT64, name STRING, latitude FLOAT64, longitude FLOAT64, point_location GEOGRAPHY);

Esta consulta crea las definiciones para las tablas "customers" y "centers", que incluirán las columnas originales de las tablas "users" y "distribution_centers". Esta consulta también agrega una columna point_location a ambas tablas; estas columnas contendrán los tipos de datos establecidos en "Geography".

2. Haz clic en Ejecutar.

Haz clic en Revisar mi progreso para verificar que completaste esta tarea correctamente.

Crear nuevas tablas para almacenar los datos relevantes

Tarea 3: Realiza una transformación en los datos de BigQuery

En esta tarea, crearás puntos de geometría con la función de ubicación geográfica ST_GEOGPOINT, en la que llamas a ST_GEOGPOINT(lon, lat).

Primero, ejecuta las siguientes consultas en SQL para crear y completar las tablas centers y customers. Estas consultas cargan los datos con una instrucción SELECT, puesto que la ubicación original (longitud y latitud) de los datos está en el conjunto de datos públicos thelook_ecommerce de BigQuery.

Un conjunto de datos público de Google es cualquier conjunto de datos que se almacena en BigQuery y que está disponible para el público en general a través del Programa de conjuntos de datos públicos de Google Cloud. Los conjuntos de datos públicos facilitan el uso de datos públicos disponibles en BigQuery sin tener que cargarlos ni mantenerlos.

1. Copia la siguiente consulta en el editor de consultas:

--load the centers table from public dataset and include geography transformation CREATE OR REPLACE TABLE `thelook_ecommerce.centers` AS SELECT id, name, latitude, longitude, ST_GEOGPOINT(dcenters.longitude, dcenters.latitude) AS point_location FROM `bigquery-public-data.thelook_ecommerce.distribution_centers` AS dcenters;

Esta consulta carga la tabla "centers", incluida la transformación de ubicación geográfica.

2. Haz clic en Ejecutar.

Nota: Puedes hacer clic en Ir a la tabla, para explorar su esquema y los tipos de datos que se crearon.

3. Ahora, copia y pega la siguiente consulta en el editor de consultas:

--load the customers table from public dataset and include geography transformation CREATE OR REPLACE TABLE `thelook_ecommerce.customers` AS SELECT id, first_name, last_name, email, age, gender, state, street_address, postal_code, city, country, traffic_source, created_at, latitude, longitude, ST_GEOGPOINT(users.longitude, users.latitude) AS point_location FROM `bigquery-public-data.thelook_ecommerce.users` AS users;

Esta consulta carga la tabla "customers", incluida la transformación de ubicación geográfica.

4. Haz clic en Ejecutar.

A continuación, deberás calcular los valores de los datos para el análisis.

Puesto que la relación entre los clientes y los centros de distribución es importante, usarás las ubicaciones de los puntos geográficos para calcular el centro de distribución más cercano a cada cliente y la distancia a la que se encuentran de esas ubicaciones.

Para ello, usa la función de ubicación geográfica ST_DISTANCE que te permite calcular la distancia mínima entre un cliente y un centro.

5. Copia y pega la siguiente subconsulta escalar en el editor de consultas:

SELECT customers.id as customer_id, ( SELECT MIN(ST_DISTANCE(centers.point_location, customers.point_location))/1000, FROM `thelook_ecommerce.centers` AS centers) AS distance_to_closest_center FROM `thelook_ecommerce.customers` AS customers ;

Esta consulta calcula la distancia en kilómetros.

6. Haz clic en Ejecutar.

Como se muestra en la captura de pantalla, la consulta devuelve la distancia entre cada cliente y el centro de distribución más cercano. Tómate un momento para revisar la instrucción SELECT y, así, comprender cómo obtiene el cálculo de la distancia.

7. Expande el menú desplegable Guardar y selecciona Guardar consulta.

Se abre el diálogo Guardar consulta.

8. En el campo Nombre, escribe Calcular la distancia del cliente al centro más cercano. Deja la configuración restante con sus valores predeterminados.
9. Haz clic en Guardar.
10. En el panel Explorer, haz doble clic en la consulta guardada en el menú desplegable Consultas guardadas para volver a ejecutar la consulta.

Haz clic en Revisar mi progreso para verificar que completaste esta tarea correctamente.

Realiza una transformación en datos de BQ

Tarea 4: Crea un procedimiento almacenado para facilitar las actualizaciones

Un procedimiento almacenado es un conjunto de instrucciones de SQL que están almacenadas en una base de datos y pueden ejecutarse como una sola unidad.

En esta tarea, crearás un procedimiento almacenado para incluir todas las definiciones de tabla, las transformaciones y, además, las instrucciones de transferencia que generaste manualmente y secuencialmente hasta el momento en las tareas de 1 a 3.

Para crear un procedimiento almacenado (en este caso, sp_create_load_tables), agrega la instrucción CREATE OR REPLACE PROCEDURE al principio de tu secuencia de comandos, seguida por el nombre de tu procedimiento almacenado y la instrucción BEGIN. Una vez que hayas agregado todos los componentes del procedimiento, agrega la instrucción END.

Además, puedes colocar tus definiciones de tabla y tus porciones de transferencia de datos en el procedimiento. Puedes colocar todas tus definiciones de tabla por delante o puedes agruparlas por tabla, según prefieras. Sin embargo, en este lab, a cada definición de tabla le sigue la porción de transferencia de datos y otras transformaciones realizadas en los pasos anteriores.

También, puedes crear columnas en tu tabla de clientes, que podría almacenar los valores de distance_to_closest_center y closest_centervalues calculados. La elegancia del procedimiento es que, si el esquema cambia, o si quieres incluir columnas adicionales o generar columnas calculadas, puedes agregarlas con facilidad o actualizar el procedimiento almacenado. Una vez actualizado, el procedimiento almacenado se puede configurar para ejecutarse con regularidad.

Ahora, crea un procedimiento almacenado que ejecute todos los pasos que ejecutaste anteriormente en las tareas de 1 a 3, como una sola unidad. El código siguiente, cuando se ejecuta, actualizará las tablas y las completará correctamente.

1. Copia y pega la siguiente consulta en una nueva pestaña Sin título:

CREATE OR REPLACE PROCEDURE `thelook_ecommerce.sp_create_load_tables`() BEGIN --Create empty product_orders_fulfillment table CREATE OR REPLACE TABLE `thelook_ecommerce.product_orders_fulfillment` ( order_id INT64, user_id INT64, status STRING, product_id INT64, created_at TIMESTAMP, returned_at TIMESTAMP, shipped_at TIMESTAMP, delivered_at TIMESTAMP, cost NUMERIC, sale_price NUMERIC, retail_price NUMERIC, category STRING, name STRING, brand STRING, department STRING, sku STRING, distribution_center_id INT64) ; --load the product_order_fulfillment table from public dataset CREATE OR REPLACE TABLE thelook_ecommerce.product_orders_fulfillment AS SELECT items.*, products.id AS product_id_products, products.name AS product_name, products.category AS product_category, FROM bigquery-public-data.thelook_ecommerce.order_items AS items JOIN bigquery-public-data.thelook_ecommerce.products AS products ON (items.product_id = products.id); --Create empty centers table CREATE OR REPLACE TABLE `thelook_ecommerce.centers` ( id INT64, name STRING, latitude FLOAT64, longitude FLOAT64, point_location GEOGRAPHY); --load the centers table from public dataset and include geography transformation CREATE OR REPLACE TABLE `thelook_ecommerce.centers` AS SELECT id, name, latitude, longitude, ST_GEOGPOINT(dcenters.longitude, dcenters.latitude) AS point_location FROM `bigquery-public-data.thelook_ecommerce.distribution_centers` AS dcenters ; --Create empty customers table CREATE OR REPLACE TABLE `thelook_ecommerce.customers` ( id INT64, first_name STRING, last_name STRING, email STRING, age INT64, gender STRING, state STRING, street_address STRING, postal_code STRING, city STRING, country STRING, traffic_source STRING, created_at TIMESTAMP, latitude FLOAT64, longitude FLOAT64, point_location GEOGRAPHY); --load the customers table from public dataset and include geography transformation CREATE OR REPLACE TABLE `thelook_ecommerce.customers` AS SELECT id, first_name, last_name, email, age, gender, state, street_address, postal_code, city, country, traffic_source, created_at, latitude, longitude, ST_GEOGPOINT(users.longitude, users.latitude) AS point_location FROM `bigquery-public-data.thelook_ecommerce.users` AS users ; END ;

2. Haz clic en Ejecutar.

Ten en cuenta que el procedimiento almacenado crea todas las tablas necesarias y las completa con los datos transformados. Sin embargo, el procedimiento almacenado no devuelve los resultados de las consultas que muestran la distancia entre los clientes y el centro de distribución más cercano. Ve al paso 7 a continuación para hacer eso.

3. Expande el menú desplegable Guardar y selecciona Guardar consulta. Se abre el diálogo Guardar consulta.
4. En el campo Nombre, escribe sp_create_load_tables. Deja la configuración restante con sus valores predeterminados.
5. Haz clic en Guardar. El procedimiento almacenado se mostrará en la sección Rutinas del conjunto de datos:
6. Haz clic en Ejecutar para ejecutar el procedimiento almacenado.

Como se mostró en el paso 5 de la tarea 3 anterior, ejecuta la instrucción SELECT SQL para obtener la distancia entre cada cliente y el centro de distribución más cercano.

7. Copia y pega la siguiente subconsulta escalar en el editor de consultas:

SELECT customers.id as customer_id, ( SELECT MIN(ST_DISTANCE(centers.point_location, customers.point_location))/1000, FROM `thelook_ecommerce.centers` AS centers) AS distance_to_closest_center FROM `thelook_ecommerce.customers` AS customers ;

Esta consulta calcula la distancia en kilómetros.

8. Haz clic en Ejecutar.

Haz clic en Revisar mi progreso para verificar que completaste esta tarea correctamente.

Crear un procedimiento almacenado para facilitar las actualizaciones Nota: No ejecutarás una consulta programada en este lab, pero es importante que comprendas las muchas maneras que existen de actualizar datos y mantener tus canalizaciones de SQL.

Para configurar una consulta programada, puedes simplemente hacer clic en Schedule en el editor de consultas. Se abre la ventana Nueva consulta programada, con varios detalles que puedes especificar. Puedes hacer esto con cualquier consulta que deba actualizarse regularmente.

Conclusión

¡Muy bien!

Usaste SQL y BigQuery correctamente para crear una canalización de datos sencilla y flexible que te permite satisfacer una necesidad comercial.

Primero, creaste un conjunto de datos y definiste esquemas de tabla para los datos que se transferirán. Luego, ejecutaste y exploraste una serie de transformaciones.

A continuación, aplicaste las transformaciones a los datos antes de cargar los datos transformados en nuevas tablas definidas.

Finalmente, formalizaste esas consultas en un procedimiento almacenado para incluir los valores calculados de interés y viste cómo configurar consultas programadas para las actualizaciones periódicas.

En este lab, aprendiste la importancia de la creación de conjuntos de datos y la definición de esquemas de tabla para los datos que contienen. Ejecutaste y examinaste una serie de transformaciones y, así, desarrollaste las habilidades para aplicar transformaciones a los datos antes de cargarlos en las nuevas tablas definidas. Además, aprendiste cómo formalizar estas consultas en un procedimiento almacenado para incluir valores calculados ​​de interés y cómo configurar consultas programadas para las actualizaciones periódicas.

Estás en un buen camino para comprender cómo puedes usar BigQuery en la creación y la administración de canalizaciones de SQL.

Finaliza el lab

Antes de que finalices el lab, asegúrate de estar conforme con la forma en que completaste todas las tareas. Cuando estés conforme, haz clic en Finalizar Lab y luego haz clic en Enviar.

Finalizar el lab te quitará el acceso al entorno del lab y no podrás volver a acceder al trabajo que completaste.

Copyright 2024 Google LLC. Todos los derechos reservados. Google y el logotipo de Google son marcas de Google LLC. El resto de los nombres de productos y empresas pueden ser marcas de las respectivas empresas a las que están asociados.