Retrieve result of an execute sql step of a scenario and assign to variable

Subha Registered Posts: 3 ✭✭✭
edited July 16 in General Discussion

I want to execute an sql in a scenario step: 'select count(*) from table ' and store the result in a variable.

How can I retrieve the result from the json file of the result set ?

Exited the exec sql step {
  "endedOn": 0,
  "success": true,
  "updatedRows": 0,
  "totalRowsClipped": false,
  "totalRows": 1,
  "log": "",
  "columns": [
      "name": "cnt",
      "type": "int",
      "dssType": "INT",
      "sqlType": 4
  "rows": [
  "hasResultset": true 

Best Answers

  • fchataigner2
    fchataigner2 Dataiker Posts: 355 Dataiker
    edited July 17 Answer ✓


    with a SQL step setup like this one:

    Screenshot 2020-11-09 at 09.40.00.png

    then you can retrieve the value of the first column on the first row with a "Define scenario variables" step like

    Screenshot 2020-11-09 at 09.41.52.png

    (note the ".join(',')" at the end of the expression: this is caused by the fact that getPath(...) evaluates a JSONPath expression and thus returns an array of values)

    If the SQL step has a name that is not friendly with variable names (like: contains spaces), you can also use a "Execute Python code" step to do the same, with

    from dataiku.scenario import Scenario
    s = Scenario()
    outputs = s.get_previous_steps_outputs()
    sql_output = [o['result'] for o in outputs if o["stepName"] == 'the_sql_step'][0]
    s.set_scenario_variables(the_first_value = sql_output['rows'][0][0])

  • fchataigner2
    fchataigner2 Dataiker Posts: 355 Dataiker
    Answer ✓

    note that for your use case, you can probably use a "set project variables" step directly (in place of the "define variables" + "execute python code")


  • Subha
    Subha Registered Posts: 3 ✭✭✭

    Thank You. This helped . After defining the scenario variables , I am using the variables in a custom python step and based on the value update the project variable.

    So , the value can be used in other scenarios. Hope, I am in the right way.


    Python Step :

    var1 = s.get_all_variables()['table_count']
    var2 = s.get_all_variables()['duplicate_count']

    #update the project variable with the variables of the scenario
    project1 = client.get_project('PROJ')
    project_variables = project1.get_variables()
    project_variables["standard"]["cnt"] = var1
    project_variables["standard"]["dup"] = var2

  • Subha
    Subha Registered Posts: 3 ✭✭✭

    Thank you so much

Setup Info
      Help me…