What Is Materialized View In Big Query ?

A materialized view is a precomputed snapshot of data in BigQuery, which stores the data physically from the output of a query onto the disk. It automatically refreshes the data from its base table periodically, ensuring the data remains up-to-date with changes to its underlying base tables. They are faster as compared to logical views because of their significant performance.

Materialized View overcomes the need to fetch data from the base tables every time the query is executed. Instead, the precomputed data stored in the view can be quickly accessed, resulting in faster query execution and efficiency.

Base Table : A base table is a Bigquery table where the actual data resides.

Materialized View

Creating Materialized View in Bigquery

To create a Materialized view in Bigquery select the appropriate gcp project where you want to create your view.

Selection of Project in the Cloud Console

Once the project is selected then write the SQL query in the Bigquery editor.

For Example :

Below is the gfg_sample_usa_view name of the materialized view which is created on the top of weather table under gfg_data dataset which resides inside the geeksforgeeks project.

Example of Materialized View

The flow diagram mentioned below depicts the illustration of a Materialized View in a GCP Project.

Flow Diagram depicting Materialized View

After writing the query in the editor, click on the Run option on top of the editor. Upon successful completion of the query you will get an option in the Query Result panel as depicted below.

Query Result Panel

Refreshing Materialized View

• Automatic Refresh
• Manual Refresh

Automatic Refresh: By default Bigquery attempts to refresh the Materialized view within 5 minutes whenever the data in the base table gets updated.

Note : We can enable/Disable the refreshing mechanism. Also, you can change the frequency cap to refreshing the Materialized View in the Bigquery as shown below.

CREATE OR REPLACE MATERIALIZED VIEW <PROJECT_ID>.<DATASET>.<MATERIALIZED_VIEW_NAME> 
OPTIONS (enable_refresh = true, refresh_interval_minutes = 60)
AS (<QUERY_EXPRESSION>);

Manual Refresh: Bigquery also supports manual intervention to refresh Materialized View by calling the below procedure.

CALL BQ.REFRESH_MATERIALIZED_VIEW(<PROJECT_ID>.<DATASET>.<MATERIALIZED_VIEW_NAME>);

Advantages of Materialized View

• Materialized View automatically refreshes the data in the background whenever the data is updated in the base table without any manual intervention.
• Querying data on the Materialized View takes lesser time, as the data is pre-computed in comparison to the base table.
• Bigquery reroutes the query to a Materialized View if, it’s running a query or subquery on the base table serving the materialized view. This makes the view cost-effective and time saving rather than querying on the base table.

Disadvantages of Materialized View

• It is not possible to create Materialized View under any Materialized View i.e. Materialized View cannot be nested.
• Materialized View cannot be created on the tables which are direct source of streaming inserts.
• The SQL query which is used to create Materialized View cannot be altered once the view is created.

Query Performance Optimization

• Aggregated data
• Filtered data
• Joined data

Aggregated data: Whenever you perform aggregation on a large data set, So to optimize the performance you can create Materialized View on the aggregated data that will improve the query performance.

Filtered data: Let’s understand this by an example assume you have 1 million records and you only want to analyze 1 lakh records every time .So create a Materialized View on top of the base table by filtering the required data which will reduce the cost of scanning the whole data and increase the efficiency whenever you use Materialized View.

Joined data: In Bigquery to improve query performance create a Materialized View on subqueries which includes heavy joins and taking long time in scanning the database. So if you use Materialized View it will drastically reduce the cost and improve the query performance.

What are the roles and Permission required to create a Materialized View in Bigquery?

First of all you should have bigquery.tables.create IAM Permission to create a view on the Bigquery table.

Following are the IAM roles/Permission required to create a Materialized View in Bigquery.

• bigquery.dataEditor
• bigquery.dataOwner
• bigquery.admin

Note: In order to access Materialized View you should have access to its Base Table.

We can check all the above mentioned roles in the IAM & Admin section of the GCP project to create a view as shown below.

IAM & Admin Section to check roles/Permission

How to Control access of Materialized View ?

Following are the ways by which we can control the access of a Materialized View in Bigquery.

• At Dataset level
• At View level
• At Column level

What makes a Materialized View different from the Logical View(View) in Bigquery ?

S.No.	Materialized View	Logical View
1.	It stores the data physically on the disk.	It is a logical structure so there is no physical data storage.
2.	It is pre-computed.	Hits the query on base table at run time.
3.	It is fast and efficient.	It is less efficient then Materialized View.
4.	Syntax : CREATE OR REPLACE MATERIALIZED VIEW <PROJECT_ID>.<DATASET>.<MATERIALIZED_VIEW_NAME> AS (<QUERY_EXPRESSION>);	Syntax : CREATE OR REPLACE VIEW <PROJECT_ID>.<DATASET>.<VIEW_NAME> AS (<QUERY_EXPRESSION>);

Conclusion

Materialized Views emerges as a powerful tool in the field of data analysis. These views streamlines the data access and enhance query performance. The automatic refresh mechanism of the Materialized Views incorporate changes in the base table ensuring the data remains updated.

Overall, Materialized Views is the game changer for the Bigquery user providing optimized data processing workflows, delivering both speed and efficiency in data analysis and retrieval.

Materialized View in Bigquery – FAQ’s

What is the purpose of materialized view?

A materialized view in a database serves the purpose of precomputing and storing the results of a query, providing faster access to data by avoiding the need to execute complex queries repeatedly. It enhances query performance and reduces computational overhead by storing the results for quick retrieval.

What is difference between view and materialized view?

A view in a database is a virtual table based on a query’s result, displaying dynamic data each time it’s queried. In contrast, a materialized view is a physical copy of query results, providing static data until explicitly refreshed, optimizing query performance at the cost of data currency.