This is an advanced topic that assumes that the reader has a solid knowledge of LookML and persistent derived tables (PDTs). To start learning about LookML, see the Get ready for development documentation page. To learn more about PDTs, see the Derived tables in Looker documentation page.

Usage

Definition

The publish_as_db_view parameter is supported only for PDTs that use datagroup or sql_trigger_value caching. PDTs using persist_for aren’t supported.

In addition, the account that Looker uses to connect to your database must have CREATE VIEW permissions for the temporary schema on your database. See the The database connection must have CREATE VIEW permissions section on this page for more information.

The publish_as_db_view parameter lets you flag a PDT for querying outside of Looker. For PDTs with publish_as_db_view set to yes, Looker creates a stable database view on the database for the PDT. The stable database view is created on the database itself, so that it can be queried outside of Looker. The concept of a stable database view is different from a LookML view, since LookML views exist on your Looker instance and not in your database itself.

The stable database view will be published (created) on the next cycle of the Looker regenerator after the PDT’s LookML is deployed to production with publish_as_db_view: yes.

Once Looker creates the stable database view on the database, the PDT must be built before you can query the stable database view on the database.

Example

Create the e_flights_pdt PDT for which a stable database view is created on the database:

The stable database view for e_flights_pdt will be published on the next cycle of the Looker regenerator after the PDT’s LookML is deployed to production. Once Looker builds the PDT, you can then query the stable database view on the database.

Accessing the PDT stable database view

When the stable database view is published, you can query it directly once you get the stable name. There are two ways to get the stable view name for the PDT:

• PDT details panel
• SQL tab of an Explore

PDT details panel

Admins or users with the see_pdts permission can get the stable database view name from the Persistent Derived Tables page in the Admin section of Looker. Click on the PDT’s three-dot menu and select PDT Details:

To query this table directly, just add the scratch schema name before the table name. For example, if the scratch schema name is tmp, you can query the stable database view with a command like this:

SQL tab of an Explore

If you don’t have access to the Persistent Derived Tables admin page, you can use information given on the SQL tab of an Explore to determine the stable view name. The stable view name uses this format:

[scratch schema name].[connection registration key]_[model_name]_[view_name]

For example, if you have a PDT in the model named faa and a view named e_flights_pdt, you only need the scratch schema name and the connection registration key. You can find both from the SQL tab in the Data section of a query on the PDT. In the CREATE TABLE or FROM statement in the SQL tab:

• The connection registration key is two characters; depending on your database dialect, it will follow either a dollar sign or the first underscore in the PDT’s table name.
• The scratch schema name is the beginning of the string following CREATE TABLE or FROM, before the “.”

In all the following examples, the connection registration key is LB, and the scratch schema name is tmp.

Here is an example with a CREATE TABLE statement for a dialect that uses a dollar sign before the connection registration key:

Here is an example with a FROM statement for a dialect that uses an underscore sign before the connection registration key:

Again, the stable database view has the following format:

[scratch schema name].[connection registration key]_[model_name]_[view_name]

Here are all the values:

• [scratch schema name] is tmp
• [connection registration key] is LB
• [model_name] is faa
• [view_name] is e_flights_pdt

This, then, is the PDT’s stable database view name:

tmp.LB_faa_e_flights_pdt

Once you have the stable database view name, you can query it directly. For example:

Things to consider

Some dialects require additional syntax

If the connection registration key begins with a number, some dialects require double quotes, backticks, brackets, or similar syntax around the second part of the stable database view name. For example, Amazon Redshift requires double quotes; so, if your connection registration key begins with a number, this would be the format:

[scratch schema name]."[connection registration key]_[model_name]_[view_name]"

For example, if the connection registration key is 84, you would put quotes around the second part of the stable table name, after the period. So your queries to the database would look something like this:

Consult the documentation for your dialect for information on the specific syntax required.

The database connection must have CREATE VIEW permissions

To create a view on your database, the account that Looker uses to connect to your database must have CREATE VIEW permissions for the database’s temporary schema.

The database username, password, and temporary schema are configured when you connect Looker to your database.

You can test your connection to see if stable view names are supported for your connection. If your dialect supports stable view names and if PDTs are enabled on the connection, Looker will test the connection to see if it will allow stable views:

• If the connection allows stable views, the connection test will return a result such as Can use stable views in temp schema "docsexamples_scratch" in database "flightstats".
• If the connection doesn’t allow stable views, the connection test will return a result such as Cannot use stable views in temp schema "docsexamples" in database "flightstats", along with further details such as CREATE VIEW command denied to user 'docsexamples'.

Dialect support for the PDT stable database view

The ability to create a PDT stable database view depends on the database dialect your Looker connection is using. In Looker 22.6, the following dialects support the PDT stable database view: