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

sonal_18 Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Adv Designer, Registered, Frontrunner 2022 Participant Posts: 12 ✭✭✭✭

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
    Sarina Dataiker, Dataiku DSS Core Designer, Dataiku DSS Adv Designer Posts: 315 Dataiker

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

    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 dataikufrom dataiku.scenario import Scenarioscenario = 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 wellscenario_id = scenario.scenario_trigger['scenarioId']value_string = f"('{scenario_id}','{trigger_name}')"# write your querysql_query = f"insert into scenario_metadata values {value_string}"# execute queryscenario.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,

Setup Info
      Help me…