Use Simple Log Service SDK for Java to use the Dedicated SQL feature - Simple Log Service

When you use an SQL statement to analyze data, the Standard SQL feature cannot scan all logs within the time period specified in a single query request. In this scenario, you can enable the Dedicated SQL feature to increase computing resources and the amount of data that can be analyzed in a single query request. This topic describes how to use Simple Log Service SDK for Java to use the Dedicated SQL feature.

Prerequisites

Simple Log Service is activated. For more information, see Activate Simple Log Service.
A Resource Access Management (RAM) user is created, and the required permissions are granted to the RAM user. For more information, see Create a RAM user and grant permissions to the RAM user.
The ALIBABA_CLOUD_ACCESS_KEY_ID and ALIBABA_CLOUD_ACCESS_KEY_SECRET environment variables are configured. For more information, see Configure environment variables in Linux, macOS, and Windows.
Important
- The AccessKey pair of an Alibaba Cloud account has permissions on all API operations. We recommend that you use the AccessKey pair of a RAM user to call API operations or perform routine O&M.
- We recommend that you do not save the AccessKey ID or AccessKey secret in your project code. Otherwise, the AccessKey pair may be leaked, and the security of all resources within your account may be compromised.
Simple Log Service SDK for Java V0.6.68 or later is installed. For more information, see Install Simple Log Service SDK for Java.

Background information

Simple Log Service provides the Dedicated SQL feature to enhance SQL analysis capabilities. You can use the feature to process hundreds of billions of data records. For more information, see Enable Dedicated SQL.

Usage notes

In this example, the public Simple Log Service endpoint for the China (Hangzhou) region is used, which is https://cn-hangzhou.log.aliyuncs.com. If you want to access Simple Log Service by using other Alibaba Cloud services that reside in the same region as your project, you can use the internal Simple Log Service endpoint, which is https://cn-hangzhou-intranet.log.aliyuncs.com. For more information about the supported regions and endpoints of Simple Log Service, see Endpoints.

Step 1: Create a Simple Log Service client

        // In this example, the AccessKey ID and AccessKey secret are obtained from environment variables. 
        String accessId = System.getenv("ALIBABA_CLOUD_ACCESS_KEY_ID");
        String accessKey = System.getenv("ALIBABA_CLOUD_ACCESS_KEY_SECRET");
        // Enter the project name. 
        String project = "aliyun-test-project";
        // Specify the Simple Log Service endpoint. In this example, the Simple Log Service endpoint for the China (Hangzhou) region is used. Replace this parameter value with your actual endpoint. 
        String host = "cn-hangzhou.log.aliyuncs.com"; 
        // Enter the name of the Logstore. 
        String logStore = "aliyun-test-logstore";
        // Specify the shard ID. 
        int shardId = 0;
        
        // Create a Simple Log Service client. 
        Client client = new Client(host, accessId, accessKey);

Step 2: Use the Dedicated SQL feature

Before you can use the Dedicated SQL feature, check whether the feature is enabled. For more information, see Enable Dedicated SQL.

Simple Log Service provides the executeLogStoreSql and executeProjectSql operations. You can call the operations to use the Dedicated SQL feature in an efficient manner.

For more information, see Alibaba Cloud Simple Log Service SDK for Java.

Note

If you want to filter data before you analyze the data, we recommend that you call the executeLogStoreSql operation and specify a query statement in the Search statement|Analytic statement format to improve analysis efficiency.

executeLogStoreSql

You can call this operation to use the Dedicated SQL feature in a specific Logstore. This operation supports the standard SQL-92 syntax. A query statement is in the Search statement|Analytic statement format, and the analytic statement follows the standard SQL-92 syntax.

import com.aliyun.openservices.log.Client;
import com.aliyun.openservices.log.common.LogContent;
import com.aliyun.openservices.log.common.LogItem;
import com.aliyun.openservices.log.common.QueriedLog;
import com.aliyun.openservices.log.exception.LogException;
import com.aliyun.openservices.log.response.GetLogsResponse;

import java.util.Date;

public class SlsSample {

