publish_as_db_view

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 theGet ready for developmentdocumentation page. To learn more about PDTs, see theDerived tables in Lookerdocumentation page.

Usage

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

Definition

Thepublish_as_db_viewparameter is supported only for PDTs that usedatagrouporsql_trigger_valuecaching. PDTs usingpersist_foraren't supported.

In addition, the account that Looker uses to connect to your database must haveCREATE VIEWpermissions for the temporary schema on your database. See theThe database connection must haveCREATE VIEWpermissionssection on this page for more information.

Thepublish_as_db_viewparameter lets you flag aPDTfor querying outside of Looker. For PDTs withpublish_as_db_viewset toyes,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 astable database viewis different from aLookML 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 theLooker regeneratorafter the PDT's LookML isdeployed to productionwithpublish_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 thee_flights_pdtPDT 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 fore_flights_pdtwill be published on the next cycle of theLooker regeneratorafter 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 modal
• SQL tab of an Explore

PDT details modal

Admins or users with thesee_pdtspermission can get the stable database view name from thePDT Detailsmodalon the Persistent Derived Tables page in theAdminsection of Looker.

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

SELECT * from tmp.CL_e_redlook_e_redlook_inc_pdt

SQL tab of an Explore

If you don't have access to thePersistent Derived Tablesadmin page, you can use information given on theSQLtab 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 namedfaaand a view namede_flights_pdt,you only need the scratch schema name and the connection registration key. You can find both from theSQLtab in theDatasection of a query on the PDT. In theCREATE TABLEorFROMstatement in theSQLtab:

• 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 followingCREATE TABLEorFROM,before the "."

In all the following example queries, the connection registration key isLB,and the scratch schema name istmp.

Here is an example Looker-generated SQL query with aCREATE TABLEstatement for a dialect that uses a dollar sign before the connection registration key. The view name ise_flights_pdt,and the generated derived table name istmp.LR$LBC5Q1576702903774_e_flights_pdt.

generate derived table e_flights_pdt
Building e_flight::e_flights_pdt in dev mode on instance b6ff28049851f1954156526c66ca9912
CREATE TABLE tmp.LR$LBC5Q1576702903774_e_flights_pdt (INDEX(flight_num)) SELECT
ontime.flight_num AS `flight_num`
ontime.carrier AS `carrier,
(TIMESTAMP(DATE (CONVERT_TZ(ontime.arr_time, 'UTC', 'America/Los_Angeles')))) AS
`arr_date``
FROM `flightstats`.`ontime` AS `ontime`
GROUP BY
1,
2,
3
-- finished e_flights_pdt => tmp.LR$LBC5Q1576702903774_e_flights_pdt

SELECT
e_flights_pdt.flight_num AS `e_flights_pdt.flight_num`,
e_flights_pdt.carrier AS `e_flights_pdt.carrier`
FROM tmp.LR$LBC5Q1576702903774_e_flights_pdt AS e_flights_pdt
GROUP BY
1,
2
ORDER BY
e_flights_pdt.flight_num
LIMIT 5

Here is an example Looker-generated SQL query with aFROMstatement for a dialect that uses an underscore sign before the connection registration key. The view name ise_flights_pdt,and the generated derived table name istmp.LR_LBFIM1580333699953_e_flights_pdt.

--use existing e flights pdt in tmp.LR_LBFIM1580333699953_e_flights_pdt

SELECT
e_flights_pdt.id AS `e_flights_pdt.id`,
DATE(e_flights_pdt.faa_event_date) AS `e_flights_pdt. faa_event_date`
FROM tmp.LR_LBFIM1580333699953_e_flights_pdt AS e_flights_pdt

GROUP BY 1,2
ORDER BY DATE(e_flights_pdt.faa_event_date) DESC
LIMIT 5

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]istmp
• [connection registration key]isLB
• [model_name]isfaa
• [view_name]ise_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:

SELECT * from tmp.LB_faa_e_flights_pdt

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 is84,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.

The database connection must haveCREATE VIEWpermissions

To create a view on your database, the account that Looker uses to connect to your database must haveCREATE VIEWpermissions for the database's temporary schema.

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

You cantest your connectionto see if stable view names are supported for your connection. If yourdialect supports stable view namesand ifPDTs 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 asCan 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 asCannot use stable views in temp schema "docsexamples" in database "flightstats",along with further details such asCREATE 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 the latest release of Looker, the following dialects support the PDT stable database view: