Work with recursive CTEs Stay organized with collections Save and categorize content based on your preferences.

In GoogleSQL for BigQuery, a WITH clause contains one or more common table expressions (CTEs) that you can reference in a query expression. CTEs can be non-recursive, recursive, or both. The RECURSIVE keyword enables recursion in the WITH clause (WITH RECURSIVE).

A recursive CTE can reference itself, a preceding CTE, or a subsequent CTE. A non-recursive CTE can reference only preceding CTEs and can't reference itself. Recursive CTEs run continuously until no new results are found, while non-recursive CTEs run once. For these reasons, recursive CTEs are commonly used for querying hierarchical data and graph data.

For example, consider a graph where each row represents a node that can link to other nodes. To find the transitive closure of all reachable nodes from a particular start node without knowing the maximum number of hops, you would need a recursive CTE in the query (WITH RECURSIVE). The recursive query would start with the base case of the start node, and each step would compute the new unseen nodes that can be reached from all the nodes seen so far up to the previous step. The query concludes when no new nodes can be found.

However, recursive CTEs can be computationally expensive, so before you use them, review this guide and the WITH clause section of the GoogleSQL reference documentation.

Create a recursive CTE

To create a recursive CTE in GoogleSQL, use the WITH RECURSIVE clause as shown in the following example:

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

The preceding example produces the following results:

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

A recursive CTE includes a base term, a union operator, and a recursive term. The base term runs the first iteration of the recursive union operation. The recursive term runs the remaining iterations and must include one self-reference to the recursive CTE. Only the recursive term can include a self-reference.

In the preceding example, the recursive CTE contains the following components:

• Recursive CTE name: CTE_1
• Base term: SELECT 1 AS iteration
• Union operator: UNION ALL
• Recursive term: SELECT iteration + 1 AS iteration FROM CTE_1 WHERE iteration < 3

To learn more about the recursive CTE syntax, rules, and examples, see WITH clause in the GoogleSQL reference documentation.

Explore reachability in a directed acyclic graph (DAG)

You can use a recursive query to explore reachability in a directed acyclic graph (DAG). The following query finds all nodes that can be reached from node 5 in a graph called 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;

The preceding example produces the following results:

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

Troubleshoot iteration limit errors

Recursive CTEs can result in infinite recursion, which occurs when the recursive term executes continuously without meeting a termination condition. To terminate infinite recursions, a limit of iterations for each recursive CTE is enforced. For BigQuery, the iteration limit is 500 iterations. Once a recursive CTE reaches the maximum number of iterations, the CTE execution is aborted with an error.

This limit exists because the computation of a recursive CTE can be expensive, and running a CTE with a large number of iterations consumes a lot of system resources and takes a much longer time to finish.

Queries that reach the iteration limit are usually missing a proper termination condition, thus creating an infinite loop, or using recursive CTEs in inappropriate scenarios.

If you experience a recursion iteration limit error, review the suggestions in this section.

Check for infinite recursion

To prevent infinite recursion, make sure the recursive term is able to produce an empty result after executing a certain number of iterations.

One way to check for infinite recursion is to convert your recursive CTE to a TEMP TABLE with a REPEAT loop for the first 100 iterations, as follows:

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 


    


Web Proxy Viewer  |  New URL  |  Original Page