Looker has featured Persistent Derived Tables (PDTs) for a number of years. This feature allowed Looker admins to specify queries that should be pre-computed on a regular basis and stored in the data warehouse as a performance improvement to any dashboards and queries relying on that data.
PDTs also had the side benefit of allowing other services in the database to take advantage of Looker queries by simply referencing the named table. However, by quirk of their implementation, this name would change whenever the the underlying LookML definition of the table was updated, breaking any downstream systems (such as Census) relying on that table.
Looker 7 was released in January 2020 with a fix to this: publish_as_db_view. This new PDT functionality creates a new view that acts as a redirect of sorts. When the table definition and name is updated, Looker PDTs with publish_as_db_view automatically update the view to point at the updated table, giving you a stable name that other systems can reference without fear of things breaking.
This article will guide you through the steps of turning on publish_as_db_view.
- First, in the derived_table section of your Looker View, add the line publish_as_db_view: yes.
- Next, make sure you're using either sql_trigger_value or datagroup_trigger to determine when your PDTs should be regenerated. persist_for is not supported.
- Trigger a rebuild of your view by navigating to it in Explore and then picking Rebuild Derived Tables & Run from the gear menu in the top right.
At this point, you should have your new Looker PDT view ready to be used by Census. However finding it is a bit difficult. You'll need four pieces of information:
[scratch schema name].[connection registration key]_[model_name]_[view_name]
The easiest place to find most of this is by switching to the SQL tab of a Looker view which shows the table being generated. In my case it's CREATE TABLE LOOKER_SCRATCH.LR$KW50C1583200743408_sessions
- scratch schema name is the first part of the text immediately after CREATE TABLE. In this case, it's LOOKER_SCRATCH.
- connection registration key is the first two letters immediately after the LR_ or LR$. In this case, it's KW.
- model_name you'll need to look for separately. In Developer mode, looking at the LookML project, it will be the name model that contains this view. In my case, it's my_looks.model.
- view_name is the model name trailing the above generated table. It should be everything after the first _ after all the numbers and letters. In this case it's sessions.
With all that put together, you should now be able to reference your Looker Views directly from SQL without worrying about breaking things. If you've given Census access to your Looker Scratch Schema, you can browse directly to the view when creating a sync. Otherwise, you can create a model that pulls from your table by using the name you've put together. In this case, it would be:
SELECT * FROM LOOKER_SCRATCH.KW_my_looks_sessionsThat's all you need to do! Looker and Census will work great together after you're all done thanks to new publish_as_db_view feature. Happy Lookering!