Puntos de control
Create a dataset
/ 25
Create a table from query results
/ 25
Perform a transformation on BiqQuery data
/ 25
Create a stored procedure to ensure easier updates
/ 25
Crea y administra canalizaciones de SQL
- Descripción general de la actividad
- Situación
- Configuración
- Tarea 1: Crea un conjunto de datos
- Tarea 2: Crea una tabla a partir de los resultados de la consulta
- Tarea 3: Realiza una transformación en los datos de BigQuery
- Tarea 4: Crea un procedimiento almacenado para facilitar las actualizaciones
- Conclusión
- Finaliza el lab
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)
- Tiempo para completar el lab: Recuerda que, una vez que comienzas un lab, no puedes pausarlo.
Cómo iniciar tu lab y acceder a la consola de Google Cloud
-
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. -
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. -
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.
También puedes encontrar el nombre de usuario de Google Cloud en el panel Detalles del lab.
- 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.
También puedes encontrar la contraseña de Google Cloud en el panel Detalles del lab.
- 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.
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.
- En el menú de navegación () de la consola de Cloud, selecciona BigQuery.
Se mostrará el menú de navegación, el panel Explorer y el Editor de consultas.
- Haz clic en el ícono de Ver acciones () junto al ID de tu proyecto y, luego, en Crear conjunto de datos.
- 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.
- Haz clic en Crear conjunto de datos.
Se mostrará thelook_ecommerce bajo el nombre del proyecto.
- 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.
- Copia y pega la siguiente consulta en la pestaña Sin título para crear la tabla
product_order_fulfillment
:
- Haz clic en Ejecutar.
- 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.
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.
- 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".
- Haz clic en Ejecutar.
Haz clic en Revisar mi progreso para verificar que completaste esta tarea correctamente.
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.
- Copia la siguiente consulta en el editor de consultas:
Esta consulta carga la tabla "centers", incluida la transformación de ubicación geográfica.
- Haz clic en Ejecutar.
- Ahora, copia y pega la siguiente consulta en el editor de consultas:
Esta consulta carga la tabla "customers", incluida la transformación de ubicación geográfica.
- 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.
- Copia y pega la siguiente subconsulta escalar en el editor de consultas:
Esta consulta calcula la distancia en kilómetros.
- 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.
- Expande el menú desplegable Guardar y selecciona Guardar consulta.
Se abre el diálogo Guardar consulta.
- En el campo Nombre, escribe Calcular la distancia del cliente al centro más cercano. Deja la configuración restante con sus valores predeterminados.
- Haz clic en Guardar.
- 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.
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.
- Copia y pega la siguiente consulta en una nueva pestaña Sin título:
- 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.
- Expande el menú desplegable Guardar y selecciona Guardar consulta. Se abre el diálogo Guardar consulta.
- En el campo Nombre, escribe sp_create_load_tables. Deja la configuración restante con sus valores predeterminados.
- Haz clic en Guardar. El procedimiento almacenado se mostrará en la sección Rutinas del conjunto de datos:
- 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.
- Copia y pega la siguiente subconsulta escalar en el editor de consultas:
Esta consulta calcula la distancia en kilómetros.
- Haz clic en Ejecutar.
Haz clic en Revisar mi progreso para verificar que completaste esta tarea correctamente.
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.