Solved

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

  • 16 July 2021
  • 6 replies
  • 43 views

Userlevel 1

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:

 

icon

Best answer by Ryannn29 12 October 2021, 17:17

View original

6 replies

Userlevel 1

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.

 

 

Userlevel 3

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

Userlevel 1

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:
 

 

Userlevel 3

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

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 : 

 

Userlevel 1

Yeah, it’s set to the default:

 

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.

Userlevel 3

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

Reply