Presentamos las Expresiones Comunes Recursivas (CTE): Haciendo que Databricks SQL sea Turing Completo

Las expresiones comunes de tabla recursivas (CTE) ahora son compatibles con Databricks. Esto proporciona una forma nativa de expresar bucles y recorridos en SQL, útil para trabajar con datos jerárquicos y estructurados en forma de grafo. Estas capacidades están alineadas con el estándar SQL y siguen patrones familiares utilizados en almacenes de datos heredados como Teradata y Snowflake, lo que facilita mucho las migraciones desde dichos almacenes. Databricks también ha contribuido con el soporte de CTE recursivas a Apache Spark™, haciéndolo completamente de código abierto.

Databricks utiliza la sintaxis estándar ANSI SQL para las CTE recursivas, incluida la palabra clave RECURSIVE.

Esta característica, aparentemente pequeña, mejora significativamente las capacidades expresivas de SQL, haciéndola teóricamente Turing completa, lo que significa que puede realizar cualquier cálculo que una computadora pueda. Las CTE recursivas permiten soluciones componibles que anteriormente requerían código procedural, como Python o herramientas externas.

CTE recursivas ya están disponibles en la vista previa pública DBSQL 2025.20 y Databricks Runtime 17.0 (próximamente en Lakeflow Declarative Pipelines). En esta publicación de blog, exploraremos cómo funcionan las CTE recursivas y cómo pueden ayudarte a resolver problemas del mundo real utilizando SQL puro.

Características clave del soporte de CTE recursivas

El soporte de CTE recursivas de Databricks incluye:

• Recorrido de estructuras similares a árboles y grafos, como organigramas, carpetas y redes de enrutamiento
• Completamente de código abierto e integrado en Apache Spark™
• Salvaguardas integradas para recursión infinita (100 pasos, 1 millón de filas)
• Salvaguardas personalizables usando MAX RECURSION LEVEL
• Soporte para recursión infinita controlada usando LIMIT

Las CTE recursivas funcionan bien tanto con sistemas tradicionales que almacenan datos jerárquicos en tablas normalizadas como con datos provenientes de aplicaciones modernas que generan jerarquías JSON/XML flexibles. Vea ejemplos a continuación de cada uno, incluidas las CTE recursivas que aprovechan el tipo de datos Variant para jerarquías JSON.

Además, el soporte para CTE recursivas simplifica las migraciones desde sistemas de bases de datos heredados. Teradata y Postgres son dos ejemplos de sistemas cuya sintaxis es idéntica, mientras que sistemas como Oracle, que utilizan la sintaxis CONNECT BY, se convierten fácilmente.

Cómo funcionan las CTE recursivas

Las CTE recursivas son expresiones comunes de tabla definidas con la palabra clave RECURSIVE. Constan de dos partes combinadas mediante UNION ALL:

1. Una subconsulta de caso base: se ejecuta una vez y siembra la recursión
2. Una subconsulta de paso recursivo: hace referencia a la propia CTE y se aplica repetidamente para generar nuevas filas.

La ejecución comienza con la consulta base. Luego, en cada iteración, se ejecuta el paso recursivo utilizando la salida del paso anterior. Esto continúa hasta que no se producen nuevas filas.

Para evitar que la recursión infinita consuma recursos excesivos, Databricks aplica dos límites de seguridad: una profundidad máxima de recursión de 100 pasos y un límite de filas de 1 millón. Si se supera cualquiera de los umbrales, la consulta falla con un error.

Si confías en que tu recursión requiere más de 100 pasos para producir todos los resultados, puedes anular el nivel máximo utilizando la indicación MAX RECURSION LEVEL:

 Para obtener más detalles, consulta la documentación de CTE.

“En bp Supply Trading and Shipping – Market Risk, comprender la generación de informes de jerarquía de cartera entre unidades de negocio es fundamental para que nuestra empresa opere de manera eficiente. Al reemplazar nuestro código heredado con CTE recursivas en Databricks SQL, redujimos un paso de preparación de datos jerárquicos de ~6 minutos a ~30 segundos, lo que representa una mejora de 12 veces.” — Dharmik Prajapati, Ingeniero de Software Principal de bp

Ejemplos de resolución de tareas iterativas utilizando CTE recursivas

Navegar datos de árboles y jerarquías: Encontrar materiales requeridos utilizando una lista de materiales

En la industria manufacturera, cada pieza fabricada requiere un conjunto de componentes para su construcción. Cada componente podría descomponerse en un conjunto más pequeño de piezas individuales. El conjunto completo de todas las piezas se llama Lista de Materiales (BOM). 

Una BOM a menudo forma una estructura similar a un árbol, o más generalmente, un grafo acíclico dirigido (DAG). En este ejemplo, analizamos las piezas de una bicicleta, que simplificaremos asumiendo una estructura de árbol, donde cada componente se utiliza en un único elemento principal.

