Creating a data set using sql with parameter

rakesh99

I am trying to import a dataset from PostgreSql . I have selected the connection and mode is -sql query and wrote my sql . I got the data . In my sql data I have column name called snapshot date .here I don't want to pull the data for all snapshot date .Is there any way where I can pull the data for a single date passing a date parameter ? I dont want it to be hard-coded in sql .it should be dynamic

Now My sql query is -select * from test_table

I want it to be like -select * from test_table where snpsht_date= select a date

Alex_Combessie

Hi,

You can define and use project variables in a SQL statement like described here: https://doc.dataiku.com/dss/latest/advanced/variables_expansion.html?highlight=variables#pig-sql

You can update the variables manually in this screen:

Or use the Dataiku API to automate the updating of the variable:

client = dataiku.api_client()
project = client.get_project(dataiku.default_project_key()) # current proj
variables = project.get_variables()
variables["standard"]["myvar"] = 'myvalue' # example to be changed
project.set_variables(variables)

Then you will reference the variable as ${myvar} in your SQL code.

Hope it helps,

Alex

rakesh99

Thanks for the information ... This is working for me now if pass one date only . is it possible to pass multiple values in a variable . Like i want my result set like
select * from test_table where snpsht_date in ( date 1 ,date 2 ... daten )
I am only able to assign a single value in my variable . ${myvar} .

can you please suggest on this ?

Alex_Combessie

If you define the variables as:
{
"myvar": "(date1, date2, date3)"
}
and then have an SQL recipe like:
select * from test_table where snpsht_date in ${myvar}
it will perform as you described.