SQL Queries and SQL Templates

djoguns

Hi,

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.

Thanks.

Marlan

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.

Marlan

djoguns

Addendum:

I have just discovered the use of:

from dataiku.core.sql import SQLExecutor2

This works magic for my purpose.

Other suggestions welcome though.

Cheers.

djoguns

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.