> [!NOTE]
> **Note:** This is an advanced topic that assumes that the reader has a solid knowledge of LookML, aggregate tables, and persistent derived tables (PDTs). To start learning about LookML, see the [Get ready for development](https://docs.cloud.google.com/looker/docs/2520/data-modeling) documentation page. To learn about aggregate tables, see the [Aggregate Awareness](https://docs.cloud.google.com/looker/docs/2520/aggregate_awareness#adding_aggregate_tables_to_your_project) documentation page. To learn more about PDTs, see the [Derived tables in Looker](https://docs.cloud.google.com/looker/docs/2520/derived-tables#persistent-derived-tables) documentation page.

## Usage

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

|---|---|
| Hierarchy [View File](https://docs.cloud.google.com/looker/docs/2520/reference/param-view) [`view`](https://docs.cloud.google.com/looker/docs/2520/reference/param-view-view) [`derived_table`](https://docs.cloud.google.com/looker/docs/2520/reference/param-view-derived-table) `publish_as_db_view` - or - [Model File](https://docs.cloud.google.com/looker/docs/2520/reference/param-model) [`explore`](https://docs.cloud.google.com/looker/docs/2520/reference/param-explore-explore) [`aggregate_table`](https://docs.cloud.google.com/looker/docs/2520/reference/param-explore-aggregate-table) [`materialization`](https://docs.cloud.google.com/looker/docs/2520/reference/param-explore-aggregate-table#materialization) `publish_as_db_view` | Default Value `no` <br /> Accepts A Boolean (`yes` or `no`) <br /> Special Rules - The stable database view is published (created) on the next cycle of the [Looker regenerator](https://docs.cloud.google.com/looker/docs/2520/derived-tables#the_looker_regenerator) after the LookML of the persisted table is [deployed to production](https://docs.cloud.google.com/looker/docs/2520/version-control-and-deploying-changes#deploying_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](https://docs.cloud.google.com/looker/docs/2520/derived-tables#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

> [!NOTE]
> **Notes:**
>
> - The `publish_as_db_view` parameter is supported only for persisted tables that use the datagroup or `sql_trigger_value` [persistence strategies](https://docs.cloud.google.com/looker/docs/2520/derived-tables#persistence_strategies). Persisted tables that use `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 database connection must have `CREATE VIEW` permissions](https://docs.cloud.google.com/looker/docs/2520/reference/param-view-publish-as-db-view#create_view) on this page for more information.

The `publish_as_db_view` parameter lets you flag persisted tables (which include both [persistent derived tables](https://docs.cloud.google.com/looker/docs/2520/derived-tables#persistent-derived-tables) and [aggregate tables](https://docs.cloud.google.com/looker/docs/2520/aggregate_awareness#adding_aggregate_tables_to_your_project) 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](https://docs.cloud.google.com/looker/docs/2520/derived-tables#the_looker_regenerator) after the persisted table's LookML is [deployed to production](https://docs.cloud.google.com/looker/docs/2520/version-control-and-deploying-changes#deploying_to_production) with `publish_as_db_view: yes`.

> [!NOTE]
> **Note:** Once Looker creates the stable database view on the database, the persisted table must be built before you can query the stable database view on the database.

## 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](https://docs.cloud.google.com/looker/docs/2520/derived-tables#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](https://docs.cloud.google.com/looker/docs/2520/derived-tables#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](https://docs.cloud.google.com/looker/docs/2520/reference/param-view-publish-as-db-view#pdt_details_modal)
- [SQL tab of an Explore](https://docs.cloud.google.com/looker/docs/2520/reference/param-view-publish-as-db-view#sql_tab_of_an_explore)

### PDT details modal

If you are an admin or a user with the [`see_pdts`](https://docs.cloud.google.com/looker/docs/2520/admin-panel-users-roles#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](https://docs.cloud.google.com/looker/docs/2520/admin-panel-database-pdts#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](https://docs.cloud.google.com/looker/docs/2520/connecting-to-your-db).

You can [test your connection](https://docs.cloud.google.com/looker/docs/2520/admin-panel-database-connections#testing_connections) to see if stable view names are supported for your connection. If your [dialect supports stable view names](https://docs.cloud.google.com/looker/docs/2520/reference/param-view-publish-as-db-view#dialect_support) and if [PDTs are enabled on the connection](https://docs.cloud.google.com/looker/docs/2520/connecting-to-your-db#persistent_derived_tables), 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 |
| Amazon Redshift 2.1+ | Yes |
| Amazon Redshift Serverless 2.1+ | 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 \& 9 | No |
| Dremio | No |
| Dremio 11+ | No |
| Exasol | Yes |
| 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 |