E-MapReduce:Asynchronous materialized view

Background​

Applications in data warehouse environments frequently execute complex queries based on multiple large tables, often involving joins and aggregations of billions of rows of data between multiple tables. Processing such queries typically consumes a large amount of system resources and time, resulting in extremely high query costs.

You can solve these problems with asynchronous materialized views in StarRocks. An asynchronous materialized view is a special physical table that stores pre-computed results based on specific query statements on base tables. When you execute complex queries on base tables, StarRocks can directly reuse the pre-computed results, avoiding repeated calculations and thereby improving query performance. The higher the query frequency or the more complex the query statement, the more significant the performance gain.

You can also use asynchronous materialized views to model your data warehouse, providing a unified data interface to upper-layer applications, hiding the underlying implementation, and protecting the security of base table detailed data.

Materialized view comparison

StarRocks synchronous materialized views (Rollup) can provide better data freshness and lower refresh costs. However, synchronous materialized views have many limitations in terms of scenarios, can only be created based on a single base table, and support only limited aggregate operators. StarRocks asynchronous materialized views can be created based on multiple base tables and support a richer set of aggregate operators.

The following table compares asynchronous materialized views and synchronous materialized views (Rollup) in StarRocks from the perspective of supported features.

Terms

Use cases

We recommend creating asynchronous materialized views if your data warehouse environment has the following requirements.

• Accelerate repeated aggregate queries

  Suppose your data warehouse environment has many queries containing the same aggregate function subqueries, consuming a large amount of computing resources. You can establish an asynchronous materialized view based on that subquery, computing and storing all results of that subquery. After successful establishment, the system will automatically rewrite query statements to directly query the intermediate results in the asynchronous materialized view, thereby reducing load and accelerating queries.

• Periodic multi-table join queries

  Suppose you need to regularly join multiple tables in your data warehouse to generate a new wide table. You can establish an asynchronous materialized view for these tables and set regular refresh rules, thereby avoiding manual scheduling of join tasks. After the asynchronous materialized view is established, queries will directly return results based on the asynchronous materialized view, avoiding the latency caused by join operations.

• Data layer category

  Suppose your base tables contain a large amount of raw data, and queries require complex ETL operations. You can implement data warehouse layering by establishing multiple layers of asynchronous materialized views on the data. This can decompose complex queries into multiple layers of simple queries, both reducing repeated calculations and helping maintenance personnel quickly locate problems. In addition, data warehouse layering can decouple raw data from statistical data, thereby protecting sensitive raw data.

• Lakehouse acceleration

  Querying data lakes may be slow due to network latency and throughput limitations of object storage. You can improve query performance by building asynchronous materialized views on top of data lakes. Additionally, StarRocks can intelligently rewrite queries to use existing materialized views, eliminating the hassle of manually modifying queries.

For specific use cases of asynchronous materialized views, see:

• Using materialized views for data modeling

• Materialized view query rewrite

• Using materialized views to accelerate data lake queries

Create asynchronous materialized views

CREATE DATABASE IF NOT EXISTS test_db;
USE test_db;
CREATE TABLE goods(
    item_id1          INT,
    item_name         STRING,
    price             FLOAT
) DISTRIBUTED BY HASH(item_id1);

INSERT INTO goods
VALUES
    (1001,"apple",6.5),
    (1002,"pear",8.0),
    (1003,"potato",2.2);

CREATE TABLE order_list(
    order_id          INT,
    client_id         INT,
    item_id2          INT,
    order_date        DATE
) DISTRIBUTED BY HASH(order_id);

INSERT INTO order_list
VALUES
    (10001,101,1001,"2022-03-13"),
    (10001,101,1002,"2022-03-13"),
    (10002,103,1002,"2022-03-13"),
    (10002,103,1003,"2022-03-14"),
    (10003,102,1003,"2022-03-14"),
    (10003,102,1001,"2022-03-14");

USE test_db;
SELECT
    order_id,
    sum(goods.price) as total
FROM order_list INNER JOIN goods ON goods.item_id1 = order_list.item_id2
GROUP BY order_id;

USE test_db;
CREATE MATERIALIZED VIEW order_mv
DISTRIBUTED BY HASH(`order_id`)
REFRESH ASYNC START('2022-09-01 10:00:00') EVERY (interval 1 day)
AS SELECT
    order_list.order_id,
    sum(goods.price) as total
FROM order_list INNER JOIN goods ON goods.item_id1 = order_list.item_id2
GROUP BY order_id;

Manually refresh asynchronous materialized views

You can manually refresh a specified asynchronous materialized view using the REFRESH MATERIALIZED VIEW command.

-- Asynchronously invoke the refresh task.
REFRESH MATERIALIZED VIEW test_db.order_mv;
-- Synchronously invoke the refresh task.
REFRESH MATERIALIZED VIEW test_db.order_mv WITH SYNC MODE;

You can cancel an asynchronously invoked refresh task using CANCEL REFRESH MATERIALIZED VIEW.

-- Cancel the refresh task.
CANCEL REFRESH MATERIALIZED VIEW test_db.order_mv;
-- Force cancel the refresh task.
CANCEL REFRESH MATERIALIZED VIEW test_db.order_mv FORCE;

Directly query asynchronous materialized views

An asynchronous materialized view is essentially a physical table that stores the complete result set pre-computed according to a specific query statement. After the materialized view is refreshed for the first time, you can directly query the materialized view.

SELECT * FROM test_db.order_mv;

Use asynchronous materialized views to rewrite and accelerate queries

StarRocks supports automatic transparent rewrite of SPJG-type asynchronous materialized view queries. Its query rewrite includes single-table rewrite, Join rewrite, aggregate rewrite, Union rewrite, and nested materialized view rewrite. Starting from v3.3.0, StarRocks further supports text-based materialized view rewrite. For detailed information, see Materialized view query rewrite.

Currently, StarRocks supports query rewrite for asynchronous materialized views based on Default Catalog, Hive Catalog, Hudi Catalog, and Iceberg Catalog. When querying Default Catalog data, StarRocks ensures strong consistency between the rewritten query results and the original query results by excluding materialized views where data is inconsistent with the base tables. When materialized view data is outdated, it will not be considered as a candidate materialized view. When querying external catalog data, StarRocks cannot detect data changes in external catalog partitions, so it does not guarantee strong consistency of results. For information about asynchronous materialized views based on External Catalog, see Using materialized views to accelerate data lake queries.

Manage asynchronous materialized views

SHOW CREATE MATERIALIZED VIEW test_db.order_mv;

DROP MATERIALIZED VIEW test_db.order_mv;