Creating a data set using sql with parameter

rakesh99
Level 1
Creating a data set using sql with parameter

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 



 



 



0 Kudos
3 Replies
Alex_Combessie
Dataiker Alumni

 



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

0 Kudos
rakesh99
Level 1
Author
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 ?
0 Kudos
Alex_Combessie
Dataiker Alumni
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.
0 Kudos