Loop join Bigquery

User06 Partner, Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Registered Posts: 1 Partner
edited July 16 in Using Dataiku


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
B.column as column_i
table0 AS A
LEFT JOIN table_i AS B
ON A.id=B.id

How can I do this in a simple way ?



  • Sarina
    Sarina Dataiker, Dataiku DSS Core Designer, Dataiku DSS Adv Designer Posts: 315 Dataiker

    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,

Setup Info
      Help me…