Retrieve the result of a scenario's SQL query change trigger

Tim86

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?

tim-wright

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

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

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