What are Databricks Materialized Views and How to Boost Query Performance Using Them?

Accessing and performing large volumes of data is crucial in data analytics and engineering. As datasets grow larger and more complex, executing queries repeatedly can become a bottleneck, slowing down data analysis and decision-making. One solution to overcome this challenge is using materialized views – a powerful database object designed to optimize query performance and store the result of expensive operations.

You might consider using materialized views to optimize workflows if you work with a cloud-based unified analytics platform like Databricks. In this blog, we will discuss materialized views, when you should use materialized views, the difference between views and Databricks materialized views, and how to use materialized views in Databricks to improve performance and resource utilization. 

What Are Materialized Views? 

In traditional database systems, when you run a query on a view, the database executes the SQL logic and returns the results. In this case, the results are not stored continuously – each time the view is queried, the database must re-run the query. This can lead to performance overhead for large, complex datasets. 

This issue can be solved by using a materialized view, which stores the resultant data in a table-like structure. This helps faster data retrieval, especially for large datasets or complex queries. In other words, it is like a snapshot of your data at a specific time and saved for future use. 

When you are working with any reporting tools or dealing with big data, where speed is paramount, materialized views offer an efficient way to optimize query performance, and they also help reduce computational loads. 

When should you use materialized views? 

1. For performance boost on complex queries:

Materialized views can be used in a database that frequently uses complex queries that involve multiple joins, aggregations, or subqueries. 

Running complex queries on a large dataset whenever needed can be slow. Meanwhile, the materialized view precomputes the results and stores them without reprocessing the same data repeatedly.

2. For Business Intelligence Workloads:

In BI platforms and data warehouses, materialized views are commonly used for fast access to pre-aggregated data.  In Business Intelligence and analytics, users frequently need to run repetitive queries on large datasets. Materialized views help reduce the load on the system and improve speedy data retrieval. 

What Are Databricks Materialized Views? 

In the context of Databricks SQL, materialized views are precomputed views that store the result of a query in a physical format, allowing for faster query performance. 

Key Characteristics of Databricks Materialized Views:

1. Physical Storage – The storage characteristics of materialized views in Databricks SQL refer to storing the results in a storage disk. 
2. Read-only format – Materialized view is typically a read-only format, and you cannot directly modify the data.
3. Data Consistency – Materialized views may not reflect the updated changes until refreshed, depending on the refresh strategy( i.e., automatic or manual).
4. Delta Lake Integration – A materialized view built on Delta Lake helps users with data lineage capabilities.  
5. Setting Access Control – Using a Materialized view, you can control who can view or interact with specific data. 

Difference Between View and Databricks Materialized View

In Databricks SQL, views and materialized views allow you to simplify complex SQL logic. However, they hold key differences in use case, performance, and behavior. The following section provides a detailed explanation of the difference between view, and Databricks materialized view. 

1. Definition

• View: 
  • A view in Databricks SQL is a virtual TABLE that stores SQL queries. 
  • In view, no data is stored; it simply represents the query’s result when executed. 

• Materialized View:
  • It is a precomputed version of the view.
  • Depending on the configuration, the materialized view is updated.
  • It stores the query results physically on disk.

2. Performance Optimization

• View:
  • If the query is complex or involves large datasets, it leads to high query latency.
  • Query on views involves real-time computation.

• Materialized view: 
  • Since materialized views store precomputed query results, it improves query performance.
  • To keep the materialized view up-to-date, you can schedule or manually trigger refreshes ( on-demand or scheduled refresh)  for faster performance.

View Example:

View Syntax

CREATE VIEW view_name AS

SELECT column1, column2, ...

FROM table_name

WHERE condition;

Create View Example 

CREATE VIEW sales_view AS 

SELECT region, product_id, SUM (sales_amount) AS total_sales

FROM sales_data 

GROUP BY region, product_id;

This view is a virtual table; every time you query it, the result is fetched from the sales_data table and aggregated.

Create Materialized View 

CREATE MATERIALIZED VIEW sales_summary AS

SELECT region, product_id, SUM(sales_amount) AS total_sales

FROM sales_data GROUP BY region, product_id;

This above query will store the aggregation result, providing faster access than recalculating the aggregation every time.

Prerequisites to Create or Refresh Materialized Views

There are several prerequisites and considerations to creating materialized views in Databricks SQL. Here’s a list of key prerequisites.

1. Permissions:

• CREATE and SELECT permission is mandatory on the databases or table you reference for the materialized view.
• Admin or DB Owner privileges are required to manage and create materialized views.
• CREATE – Required permission on the target DB.
• SELECT – Permission on the source tables or views.

Example:

GRANT CREATE, SELECT ON DATABASE my_database TO user_name;

2. Refresh Strategy: 

    It is important to determine the refresh strategy for the materialized view.

• Manual refresh – You can manually refresh the materialized view using the REFRESH MATERIALIZED VIEW command.
• Scheduled refresh – Either configuring scheduled refresh to refresh the materialized view automatically, for example: daily, weekly etc.,.

REFRESH MATERIALIZED VIEW monthly_sales_summary;

3. Database Version Support:

• Not all Database versions support materialized views. It is important to check whether the DBMS version supports materialized view.

Example of creating a materialized view:

CREATE MATERIALIZED VIEW my_materialized_view AS

SELECT column1, column2, SUM (column3)

FROM my_table

GROUP BY column1, column2;

To manually refresh the materialized view:

REFRESH MATERIALIZED VIEW my_materialized_view;

