Doris Materialized Views (Creation and Usage)

Doris is an open-source distributed database system that supports the creation and use of materialized views.

Concept of Materialized Views

A materialized view is a view that stores data at the storage layer, enabling rapid retrieval of required data. It can improve query performance and provide a better data access experience.

Creating Materialized Views

Doris supports two types of materialized views: physical materialized views and logical materialized views.

Physical Materialized View

A physical materialized view is a type of materialized view based on physical storage; it creates a replica at the storage layer.

CREATE PHYSICAL VIEW view_name AS SELECT * FROM table_name;

Logical Materialized View

A logical materialized view is a type of materialized view based on virtual storage; it does not create any replicas at the storage layer.

CREATE LOGICAL VIEW view_name AS SELECT * FROM table_name;

Using Materialized Views

Materialized views can be used to simplify complex queries or as alternatives to subqueries.

Examples

Assume we have a table users containing user information. We can create the following physical materialized view:

CREATE PHYSICAL VIEW user_info AS SELECT * FROM users;

Then, we can use the materialized view to retrieve user information:

SELECT * FROM user_info WHERE id = 1;

Advantages

Materialized views have the following advantages:

• Rapid data retrieval: Since they retrieve data directly from the storage layer, materialized views provide a fast data access experience.
• Simplification of complex queries: By creating a simple materialized view, developers can simplify complex queries.
• Performance improvement: By reducing the number of queries executed at the storage layer, materialized views can improve overall performance.

Conclusion

Materialized views are another powerful feature of Doris, enabling rapid data retrieval and providing a better data access experience. Developers can create physical or logical materialized views and then use them to simplify complex queries or as alternatives to subqueries.