Supongamos que queremos calcular cuántas materias primas se necesitan para construir una bicicleta. Considere la siguiente BOM:

Cada fila describe un componente, la pieza más grande a la que pertenece y cuántos componentes se necesitan para ensamblar una unidad del elemento principal.

La CTE recursiva comienza con un objetivo: construir una bicicleta. Ese es el caso base. En cada paso recursivo, descomponemos los componentes en sus subcomponentes. Por ejemplo, una bicicleta incluye un cuadro, una transmisión y dos ruedas. Cada rueda, a su vez, consta de un neumático y 32 radios. La estructura recursiva se vuelve clara a medida que desglosamos las piezas en partes más pequeñas.

Una vez que hemos expandido completamente la jerarquía, filtramos los componentes intermedios (padres) para conservar solo las materias primas necesarias para el ensamblaje.

Esta consulta calcula la cantidad total de cada material base necesario para construir una bicicleta:

Búsqueda de rutas basada en el recorrido del grafo: Encontrar todas las rutas de vuelo desde una ciudad

Examinemos un problema utilizando una estructura de datos de grafo. Un grafo consta de un conjunto de nodos conectados por aristas. Se utiliza para representar relaciones o conexiones entre pares de elementos. Resolver un problema de grafos solía requerir Python, lógica de scripting complicada o una biblioteca externa. Ahora, las consultas recursivas lo simplifican.

Un problema típico de estructura de grafos es el de los viajes aéreos: ¿a qué aeropuertos puedo llegar utilizando una serie de vuelos? Supongamos que tenemos el siguiente conjunto de vuelos que existen en un día:

Cada vuelo tiene los códigos IATA de su origen y destino, junto con las horas de salida y llegada.

Supongamos que una persona llega al aeropuerto BEG a las 8 AM y quiere encontrar todas las rutas de viaje posibles que puede hacer ese día.

Este problema se plantea de forma natural como un problema iterativo. Cada vez que descubrimos una nueva ciudad a la que podemos llegar, encontramos todos los vuelos que salen de allí después de nuestra hora de llegada. Por esta razón, en la CTE recursiva, hacemos un seguimiento de la hora de llegada a cada aeropuerto. 

Esto produce el conjunto de todos los aeropuertos alcanzables, junto con el número y el conjunto de vuelos requeridos.

Esta consulta puede ayudar a los usuarios a explorar todos los destinos alcanzables dadas las restricciones del horario, lo que permite aplicaciones como la planificación de viajes, el enrutamiento de paquetes o la logística de transporte.

En el ejemplo anterior, definimos los nombres de las columnas en la cláusula WITH RECURSIVE ... AS (...) . Aquí, las definimos en la consulta ancla en su lugar. Ambos enfoques son válidos en CTEs recursivas en Databricks.

Recorrer datos semiestructurados y no estructurados: Encontrar todos los empleados almacenados en un archivo JSON

Los sistemas tradicionales a menudo almacenan datos jerárquicos en tablas rígidas y normalizadas. Mientras tanto, las aplicaciones modernas generan frecuentemente jerarquías JSON/XML flexibles. La combinación de CTEs recursivas de Databricks con el tipo VARIANT le permite migrar estos patrones de datos sin problemas, lo que le permite consultar datos normalizados tradicionales y estructuras JSON/XML flexibles en un solo sistema.

En este ejemplo, se nos da una jerarquía de empresa (relativamente pequeña). Pero en lugar de una tabla completamente estructurada, se nos da en forma de JSON:

Supongamos que queremos una lista de todos los empleados y sus títulos en una tabla. Los campos de las personas en la empresa no siguen el mismo esquema: algunos tienen subordinados directos, mientras que otros no; algunos tienen su ubicación, ¡otros no! Con el uso del tipo VARIANT en Databricks, todas sus necesidades comunes se pueden utilizar dentro de una CTE recursiva para explorar completamente la estructura anidada del JSON, mientras que sus diferencias se pueden ignorar.

The base case of the recursion is the full JSON data of the root employee, which includes a list of their subordinates. In each recursive step, the query processes each subordinate's data and repeats the process until it reaches an employee with no subordinates.

Here’s the recursive query for this example:

Even though all CTEs here are under a WITH RECURSIVE block, only the one with actual recursion is treated as recursive. Databricks is smart enough to detect which ones need recursion—even if you mark them all!

The output of the query:

How to get started

Get started with recursive CTEs by reading the Databricks documentation. 

The best data warehouse is a lakehouse. To learn more about Databricks SQL, visit our website or read the documentation. You can also check out the product tour for Databricks SQL. If you want to migrate your existing warehouse to a high-performance, serverless data warehouse with a great user experience and lower total cost, then Databricks SQL is the solution — try it for free.

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