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

[Subha]

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": [
    [
      "183448174"
    ]
  ],
  "hasResultset": true 

[fchataigner2 · Accepted solution]

Hi,

with a SQL step setup like this one:

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

(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 · Accepted solution]

Hi,

with a SQL step setup like this one:

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

(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])

[Subha]

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 :

s=Scenario()
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
project1.set_variables(project_variables)

[fchataigner2 · Accepted solution]

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]

Thank you so much 😊