1

I have an edges table in my Bigquery database that represents the edges of a directed cyclic graph, with two columns: (node_from, node_to).

Given a set of initial nodes (initial_node), I'd like to be able to traverse the graph, but in an undirected way.

What I mean is, for instance for the following graph :

(a->b)
(c->b)
(c->d)

If initial_node is a, b, c, or d, in any case, I would get [a, b, c, d].

My problem is basically the same as this solved issue: PostgreSQL SQL query for traversing an entire undirected graph and returning all edges found, except that I want to write it in Bigquery.

I've try to turn the proposed solution in that issue from PostgresSQL to Bigquery:

CREATE temp TABLE edges (
`from` STRING,
`to` STRING
);

INSERT INTO edges (`from`, `to`) VALUES
('initial_node', 'a'), ('a', 'b'), ('a', 'c'), ('c', 'd');

WITH RECURSIVE graph AS (
SELECT ARRAY(SELECT DISTINCT `to` FROM `edges` WHERE `from` = 'initial_node') AS points
UNION ALL
SELECT points || ARRAY(SELECT DISTINCT `to` 
          FROM `edges` 
          WHERE `from` IN UNNEST(g.points) 
            AND `to` NOT IN UNNEST(g.points) 
            AND `to` != 'initial_node') AS points
FROM graph g
WHERE ARRAY_LENGTH(g.points) > 0
)
SELECT DISTINCT point
FROM graph
CROSS JOIN UNNEST(points) AS point
ORDER BY 1;

However, this cause an error message: Query error: An unsupported query pattern using WITH RECURSIVE was detected (such as using IN or EXISTS within the recursive term). Please rewrite the query.

Is Bigquery capable for this kind of computation. If yes, how can I code this.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.