    public static void main(String args[]) throws LogException,
            InterruptedException {
        // In this example, the AccessKey ID and AccessKey secret are obtained from environment variables. 
        String accessId = System.getenv("ALIBABA_CLOUD_ACCESS_KEY_ID");
        String accessKey = System.getenv("ALIBABA_CLOUD_ACCESS_KEY_SECRET");
        // Enter the project name. 
        String project = "aliyun-test-project";
        // Specify the Simple Log Service endpoint. In this example, the Simple Log Service endpoint for the China (Hangzhou) region is used. Replace this parameter value with your actual endpoint. 
        String host = "cn-hangzhou.log.aliyuncs.com";
        // Enter the name of the Logstore. 
        String logStore = "aliyun-test-logstore";
        // Specify the shard ID. 
        int shardId = 0;

        // Create a Simple Log Service client. 
        Client client = new Client(host, accessId, accessKey);

        // Execute an SQL statement in the specified Logstore. 
        try {
            String sql = "* | select count(1)";
            int from = (int) (new Date().getTime() / 1000 - 600);
            int to = (int) (new Date().getTime() / 1000);
            GetLogsResponse logsResponse = client.executeLogstoreSql(project, logStore, 1627268185, 1627269085, "* | SELECT count(*)", true);
            System.out.println("Returned sql result count:" + logsResponse.GetCount());
            for (QueriedLog log : logsResponse.getLogs()) {
                LogItem item = log.GetLogItem();
                System.out.println("time : " + item.mLogTime);
                for (LogContent content : item.mContents) {
                    System.out.println(content.mKey + ":" + content.mValue);
                }
            }
            // Display the statistics about the analysis results. 
            // The number of lines of log data that is processed. 
            System.out.println("proccesedRows:" + logsResponse.getProcessedRow());
            // The time that is consumed to execute the SQL statement. 
            System.out.println("elapsedMilli:" + logsResponse.getElapsedMilliSecond());
            // The CPU time that is consumed to execute the SQL statement after the Dedicated SQL feature is enabled. Unit: seconds. You are charged for the Dedicated SQL feature based on the CPU time. For more information, see the topics that are related to billable items. 
            System.out.println("cpuSec:" + logsResponse.getCpuSec());
            // The number of CPU cores that are used to execute the SQL statement after the Dedicated SQL feature is enabled. 
            System.out.println("cpuCores:" + logsResponse.getCpuCores());
            System.out.println("requestId:" + logsResponse.GetRequestId());

        } catch (LogException e) {
            System.out.println("error code :" + e.GetErrorCode());
            System.out.println("error message :" + e.GetErrorMessage());
            throw e;
        }
    }
}

You can call the executeLogStoreSql operation to use the Dedicated SQL feature. You must create a request in the GetLogsResponse logsResponse = client.executeLogstoreSql(project, logStore, from, to, query, powerSql) format. The following table describes the parameters.

Parameter	Type	Required	Example	Description
project	String	Yes	N/A	The name of the project. When you create a Simple Log Service client, you must specify a value for the `project` parameter. Therefore, you do not need to configure the parameter again.
logStore	String	Yes	N/A	The name of the Logstore. When you create a Simple Log Service client, you must specify a value for the `logStore` parameter. Therefore, you do not need to configure the parameter again.
from	Long	Yes	1627268185	The beginning of the time range to query. The value is a UNIX timestamp representing the number of seconds that have elapsed since January 1, 1970, 00:00:00 UTC.
to	Long	Yes	1627269085	The end of the time range to query. The value is a UNIX timestamp representing the number of seconds that have elapsed since January 1, 1970, 00:00:00 UTC.
query	String	Yes	"* \| SELECT count(*)"	Simple Log Service For more information, see Syntax. By default, Simple Log Service returns 100 rows of data. You can use a LIMIT clause to specify the number of data rows to return. For more information, see LIMIT clause.
powerSql	Boolean	No	true	Specifies whether to use the Dedicated SQL feature. For more information, see Enable Dedicated SQL. Valid values: true: uses the Dedicated SQL feature. false (default): uses the Standard SQL feature.

executeProjectSql

import com.aliyun.openservices.log.Client;
import com.aliyun.openservices.log.common.LogContent;
import com.aliyun.openservices.log.common.LogItem;
import com.aliyun.openservices.log.common.QueriedLog;
import com.aliyun.openservices.log.exception.LogException;
import com.aliyun.openservices.log.response.GetLogsResponse;

import java.util.Date;

public class SlsSample {