Regarding refresh methods, logs, and scheduling, consult  your database documentation for specific environment details (e.g., Oracle, PostgreSQL, SQL Server, etc.) Creating and refreshing materialized views requires consideration of storage, query, and refresh strategy.

How to Create a Databricks Materialized View

Creating a materialized view involves following a step-by-step procedure for optimal performance and use.

Before you begin with a materialized view, these are the following prerequisites.

1. Make sure you have a Databricks workspace with Databricks SQL.
2. Check sufficient privileges, including CREATE, SELECT.
3. Databricks Runtime version support.

Step 1:  Preparing SQL Query

Using SQL query, materialized views can be created; it can be a simple or a complex query, but it must fetch the right data. Since they store pre-computed data,  it improves performance for complex queries.

An example query to calculate sales of each product:

SELECT product_idx_sales , SUM (amount) AS total_product_sales

FROM sales

GROUP BY product_idx_sales;

Step 2: Creating Materialized View

Using the CREATE MATERIALIZED VIEW statement, you can create a materialized view.

Syntax

CREATE MATERIALIZED VIEW <view_name> AS

<SELECT_statement>;

Example of materialized view as follows:

CREATE MATERIALIZED VIEW sales_summary AS

SELECT product_idx_sales , SUM(amount) AS total_product_sales

FROM sales

GROUP BY product_idx_sales ;

Step 3: Verify Materialized View

You can check the list of tables/views in your database by using SHOW and DESCRIBE statements.

SHOW TABLES;

The above statement is used to check materialized views.

Step 4:  Materialized View Refresh ( Optional )

The REFRESH MATERIALIZED VIEW command can be used to view the latest update on the table. To set a scheduled refresh, you can automate the refreshing process using Databricks Jobs. It runs an SQL command based on a refresh strategy ( e.g. weekly, monthly).

REFRESH MATERIALIZED VIEW sales_summary;

Once the materialized view is created, you can view it using SELECT.

SELECT * FROM sales_summary;

Step 5: Monitoring the Performance of Materialized View

If the dataset is large, it is important to monitor the performance of the materialized view as it takes up a significant amount of storage. Optimizing your query is also recommended if the refresh time is long. 

Note: SQL queries and jobs can be monitored in the Databricks SQL Warehouses tab and the Jobs tab.

A Complete Workflow – From creating a materialized query, refreshing it, and querying it.

1. Create:

CREATE MATERIALIZED VIEW sales_summary AS

SELECT product_idx_sales, SUM (amount) AS total_sales

FROM sales

GROUP BY product_idx_sales ;

2. Verify the materialized view:

SHOW TABLES;  // This command lists the  available tables and views

DESCRIBE FORMATTED sales_summary;  //To check the details of the materialized view.

3. Query the materialized view:

SELECT * FROM sales_summary;

4. Refreshing manually:

REFRESH MATERIALIZED VIEW sales_summary;

Following the above steps, you can optimize your workflow with precomputed, fast-access data.

How to Refresh Materialized Views in Databricks SQL

In Databricks SQL, materialized views are not refreshed automatically. It can be automated via Databricks Jobs or using specific SQL commands. 

1. Manual Refresh 

The REFRESH MATERIALIZED VIEW command can be used to refresh a materialized view manually. It will recompute the content based on the underlying base tables. 

Syntax for materialized view refresh:

REFRESH MATERIALIZED VIEW <view_name>;

Example: If you have a materialized view named as “sales_summary”, you can use this command to refresh. This command updates it with the latest data from the source tables.  

REFRESH MATERIALIZED VIEW sales_summary;

2. Automatic Refresh

Using Databricks Jobs, you can keep the materialized view up-to-date without any manual interventions.

Steps:

1. In Databricks Workspace, navigate to the Jobs tab.
2. Click on Create Job to create a new job.
3. In the task section, choose SQL task and provide necessary SQL statements, like:

REFRESH MATERIALIZED VIEW sales_summary;

4. Schedule job intervals, for example, daily, weekly, or monthly.
5. Click Create will save the job.

Example:

SQL Task:

REFRESH MATERIALIZED VIEW sales_summary;

Job Schedule: Run weekly at midnight.

It is always a best practice to automate the refresh process for routine updates for critical pipelines and monitor resource performance. 

Limitations of Materialized View in Databricks SQL

You should be aware of certain limitations of a materialized view in Databricks SQL when using them. Below are some of the limitations:

1. It has only limited refresh options.
2. Databricks SQL does not support automatic refresh, unlike other databases  (e.g., Oracle and PostgreSQL). You either have to trigger the refresh manually or via automated jobs.
3. To avoid performance bottlenecks, you need to optimize query performance.
4. Monitoring the storage costs, as materialized view consumes additional storage. 
5. Limited query optimization.

Conclusion

Materialized views are particularly beneficial in environments like Business Intelligence (BI) platforms or large data processing workflows, where queries are repetitive and performance is paramount. Unlike regular views, which compute results on demand, materialized view stores the resultant table in a physical format, enabling faster performance. 

There are certain limitations in materialized views as well. A manual or automatic refresh strategy is required to keep up-to-date data changes in the underlying base tables. Despite these limitations, Databricks can be a powerful tool for efficiently handling data workflows. As the data landscape grows, materialized views become a much more important part of the performance optimization toolkit for engineers and data analysts working with cloud-based platforms. 

Ready to simplify your data migration process? Hevo makes it easy to move your data seamlessly to Databricks, ensuring smooth integration with features like materialized views. Try Hevo today and accelerate your data pipeline with no-code, hassle-free migration!

 FAQs