Loop join Bigquery

User06

Hi,

I am trying to compute multiple joins across the same table in Bigquery. For example, my query would be (in a simple language) :

For i = 1 to 24 :
CREATE TABLE table0 as
SELECT
A.*,
B.column as column_i
FROM
table0 AS A
LEFT JOIN table_i AS B
ON A.id=B.id

How can I do this in a simple way ?

Sarina

Hi @User06
,

There are many ways you can do a join within the same table in DSS. If you are really doing the join 24 times, then it might be easiest to generate the query in Python similar to your sample code, and then execute the query using the SQLExecutor2 method. You will also probably want to check first that your query is performant.

If you had fewer joins, then using a visual join recipe in DSS to join the same input table multiple times would be another good option, as would using a SQL Query recipe. Since it sounds like creating the query would be easiest in Python, using a Python recipe with SQLExecutor2 to execute the query does seem simplest in your case.

Let me know if you have any questions about your implementation.

Thank you,
Sarina