Getting started with Looker

Best Practices

Documentation

Community

Training

Certification

Articles in this section

See more

sql_trigger_value

Avatar
Looker Support
  • Updated
Follow

Usage

view: my_view {
  derived_table: {
    sql_trigger_value: SELECT CURDATE() ;;
    …
  }
}

Hierarchy

view
derived_table
sql_trigger_value

Default Value

None

Accepts

A SQL statement that results in one row and one column

Definition

Consider instead using a datagroup and datagroup_trigger, described on the Caching queries and rebuilding PDTs with datagroups documentation page.

sql_trigger_value lets you trigger the regeneration of a persistent derived table based on a SQL statement that you provide. If the result of the SQL statement is different from the previous value, the PDT is regenerated.

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.

Looker does not perform time zone conversion for sql_trigger_value. When you use sql_trigger_value to trigger a PDT rebuild at midnight or at a specific time of day, the trigger will occur in the time zone your database is configured for.

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 drop-down menu, which you’ll find in the upper right of the screen after running a query:

See the Derived tables in Looker documentation page for further details about the Rebuild Derived Tables & Run option.

Examples

Create a PDT on MySQL that rebuilds once per day at midnight:

view: clean_events { derived_table: { sql: SELECT * FROM events WHERE type NOT IN ('test', 'staff') ;; sql_trigger_value: SELECT CURDATE() ;; } }

The following sections show SQL to use for various PDT rebuilding strategies on different dialects:

MySQL

Desired Regeneration Schedule SQL to Use
Once per day at midnight SELECT CURDATE()
Once per day at a specific hour
Coordinated Universal Time (UTC)		 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
Coordinated Universal Time (UTC)		 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

BigQuery

Desired Regeneration Schedule SQL to Use
Once per day at midnight Pacific Time SELECT FORMAT_TIMESTAMP('%F', CURRENT_TIMESTAMP(), 'America/Los_Angeles')
Once per day at a specific hour SELECT FLOOR(((TIMESTAMP_DIFF(CURRENT_TIMESTAMP(),'1970-01-01 00:00:00',SECOND)) - 60*60*3)/(60*60*24))
Replace the “3” with the hour of day you would like the regeneration to occur
Every hour SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP())
Every X hours SELECT FLOOR((TIMESTAMP_DIFF(CURRENT_TIMESTAMP(),'1970-01-01 00:00:00',SECOND)) / (2*60*60))
Replace the “2” with the number of hours you would like between each regeneration

Snowflake

Desired Regeneration Schedule SQL to Use
Once per day at midnight SELECT CURRENT_DATE()
Once per day at a specific hour
Coordinated Universal Time (UTC)		 SELECT FLOOR((DATE_PART('EPOCH_SECOND', CURRENT_TIMESTAMP) - 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(CURRENT_TIME())
Every X hours
Coordinated Universal Time (UTC)		 SELECT FLOOR(DATE_PART('EPOCH_SECOND', CURRENT_TIMESTAMP) / (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

Using sql_trigger_value will cause LookML validation errors unless you have enabled persistence for derived tables in your database connection settings. Most customers do set up persistent derived tables when they initially configure a database connection. The most common exception to this rule is for customers that connect Looker to a PostgreSQL read-only, hot-swap secondary.

sql_trigger_value works differently between Development Mode and Production Mode

sql_trigger_value should work as expected in Production Mode. 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 the Persisted tables in Development Mode section of the Derived tables in Looker documentation page for more information.