Presentación de procedimientos almacenados SQL en Databricks

Administrar tareas repetitivas de SQL, como limpiar datos, actualizar reglas de negocio o ejecutar lógica por lotes, puede ser tedioso y propenso a errores si copias y pegas código.

Ahora, los Procedimientos Almacenados de SQL en Databricks (ya disponibles en general) te permiten almacenar esa lógica una vez, ejecutarla cuando la necesites y mantenerla bajo el control de Unity Catalog.

Ya sea que estés limpiando datos antes del análisis, actualizando tablas según criterios de negocio o migrando cargas de trabajo de un almacén de datos empresarial heredado, los procedimientos almacenados hacen que el proceso sea más simple, consistente y fácil de mantener.

Databricks admite estándares abiertos e interoperabilidad, evitando implementaciones propietarias o específicas del proveedor. Los Procedimientos Almacenados de SQL siguen el estándar ANSI/PSM SQL y se contribuirán a Apache Spark™ de código abierto.

Los procedimientos se utilizan ampliamente en tareas administrativas, gestión de datos y flujos de trabajo ETL, especialmente en almacenes de datos empresariales (EDW). Para los clientes que migran de EDW a Databricks, los procedimientos almacenados existentes se pueden migrar sin reescribir, lo que simplifica la transición. Y como siempre, el mejor almacén de datos es un lakehouse.

Para uno de nuestros casos de uso críticos en torno a la segmentación de clientes, aprovechamos los Procedimientos Almacenados de SQL con DBSQL para lograr un mejor rendimiento, escalabilidad y eficiencia de costos. Estar familiarizado con SQL nos ayudó a implementar y desplegar la solución en producción en muy poco tiempo. El uso de Procedimientos Almacenados nos ha permitido gestionar la lógica compleja de manera más efectiva, manteniendo la arquitectura general simplificada y mantenible. —SambaSiva Rao, Sr. Data Engineer/Architect, ClicTechnologies

Los Procedimientos Almacenados de SQL ya están disponibles en general.

Descripción general de los Procedimientos Almacenados de SQL

¿Qué son los Procedimientos Almacenados?

En los flujos de trabajo de procesamiento de datos, los clientes pueden tener dificultades para mantener la consistencia y el rendimiento de tareas repetitivas y lógica compleja. Los procedimientos almacenados son un gran enfoque en estos casos, asegurando que los datos se procesen de manera consistente y estandarizada, y que el rendimiento sea óptimo.

Para tareas de limpieza de datos, los procedimientos pueden aplicar transformaciones como convertir formatos de fecha inconsistentes en una estructura estandarizada, eliminar espacios en blanco al principio y al final de los campos de texto, y reemplazar o corregir valores erróneos. Esto asegura que sus datos estén preparados para el análisis posterior. Consulte el ejemplo detallado de ETL a continuación.

En el lado de la gestión de datos, los procedimientos almacenados pueden actualizar eficientemente los valores de las tablas basándose en reglas de negocio definidas, como marcar registros obsoletos, recalcular campos o sincronizar datos entre tablas relacionadas. Al encapsular estas operaciones en procedimientos, los equipos pueden garantizar una ejecución consistente, reducir la intervención manual y mejorar la calidad de los datos a escala. Consulte el ejemplo detallado de gestión de datos a continuación, utilizando procedimientos almacenados para actualizar un programa de fidelización/membresía.

Entonces, ¿qué son los Procedimientos? Son colecciones precompiladas de sentencias SQL que permiten a un usuario gestionar su lógica SQL en una unidad única y reutilizable. Los procedimientos se almacenan en Unity Catalog, lo que significa que están gobernados y encapsulan completamente los permisos. Cuando se llama a un procedimiento almacenado, la base de datos ejecuta estas operaciones predefinidas, ofreciendo el beneficio de seguridad mejorada, mantenimiento simplificado de cargas de trabajo complejas y el potencial de un rendimiento mejorado.

¿Qué está soportado?

Hay 5 comandos principales que soportan procedimientos: CREATE, CALL, DESCRIBE, SHOW y DROP.