    public static void main(String args[]) throws LogException,
            InterruptedException {
        // In this example, the AccessKey ID and AccessKey secret are obtained from environment variables. 
        String accessId = System.getenv("ALIBABA_CLOUD_ACCESS_KEY_ID");
        String accessKey = System.getenv("ALIBABA_CLOUD_ACCESS_KEY_SECRET");
        // Enter the project name. 
        String project = "aliyun-test-project";
        // Specify the Simple Log Service endpoint. In this example, the Simple Log Service endpoint for the China (Hangzhou) region is used. Replace this parameter value with your actual endpoint. 
        String host = "cn-hangzhou.log.aliyuncs.com"; 
        // Enter the name of the Logstore. 
        String logStore = "aliyun-test-logstore";
        // Specify the shard ID. 
        int shardId = 0;
        
        // Create a Simple Log Service client. 
        Client client = new Client(host, accessId, accessKey);
        // Execute an SQL statement in the specified project. 
        try {
            int now = (int) (new Date().getTime() / 1000);
            String sql = "select count(1) as cnt from xxx where __time__ > " + now;
            GetLogsResponse logsResponse = client.executeProjectSql(project, "select avg(latency),max(latency) ,count(1) as c from sample-logstore where status>200 and __time__>=1500975424 and __time__ < 1501035044 GROUP BY method ORDER BY c", true);
            System.out.println("Returned sql result count:" + logsResponse.GetCount());
            for (QueriedLog log : logsResponse.getLogs()) {
                LogItem item = log.GetLogItem();
                for (LogContent content : item.mContents) {
                    System.out.println(content.mKey + ":" + content.mValue);
                }
            }
            // Display the statistics about the analysis results. 
            // The number of lines of log data that is processed. 
            System.out.println("proccesedRows:" + logsResponse.getProcessedRow());
            // The time that is consumed to execute the SQL statement. 
            System.out.println("elapsedMilli:" + logsResponse.getElapsedMilliSecond());
            // The CPU time that is consumed to execute the SQL statement after the Dedicated SQL feature is enabled. Unit: seconds. You are charged for the Dedicated SQL feature based on the CPU time. For more information, see the topics that are related to billable items. 
            System.out.println("cpuSec:" + logsResponse.getCpuSec());
            // The number of CPU cores that are used to execute the SQL statement after the Dedicated SQL feature is enabled. 
            System.out.println("cpuCores:" + logsResponse.getCpuCores());
            System.out.println("requestId:" + logsResponse.GetRequestId());
        } catch (LogException e) {
            System.out.println("error code :" + e.GetErrorCode());
            System.out.println("error message :" + e.GetErrorMessage());
            throw e;
        }
    }
}

You can call the executeProjectSql operation to use the Dedicated SQL feature. You must create a request in the GetLogsResponse logsResponse = client.executeProjectSql(project, query, powerSql) format. The following table describes the parameters.

Parameter	Type	Required	Example	Description
project	String	Yes	N/A	The name of the project. When you create a Simple Log Service client, you must specify a value for the `project` parameter. Therefore, you do not need to configure the parameter again.
query	String	Yes	"select avg(latency),max(latency) ,count(1) as c from sample-logstore where status>200 and __time__>=1500975424 and __time__ < 1501035044 GROUP BY method ORDER BY c"	By default, Simple Log Service returns 100 rows of data. You can use a LIMIT clause to specify the number of data rows to return. For more information, see LIMIT clause. Note To write a standard SQL statement, you must specify the filter conditions and query time range in a WHERE clause of the SQL statement. You can include the `__time__` field in the `WHERE` clause to specify the query time range.
powerSql	Boolean	No	true	Specifies whether to use the Dedicated SQL feature. For more information, see Enable Dedicated SQL. Valid values: true: uses the Dedicated SQL feature. false (default): uses the Standard SQL feature.

References

For more information about how to enable the Dedicated SQL feature in the Simple Log Service console, see Enable Dedicated SQL.
For more information about how to enable the Dedicated SQL feature by using the API, see CreateSqlInstance.

If the response that is returned by Simple Log Service contains error information after you call an API operation, the call fails. You can handle errors based on the error codes that are returned when API calls fail. For more information, see Error codes.
Alibaba Cloud OpenAPI Explorer provides debugging capabilities, SDKs, examples, and related documents. If you use OpenAPI Explorer, you can debug Simple Log Service API operations without the need to manually encapsulate requests or sign the requests. For more information, visit OpenAPI Explorer.
Simple Log Service provides the command-line interface (CLI) to meet the requirements for automated configurations in Simple Log Service. For more information, see Overview of Simple Log Service CLI.
For more information about sample code, see Alibaba Cloud Simple Log Service SDK for Java on GitHub.
For more information about sample code, see Alibaba Cloud Simple Log Service SDK for Python on GitHub.