Scenario SQL query change trigger

Options
davidmakovoz
davidmakovoz Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2022, Neuron 2023 Posts: 67 Neuron

I already posted this question earlier , but perhaps it wasn't clear, so I'll try to be more precise here.

Custom python trigger code uses t.fire() to trigger the scenario when the condition is met.

What is the equivalent of that t.fire() in the SQL query change trigger?

Thank you

Tagged:

Best Answer

Answers

  • fchataigner2
    fchataigner2 Dataiker Posts: 355 Dataiker
    Options

    Hi,

    a "sql query change" trigger initiates a run of the scenario if it detects a change in the data returned by the query, be it in the number of rows returned or the values returned. Typically, such a trigger is used with a query that aggregates a table, like computing a row count, or a max of a timestamp column.

  • davidmakovoz
    davidmakovoz Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2022, Neuron 2023 Posts: 67 Neuron
    edited July 17
    Options

    @fchataigner2
    thank you for your reply. I don't like creating duplicate entries, but here is what I posted in the first one (link above):

    I have two triggers: a SQL query change trigger and a python Custom trigger.

    They both check the same table dataiku_poc.CMR_CAMP_COPY

    Here is what I have in the SQL query change trigger

    select count(*) from dataiku_poc.CMR_CAMP_COPY;

    Here is what I have in the python Custom Trigger

    import dataiku
    from dataiku import pandasutils as pdu
    import pandas as pd
    from dataiku.scenario import Trigger
    mydataset = dataiku.Dataset("CMR_CAMP_COPY")
    mydataset_df = mydataset.get_dataframe()
    p = dataiku.Project() 
    variables = p.get_variables() 
    CMR_count = int(variables["local"]["CMR_count"])
    t = Trigger()
    new_count = len(mydataset_df)
    if new_count != CMR_count: 
        variables["local"]["CMR_count"] = new_count 
        p.set_variables(variables)    
        t.fire()

    Both triggers have Run every 10 seconds, Grace period 0 seconds.

    Every time I delete or insert rows in the table the python Custom trigger is triggered and the SQL never triggers.

    What I am doing wrong? Is there a way to troubleshoot it?

    Thank you

  • fchataigner2
    fchataigner2 Dataiker Posts: 355 Dataiker
    Options

    the setup of the sql trigger looks fine, so maybe 1) check that the query runs in a notebook and returns results and 2) check the backend.log of the instance for exceptions in case some arise in the context of ActiveTriggerLifecycleThread threads (there is no proper debug of these triggers). You can also check the scenario's charts to see if the only triggers firing are the python ones

  • davidmakovoz
    davidmakovoz Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2022, Neuron 2023 Posts: 67 Neuron
    Options

    I did make sure the SQL runs in the notebook, and I've been constantly checking Last Runs to see if the SQL trigger triggered.

    Where do I find backend.log?

Setup Info
    Tags
      Help me…