• CREATE PROCEDURE: Define y almacena un nuevo procedimiento almacenado dentro de Unity Catalog. Especifica el nombre del procedimiento, los parámetros (si los hay) y las sentencias SQL que se ejecutarán cuando se llame al procedimiento.
• CALL PROCEDURE: Ejecuta un procedimiento almacenado creado previamente; pasando los parámetros requeridos.
• DESCRIBE PROCEDURE: Devuelve metadatos básicos sobre un procedimiento existente, como su nombre y parámetros. Con EXTENDED, la descripción incluirá metadatos adicionales, como el propietario, la fecha y hora de creación, el tipo de seguridad, etc.
• SHOW PROCEDURES: Lista todos los procedimientos almacenados disponibles dentro del esquema de catálogo actual.
• DROP PROCEDURE: Elimina un procedimiento existente del almacenamiento.

Al crear un procedimiento, puedes usar varios tipos de parámetros para controlar la entrada y la salida.

• Parámetro `IN`: Se utiliza para pasar valores a un procedimiento como entrada. Por ejemplo, podrías pasar un ID de cliente para recuperar o procesar solo los datos de ese cliente. El procedimiento puede leer pero no modificar estos valores.
• Parámetro `OUT`: Se utiliza para devolver valores de un procedimiento, después de haber sido asignados. Por ejemplo, podrías pasar un ID de cliente y devolver su estado de cuenta o el total de ventas calculado, para su posterior procesamiento fuera del procedimiento.
• Parámetro `INOUT`: Cumple un doble propósito, permitiendo que un valor se pase a un procedimiento, se modifique dentro de él y se devuelva el valor actualizado. Funciona tanto como entrada como salida.

Estos parámetros se pueden asignar a:

• Variables locales, declaradas dentro de un script/procedimiento
• Variables de sesión, declaradas dentro de la sesión, e incluso fuera del script/procedimiento

La lógica encapsulada dentro de un Procedimiento Almacenado de SQL se basa en SQL Scripting. Un procedimiento almacenado puede considerarse como un script reutilizable con parámetros, gobernado por Unity Catalog. Puedes leer sobre Scripting en estos dos blogs introductorios:

• Introducción a SQL Scripting Parte 1
• Introducción a SQL Scripting Parte 2
• Revisa la documentación de SQL Scripting

Se admiten llamadas anidadas y recursivas de procedimientos, lo que significa que los clientes pueden organizar sus unidades de trabajo o lógica de negocio convenientemente en procedimientos separados, haciendo que todo el flujo de ejecución de SQL sea más modular. Esto mejora la legibilidad y el mantenimiento.

¿En qué se diferencian los Procedimientos de las Funciones?

Los procedimientos se agrupan con las Funciones en Unity Catalog en la interfaz de usuario. Sin embargo, los procedimientos y las funciones, aunque permiten reutilizar la lógica SQL, sirven para propósitos diferentes.

Una función se utiliza para devolver un valor o una tabla. Debe usarse dentro de una consulta SQL y no puede incluir SQL dinámico o lógica procedural. Un procedimiento, por el contrario, se utiliza para ejecutar una secuencia de sentencias SQL. Puede incluir flujo de control, variables, bucles y SQL dinámico usando IDENTIFIER y EXECUTE IMMEDIATE. Llamas a un procedimiento como un comando independiente, típicamente para realizar una tarea o flujo de trabajo.

Ejemplos de uso de Procedimientos Almacenados de SQL

Ahora que hemos cubierto las capacidades de los Procedimientos Almacenados de SQL, exploremos algunos ejemplos para demostrar su valor y los problemas que ayudan a resolver.

Puedes usar este notebook para seguir el ejemplo - contiene todos los ejemplos de esta publicación, así como comandos de preparación de datos.

ETL – Limpieza de datos: Preparación de tablas de capa Silver o Gold

Si sigues la típica arquitectura Medallion, sabes que mover datos de Bronze a Silver (o de Silver a Gold) puede requerir limpiar, transformar, agregar y formatear datos. Los Stored Procedures son excelentes para gestionar procesos repetitivos como estos dentro de un flujo de trabajo ETL. 

En este escenario ETL, se utiliza un Procedure para:

