Sign up to take part
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
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
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")
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])
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)
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")
Thank you so much 😊