Sign up to take part
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
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.
Hi @Tim86 and @tim-wright,
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.
Marlan
@Marlan That was my initial thought also.
The first tested trigger on my end was something like:
select max(Field) from Table.
Unfortunately it produced the same.