Simple Log Service:Improve query performance on large-scale data with materialized views

Why use materialized views

SLS enables real-time data analysis using SQL. Each SQL query analyzes all data within a specified time range. For large datasets, the standard execution mode may return inaccurate results, time out, or exceed concurrency limits.

High-performance fully accurate query and analysis (Dedicated SQL) significantly increases computing power. However, this mode has a data size limit and may result in long execution times for very large datasets.

For scenarios with strict requirements on SQL execution time—such as dashboard refresh—consider using materialized views. Materialized views incrementally pre-compute and save intermediate results of SQL sub-statements. When you refresh a report, the system automatically uses these pre-computed results to significantly improve performance.

How it works

Step 1: Configure permissions

An Alibaba Cloud account has all permissions by default and requires no extra authorization. If you are a Resource Access Management (RAM) user, ensure you have the following permissions:

Step 2: Import the SDK

Import the following version of the SLS SDK for Java:

<dependency>
    <groupId>com.aliyun.openservices</groupId>
    <artifactId>aliyun-log</artifactId>
    <version>0.6.138</version>
</dependency>

Step 3: Create, list, get, and delete materialized views

The following example shows how to create, list, retrieve details of, and delete materialized views. Modify and run the code as needed.

Obtain parameters:

• To obtain the AccessKey ID and AccessKey secret, see Create an AccessKey pair.

• To obtain the host endpoint:

  1. Log on to the Simple Log Service console.

  2. In the Projects list, click the target project.

  3. Click the icon next to the project name to go to the project overview page. In the Endpoint section, copy the public endpoint.

import com.aliyun.openservices.log.Client;
import com.aliyun.openservices.log.exception.LogException;
import com.aliyun.openservices.log.request.CreateMaterializedViewRequest;
import com.aliyun.openservices.log.request.ListMaterializedViewsRequest;
import com.aliyun.openservices.log.response.GetMaterializedViewResponse;
import com.aliyun.openservices.log.response.ListMaterializedViewsResponse;

import java.text.SimpleDateFormat;
import java.util.concurrent.TimeUnit;

public class MvDemo
{
    static String accessId = System.getenv("ALIBABA_CLOUD_ACCESS_KEY_ID");
    static String accessKey = System.getenv("ALIBABA_CLOUD_ACCESS_KEY_SECRET");
    /**
     * The SLS endpoint. This example uses Hangzhou. Change it to your actual region.
     */
    static String host = "cn-hangzhou.log.aliyuncs.com";
    /**
     * Create an SLS client.
     */
    static Client client = new Client(host, accessId, accessKey);
    /**
     * The project name.
     */
    static String projectName = "xxx";
    /**
     * The logstore name.
     */
    static String logstoreName = "xxx";

    public static void main(String[] args) throws Exception {
        String materializedViewName = "test_mv";

        createMv(materializedViewName); // Create a materialized view with the specified name.

        listMv(); // Get the list of materialized views in the current project.

        getMv(materializedViewName); // Get information about the current materialized view.
        
        // deleteMv(materializedViewName); // Delete the materialized view.
    }

    static int dateStrToSecond(String dateStr) throws Exception {
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        return (int)TimeUnit.MILLISECONDS.toSeconds(simpleDateFormat.parse(dateStr).getTime());
    }

    static void createMv(String materializedViewName) throws Exception {
        // The SQL query to accelerate with the materialized view.
        String originalSql = "* | select count(l1) as cnt, l2 from stability group by l2";
        // The scheduled materialization period in minutes.
        int aggIntervalMins = 60;
        // The start time for materialization. Based on the write time of raw data.
        // Only raw data after this time is materialized.
        int startTime = dateStrToSecond("2025-07-30 00:00:00");
        // The TTL of the materialization logstore.
        // A value of 0 means it is the same as the source logstore's TTL.
        // The TTL of the materialization logstore cannot be less than the source logstore's TTL.
        int ttl = 0;
        // The number of materialized views in a single project cannot exceed 100.
        CreateMaterializedViewRequest request = new CreateMaterializedViewRequest(
            projectName, materializedViewName, logstoreName, originalSql, aggIntervalMins, startTime, ttl);
        client.createMaterializedView(request);
        System.out.println("create materialized view " + materializedViewName);
    }

    static void listMv() throws LogException
    {
        // 0 and 10 are parameters for paginated queries.
        ListMaterializedViewsRequest request = new ListMaterializedViewsRequest(projectName, "", 0, 10);
        ListMaterializedViewsResponse response = client.listMaterializedViews(request);
        System.out.println("total materialized view count: " + response.getTotal());
        for (String materializedView : response.getMaterializedViews()) {
            System.out.println(materializedView);
        }
    }

    static void getMv(String materializedViewName) throws Exception
    {
        GetMaterializedViewResponse response = client.getMaterializedView(projectName, materializedViewName);
        System.out.println("get materialized view detail, name: " + materializedViewName);
        System.out.println("originalSql: " + response.getOriginalSql());
        System.out.println("startTime: " + response.getStartTime());
        System.out.println("ttl: " + response.getTtl());
    }

    static void deleteMv(String materializedViewName) throws LogException {
        client.deleteMaterializedView(projectName, materializedViewName);
        System.out.println("delete materialized view " + materializedViewName);
    }

}