publish_as_db_view

Usage

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

- or -

explore
aggregate_table
materialization
publish_as_db_view
Default Value
no

Accepts
A Boolean (yes or no)

Special Rules
  • The stable database view is published (created) on the next cycle of the Looker regenerator after the LookML of the persisted table is deployed to production with publish_as_db_view: yes
  • Stable database views are supported only for persisted tables that use datagroup or sql_trigger_value persistence strategies. Persisted tables that use persist_for aren't supported.
  • Stable database views are supported only for database connections with CREATE VIEW permissions for the temporary schema.

Definition

The publish_as_db_view parameter lets you flag persisted tables (which include both persistent derived tables and aggregate tables for querying outside of Looker. For persisted tables with publish_as_db_view set to yes, Looker creates a stable database view on the database for the persisted table. 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 persisted table's LookML is deployed to production with publish_as_db_view: yes.

Examples

Creating a stable database view for a PDT

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

Creating a stable database view for an aggregate table

Create the weekly_orders aggregate table for which a stable database view is created on the database:

explore: orders {
  aggregate_table: weekly_orders {
    materialization: {
      sql_trigger_value: SELECT CURDATE() ;;
      publish_as_db_view:  yes
    }
    query: {
      dimensions: [orders.status, orders.created_week]
      measures: [orders.count]
    }
  }
}

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

Accessing the stable database view

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

PDT details modal

If you are an admin or a user with the see_pdts permission, you can use the Persistent Derived Tables page in the Admin section of Looker to get the stable database view name for a persisted table:

  1. Click the Looker Main menu icon and select Admin, if the Admin menu isn't already displayed. (If you are in the Explore or Develop section of the Looker Main menu, you may have to click the back arrow to see the Admin menu.)
  2. From the Admin menu, select Persistent Derived Tables.
  3. On the Persistent Derived Tables page, search for the name of your persisted table.
  4. Click the three-dot menu for your persisted table, and then select PDT Details.
  5. In the PDT Details modal, look for the Stable Name field.

To query the stable view table directly, 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:


SELECT * from tmp.CL_e_redlook_e_redlook_inc_pdt

SQL tab of an Explore

If you don't have access to the Persistent Derived Tables admin page, you can use the information that is included on the SQL tab of an Explore to determine the stable view name for a persisted table. The stable view name uses this format:

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

For example, if you have a persisted table that is in the model named faa and a view with the name 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 persisted table. In the CREATE TABLE or FROM statement in the SQL tab, you can find the information as follows:

  • 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 persisted table's table name.
  • The scratch schema name is the beginning of the string following CREATE TABLE or FROM, before the "."

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

Here is an example of a Looker-generated SQL query with a CREATE TABLE statement for a dialect that uses a dollar sign before the connection registration key. The view name is e_flights_pdt, and the generated derived table name is tmp.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 of a Looker-generated SQL query with a FROM statement for a dialect that uses an underscore sign before the connection registration key. The view name is e_flights_pdt, and the generated derived table name is tmp.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] is tmp
  • [connection registration key] is LB
  • [model_name] is faa
  • [view_name] is e_flights_pdt

This, then, is the persisted table'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 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.

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 stable database views

The ability to create a persisted table stable database view depends on the database dialect that your Looker connection is using. In the latest release of Looker, the following dialects support the persisted table stable database view:

Dialect Supported?
Actian Avalanche
Yes
Amazon Athena
Yes
Amazon Aurora MySQL
Yes
Amazon Redshift
Yes
Apache Druid
No
Apache Druid 0.13+
No
Apache Druid 0.18+
No
Apache Hive 2.3+
Yes
Apache Hive 3.1.2+
Yes
Apache Spark 3+
Yes
ClickHouse
No
Cloudera Impala 3.1+
Yes
Cloudera Impala 3.1+ with Native Driver
Yes
Cloudera Impala with Native Driver
Yes
DataVirtuality
No
Databricks
Yes
Denodo 7
No
Denodo 8
No
Dremio
No
Dremio 11+
No
Exasol
Yes
Firebolt
No
Google BigQuery Legacy SQL
No
Google BigQuery Standard SQL
Yes
Google Cloud PostgreSQL
Yes
Google Cloud SQL
No
Google Spanner
No
Greenplum
Yes
HyperSQL
No
IBM Netezza
No
MariaDB
Yes
Microsoft Azure PostgreSQL
Yes
Microsoft Azure SQL Database
Yes
Microsoft Azure Synapse Analytics
Yes
Microsoft SQL Server 2008+
Yes
Microsoft SQL Server 2012+
Yes
Microsoft SQL Server 2016
Yes
Microsoft SQL Server 2017+
Yes
MongoBI
No
MySQL
Yes
MySQL 8.0.12+
Yes
Oracle
Yes
Oracle ADWC
No
PostgreSQL 9.5+
Yes
PostgreSQL pre-9.5
Yes
PrestoDB
Yes
PrestoSQL
No
SAP HANA
Yes
SAP HANA 2+
Yes
SingleStore
Yes
SingleStore 7+
Yes
Snowflake
Yes
Teradata
Yes
Trino
No
Vector
Yes
Vertica
Yes