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

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

Best Answers

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

    Hi,

    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 ✓
    Options

    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

  • Subha
    Subha Registered Posts: 3 ✭✭✭
    Options

    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.

    Define_Var.jpg

    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)

  • Subha
    Subha Registered Posts: 3 ✭✭✭
    Options

    Thank you so much

Setup Info
    Tags
      Help me…