Consulta de modelos gráficos con CTE recursivo de Couchbase

Las expresiones comunes de tabla (CTE) recursivas y CONNECT BY de Oracle son construcciones SQL muy conocidas entre los usuarios de RDBMS, que facilitan la delegación de la exploración de estructuras de datos complejas e interdependientes a la capa de base de datos para mejorar la eficacia del procesamiento.

Estas construcciones son cruciales para consultar estructuras de datos interdependientes, un requisito común en varios sectores, como las finanzas, la gestión de la cadena de suministro, la gestión de las relaciones con los clientes (CRM), las reservas de viajes y, más recientemente, las redes sociales. Conscientes de su importancia, los principales sistemas de gestión de bases de datos relacionales (RDBMS), como PostgreSQL, MySQL (a partir de la versión 8.0), SQL Server, Oracle y SQLite, ofrecen soporte para CTE recursivas.

Por el contrario, las bases de datos NoSQL, diseñadas para gestionar una amplia gama de modelos de datos como documentos, datos clave-valor, de columnas anchas y gráficos, dan prioridad a la escalabilidad, la alta disponibilidad, la flexibilidad y el rendimiento en sistemas distribuidos. En estos entornos, el concepto de CTE, recursivo o no, no se aborda directamente. Los usuarios suelen recurrir a soluciones especializadas, como las bases de datos de grafos (Cypher para Neo4J y AQL para ArangoDB, por ejemplo), para gestionar estructuras de datos complejas.

Couchbase se distingue con SQL para JSON, ofreciendo un enfoque único a CTEs recursivos que también extienden su soporte multi modelo. Este blog profundizará en tres temas principales:

1. 1. Cómo se puede aprovechar un único DBMS como Couchbase para estructuras de datos complejas para una serie de casos de uso, de la misma manera que RDBMS podría, pero sin la necesidad de una base de datos más dedicada. 
   2. El uso del constructo Couchase SQL++ para consultar, transformar y proyectar estas relaciones complejas utilizando un constructo SQL que es familiar a los usuarios de RDBMS.
   3. Mejores prácticas para gestionar el consumo de recursos con CTE recursivo.

Lista de materiales

La lista de materiales es un componente fundamental en la fabricación y la ingeniería, ya que detalla las materias primas, las piezas y los componentes necesarios para fabricar un producto. Suele tener una estructura jerárquica, en la que las piezas se componen de otras piezas o materiales.

Este ejemplo incluirá los componentes y subcomponentes básicos de un ordenador de sobremesa.

Supongamos que queremos enumerar todas las piezas y subpiezas necesarias para construir un ordenador de sobremesa, junto con las cantidades necesarias de cada una.

Esta consulta inicializa la recursión con el elemento de nivel superior (el ordenador de sobremesa) y une recursivamente la tabla de componentes consigo misma para recorrer la jerarquía en sentido descendente, ajustando las cantidades según sea necesario para cada componente y subcomponente.

El resultado listará todas las piezas necesarias para el Ordenador de Sobremesa, incluyendo la cantidad de cada una y su nivel jerárquico, lo que puede ser útil para entender la estructura de ensamblaje o para fines de inventario y pedidos.

Explicación

El CTE comienza con la Bicicleta (donde ParentPartID es NULL) y luego busca recursivamente todos los componentes y subcomponentes.

La cantidad se ajusta en cada nivel para reflejar el número total necesario para una bicicleta.

La columna Nivel, aunque no es estrictamente necesaria para todos los análisis de listas de materiales, ayuda a comprender la profundidad de cada pieza dentro de la jerarquía.

Este enfoque permite un desglose detallado de todos los materiales y componentes necesarios para fabricar un producto, esencial para la gestión de inventarios, la estimación de costes y la planificación de la producción en las operaciones de fabricación.

Redes sociales

Una aplicación común en un caso de uso de redes sociales es encontrar los grados de conexión entre dos usuarios -esencialmente, cómo están conectados los usuarios a través de una cadena de amigos comunes. Digamos que necesitamos determinar el camino más corto (en términos de grados de conexión) entre dos usuarios en una red social. Esto puede ayudar en funciones como sugerir amistades o comprender la dinámica de la red.

Consideremos que tenemos los siguientes usuarios y cómo son amigos entre sí, por ejemplo, Alice es amiga de Bob, y también de Charlie. Pero Alice no es amiga de Dana.

En esta red ampliada, los usuarios se conectan de diversas maneras, creando múltiples vías a través de las cuales pueden conectarse.

Vamos a encontrar los grados de conexión entre Alice[1] y Frank[6], incluyendo los nombres de los usuarios a lo largo del camino.

Explicación

• • Caso base: El CTE comienza identificando las conexiones directas de Alice (UserID 1), incluyendo los nombres de usuario para facilitar la lectura.
  • Paso recursivo: A continuación, busca recursivamente los amigos de esas conexiones, ampliando la búsqueda y rastreando el grado de conexión. Los JOIN garantizan que se incluyan los nombres de usuario tanto del usuario inicial como de sus amigos en cada paso.
  • Terminación y filtrado: La recursión continúa hasta que encuentra conexiones con Frank (UserID 6). La consulta filtra los caminos que llevan a Frank y ordena los resultados por el grado de conexión para identificar los caminos más cortos.