• Cargar datos de datos sin procesar en una tabla de hechos
• Seleccionar datos según el rango de fechas y el origen de la venta (web, móvil, punto de venta, proveedor externo)
• Limpiar y formatear los datos convirtiendo fechas a un formato específico y eliminando espacios en blanco
• Una vez limpios, cargar los datos en una tabla 'limpia'
• Agregar un registro de log basado en la marca de tiempo, fechas de inicio y fin, y origen de la venta
• Usando el Procedure, buscar las ventas de la aplicación móvil para junio de 2025

Los Procedures como este ayudan a estandarizar los productos de datos. Cualquier usuario de este procedure producirá datos en la misma estructura, independientemente del rango de fechas o el punto de venta. Este es un beneficio principal de reutilizar código. La reutilización de código será naturalmente menos propensa a errores, ya que la misma lógica se ejecuta cada vez.

Gestión de datos: Actualizar una tabla según criterios de negocio

La gestión de datos es la práctica de asegurar que tus datos sean precisos, consistentes y accesibles de manera eficiente, cualidades que son esenciales para cualquier organización que busque tomar decisiones basadas en datos. Sin una gestión de datos sólida, incluso los esfuerzos de análisis o informes más avanzados pueden verse socavados por información poco confiable o inconsistente. 

Examinemos un ejemplo común en industrias comerciales donde una empresa establece un programa de fidelización para ofrecer beneficios a los clientes según su nivel. Las aerolíneas tienen programas de viajero frecuente y la mayoría de las franquicias minoristas tienen programas de recompensas, etc. A medida que los clientes vuelan más con la misma aerolínea o compran más artículos de la misma franquicia, los clientes obtienen más beneficios.

Aquí hay un ejemplo de cómo se pueden usar los Stored Procedures para gestionar y actualizar un programa de fidelización minorista estándar. Hay dos procedures que se utilizan para gestionar los niveles de fidelización de los clientes: uno para actualizar el nivel de fidelización de un cliente específico para el customerID proporcionado, y otro que actualiza el nivel de fidelización de todos los clientes de un país proporcionado.

Ahora usemos el procedure creado para actualizar los niveles de fidelización de los clientes de Serbia, Alemania y Canadá, y luego verifiquemos los registros actualizados:

La consulta anterior produce el siguiente resultado:

Al encapsular la lógica de actualización de nivel en procedures respectivos, evitamos la duplicación de código y también eliminamos la complejidad del llamador, que solo necesita invocar el procedure con los parámetros adecuados.

¿Qué sigue?

Con los SQL Stored Procedures ahora en DBSQL, los clientes pueden continuar migrando cargas de trabajo de data warehouse empresariales heredadas al lakehouse. Según los comentarios de los clientes, hay varias capacidades clave que queremos abordar a medida que avanzamos hacia la GA:

• SQL Stored Procedures en Apache Spark™: Soporte para todos los SQL Procedures en código abierto
• Soporte para SQL SECURITY DEFINER: Permitir a los clientes ejecutar la lógica del procedure con los permisos del creador del procedure (definer)

Los clientes que deseen compartir comentarios o solicitudes relacionadas con SQL Scripting y Procedures pueden hacerlo a través de este formulario.

Otros dos constructos SQL importantes son necesarios para migrar Stored Procedures de sistemas heredados: las Tablas Temporales y las Transacciones. Las Tablas Temporales ya están disponibles de forma general en DBSQL, mientras que el soporte para Transacciones (multi-sentencia y multi-tabla) estará disponiblePróximamente. 

Ya sea que ya seas usuario de Databricks o que migres desde otro Data Warehouse, los Procedimientos Almacenados SQL son una funcionalidad que deberías usar para simplificar la gestión de flujos de trabajo SQL complejos. Empieza con los Procedimientos Almacenados SQL leyendo la documentación de Databricks.

Para saber más sobre Databricks SQL, visita nuestro sitio web o lee la documentación. También puedes ver el recorrido del producto para Databricks SQL. Si quieres migrar tu almacén de datos actual a un almacén de datos serverless de alto rendimiento con una excelente experiencia de usuario y un menor costo total, entonces Databricks SQL es la solución — pruébalo gratis.

(Esta entrada del blog ha sido traducida utilizando herramientas basadas en inteligencia artificial) Publicación original