User Guide Getting Started Help Center Documentation Community Training
New LookML
Old LookML
New LookML
Looker
  
English
Français
Deutsch
日本語
sql_trigger_value

Usage

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

Hierarchy

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, as described on this page about caching.

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:

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

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 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 information about persist_for here.

Top