Trabaja con CTE recurrentes

En GoogleSQL para BigQuery, una cláusula WITH contiene una o más expresiones de tabla comunes (CTE) con tablas temporales a las que puedes hacer referencia en una expresión de consulta. Las CTE pueden ser no recurrentes, recurrentes o ambas. La palabra clave RECURSIVE habilita la recurrencia en la cláusula WITH (WITH RECURSIVE).

Una CTE recurrente puede hacer referencia a sí misma, a una CTE anterior o a una CTE posterior. Una CTE no recurrente puede hacer referencia solo a CTE anteriores y no puede hacer referencia a sí misma. Las CTE recurrentes se ejecutan de forma continua hasta que no se encuentren resultados nuevos, mientras que las CTE no recurrentes se ejecutan una vez. Por estas razones, las CTE recurrentes se suelen usar para consultar datos jerárquicos y datos de gráficos.

Por ejemplo, considera un gráfico en el que cada fila representa un nodo que puede vincularse con otros nodos. Para encontrar el cierre transitivo de todos los nodos accesibles de un nodo de inicio en particular sin conocer la cantidad máxima de saltos, necesitarías una CTE recurrente en la consulta (WITH RECURSIVE). La consulta recurrente comenzaría con el caso base del nodo de inicio, y en cada paso se calcularían los nodos nuevos no vistos que se pueden alcanzar desde todos los nodos vistos hasta el paso anterior. La consulta finaliza cuando no se pueden encontrar nodos nuevos.

Sin embargo, las CTE recurrentes pueden ser costosas en términos de procesamiento, por lo que, antes de usarlas, revisa esta guía y la sección WITH de la documentación de referencia de GoogleSQL.

Crea una CTE recurrente

Para crear una CTE recurrente en GoogleSQL, usa la cláusula WITH RECURSIVE, 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

En el ejemplo anterior, se producen los siguientes resultados:

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

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

En el ejemplo anterior, la CTE recurrente contiene los siguientes componentes:

• Nombre de la CTE recurrente: CTE_1
• Término base: SELECT 1 AS iteration
• Operador de unión: UNION ALL
• Término recurrente: 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 la CTE recurrente, consulta la cláusula WITH en la documentación de referencia de GoogleSQL.

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

Puedes usar una consulta recurrente para explorar la accesibilidad en un grafo acíclico dirigido (DAG). En la siguiente consulta, se encuentran todos los nodos a los que se puede acceder desde el nodo 5 en un grafo 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;

En el ejemplo anterior, se producen los siguientes resultados:

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

Soluciona problemas de errores de límite de iteración

Las CTE recurrentes pueden generar una recurrencia infinita, lo que ocurre cuando el término recurrente se ejecuta de forma continua sin cumplir una condición de finalización. A fin de finalizar las recurrencias infinitas, se aplica un límite de iteraciones para cada CTE recurrente. Para BigQuery, el límite de iteraciones es de 500 iteraciones. Una vez que una CTE recurrente alcanza la cantidad máxima de iteraciones, la ejecución de la CTE se anula con un error.

Este límite existe porque el procesamiento de una CTE recurrente puede ser costoso, y la ejecución de un CTE con una gran cantidad de iteraciones consume muchos recursos del sistema y lleva mucho más tiempo para finalizar.

Por lo general, las consultas que alcanzan el límite de iteración no tienen una condición de finalización adecuada, por lo que se crea un bucle infinito o se usan CTE recurrentes en situaciones inadecuadas.

Si experimentas un error de límite de iteraciones de recurrencia, revisa las sugerencias de esta sección.

Verifica la recurrencia infinita

Para evitar la recurrencia infinita, asegúrate de que el término recurrente pueda producir un resultado vacío después de ejecutar una cierta cantidad de iteraciones.

Una forma de verificar la recurrencia infinita es convertir tu CTE recurrente en una TEMP TABLE con un bucle REPEAT durante las primeras 100 iteraciones, de la siguiente manera:

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;

Reemplaza los siguientes valores:

• recursive_cte_name: la CTE recurrente que se depurará.
• base_expression: el término base de la CTE recurrente.
• recursive_expression: el término recurrente de la CTE recurrente.
• termination_condition_expression: La expresión de finalización de la CTE recurrente.

Por ejemplo, considera 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 el TestCTE para la recurrencia 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 produce los siguientes resultados, que incluyen el ID de iteración y la cantidad de filas que se produjeron 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 que se generaron durante la iteración 2:

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

Si la cantidad de filas siempre es mayor que cero, lo que es verdadero en este ejemplo, es probable que la muestra tenga una recurrencia infinita.

Verifica el uso correcto de la CTE recurrente

Verifica que usas la CTE recurrente en una situación adecuada. Las CTE recurrentes pueden ser costosas de procesar, ya que están diseñadas para consultar datos de jerarquía y datos de grafos. Si no consultas estos dos tipos de datos, considera alternativas, como usar la sentencia LOOP con una CTE no recurrente.

Divide un CTE recurrente en varias CTE recurrentes

Si crees que tu CTE recurrente necesita más de la cantidad máxima de iteraciones permitidas, es posible que puedas dividir tu CTE recurrente en varias CTE recurrentes.

Puedes dividir una CTE recurrente con una estructura de consulta similar a la que se muestra a continuación:

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
[...]

Reemplaza los siguientes valores:

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

Por ejemplo, el siguiente código divide una CTE en tres CTE 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, 500 iteraciones se reemplazan por 10 iteraciones para que sea más rápido ver los resultados de la consulta. La consulta produce 30 filas, pero cada CTE recurrente solo itera 10 veces. El resultado será similar a esto:

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

Puedes probar la consulta anterior en iteraciones mucho más grandes.

Usa un bucle en lugar de una CTE recurrente

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

Cambia el límite recurrente

Si crees que se aplican los siguientes factores, comunícate con el servicio de atención al cliente para aumentar el límite recurrente:

• Si tienes una razón válida para que tu CTE recurrente ejecute más de 500 iteraciones.
• Si estás de acuerdo con una ejecución mucho más larga.

Ten en cuenta que aumentar el límite recurrente tiene posibles riesgos:

• Tu CTE puede fallar con un mensaje de error diferente, por ejemplo, de que se superó el límite de memoria o se agotó el tiempo de espera.
• Si tu proyecto usa el modelo de precios on demand, es posible que tu CTE falle de todas formas con un error de nivel de facturación hasta que cambies al modelo de precios basado en la capacidad.
• Una CTE recurrente con una gran cantidad de iteraciones consume muchos recursos y esto puede afectar a otras consultas que se ejecutan dentro de la misma reserva, ya que compiten por los recursos compartidos.

Precios

Si usas la facturación on demand, BigQuery cobra según la cantidad de bytes que se procesan durante la ejecución de una consulta con una CTE recurrente.

Para obtener más información, consulta Cálculo del tamaño de la consulta.

Cuotas

Para obtener más información sobre las cuotas y los límites de CTE recurrentes, consulta Cuotas y límites.