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
materialized_view

add

New in Looker 21.10, Looker supports materialized views for dialects that support them. Materialized views allow you to leverage your database’s functionality to persist derived tables in your Looker project.

Usage

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

Hierarchy

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

To query a materialized view directly, just 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.NN_e_redlook_e_redlook_materialized

Requirements for materialized views

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

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:

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:

Top