Trabajar con CTEs recursivas

En GoogleSQL para BigQuery, una cláusula WITH contiene una o varias expresiones de tabla comunes (ETCs) a las que puedes hacer referencia en una expresión de consulta. Las CTEs pueden ser no recursivas, recursivas o ambas. La palabra clave RECURSIVE permite la recursión en la cláusula WITH (WITH RECURSIVE).

Una CTE recursiva puede hacer referencia a sí misma, a una CTE anterior o a una CTE posterior. Una CTE no recursiva solo puede hacer referencia a CTEs anteriores y no puede hacer referencia a sí misma. Las CTEs recursivas se ejecutan continuamente hasta que no se encuentran nuevos resultados, mientras que las CTEs no recursivas se ejecutan una vez. Por estos motivos, las CTEs recursivas se suelen usar para consultar datos jerárquicos y gráficos.

Por ejemplo, supongamos que tenemos un gráfico en el que cada fila representa un nodo que puede enlazarse a otros nodos. Para encontrar el cierre transitivo de todos los nodos a los que se puede acceder desde un nodo de inicio concreto sin conocer el número máximo de saltos, necesitarías una CTE recursiva en la consulta (WITH RECURSIVE). La consulta recursiva empezaría con el caso base del nodo de inicio y, en cada paso, se calcularían los nuevos nodos no vistos a los que se puede acceder desde todos los nodos vistos hasta el paso anterior. La consulta finaliza cuando no se encuentran nodos nuevos.

Sin embargo, las CTEs recursivas pueden ser computacionalmente costosas, por lo que, antes de usarlas, consulta esta guía y la sección de la cláusula WITH de la documentación de referencia de GoogleSQL.

Crear una CTE recursiva

Para crear una CTE recursiva en GoogleSQL, usa la cláusula WITH RECURSIVE, tal como se muestra en el siguiente ejemplo:

WITH RECURSIVE
  CTE_1 AS (
    (SELECT 1 AS iteration UNION ALL SELECT 1 AS iteration)
    UNION ALL
    SELECT iteration + 1 AS iteration FROM CTE_1 WHERE iteration < 3
  )
SELECT iteration FROM CTE_1
ORDER BY 1 ASC

El ejemplo anterior produce los siguientes resultados:

/*-----------*
 | iteration |
 +-----------+
 | 1         |
 | 1         |
 | 2         |
 | 2         |
 | 3         |
 | 3         |
 *-----------*/

Una CTE recursiva incluye un término base, un operador de unión y un término recursivo. El término base ejecuta la primera iteración de la operación de unión recursiva. El término recursivo ejecuta las iteraciones restantes y debe incluir una autorreferencia a la CTE recursiva. Solo el término recursivo puede incluir una autorreferencia.

En el ejemplo anterior, el CTE recursivo contiene los siguientes componentes:

• Nombre de CTE recursiva: CTE_1
• Término base: SELECT 1 AS iteration
• Operador de unión: UNION ALL
• Término recursivo: SELECT iteration + 1 AS iteration FROM CTE_1 WHERE iteration < 3

Para obtener más información sobre la sintaxis, las reglas y los ejemplos de CTE recursivos, consulta la WITHcláusula en la documentación de referencia de GoogleSQL.

Explorar la accesibilidad en un grafo acíclico dirigido (DAG)

Puedes usar una consulta recursiva para explorar la accesibilidad en un gráfico acíclico dirigido (DAG). La siguiente consulta busca todos los nodos a los que se puede acceder desde el nodo 5 en un gráfico llamado GraphData:

WITH RECURSIVE
  GraphData AS (
    --    1          5
    --   / \        / \
    --  2 - 3      6   7
    --      |       \ /
    --      4        8
    SELECT 1 AS from_node, 2 AS to_node UNION ALL
    SELECT 1, 3 UNION ALL
    SELECT 2, 3 UNION ALL
    SELECT 3, 4 UNION ALL
    SELECT 5, 6 UNION ALL
    SELECT 5, 7 UNION ALL
    SELECT 6, 8 UNION ALL
    SELECT 7, 8
  ),
  R AS (
    (SELECT 5 AS node)
    UNION ALL
    (
      SELECT GraphData.to_node AS node
      FROM R
      INNER JOIN GraphData
        ON (R.node = GraphData.from_node)
    )
  )
SELECT DISTINCT node FROM R ORDER BY node;

El ejemplo anterior produce los siguientes resultados:

/*------*
 | node |
 +------+
 | 5    |
 | 6    |
 | 7    |
 | 8    |
 *------*/

