Synchronized materialized views

Background

In StarRocks, a synchronized materialized view can only be created on a single base table in the Default Catalog. It acts as a special type of query acceleration index.

Limitations

• Synchronized materialized views support WHERE clauses as of v3.1.8.

• Disaggregated storage and compute instances do not currently support synchronized materialized views.

Prerequisites

Before you create a synchronized materialized view, verify that it is needed to accelerate queries in your data warehouse.

The following example is based on the sales_records table, which contains the transaction ID (record_id), seller ID (seller_id), store ID (store_id), sale date (sale_date), and sale amount (sale_amt) for each transaction.

1. Create the table and load data into it.

   CREATE DATABASE IF NOT EXISTS load_test;
   USE load_test;
   CREATE TABLE sales_records(
       record_id INT,
       seller_id INT,
       store_id INT,
       sale_date DATE,
       sale_amt BIGINT
   ) DISTRIBUTED BY HASH(record_id);
   INSERT INTO sales_records
   VALUES
       (001,01,1,"2022-03-13",8573),
       (002,02,2,"2022-03-14",6948),
       (003,01,1,"2022-03-14",4319),
       (004,03,3,"2022-03-15",8734),
       (005,03,3,"2022-03-16",4212),
       (006,02,2,"2022-03-17",9515);

2. Measure query time.

   This business scenario requires frequent analysis of sales figures from different stores, so queries heavily use the sum() function, consuming significant system resources.

   SELECT store_id, SUM(sale_amt)
   FROM load_test.sales_records
   GROUP BY store_id;

   The output shows that the query takes 25 ms.

   The result set has three rows. The sum(sale_amt) values for store_id 1, 2, and 3 are 12892, 16463, and 12946, respectively.

3. Run EXPLAIN to view the Query Profile for the query.

   EXPLAIN SELECT store_id, SUM(sale_amt)
   FROM load_test.sales_records
   GROUP BY store_id;

   In the output, the rollup field shows sales_records (the base table), which means the query was not accelerated by a materialized view.

Create a synchronized materialized view

Run the CREATE MATERIALIZED VIEW statement to create a synchronized materialized view for a specific query.

Based on the query above, the following example creates a synchronized materialized view for the sales_records table to group by store and sum the sales amount for each store.

CREATE MATERIALIZED VIEW store_amt AS
SELECT store_id, SUM(sale_amt)
FROM load_test.sales_records
GROUP BY store_id;

Check the build status

Creating a synchronized materialized view is an asynchronous operation. A successful return from the CREATE MATERIALIZED VIEW statement indicates that the creation task has been submitted successfully. Run SHOW ALTER MATERIALIZED VIEW to check the build status of synchronized materialized views in the current database.

USE load_test;
SHOW ALTER MATERIALIZED VIEW;

In the output, RollupIndexName shows the name of the synchronized materialized view (store_amt). A State of FINISHED indicates that the materialized view has been successfully created on the sales_records table.

Query a synchronized materialized view

A synchronized materialized view is essentially an index on the base table, not a physical table. To query it directly, you must use the [_SYNC_MV_] hint.

SELECT * FROM <mv_name> [_SYNC_MV_];-- Do not omit the brackets [] in the hint.

In this example, run the following command to query the synchronized materialized view.

USE load_test;
SELECT * FROM store_amt [_SYNC_MV_];

Accelerate queries with query rewrite

The new synchronized materialized view pre-computes and stores the results for the preceding query. Subsequent queries can read these results directly for faster execution. After the view is created, run the same query again to measure the performance improvement.

SELECT store_id, SUM(sale_amt)
FROM load_test.sales_records
GROUP BY store_id;

The output shows that the query now takes 17 ms, a significant reduction.

The query returns the same three rows as before: the sum(sale_amt) is 12892 for store_id 1, 16463 for store_id 2, and 12946 for store_id 3.

Verify materialized view hits

You can run EXPLAIN again to check whether the query hits the synchronized materialized view.

EXPLAIN SELECT store_id, SUM(sale_amt)
FROM load_test.sales_records
GROUP BY store_id;

In the output from the Query Profile, the rollup field now shows store_amt, confirming that the query used the synchronized materialized view.

View materialized view schema

You can run the DESC <tbl_name> ALL command to view the schema of a table and all its synchronized materialized views.

USE load_test;
DESC sales_records ALL;

Example output:

The result includes two index definitions. The first, for sales_records (type DUP_KEYS), lists the base table's five fields: record_id(int), seller_id(int), store_id(int), sale_date(date), and sale_amt(bigint). The second, for store_amt (type AGG_KEYS), lists the materialized view's two fields: store_id(int) and mv_sum_sale_amt(bigint). The Extra value for mv_sum_sale_amt is SUM, which indicates that the SUM aggregate function is used.

Drop a synchronized materialized view

You may need to drop a synchronized materialized view in the following situations:

• Creation error: If an error occurs during creation, you must drop the failed view.

• Performance degradation: A large number of synchronized materialized views can slow data loading. You may also have redundant views that are safe to remove.

• Low usage: If query frequency is low and your workload can tolerate higher query latency, consider dropping it.

Drop a view during creation

To drop a synchronized materialized view that is being created, you can cancel the creation task. First, get the JobID of the creation task by following the instructions in Check the build status. Then, run the CANCEL ALTER command to cancel the task.

CANCEL ALTER TABLE ROLLUP FROM sales_records (12090);

Drop a created view

You can run the DROP MATERIALIZED VIEW <mv_name> command to drop an existing synchronized materialized view.

USE load_test;
DROP MATERIALIZED VIEW store_amt;

Best practices

Exact deduplication

The following example uses the advertiser_view_record table, which contains ad click details: click date (click_time), advertiser (advertiser), channel (channel), and user ID (user_id).

CREATE TABLE advertiser_view_record (
    click_time DATE,
    advertiser VARCHAR(10),
    channel VARCHAR(10),
    user_id INT
) DISTRIBUTED BY HASH(click_time);

In this scenario, you frequently run the following statement to query unique visitors (UV) for ad clicks.

SELECT advertiser, channel, count(distinct user_id)
FROM advertiser_view_record
GROUP BY advertiser, channel;

To accelerate exact deduplication queries, create a synchronized materialized view on the detail table and use the bitmap_union() function for pre-aggregation.

CREATE MATERIALIZED VIEW advertiser_uv AS
SELECT advertiser, channel, bitmap_union(to_bitmap(user_id)) AS user_bitmap
FROM advertiser_view_record
GROUP BY advertiser, channel;

After the synchronized materialized view is created, subsequent COUNT(DISTINCT user_id) queries are automatically rewritten to use the pre-aggregated bitmap data, allowing the query to efficiently hit the materialized view.

Approximate deduplication

Using the advertiser_view_record table as an example, if you need to perform approximate deduplication for UV queries, you can create a synchronized materialized view on the detail table and use the hll_union() function to pre-aggregate data.

CREATE MATERIALIZED VIEW advertiser_uv2 AS
SELECT advertiser, channel, hll_union(hll_hash(user_id)) AS user_hll
FROM advertiser_view_record
GROUP BY advertiser, channel;

Creating a prefix index

For example, the base table tableA contains columns k1, k2, and k3, where k1 and k2 are sort keys. If your workload includes queries with a WHERE clause on k3 (for example, WHERE k3 = x) and you want to use a prefix index to accelerate them, you can create a synchronized materialized view with k3 as its first column. This effectively makes k3 a prefix index, improving retrieval speed and reducing query response time.

CREATE MATERIALIZED VIEW k3_as_key AS
SELECT k3, k2, k1
FROM tableA;