SQL Queries and SQL Templates

djoguns Registered Posts: 11 ✭✭✭✭


Is there a way to use multiple SQL scripts stored in Managed Folder with our exposed database on DSS (or with SQL Recipe).

The reason for this is simply because we don't want to hardcode our query date ranges. We want to have as much flexibilities and automated approach as much as possible. Kindly help out if there is some alternative to do this.

Outside of DSS, we have used this jinjasql to create our templates and connect with pyodbc via python. Unfortunately, pyodbc is not possible on DSS.

JBDC - https://pypi.org/project/JayDeBeApi/ was able to install in our env, but we can't find any sample connection mechanism for this from DSS docs. It would be nice to connect via python recipe for this.

Any suggestion or recommendation would be greatly appreciated.


Best Answer

  • Marlan
    Marlan Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Dataiku Frontrunner Awards 2021 Participant, Neuron 2023 Posts: 317 Neuron
    Answer ✓

    Hi @djoguns

    I have a couple of suggestions for you.

    First, you are aware of the DSS project variables functionality? The following links shows how they can be used in SQL code scripts: https://doc.dataiku.com/dss/latest/code_recipes/variables_expansion.html

    You define them from Variables on the three dot menu. I use variables all the time to specify date ranges. I usually reference the variables in code without the singe quotes. This means I have to include the quotes in the variable value but also means I can use CURRENT_DATE or other expressions as the variable value without changing my SQL code.

    Second, here are some information on working with SQL from Python: https://community.dataiku.com/t5/Online-Events/Using-the-Dataiku-DSS-Python-API-for-Interfacing-with-SQL/ba-p/7797 This includes some tips for using SQLExecutor2 in particular.

    Finally, since you mentioned using jinjasql, I'm wondering what you think about the idea of Dataiku building in support of jinja templates within SQL recipes. I have proposed this to Dataiku as I think it'd really extend the functionality of SQL recipes.



  • djoguns
    djoguns Registered Posts: 11 ✭✭✭✭
    edited July 17


    I have just discovered the use of:

    from dataiku.core.sql import SQLExecutor2

    This works magic for my purpose.

    Other suggestions welcome though.


  • djoguns
    djoguns Registered Posts: 11 ✭✭✭✭

    Thanks Marlan.

    The SQLExecutor2 is magical.

    I have not seen the DSS jinja templates within SQL recipe but the jinjasql does the task for me.

    Many thanks for your response.

Setup Info
      Help me…