Loop join Bigquery

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

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 ?

Tagged:

Answers

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

    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

Setup Info
    Tags
      Help me…