sql_trigger_value

LookML
Version

On this Page
Docs Menu

Go to View Parameter List

Usage

sql_trigger_value is a child of
derived_table is a child of
view
view: my_view
derived_table:
...
1st
Tab
2nd
Tab
sql_trigger_value: SELECT ...
a sql query that returns
one row and one column

Definition

Consider instead using datagroups and persist_with, which provide additional functionality.

sql_trigger_value lets you trigger the regeneration of a persistent derived table based on a SQL query that you provide.

The sql_trigger_value parameter will only consider the first row and column in the SQL you write. Therefore, we strongly recommend that you write your query to return just one value (one row and one column). This removes any confusion for future developers and protects non-streaming SQL dialects from loading large result sets into memory.

By default, every 5 minutes Looker runs the SQL query that you write, as long as another persistent derived table is not in the process of being built. If the results of the SQL query change, Looker will re-generate the derived table. You can change this schedule as desired by using the PDT And Datagroup Maintenance Schedule setting in Looker’s admin settings.

For example, suppose you were running MySQL and used:

sql_trigger_value: SELECT_CURDATE()

The results would be like:

sql_trigger_value Run Time sql_trigger_value Result
2015-01-01 00:00 2015-01-01
2015-01-01 00:05 2015-01-01
2015-01-01 00:10 2015-01-01
2015-01-01 23:55 2015-01-01
2015-01-02 00:00 2015-01-02
2015-01-02 00:05 2015-01-02

You can see that the value of this SQL query will change once per day at midnight, so the derived table will be regenerated at these times.

If your admin has given you the develop permission, you can force a derived table to regenerate before its sql_trigger_value query has changed. Select the Rebuild Derived Tables & Run option from the Explore Gear dropdown menu, which you’ll find in the upper right of the screen after running a query:

Examples

MySQL

Desired Regeneration Schedule SQL To Use
Once per day at midnight SELECT CURDATE()
Once per day at a specific hour SELECT FLOOR((UNIX_TIMESTAMP(NOW()) - 60*60*3)/(60*60*24))
Replace the “3” with the hour of day you would like the regeneration to occur
When a particular table is updated SELECT COUNT(*) FROM table
Every hour SELECT HOUR(CURTIME())
Every X hours SELECT FLOOR(UNIX_TIMESTAMP() / (1*60*60))
Replace the “1” with the number of hours you would like between each regeneration
Never update data SELECT 1

Redshift

Desired Regeneration Schedule SQL To Use
Once per day at midnight SELECT CURRENT_DATE
Once per day at a specific hour SELECT FLOOR((EXTRACT(epoch from GETDATE()) - 60*60*3)/(60*60*24))
Replace the “3” with the hour of day you would like the regeneration to occur
When a particular table is updated SELECT COUNT(*) FROM table
Every hour SELECT DATE_PART('hour', GETDATE())
Every X hours SELECT FLOOR(EXTRACT(epoch from GETDATE()) / (1*60*60))
Replace the “1” with the number of hours you would like between each regeneration
Never update data SELECT 1

Postgres

Desired Regeneration Schedule SQL To Use
Once per day at midnight SELECT CURRENT_DATE
Once per day at a specific hour SELECT FLOOR((EXTRACT(epoch from NOW()) - 60*60*3)/(60*60*24))
Replace the “3” with the hour of day you would like the regeneration to occur
When a particular table is updated SELECT COUNT(*) FROM table
Every hour SELECT DATE_PART('hour', NOW())
Every X hours SELECT FLOOR(EXTRACT(epoch from NOW()) / (1*60*60))
Replace the “1” with the number of hours you would like between each regeneration
Never update data SELECT 1

Common Challenges

sql_trigger_value Requires That You Have Set Up Persistent Derived Tables

sql_trigger_value will have no effect unless you have enabled persistence for derived tables on your Looker instance. Most customers do set up persistent derived tables when they initially configure Looker. The most common exception to this rule is for customers that connect Looker to a PostgreSQL read-only, hot-swap slave.

sql_trigger_value Works Differently Between Development Mode And Production Mode

sql_trigger_value should work as expected when in production mode. When in development mode all derived tables are treated as if persist_for: 24 hours has been used, no matter what setting you have implemented. See information about persist_for here.

Still have questions?
Go to Discourse - or - Email Support
Top