Asynchronous Materialized View Guide

Use cases

Asynchronous materialized views apply to the following scenarios:

• Query acceleration: Pre-compute results for frequent queries with complex aggregations and multi-table joins. The system transparently rewrites incoming queries to use the materialized view, significantly reducing latency.

• Simplifying ETL pipelines: Use materialized views for layered aggregation and ETL processing on raw data, building data layers such as ODS, DWD, DWS, and ADS while reducing redundant computations.

• Lakehouse query acceleration: Cache hot query results from external data lakes such as Hive or Iceberg locally in SelectDB, leveraging its high-performance engine to dramatically improve query performance.

• Improving write efficiency: Reduce resource contention during data ingestion, improving write efficiency while maintaining data consistency and integrity.

Limitations

Note the following limitations when using asynchronous materialized views:

• Eventual consistency: Data in an asynchronous materialized view lags behind its base tables depending on the refresh policy and does not guarantee strong consistency. For real-time data requirements, consider synchronous materialized views.

• Window function restrictions: If a materialized view's SQL definition contains a window function, transparent rewrite is not supported in the current version. You can still query the materialized view directly.

• Join table count limit: The number of tables joined in the materialized view definition must not exceed the number of tables joined in the query. Otherwise, transparent rewrite fails.

• Limitations on partitioned incremental refresh for external tables: Hive external tables support partitioned incremental refresh starting from version 2.1. Iceberg, Paimon, and Hudi external tables support this feature starting from version 3.1. JDBC and ES external tables do not support partitioned incremental refresh. To refresh these tables, you must specify a COMPLETE full refresh.

• Data model of the materialized view: A materialized view itself must use the duplicate key model, regardless of the data models of its base tables.

• Limitations on set operations and special syntax: Materialized views that contain UNION ALL, LIMIT, ORDER BY, or CROSS JOIN can be created but cannot be used for transparent rewriting.

How it works

MTMV (Multi-Table Materialized View)

SelectDB's asynchronous materialized view is built on the Multi-Table Materialized View (MTMV) framework, which decouples the view definition (the query statement) from its base tables. Refresh tasks run asynchronously in the background, and each materialized view has an independent refresh job managed by a scheduler.

Refresh mechanism

An asynchronous materialized view refreshes as follows:

1. A refresh job is triggered (manually, on a schedule, or ON COMMIT).

2. The system checks if data in the base tables has changed since the last refresh. This check is only supported for internal tables and Hive external tables.

3. For a partitioned materialized view, the system refreshes only the partitions with data changes (partitioned incremental refresh). For a non-partitioned materialized view, it refreshes all data (full refresh).

4. After the refresh is complete, the version information and status (such as NORMAL or SCHEMA_CHANGE) of the materialized view are updated.

Transparent rewrite (SPJG algorithm)

The SelectDB query optimizer Nereids supports transparent rewrite based on the SPJG (SELECT-PROJECT-JOIN-GROUP-BY) pattern. When a query is submitted, the optimizer automatically checks whether an existing materialized view can satisfy it and, if so, rewrites the query to use the view. This accelerates queries without any changes to the application SQL.

Transparent rewrite checks the following conditions:

• Whether the output columns of the query can be derived from the columns of the materialized view.

• Whether the join relationships in the query are covered by the materialized view.

• Whether the filter conditions of the query are within the data range of the materialized view.

• Whether the data in the materialized view is in a valid state (NORMAL).

Create materialized views from a data lake

You can create an asynchronous materialized view based on external data sources accessed through a catalog to accelerate Lakehouse queries. The syntax is the same as for internal tables, but note the following:

• The refresh process uses the data lake's cached metadata (such as partition version information) within SelectDB, not metadata fetched directly from the external environment. Refreshed data is consistent with what you would get by querying the data lake through SelectDB, but may differ from results obtained through other engines depending on cache freshness.

• If an external process (such as a Spark, Hive, or Flink job) modifies the underlying Hive table data without updating the metadata (for example, by running INSERT OVERWRITE), the materialized view may assume its data is in sync when it is not, leading to inconsistent query results. To resolve this, manually force a refresh of the materialized view.

• When creating a partitioned materialized view from an Iceberg table, the table must have a single partition column. Partition evolution is supported to a limited extent. For example, changes in the time range of a time-based partition are supported, but changes to the partition field itself are not and will cause the refresh to fail.

• For Hudi tables, the system cannot detect base table data changes. Once the materialized view (or a partition) has been refreshed, it is considered in sync with the base table. Materialized views on Hudi tables are therefore best suited for manual, on-demand refresh.

Data lake refresh support

The following table shows the refresh capabilities for different table and catalog types.

Data lake transparent rewrite support

The following table lists transparent rewrite support by table and catalog type. "Data change awareness" indicates that the materialized view detects base table changes and uses the latest data for queries.

Relationship with internal OLAP tables

An asynchronous materialized view's SQL definition has no restrictions on the data model of its base tables — it supports the duplicate key model, primary key model (both Merge-on-Write and Merge-on-Read), and aggregate model. The view itself is implemented as an OLAP table with a duplicate key model and theoretically supports all core features of that model. However, to ensure stable and efficient refreshes, the following restrictions apply:

• SelectDB automatically creates and manages partitions for a materialized view based on its base tables. You cannot perform partition operations directly on the materialized view.

• Because a materialized view has associated background jobs, you cannot use DROP TABLE or RENAME TABLE commands on it. Instead, you must use materialized view-specific commands, such as DROP MATERIALIZED VIEW.

• The system automatically infers the data types for the materialized view's columns from the query statement at creation, and you cannot modify them. Changing them could cause the refresh job to fail.

• You must modify properties specific to a materialized view using materialized view-specific commands. You can modify other common properties with the ALTER TABLE command.

References

• To create, query, and maintain an Asynchronous Materialized View, see Create, Query, and Maintain Asynchronous Materialized Views.

• For best practices, see Asynchronous Materialized View Best Practices.

• For frequently asked questions, see Asynchronous Materialized View FAQ.