I have a SQL query change trigger on a scenario to start my scenario when it detects a change in the result of the query. I would like to take the result of that query and use it in one of the steps, but I can't figure out how. My workaround is to run the same query again as the first step (an Execute SQL step). Then I use parseJson(stepoutput_<step name>) to get the value from that query. However, I'd rather only run the query once instead of twice, if I could retrieve the result from the trigger's query. Is there another variation of parseJson which could do that?
That's a good question, @Tim86 . I was unaware so did some digging. According to the section on Trigger parameters here: https://doc.dataiku.com/dss/latest/scenarios/triggers.html#trigger-parameters It states that the results of the trigger query are passed as parameters that can be accessed in scenario steps.
As far as I know, the only way to access this information, is via that Dataiku API in a subsequent step (in a custom Python script). The only relational "database" I have configured is Snowflake and it does NOT appear to be returning the query results as I would expect. Here are some screenshots of what I did - maybe it will work for your scenario?
SQL Query Trigger:
Subsequent Python Step (printing out the Trigger Parameters)
Log File (circling the output of the print statement - there are keys for "columns" and "rows" but no values. Incidentally the "hasResultSet" key is set to False)
@Marlan appears to have much more knowledge than I on this subject (see his post here: https://community.dataiku.com/t5/Using-Dataiku-DSS/Scenario-Custom-Trigger-Tips/m-p/5561#M3482 where he details adding custom parameters to custom triggers that can be accessed in a similar fashion to above). He may have more insight than I do on your specific problem.
Unfortunately my post is pretty much the extent of my knowledge on the particular topic of accessing a trigger value in a scenario step.
It certainly does seem reasonable that you would be able to capture sql query trigger values using get_trigger_params(). But yeah the test didn't appear to bear that out.
Wondering what one would see via get_trigger_params() if the sql query trigger just returned just one row. When I've used these, I typically write the query to return one column and one row (e.g., select count(*) or select max(some_date)).
It's possible that the value might get captured in that situation.