Add support for Connection as SQL Recipe Input
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)
Comments
-
Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 2,112 Neuron
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.
-
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.