Add support for Connection as SQL Recipe Input

[Mattcadam]

The challenge

When we write SQL recipes, we sometimes have several tables attached (10 tables or more). It can be very tedious to add all the tables in the flow area (Dataset > SQL Databases > Oracle then configure table) and can lead to a messy workflow.

The solution

Add the possibility of selecting the entire connection as an entry in the Visual SQL recipe. This is already possible when the code recipe is used with :

class dataiku.SQLExecutor2(connection=None, dataset=None)

[Turribeach]

Apologies but I don't understand this idea. Regarding the challenge you can already import datasets in builk using the Dataset => Search and Import => Connection Explorer option. Regarding the solution I am not sure what you mean here. 

[Turribeach]

Apologies but I don't understand this idea. Regarding the challenge you can already import datasets in builk using the Dataset => Search and Import => Connection Explorer option. Regarding the solution I am not sure what you mean here. 

[Mattcadam]

Hi @Turribeach ,

I want to do it the same way as in Python. My need is the following where  I create an SQLExecutor with "MY_CONNECTION" without list the 8 tables.

from dataiku import SQLExecutor2

executor = SQLExecutor2('MY_CONNECTION')

query = """
    SELECT 
        * 
    FROM T1
    LEFT JOIN T2
        ON T1.A = T2.A
    LEFT JOIN T2
        ON T1.A = T3.A
    LEFT JOIN T2
        ON T1.A = T4.A
    LEFT JOIN T2
        ON T1.A = T5.A
    LEFT JOIN T2
        ON T1.A = T6.A
    LEFT JOIN T2
        ON T1.A = T7.A
    LEFT JOIN T2
        ON T1.A = T8.A
"""

df = executor.query_to_df(query=query)

As you said, to do the same in the flow with a SQL Recipe, I can use Connection Explorer and select the 8 tables (And it's needed for visual Recipe).

But my idea is jsut to list the database connection as input.

[Mattcadam]

Hi @Turribeach ,

I want to do it the same way as in Python. My need is the following where  I create an SQLExecutor with "MY_CONNECTION" without list the 8 tables.

from dataiku import SQLExecutor2

executor = SQLExecutor2('MY_CONNECTION')

query = """
    SELECT 
        * 
    FROM T1
    LEFT JOIN T2
        ON T1.A = T2.A
    LEFT JOIN T2
        ON T1.A = T3.A
    LEFT JOIN T2
        ON T1.A = T4.A
    LEFT JOIN T2
        ON T1.A = T5.A
    LEFT JOIN T2
        ON T1.A = T6.A
    LEFT JOIN T2
        ON T1.A = T7.A
    LEFT JOIN T2
        ON T1.A = T8.A
"""

df = executor.query_to_df(query=query)

As you said, to do the same in the flow with a SQL Recipe, I can use Connection Explorer and select the 8 tables (And it's needed for visual Recipe).

But my idea is jsut to list the database connection as input.