Discover this year's submissions to the Dataiku Frontrunner Awards and give kudos to your favorite use cases and success stories!READ MORE

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

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

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?

0 Kudos
1 Reply

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()

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.: 

Screen Shot 2021-09-16 at 5.46.03 PM.png

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,