Introduction to materialized views

Materialized views are precomputed views that periodically store the results ofa SQL query. Materialized views can reduce the total processing time and chargesrelated to querying by storing query results, reducing the amount of data to bescanned for each query. BigQuery verifies that materialized viewsprovide fresh data by computing updates in the background. This process isperformedincrementally byusing only the changed data in the base table, subject to anumber ofconsiderations. Materialized views can be either queried directly orused by BigQuery to optimize queries to their base tables.

Key characteristics of materialized views include the following:

• Zero maintenance. Materialized views are precomputed in the background whenthe base tables change. Any incremental data changes from the base tables areautomatically added to the materialized views, with no user action required.
• Fresh data. Materialized views return fresh data. If changes to basetables might invalidate the materialized view, then data is read directlyfrom the base tables. If the changes to the base tables don't invalidate thematerialized view, then rest of the data is read from the materialized view andonly the changes are read from the base tables.
• Smart tuning.If any part of a query against the base table can beresolved by querying the materialized view, then BigQueryreroutes the query to use the materialized view for better performanceand efficiency.

Compare with logical views

The following table summarizes the similarities and differences betweenBigQuery logical views and materialized views.

¹ The--allow_non_incremental_definition option supports an expanded range of SQL queries to create materialized views. For a list of supported materialized views, seeQuery limitations.

² The--max_staleness option provides consistently high performance with controlled costs when processing large, frequently changing datasets.

Use cases

Materialized views can optimize queries with high computation cost and smalldataset results. Processes that benefit from materialized views include onlineanalytical processing (OLAP) operations that require significant processing withpredictable and repeated queries like those in from extract, transform, load(ETL) processes or business intelligence (BI) pipelines.

The following use cases highlight the value of materialized views. Materializedviews can improve query performance if you frequently require the following:

• Pre-aggregate data. Aggregation of streaming data.
• Pre-filter data. Run queries that only read a particularsubset of the table.
• Pre-join data. Query joins, especially between large and small tables.
• Recluster data. Run queries that would benefit from a clusteringscheme that differs from the base tables.

Smart-tuning

Materialized views can be used to transparently improve the performance ofqueries without modifying them. You can use a materialized view to optimize setsof queries with common patterns, such as those generated by a BI tool. For moreinformation seeUse materialized views.

Authorized materialized views

You can create an authorized materialized view to share a subset of data from asource dataset to a view in a secondary dataset. You can then share this view tospecific users and groups (principals) who can view the data you share.Principals can query the data you provide in a view, but they can't accessthe source dataset directly.

Authorized views and authorized materialized views are authorized in the sameway. For details, seeAuthorized views.

Interaction with other BigQuery features

The following BigQuery features work transparently withmaterialized views:

• Query plan explanation: The query planreflects which materialized views are scanned (if any), and shows how manybytes are read from the materialized views and base tables combined.

• Query caching: The results of a querythat BigQuery rewrites usinga materialized view can be cached subject to the usual limitations (using ofdeterministic functions, no streaming into the base tables, etc.).

• Cost restriction:If you have set a value for maximum bytes billed,and a query would read a number of bytes beyond the limit, the query failswithout incurring a charge, whether the query uses materialized views, thebase tables, or both.

• Cost estimation using dry run:A dry run repeats query rewrite logicusing the available materialized views and provides a cost estimate. You can usethis feature as a way to test whether a specific query uses anymaterialized views.

BigLake metadata cache-enabled tables

Materialized views overBigLake metadata cache-enabledtables canreference structured data stored in Cloud Storage and Amazon Simple Storage Service (Amazon S3).These materialized views functionlike materialized views over BigQuery-managed storage tables,including the benefits of automatic refresh and smart tuning. Other benefitsinclude the pre-aggregating, pre-filtering, and pre-joining of data storedoutside of BigQuery. Materialized views overBigLake tables are stored in and have all of the characteristicsofBigQuery managed storage.

When you create a materialized view over an Amazon S3BigLake table, the data in the materialized view isn'tavailable for joins with BigQuery data. To make Amazon S3data in a materialized view available for joins, create areplica of the materialized view.You can only create materialized view replicas overauthorized materialized views.

Limitations

• Limits on base table references and other restrictions might apply.For more information about materialized view limits, seeQuotasand limits.
• The data of a materialized view cannot be updated or manipulated directlyusing operations such asCOPY,EXPORT,LOAD,WRITE, or datamanipulation language (DML) statements.
• You cannot replace an existing materialized view with a materialized viewof the same name.
• The view SQL cannot be updated after the materialized view is created.
• A materialized view must reside in the same organization as its base tables,or in the same project if the project does not belong to an organization.
• Materialized views use a restricted SQL syntax and a limited set ofaggregation functions. For more information, seeSupportedmaterialized views.
• Materialized views cannot be nested on other materialized views.
• Materialized views cannot query external or wildcard tables,logical views¹, or snapshots.
• Only the GoogleSQL dialect is supported for materialized views.
• You can set descriptions for materialized views, but you cannot setdescriptions for the individual columns in the materialized view.
• If you delete a base table without first deleting the materialized view,queries and refreshes of the materialized view fail. If you recreate the basetable, you must also recreate the materialized view.
• If a materialized view has achange data capture-enabledbase table, then that table can't be referenced in the same query as thematerialized view.
• Only non-incremental materialized view can haveSpannerexternal dataset tables base tables.If a non-incremental materialized view's last refresh occurredoutside themax_staleness interval, then the query reads the baseSpanner external dataset tables. To learn more aboutSpanner external dataset tables, seeCreate materialized views over Spanner external datasets.

¹Logical view reference support is inpreview. For more information, seeReference logical views.

Limitations of materialized views over BigLake tables

• Partitioning of the materialized view is not supported. The basetables can use hive partitioning but the materialized view storage cannot bepartitioned in BigLake tables. This means that anydeletion in a base table causes a full refresh of the materialized view. Formore details seeIncremental updates.
• The-max_stalenessoption value of thematerialized view must be greater than that of the BigLake basetable.
• Joins between BigQuery managed tables andBigLake tables are not supported in a single materialized viewdefinition.
• BigQuery BI Engine doesn't support acceleration of materialized views overBigLake tables.

Materialized views pricing

Costs are associated with the following aspects of materialized views:

• Querying materialized views.
• Maintaining materialized views, such as when materialized views arerefreshed. The cost for automatic refresh is billed to the project where the viewresides. The cost for manual refresh is billed to the project in which the manualrefresh job is run. For more information about controlling maintenance cost,seeRefresh job maintenance.
• Storing materialized view tables.

¹Where possible, BigQuery reads only the changes sincethe last time the view was refreshed. For more information, seeIncremental updates.

Storage cost details

ForAVG,ARRAY_AGG, andAPPROX_COUNT_DISTINCT aggregate values in amaterialized view, the final value is not directly stored. Instead,BigQuery internally stores a materialized view as an intermediatesketch, which is used to produce the final value.

As an example, consider a materialized view that's created with the followingcommand:

CREATEMATERIALIZEDVIEWproject-id.my_dataset.my_mv_tableASSELECTdate,AVG(net_paid)ASavg_paidFROMproject-id.my_dataset.my_base_tableGROUPBYdate

While theavg_paid column is rendered asNUMERIC orFLOAT64 to the user,internally it is stored asBYTES, with its content being an intermediatesketch in proprietary format. Fordata size calculation,the column is treated asBYTES.

What's next

• Create materialized views
• Use materialized views
• Manage materialized views