Usage
view: my_view { derived_table: { materialized_view: yes ... } }
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
|
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.
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
}
}
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
.
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
To use materialized views in your Looker project, you need the following:
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:
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"
.CREATE VIEW
permission, the connection test will return a result such as Can use stable views in temp schema "docsexamples_scratch" in database "flightstats"
.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:
materialized_view: yes
statement also has a datagroup, sql_trigger_value
, or persist_for
parameter, the materialized_view: yes
statement will take precedence.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.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 |
Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
Last updated 2024-05-14 UTC.