Add support for Connection as SQL Recipe Input

Options
Mattcadam
Mattcadam Registered Posts: 2
edited July 16 in Product Ideas

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)

3
3 votes

New · Last Updated

Comments

  • Turribeach
    Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 1,737 Neuron
    Options

    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
    Mattcadam Registered Posts: 2
    edited July 17
    Options

    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).

    Capture d'écran 2024-01-30 144516.png

    But my idea is jsut to list the database connection as input.Capture d'écran 2024-01-30 144938.png

Setup Info
    Tags
      Help me…