Solucionar errores de límite de iteración

Las CTEs recursivas pueden dar lugar a una recursión infinita, que se produce cuando el término recursivo se ejecuta continuamente sin cumplir una condición de finalización. Para terminar las recursiones infinitas, se aplica un límite de iteraciones a cada CTE recursivo. En BigQuery, el límite de iteraciones es de 500. Cuando una CTE recursiva alcanza el número máximo de iteraciones, la ejecución de la CTE se cancela y se produce un error.

Este límite se debe a que el cálculo de un CTE recursivo puede ser costoso y a que ejecutar un CTE con un gran número de iteraciones consume muchos recursos del sistema y tarda mucho más en completarse.

Las consultas que alcanzan el límite de iteración suelen carecer de una condición de finalización adecuada, lo que crea un bucle infinito, o bien utilizan CTEs recursivas en situaciones inadecuadas.

Si se produce un error de límite de iteración de recursión, consulta las sugerencias de esta sección.

Comprobar si hay recursión infinita

Para evitar la recursión infinita, asegúrate de que el término recursivo pueda producir un resultado vacío después de ejecutar un número determinado de iteraciones.

Una forma de comprobar si hay recursión infinita es convertir tu CTE recursivo en un TEMP TABLE con un bucle REPEAT para las primeras 100 iteraciones, como se indica a continuación:

DECLARE current_iteration INT64 DEFAULT 0;

CREATE TEMP TABLE recursive_cte_name AS
SELECT base_expression, current_iteration AS iteration;

REPEAT
  SET current_iteration = current_iteration + 1;
  INSERT INTO recursive_cte_name
    SELECT recursive_expression, current_iteration
    FROM recursive_cte_name
    WHERE termination_condition_expression
      AND iteration = current_iteration - 1
      AND current_iteration < 100;
  UNTIL NOT EXISTS(SELECT * FROM recursive_cte_name WHERE iteration = current_iteration)
END REPEAT;

Sustituye los siguientes valores:

• recursive_cte_name: el CTE recursivo que se va a depurar.
• base_expression: el término base del CTE recursivo.
• recursive_expression: el término recursivo de la CTE recursiva.
• termination_condition_expression: expresión de terminación de la CTE recursiva.

Por ejemplo, considere la siguiente CTE recursiva llamada TestCTE:

WITH RECURSIVE
  TestCTE AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 3 FROM TestCTE WHERE MOD(n, 6) != 0
  )

En este ejemplo se usan los siguientes valores:

• recursive_cte_name: TestCTE
• base_expression: SELECT 1
• recursive_expression: n + 3
• termination_condition_expression: MOD(n, 6) != 0

Por lo tanto, el siguiente código probaría TestCTE para comprobar si hay recursión infinita:

DECLARE current_iteration INT64 DEFAULT 0;

CREATE TEMP TABLE TestCTE AS
SELECT 1 AS n, current_iteration AS iteration;

REPEAT
SET current_iteration = current_iteration + 1;

INSERT INTO TestCTE
SELECT n + 3, current_iteration
FROM TestCTE
WHERE
  MOD(n, 6) != 0
  AND iteration = current_iteration - 1
  AND current_iteration < 10;

UNTIL
  NOT EXISTS(SELECT * FROM TestCTE WHERE iteration = current_iteration)
    END REPEAT;

-- Print the number of rows produced by each iteration

SELECT iteration, COUNT(1) AS num_rows
FROM TestCTE
GROUP BY iteration
ORDER BY iteration;

-- Examine the actual result produced for a specific iteration

SELECT * FROM TestCTE WHERE iteration = 2;

El ejemplo anterior genera los siguientes resultados, que incluyen el ID de iteración y el número de filas que se han generado durante esa iteración:

/*-----------+----------*
 | iteration | num_rows |
 +-----------+----------+
 | 0         | 1        |
 | 1         | 1        |
 | 2         | 1        |
 | 3         | 1        |
 | 4         | 1        |
 | 5         | 1        |
 | 6         | 1        |
 | 7         | 1        |
 | 8         | 1        |
 | 9         | 1        |
 | 10        | 1        |
 *-----------+----------*/

Estos son los resultados reales obtenidos durante la iteración 2:

/*----------+-----------*
 | n        | iteration |
 +----------+-----------+
 | 7        | 2         |
 *----------+-----------*/

Si el número de filas siempre es mayor que cero, como ocurre en este ejemplo, es probable que la muestra tenga una recursión infinita.

Verificar el uso adecuado de la CTE recursiva

