Knowledge Drop

How to Persist Derived Tables in Looker


Userlevel 2

Last tested: Jul 1, 2019
 

In order to persist tables in Looker, PDTs must be enabled for the connection and the scratch schema must be set up according to the database configuration instructions.

Once the scratch schema has been set up and the connection allows for PDTs, derived tables can be persisted by adding one of the below LookML parameters to your derived_table definition.

  • persist_for
    • persist_for lets you set the maximum amount of time that a persistent derived table may be used before it is regenerated. Once the maximum amount of time has been reached, the PDT is dropped from the scratch schema.
    • When a user runs a query that relies on a persist_for derived table, Looker checks the age of the table against persist_for. If the age is greater than the persist_for setting, the derived table is regenerated before the query is run. If the age is less than the persist_for setting, the existing derived table is used.
  • sql_trigger_value
    • 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.
  • datagroup_trigger
    • datagroup_trigger lets you specify the datagroup to use as a caching policy for the derived table.

 

This content is subject to limited support.                

 


0 replies

Be the first to reply!

Reply