home User Guide Getting Started Help Center Documentation Community Training Certification
menu
close
settings
Looker keyboard_arrow_down
language keyboard_arrow_down
English
Français
Deutsch
日本語
search
print
publish_as_db_view

This is an advanced topic that assumes 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 Using Derived Tables documentation page.

Usage

view: my_view {
  derived_table: {
    publish_as_db_view: yes
    …
  }
}

Hierarchy

publish_as_db_view

Default Value

no

Accepts

A Boolean (yes or no)

Special Rules

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.

The publish_as_db_view parameter allows you to 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) when 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:

view: e_flights_pdt { derived_table: { publish_as_db_view: yes datagroup_trigger: e_flights_default_datagroup explore_source: ontime { timezone: "America/Los_Angeles" column: flight_num {} column: carrier {} column: arr_date {} } } dimension: flight_num {} dimension: carrier {} dimension: arr_date { type: date } }

The stable database view for e_flights_pdt will be published when 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

Once the stable database view is published, you can query it directly. To get the name of the stable database view, use the format:

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

Let’s say we have a PDT in the model named faa and the view named e_flights_pdt. Now we just 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. Look in the CREATE TABLE or FROM statement in the SQL tab, as highlighted in the following examples:

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:

So, going back to the stable database view format:

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

We have all of the values:

So now we can put all the pieces together to get 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:

SELECT * from tmp.LB_faa_e_flights_pdt

Things to Consider

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, the format would be:

[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:

SELECT * from tmp."84_faa_e_flights_pdt"

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

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 the most current Looker release, the following dialects support the PDT stable database view:

Top