Materialized View


What is a Materialized View?



A materialized view is a database object that contains the results of a query. It may be a local copy of data located remotely, or a subset of the rows and/or columns of a table, or a summary using an aggregate function. The name materialized views can be broken down into two parts - materialization and views:

Materialization involves caching intermediate results of a query using additional storage space. This process accelerates the computation by reusing precomputed materialized results.

Views emphasize logical semantics, providing a unified schema for the upper layer, shielding complexity, and simplifying permission management.

For example, let's say you run an online store, and you have a database with millions of rows containing sales data. You often need to know the total sales for each product category. Instead of executing a complex query every time, you can create a materialized view that calculates the total sales for each category in advance. When you need the information, the database quickly reads the pre-aggregated data from the materialized view, saving time and resources.

Basic Concepts 

  • Base Table: The original table specified when creating a materialized view (e.g., sales data table).

  • Materialized Table: A new table that is created based on the base table (e.g., total sales per product category).

  • Materialized View: The user-visible materialized view, which may include both the materialized table and incremental data.

  • Materialized View Task: A task that defines the processing from the base table to the materialized view table.

Materialized View_starrocks_celerdata


Understanding Core Functionalities of Materialized Views

The capabilities of materialized views are foundational to enhancing data processing and query performance. Let's explore these functionalities in detail:

  • Materialization: Materialization is a process where materialized views take the results of computations and store them as physical tables within a storage system. These tables are structurally identical to regular tables, enabling efficient data retrieval without the need for recalculating the results. This process is directly tied to the base table. Materialized views take data from the base table and store it in a new form, ensuring efficient data retrieval without recalculating. This new form is what we call a Materialized Table.

    Materialized View 2
  • Partitioning: Partitioning a materialized view, for instance, by day, facilitates granular control over view refreshment, maintenance, and Time to Live (TTL) settings. This strategy can align with external table partitions (e.g., Hive/Iceberg), allowing materialized views to automatically update based on changes in these external sources.

  • Refresh Mechanisms: Materialized views support various refresh methods to ensure data remains current. These include automatic refreshes triggered by data imports, scheduled refreshes (e.g., hourly), manual refreshes via external systems, and incremental refreshes for certain scenarios. Such flexibility caters to diverse business needs, optimizing data freshness and accessibility.

  • Resource Group for Isolation: Addressing the challenge of resource isolation, materialized views use Resource Groups in systems to segregate front-end query loads from maintenance operations. This setup allows for concurrent execution of different workloads within the same cluster, ensuring materialized views do not detract from front-end query performance.

  • Advanced Query Support: The support for complex queries, such as joins and aggregations, extends the utility of materialized views beyond simple data storage. By allowing the materialization of results from a wide array of data sources, including JDBC-accessed systems (MySQL, PostgreSQL), Lake tables (Hive, Iceberg), and native database storage, materialized views not only streamline access to precomputed data but also significantly reduce the load on the database during query execution.

Materialized views simplify precomputing query results according to user-specified refresh modes, thereby avoiding the overhead of repeated computations. It showcases the ability to efficiently schedule tasks, perform precalculations, and make incremental updates as needed.

Behind its simple syntax, materialized views possess the ability to rewrite queries, enabling the optimizer to automatically identify SQL queries that can be accelerated and transform them into precomputed results, significantly reducing computational overhead. This capability opens up new opportunities for users to improve their systems' performance without having to manually change the SQL code. It provides a way to balance the costs and benefits, offering flexibility in how performance is managed.

Materialized View 1


What Problems Do Materialized Views Solve? 

By bringing these important features together, materialized views solve two big problems in the world of data engineering:

Complexity of Data Processing:

  • Simplifying Data Processing Complexity: Materialized views use a declarative syntax to model data processing tasks, significantly simplifying the complexity associated with data engineering. This approach minimizes the need for users to delve into the intricate details of data computation processes.

  • Refresh Concept for Data Flow: The refresh mechanism abstracts the flow of data, reducing the burden of managing dependencies and quality issues across multiple systems. This streamlined process facilitates easier and more reliable data management practices.

  • Resource Isolation and Efficiency: Through the use of Resource Groups, materialized views effectively tackle the issue of resource isolation. This technology allows for the efficient allocation and utilization of computational resources, ensuring that data processing tasks do not interfere with each other and that simple computations do not require additional resources.

  • Transparent Acceleration and Flexible Data Modeling: Materialized views enable transparent query acceleration and flexible data modeling, allowing businesses to adapt their data structures and processing strategies in response to evolving needs without extensive manual intervention. This capability ensures that data management is both efficient and adaptable, catering to the dynamic requirements of modern data landscapes.

Balancing Performance, Timeliness, and Cost

  • Materialized views present a solution to the trade-offs between performance, timeliness, and cost in data processing. By offering various refresh modes, they enable organizations to fine-tune their data management strategies to align with specific operational requirements and objectives, optimizing the balance between resource expenditure and data accessibility.


StarRocks' Materialized Views for Data Lake Query Acceleration

StarRocks enhances data lake analysis with robust querying capabilities suitable for exploratory analysis. Its materialized views are engineered to pre-compute and accelerate queries, drawing from both internal and external sources such as Apache Hive, Apache Hudi, Iceberg, and Delta Lake. The materialized views in StarRocks offer the following benefits: 

  • On-demand Transparent Acceleration: The implementation of materialized views for transparent acceleration allows for the dynamic optimization of query performance as needed. This eliminates the necessity for extensive data governance and pre-modeling, thereby reducing the time and cost associated with data preparation.

  • Flexible Data Modeling: Materialized views enable flexible data modeling, significantly enhancing the data quality and query performance of the data lake. This allows for an adaptable approach to handling diverse data structures and analytical requirements without the complexity of traditional modeling processes.

  • Incremental Computation with MVs: Materialized views facilitate an incremental processing pipeline, enhancing the timeliness of data and reducing end-to-end latency, effectively addressing the timeliness issues within an integrated lakehouse framework.

  • Unified Storage and Querying: It stores detailed, archived, and semi-structured data in the lake, while refined, processed data resides in the warehouse. A unified querying engine is employed for analysis, with materialized views acting as connectors between the lake and warehouse, ensuring seamless access and analysis across both environments.