How to manually rebuild persistent derived tables

done
low_priority
reply

#1

It’s usually best to rebuild persistent derived tables on a consistent schedule using sql_trigger_value. However, sometimes you might want to manually rebuild a derived table, or a set of them, right when you want to query them.

This can be done by:

  1. Creating a query that uses the persistent derived table(s) that you would like to rebuild.

  2. Clicking Rebuild Derived Tables and Run in the gear dropdown.

Note: for Looker-hosted instances, this option will only work if the table takes less than 1 hour to rebuild (due to browser timeouts).

This will rebuild all derived tables directly referenced in the query, as well as all derived tables those derived tables refer to. Read more about how Rebuild Derived Tables and Run works here.

For Long Running Queries

If your PDT takes longer than 1 hour to build, you can manually rebuild by following these steps:

  1. Go into dev mode

  2. Make a small change to the SQL of the derived table. This could be adding a comment in the body of the SQL block (be sure to comment in the format allowed by your dialect of SQL).

  3. Commit and Deploy this small change to production.

  4. Looker’s PDT runner will notice the change to the SQL and start building a new version of the table within about 5 minutes (as long as there are no other PDTs being built at the same time).

Your PDT will now rebuild in the background. This method will only rebuild the table that was modified- not any dependent tables.

Note that this will cause the PDT to be unavailable to all users until it finishes rebuilding.


(romain.ducarrouge) #2

With the version 4.18 of Looker, only users with the develop permission will see the Rebuild Derived Tables & Run option.
Looker 4.18 Release Notes


(Alessandro Puccetti) #3

Is it possible to trigger a rebuilt using an API call?


(Izzy) #4

I don’t believe it is possible to do that— You can only manually rebuild a derived table by hitting the “Rebuild Derived Tables & Run” button, or by editing the sql of the derived table and deploying to production.


(Izzy) #5

Hmm actually, I just second guessed myself. What if you have a look that runs off of the PDT, and then you hit the run_look endpoint with rebuild_pdts set to TRUE? That would rebuild them… It could be a super simple one-off look just for purposes of refreshing.