Esta consulta demuestra cómo trazar y enumerar rutas a través de una red social, incluyendo nombres de usuario para mayor claridad. Proporciona una base para análisis más complejos, como la identificación de todas las conexiones mutuas o la exploración de estructuras de red.

Caso práctico de navegación por redes de grafos

Para este caso de uso, utilizaré una versión resumida de los datos de rutas de America Airlines. Nótese que esto no es de la muestra de viajes de Couchbase. En este ejemplo usamos Couchbase SQL++ Recursive CTE query para encontrar todos los vuelos de LAX a MAD con < 2 paradas de este conjunto de datos de muestra. Tenga en cuenta que estos datos de muestra no se basan en la viaje-muestrasino una versión simplificada de las rutas AA para 2008. 

Explicación

• • La rutaCTE comienza con todos los vuelos que salen de LAX.
  • La parte recursiva de routeCTE busca vuelos que conecten desde la última parada de la ruta actual con otros aeropuertos, evitando las rutas que vuelven a LAX.
  • La matriz de rutas acumula la secuencia de códigos de aeropuerto para mostrar la ruta tomada.
  • La consulta muestra todas las rutas que terminan en MAD, detallando las rutas encontradas.

Buenas prácticas CTE recursivo

Al utilizar expresiones de tabla comunes (CTE) recursivas en SQL++, los desarrolladores deben ser conscientes de las implicaciones de la naturaleza recursiva y del coste para el procesamiento de consultas. He aquí las mejores prácticas:

Establecer límites para la profundidad de recursión - Establezca siempre un límite en la profundidad de la recursión para evitar bucles infinitos y un consumo excesivo de recursos. Utilice un contador o una condición dentro de la CTE recursiva para controlar la profundidad a la que puede llegar la recursión, incluya las opciones límites.

Supervisar el rendimiento - Las CTE recursivas pueden consumir muchos recursos. Controla de cerca el rendimiento, especialmente en el caso de consultas largas o complejas, y optimízalas si es necesario. Esto puede implicar la indexación o la descomposición de CTE demasiado complejas.

Evite complejidades innecesarias - Mantenga la lógica dentro de la parte recursiva del CTE tan simple como sea posible. Las condiciones o cálculos demasiado complejos pueden reducir considerablemente el rendimiento. Compruebe que la condición JOIN es correcta.

Garantizar una estructura de datos correcta - Compruebe que sus datos están estructurados correctamente para la recursión. Los datos incorrectos o mal formados pueden dar lugar a resultados erróneos o consultas ineficaces.

Pruebas exhaustivas - Pruebe a fondo los CTE recursivos con varios conjuntos de datos, incluidos los casos extremos. Esto ayuda a detectar problemas de bucles infinitos, resultados incorrectos o cuellos de botella en el rendimiento.

Establecer la cuota de memoria - Establezca la cuota de memoria a nivel de petición o de nodo para evitar un uso excesivo de memoria en la consulta recursiva.

Limitaciones

Los CTEs recursivos son una característica poderosa en Couchbase SQL++, que se encuentran comúnmente en otros RDBMS. Permiten la ejecución de consultas complejas, como atravesar datos jerárquicos y de grafos o realizar cálculos iterativos que son difíciles de expresar con SQL estándar. Sin embargo, existen limitaciones y consideraciones a tener en cuenta cuando se utilizan CTE recursivas. Estas limitaciones suelen estar relacionadas con el rendimiento, las restricciones sintácticas y la complejidad de las consultas. A continuación se detallan algunas de ellas:

Agregados: Los CTE recursivos no suelen permitir funciones de agregación (MIN(), MAX(), SUM(), AVG(), etc.) o DISTINCT dentro de la parte recursiva del CTE. Estas operaciones no tienen sentido en el contexto de la adición recursiva de filas porque implican un conjunto de resultados final después de que se haya resuelto toda la recursividad.

Funciones de ventana: Al igual que las funciones de agregado, las funciones de ventana (ROW_NUMBER(), RANK(), etc.) no suelen utilizarse dentro de la parte recursiva de la CTE. Están pensadas para su uso en un conjunto de filas devueltas por la consulta, lo que las hace adecuadas para el término no recursivo o en una consulta que seleccione desde la CTE recursiva.

LIMITAR / PEDIR POR: Estas cláusulas no están permitidas dentro del miembro recursivo del CTE. El razonamiento es que pertenecen a la ordenación del conjunto de resultados final y no tiene sentido en el contexto de la construcción del conjunto recursivo, donde los resultados intermedios se construyen acumulativamente a través de cada iteración.

Próximos pasos

• • Más información Couchbase SQL
  • Más ejemplos de CTE en el blog: Procesamiento recursivo de consultas en SQL (N1QL)
  • Empieza con un prueba gratuita de Couchbase Capella