Community Conundrum 28: News Engagement is live! Read More

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

Level 1
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?

0 Kudos
3 Replies
Neuron
Neuron

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:

Trigger.PNG

Subsequent Python Step (printing out the Trigger Parameters)

Scenario Step.PNG

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)

Log.PNG

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

 

 

Neuron
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

Neuron
Neuron

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

 

A banner prompting to get Dataiku DSS