Retrieve result of an execute sql step of a scenario and assign to variable
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
Best Answers
-
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])
-
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")
Answers
-
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) -
Thank you so much