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

Options
Tim86
Tim86 Dataiku DSS Core Concepts, Registered Posts: 1 ✭✭✭

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
    tim-wright Partner, L2 Designer, Snowflake Advanced, Neuron 2020, Registered, Neuron 2021, Neuron 2022 Posts: 77 Partner
    Options

    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.

  • Marlan
    Marlan Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Dataiku Frontrunner Awards 2021 Participant, Neuron 2023 Posts: 316 Neuron
    Options

    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
    tim-wright Partner, L2 Designer, Snowflake Advanced, Neuron 2020, Registered, Neuron 2021, Neuron 2022 Posts: 77 Partner
    Options

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

Setup Info
    Tags
      Help me…