Retrieve the result of a scenario's SQL query change trigger
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?
Answers
-
tim-wright Partner, L2 Designer, Snowflake Advanced, Neuron 2020, Registered, Neuron 2021, Neuron 2022 Posts: 77 Partner
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. -
Marlan Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Dataiku Frontrunner Awards 2021 Participant, Neuron 2023 Posts: 321 Neuron
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
-
tim-wright Partner, L2 Designer, Snowflake Advanced, Neuron 2020, Registered, Neuron 2021, Neuron 2022 Posts: 77 Partner
@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.