Verifica que estás usando la CTE recursiva en un caso adecuado. Las CTEs recursivas pueden ser costosas de calcular porque están diseñadas para consultar datos jerárquicos y datos de gráficos. Si no consultas estos dos tipos de datos, considera alternativas, como usar la instrucción LOOP con una CTE no recursiva.

Dividir una CTE recursiva en varias CTEs recursivas

Si crees que tu CTE recursiva necesita más iteraciones de las permitidas, puedes dividirla en varias CTEs recursivas.

Puedes dividir una CTE recursiva con una estructura de consulta similar a la siguiente:

WITH RECURSIVE
  CTE_1 AS (
    SELECT base_expression
    UNION ALL
    SELECT recursive_expression FROM CTE_1 WHERE iteration < 500
  ),
  CTE_2 AS (
    SELECT * FROM CTE_1 WHERE iteration = 500
    UNION ALL
    SELECT recursive_expression FROM CTE_2 WHERE iteration < 500 * 2
  ),
  CTE_3 AS (
    SELECT * FROM CTE_2 WHERE iteration = 500 * 2
    UNION ALL
    SELECT recursive_expression FROM CTE_3 WHERE iteration < 500 * 3
  ),
  [, ...]
SELECT * FROM CTE_1
UNION ALL SELECT * FROM CTE_2 WHERE iteration > 500
UNION ALL SELECT * FROM CTE_3 WHERE iteration > 500 * 2
[...]

Sustituye los siguientes valores:

• base_expression: la expresión del término base de la CTE actual.
• recursive_expression: la expresión del término recursivo de la CTE actual.

Por ejemplo, el siguiente código divide una CTE en tres CTEs distintas:

WITH RECURSIVE
  CTE_1 AS (
    SELECT 1 AS iteration
    UNION ALL
    SELECT iteration + 1 AS iteration FROM CTE_1 WHERE iteration < 10
  ),
  CTE_2 AS (
    SELECT * FROM CTE_1 WHERE iteration = 10
    UNION ALL
    SELECT iteration + 1 AS iteration FROM CTE_2 WHERE iteration < 10 * 2
  ),
  CTE_3 AS (
    SELECT * FROM CTE_2 WHERE iteration = 10 * 2
    UNION ALL
    SELECT iteration + 1 AS iteration FROM CTE_3 WHERE iteration < 10 * 3
  )
SELECT iteration FROM CTE_1
UNION ALL
SELECT iteration FROM CTE_2 WHERE iteration > 10
UNION ALL
SELECT iteration FROM CTE_3 WHERE iteration > 20
ORDER BY 1 ASC

En el ejemplo anterior, se han sustituido 500 iteraciones por 10 para que los resultados de la consulta se muestren más rápido. La consulta genera 30 filas, pero cada CTE recursivo solo se repite 10 veces. La salida tiene este aspecto:

/*-----------*
 | iteration |
 +-----------+
 | 2         |
 | ...       |
 | 30        |
 *-----------*/

Podrías probar la consulta anterior en iteraciones mucho más grandes.

Usar un bucle en lugar de una CTE recursiva

Para evitar los límites de iteración, considera la posibilidad de usar un bucle en lugar de una CTE recursiva. Puedes crear un bucle con una de las varias instrucciones de bucle, como LOOP, REPEAT o WHILE. Para obtener más información, consulta Bucles.

Cambiar el límite recursivo

Si crees que se dan los siguientes factores, ponte en contacto con el servicio de atención al cliente para aumentar el límite recursivo:

• Tienes un motivo válido para que tu CTE recursiva se ejecute más de 500 iteraciones.
• No te importa que la ejecución sea mucho más larga.

Ten en cuenta que aumentar el límite recursivo conlleva ciertos riesgos:

• Es posible que tu CTE falle y muestre otro mensaje de error, como que se ha superado el límite de memoria o que se ha agotado el tiempo de espera.
• Si tu proyecto usa el modelo de precios bajo demanda, es posible que tu CTE siga fallando con un error de nivel de facturación hasta que cambies al modelo de precios basado en la capacidad.
• Una CTE recursiva con un gran número de iteraciones consume muchos recursos. Esto podría afectar a otras consultas que se estén ejecutando en la misma reserva, ya que compiten por los recursos compartidos.

Precios

Si usas la facturación bajo demanda, BigQuery cobra según el número de bytes que se procesan durante la ejecución de una consulta con una CTE recursiva.

Para obtener más información, consulta la sección sobre cómo se calcula el tamaño de las consultas.

Cuotas

Para obtener información sobre las cuotas y los límites de las CTEs recursivas, consulta Cuotas y límites.