Introduction
In enterprise data analytics, insufficient data lake query performance is a common challenge. To improve query efficiency, many companies choose to migrate data into a data warehouse. While this approach can solve performance issues, it also increases the complexity of data governance and the cost of ongoing maintenance.
This article explores how materialized views can help data lake performance catch up with data warehouse performance, enabling real-time data analytics without unnecessary complexity.
Limitations of Traditional Data Lake Query Approaches
Limitation 1: Complex Development for Precomputation
Traditional approaches accelerate queries by using tools such as Spark to build aggregate tables. This requires data engineers to fully plan the layered modeling pipeline before application development begins, which increases both development complexity and time cost.
Limitation 2: Low Resource Efficiency
Many prebuilt tables are not fully used in actual analytics workflows. This leads to wasted compute resources and storage space, extends project development cycles, and increases costs.
Limitation 3: Poor User Experience
Users often need to adjust SQL queries frequently so that they point to new tables containing precomputed values. Because users may not fully understand the underlying optimization mechanisms, it is often difficult for them to achieve optimal query performance.
Materialized Views for Data Lake Acceleration
Since 2021, materialized view-based acceleration has become an important direction for Lakehouse architectures and data lake query optimization.
In 2022, asynchronous materialized views were introduced in version v2.4. After continuous iteration and optimization, this capability has become a core technology for improving data lake query performance. Today, materialized view capabilities have been validated in production environments across more than 300 enterprises in industries such as internet, finance, and retail.
1. What Is a Materialized View?
A materialized view is a special physical table that stores precomputed query result sets to accelerate queries. Queries can still run against the original data, and materialized views can be added on demand at any time to optimize query performance.
This approach provides flexibility while avoiding repeated execution of complex computation logic, significantly shortening development cycles.
2. Materialized Views: Automatic Query Rewrite for Significant Performance Gains
A key advantage of modern materialized view capabilities is automatic query optimization. When a query is executed, the CBO optimizer can automatically rewrite the query and precisely match it to the optimal materialized view, improving performance without requiring users to modify SQL.
Materialized views support integration with mainstream data sources such as Hive, Hudi, Iceberg, and Delta Lake. They can also be built from mixed sources and support both single-table and multi-table queries.
Whether users need a quick summary of key metrics, associated analysis across multiple data sources, or comprehensive queries that integrate different datasets, materialized views can deliver faster query performance.
In the broader Lakehouse trend, materialized views offer unique value. They can be created based on external Catalogs, eliminating much of the previously complex and time-consuming ETL work. This enables enterprises to integrate and use data from both data lakes and data warehouses more efficiently.
Trip.com Achieves a 10x Performance Improvement with Materialized Views
Trip.com’s unified reporting platform, Artnova, supports reporting requirements across the group’s business units, including hotels, flights, and corporate travel. Users can customize reports to obtain key business data.
However, complex SQL queries, large base tables, and high-concurrency requests created major challenges for the system. Users often send emails on the hour, causing the system to process thousands of complex SQL queries at the same time. Reducing latency and improving response speed became critical.
To address these technical challenges, Trip.com adopted a transparent data acceleration strategy and used materialized views to optimize slow SQL queries. When a slow query occurs, the platform only needs to analyze the SQL logic and create a suitable materialized view, avoiding large-scale changes to the data warehouse structure.
By using technologies such as direct queries on the lake, Data cache, vectorized computing, and I/O merging, Trip.com achieved more than a 7x performance improvement.
With the Catalog+MV approach, materialized views were created for specific slow queries, resulting in an average 10x acceleration for reports on Hive external tables, with compatibility reaching 99%. Today, more than 100,000 queries have been successfully migrated from Trino to Hive external tables, significantly improving data processing efficiency.
Learn more materialized view use cases: DiDi, Trip.com, WeChat, and a leading joint-stock commercial bank.
Enterprise Capability Upgrade: Auto-MV
In real-world query scenarios, materialized views require architects to combine business and technical considerations when designing layered data warehouse architectures and optimizing materialized view schemas. For some enterprises, this creates a high adoption barrier.
Auto-MV further strengthens enterprise-grade capabilities by automating and simplifying materialized view design and usage.
Auto-MV simplifies layered data warehouse design. Through recommended materialized view patterns, or MV schemas, it helps users build a leaner and more efficient data architecture.
At the same time, Auto-MV can effectively simplify ETL workflows. It automatically uses materialized views to complete operations such as data joining, wide-table processing, and aggregate computation, providing efficient data services for business teams.
Its precomputation mechanism accelerates queries and can automatically rewrite query statements to use existing materialized views, enabling acceleration without business users needing to be aware of the underlying changes.
In addition, MirrorShip provides a complete monitoring and operations system, with flexible deployment models for multiple scenarios to meet the needs of enterprise users.
To learn more about the latest materialized view capabilities, contact the team or visit the MirrorShip website to view the related technical documentation.
