Creating a data set using sql with parameter

Options
rakesh99
rakesh99 Registered Posts: 5 ✭✭✭✭

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

Tagged:

Answers

  • Alex_Combessie
    Alex_Combessie Alpha Tester, Dataiker Alumni Posts: 539 ✭✭✭✭✭✭✭✭✭
    Options

    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
    rakesh99 Registered Posts: 5 ✭✭✭✭
    Options
    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
    Alex_Combessie Alpha Tester, Dataiker Alumni Posts: 539 ✭✭✭✭✭✭✭✭✭
    Options
    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.
Setup Info
    Tags
      Help me…