Sign up to take part
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
Retrieve result of an execute sql step of a scenario and assign to variable
Added on November 8, 2020 5:11PM
Likes: 0
Replies: 4
Subha
Registered Posts: 3 ✭✭✭
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