Does `sql_trigger` work for data groups used normal explores?

Ryan_PS
Participant III

Does `sql_trigger` work for data groups used in normal explores, or just PDTs?

For a normal explore (not a PDT), I’ve been testing the below `sql_trigger` which should pretty much refresh every time I refresh the dashboard since the value returned will always change, being that it’s a timestamp.

datagroup: product_page_daily {
  sql_trigger: SELECT CURRENT_TIMESTAMP() ;;
}

However, it still keeps using the cache:

de8579ab-53ab-4fdb-b6f3-8343963b1077.png
Solved Solved
0 6 835
1 ACCEPTED SOLUTION

Ryan_PS
Participant III

Yeah, it’s set to the default:

408987fa-35d3-43ca-8306-e2a4ad80b2e5.png

Therefore, I agree–it should be resetting every 5 minutes when the sql trigger is run.

Update: You know what, I think this is because I didn’t have my change the sql_trigger published. I’m guessing it needs to be on the master branch since the sql_triggers are run externally by a cron job (instead of based on my branch’s code, probably). I will test this.

View solution in original post

6 REPLIES 6

Ryan_PS
Participant III

Does anyone know the answer to this question–whether `sql_trigger` works for explores or not? The `sql_trigger` documentation seems to only mention PDTs: https://docs.looker.com/reference/model-params/datagroup#sql_trigger

I see that my original post was wrong in assuming that it runs the `sql_trigger` every time a dashboard loads. Instead, the documentation indicates that the `sql_trigger` query runs, by default, every 5 minutes: https://docs.looker.com/setup-and-management/connecting-to-db#pdt_maintenance_schedule

Regardless, I still can’t seem to get an explore’s cache to reset based on a `sql_trigger` query.

antho
Participant IV

Hello,

Have you added a persist_wth parameter in your explore definition in the model file?

https://docs.looker.com/reference/explore-params/persist_with

Normally, once you add a persist with: datagroup_name to your explore definition, your explore will start using the caching policy setup in your datagroup sql trigger.

Regarding update frequency, datagroups are managed by the Looker regenerator which indeed run all sql triggers each 5 minutes (this default CRON is configurable in the connection settings). Everytime the value of the sql query changes, the cache gets invalidated and queries from your explores will ping the database again.

Antho

Ryan_PS
Participant III

Yep, already have `persist_with`. I currently have `max_cache_age` set to 24 hours, but with the `sql_trigger`, it should’ve reset long ago. The cache is currently 12 hours old, but should’ve been reset several times since:
 

4732c05c-2108-4fac-a523-c168fd74624d.png

antho
Participant IV

Have you checked the configuration of the datagroup/PDT settings in your connection ? In my case, the CRON is setup to refresh each 15min

7a5aed0b-8297-49aa-9788-d40e39326bd1.png

https://docs.looker.com/setup-and-management/connecting-to-db

Because given your SQL trigger, it should invalidate the cache at every run so I would also check the datagroup status page to see what happens : 

075a87d5-c8e5-43d6-a857-a052bf2b2f9a.png

Ryan_PS
Participant III

Yeah, it’s set to the default:

408987fa-35d3-43ca-8306-e2a4ad80b2e5.png

Therefore, I agree–it should be resetting every 5 minutes when the sql trigger is run.

Update: You know what, I think this is because I didn’t have my change the sql_trigger published. I’m guessing it needs to be on the master branch since the sql_triggers are run externally by a cron job (instead of based on my branch’s code, probably). I will test this.

antho
Participant IV

oh yes! it makes sense, this should be in production to work!

Top Labels in this Space
Top Solution Authors