how to insert into sql table details like ${scenarioName},${triggerName},${firstFailedStepName}?

sonal_18

i want to insert default variable values from scenario runs into SQL table. But im facing issue when I'm using insert into table ("Scenario_Name") values ( ${scenarioName});

how can i insert the scenarioName inside a sql table?

Sarina

Hi @sonal_18
,

There are some scenario variables available within the scenario SQL step, but Scenario Name + Trigger name are not available. The ones that are available are the same ones that are returned if you run a Python step with code that calls get_all_variables()

from dataiku.scenario import Scenario
scenario = Scenario()
print(scenario.get_all_variables())

So you can use this to check what variables are available. If the variable is available, your approach should work fine to call the interpolated variable, i.e.:

For other fields, like scenario name and trigger name you could either:

1. define them in an earlier Python scenario step to then use in your SQL step
2. simply use a Python step to both pull the values you want and utilize the the execute_sql() command in the Python step to execute your SQL insert command. This will allow you to pull all fields from the Python API that are available from within a scenario. Here is an example of this approach using the following Python code in a Python step:

import dataiku
from dataiku.scenario import Scenario

scenario = Scenario()

trigger_name = scenario.get_trigger_name()
# scenario name is not pullable directly, but scenario ID can be pulled with the following. You can pull the scenario from the ID if needed as well
scenario_id = scenario.scenario_trigger['scenarioId']
value_string = f"('{scenario_id}','{trigger_name}')" 

# write your query
sql_query = f"insert into scenario_metadata values {value_string}"
# execute query
scenario.execute_sql('postgres', sql_query)

The above string interpolation requires Python 3.

Let me know if you have any questions about this approach!

Thank you,
Sarina