materialized_view

Usage

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

Hierarchy
view
derived_table
materialized_view
Default Value
no

Accepts
A Boolean (yes or no)

Special Rules
materialized_view is supported only on specific dialects

Definition

The materialized view functionality is an advanced feature. Depending on your dialect, a materialized view can consume large resources, so it is important that you understand your dialect's implementation of materialized views. See your dialect's documentation for information on the behavior of your dialect and the frequency with which the dialect refreshes data for materialized views.

Materialized views allow you to leverage your database's functionality to persist derived tables in your Looker project. If your database dialect supports materialized views and your Looker connection is configured with the Persistent Derived Tables option enabled, you can create a materialized view by specifying materialized_view: yes for a derived table. Materialized views are supported for both native derived tables and SQL-based derived tables.

Similar to a persistent derived table (PDT), a materialized view is a query result that is stored as a table in the scratch schema of your database. The key difference between a PDT and a materialized view is in how tables are refreshed:

  • For PDTs, the persistence strategy is defined in Looker, and the persistence is managed by Looker.
  • For materialized views, the database is responsible for maintaining and refreshing the data in the table.

For this reason, the materialized view functionality requires advanced knowledge of your dialect and its features. In most cases, your database will refresh the materialized view any time the database detects new data in the tables that are queried by the materialized view. Materialized views are optimal for scenarios that require real-time data.

If a derived table with a materialized_view: yes statement also has a datagroup, sql_trigger_value, or persist_for parameter, the materialized_view: yes statement will take precedence.

Example

This e_flights_pdt derived table has the statement materialized_view: yes, so a materialized view is created in the database's scratch schema:


view: e_flights_pdt {
  derived_table: {
    materialized_view: yes
    explore_source: ontime {
      column: flight_num {}
      column: carrier {}
      column: arr_date {}
    }
  }
  dimension: flight_num {}
  dimension: carrier {}
  dimension: arr_date {
    type: date
  }
}

When Looker creates the materialized view

Looker generates materialized views in the same way as other PDTs. If you create the materialized view and query it in Development Mode, Looker will create a development version of the materialized view, which can be used for production as well. See the Persisted tables in Development Mode section on the Derived tables in Looker documentation page for details.

Otherwise, the materialized view is created during the Looker regenerator's next cycle, after the associated derived table's LookML is deployed to production with materialized_view: yes.

Stable database views for materialized views

Looker automatically creates a stable database view for each materialized view. The stable database view is created on the database itself, so that it can be queried outside of Looker. This is the same stable view functionality that is used with the publish_as_db_view parameter.

Looker creates the stable view during the Looker regenerator's next cycle, after the materialized view's LookML is deployed to production. Once the stable database view is published, you can query it directly.

Admins or users with the see_pdts permission can get the stable database view name from the PDT Details modal on the Persistent Derived Tables page in the Admin section of Looker.

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


SELECT * from tmp.NN_e_redflight_e_redflight_publish_as_db

Requirements for materialized views

To use materialized views in your Looker project, you need the following:

  • A database dialect that supports materialized views. See the Dialect support for materialized views section on this page for the list of dialects that support materialized views.
  • A scratch schema on your database. This can be any schema on your database, but we recommend creating a new schema that will be used only for this purpose. Your database administrator must configure the schema with write permission for the Looker database user.
  • A Looker connection that is configured with the Persistent Derived Tables option enabled. This is usually set up when you initially configure your Looker connection (see the Looker dialects documentation page for instructions for your database dialect), but you can also enable PDTs for your connection after the initial setup.
  • A Looker connection with the CREATE TABLE permission for the temporary schema on your database. This is the same permission that is required for creating PDTs. In addition, to create the stable database view for the materialized view, the connection must have CREATE VIEW permissions for the temporary schema on your database. You can test the connection to verify that the connection has these permissions:
    • If PDTs are enabled on the connection, and the connection has the CREATE TABLE permission, the connection test will return a result such as Can use persistent derived tables in temp schema "docsexamples_scratch" in database "demo_db".
    • If the connection allows stable views and the connection has the CREATE VIEW permission, the connection test will return a result such as Can use stable views in temp schema "docsexamples_scratch" in database "flightstats".

Important considerations for materialized views

With materialized views, Looker does not maintain and refresh the data in the table. For this reason, the materialized view functionality requires advanced knowledge of your dialect and its features. Here are some things to consider when you're creating a materialized view:

  • Some dialects have limitations for materialized views, such as default maximum refresh intervals and support for joins. Looker does not generate LookML errors about dialect-specific functionality of materialized views. Instead, Looker will generate an error if the materialized view fails to build, either as an event in the PDT Event Log or as a runtime error if you try to query the materialized view. Consult your dialect's documentation about limitations for materialized views.
  • Some dialects check for query freshness when materialized views are queried, which may add a small delay to getting query results. Consult your dialect's documentation to see if this is the case for your dialect.
  • Some dialects will attempt to refresh the materialized view incrementally instead of fully rebuilding it. Consult your dialect's documentation for information.
  • If your materialized view uses a base table that is dropped from the database, you may not be able to query the materialized view, and new versions will fail to build.
  • If a derived table with a materialized_view: yes statement also has a datagroup, sql_trigger_value, or persist_for parameter, the materialized_view: yes statement will take precedence.
  • Materialized views support the same dialect-specific parameters that are supported by derived tables in general, such as partitioning, sortkeys, and indexes.
  • In the case of cascading derived tables, materialized views can depend on Looker PDTs, with the following caveats:
    • You cannot use a derived table with the persist_for persistence strategy in the definition of a derived table with materialized_view: yes. For materialized views, the source table for a materialized view must always be present on the database. persist_for derived tables are dropped from your database after the amount of time specified in the persist_for parameter, so they are not guaranteed to be present on the database.
    • PDTs rebuild with a unique name, so if a materialized view uses a PDT in its definition, the materialized view will update to point at the new version of the PDT each time the PDT is rebuilt. This means that the materialized view will essentially rebuild from scratch if a dependency completely rebuilds, which may impact performance. In this case, a better option is to reference a base table that is append-only, or to reference an incremental PDT that is defined using Looker.

Dialect support for materialized views

The ability to make a derived table into a materialized view depends on the database dialect your Looker connection is using. In the current Looker release, the following dialects support materialized views:

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