Simple Log Service:Use Simple Log Service SDK for Java to create a Scheduled SQL job

Sample code

In this example, an App.java file is created to store analysis results from a source Logstore to a destination Logstore. Example:

import com.aliyun.openservices.log.Client;
import com.aliyun.openservices.log.common.*;
import com.aliyun.openservices.log.exception.LogException;
import com.aliyun.openservices.log.request.CreateScheduledSQLRequest;

public class App {
    // In this example, the AccessKey ID and AccessKey secret are obtained from environment variables. 
    static String accessId = System.getenv("ALIBABA_CLOUD_ACCESS_KEY_ID");
    static String accessKey = System.getenv("ALIBABA_CLOUD_ACCESS_KEY_SECRET");

    // Specify a project name and a Logstore name. 
    static String sourceProject="aliyun-test-sourceProject";
    static String destProject="aliyun-test-destProject";
    static String sourceLogstore = "logstore-name";
    static String destLogstore = "project-name";
    static String roleArn = "acs:ram::11111111:role/aliyunlogetlrole";
    // The Simple Log Service endpoint. In this example, the Simple Log Service endpoint for the China (Hangzhou) region is used. Replace the parameter value with the actual endpoint. 
    static String endpoint = "http://cn-hangzhou.log.aliyuncs.com";
    static String destEndpoint = "http://cn-hangzhou-intranet.log.aliyuncs.com";
    static long fromTime = 1648105200; //2022-03-23 15:00:00
    private static String script = "* | select a,b,c from log";

    private static ScheduledSQLBaseParameters generateParams(String dataFormat) {
        if (dataFormat.equalsIgnoreCase("log2log")) {
            return null;
        } else if (dataFormat.equalsIgnoreCase("log2metric")) {
            Log2MetricParameters params = new Log2MetricParameters();
            params.setMetricKeys("[\"a\", \"b\", \"c\"]");
            params.setLabelKeys("[\"d\", \"e\", \"f\"]");
            params.setHashLabels("[\"d\", \"f\"]");
            params.setAddLabels("{\"m\":\"h\", \"n\":\"i\"}");
            params.setTimeKey("time");
            return params;
        } else if (dataFormat.equalsIgnoreCase("metric2metric")) {
            Metric2MetricParameters params = new Metric2MetricParameters();
            params.setMetricName("name");
            params.setHashLabels("[\"d\", \"f\"]");
            params.setAddLabels("{\"m\":\"h\", \"n\":\"i\"}");
            return params;
        }
        return null;
    }

    private static ScheduledSQLConfiguration generateConfig() {
        ScheduledSQLConfiguration scheduledSQLConfiguration = new ScheduledSQLConfiguration();
        scheduledSQLConfiguration.setScript(script);
        scheduledSQLConfiguration.setSqlType("searchQuery");
        scheduledSQLConfiguration.setResourcePool("enhanced");
        scheduledSQLConfiguration.setRoleArn(roleArn);
        scheduledSQLConfiguration.setDestRoleArn(roleArn);
        scheduledSQLConfiguration.setSourceLogstore(sourceLogstore);
        scheduledSQLConfiguration.setDestEndpoint(destEndpoint);
        scheduledSQLConfiguration.setDestProject(destProject);
        scheduledSQLConfiguration.setDestLogstore(destLogstore);
        scheduledSQLConfiguration.setDataFormat("log2log");
        scheduledSQLConfiguration.setFromTimeExpr("@m-1m");
        scheduledSQLConfiguration.setToTimeExpr("@m");
        scheduledSQLConfiguration.setMaxRetries(20);
        scheduledSQLConfiguration.setMaxRunTimeInSeconds(600);
        scheduledSQLConfiguration.setFromTime(fromTime);
        scheduledSQLConfiguration.setToTime(0L);

        ScheduledSQLBaseParameters params = generateParams(scheduledSQLConfiguration.getDataFormat());
        scheduledSQLConfiguration.setParameters(params);

        return scheduledSQLConfiguration;
    }

    private static ScheduledSQL generateScheduledSQL() {
        ScheduledSQL scheduledSQLStructure = new ScheduledSQL();
        scheduledSQLStructure.setName("job-name");
        scheduledSQLStructure.setDisplayName("display-name");
        scheduledSQLStructure.setDescription("desc-name");
        ScheduledSQLConfiguration scheduledSQLConfiguration = generateConfig();
        scheduledSQLStructure.setConfiguration(scheduledSQLConfiguration);
        JobSchedule jobSchedule = new JobSchedule();
        jobSchedule.setType(JobScheduleType.FIXED_RATE);
        jobSchedule.setInterval("1m");
        jobSchedule.setDelay(10);
        jobSchedule.setRunImmediately(false);
        scheduledSQLStructure.setSchedule(jobSchedule);
        return scheduledSQLStructure;
    }

    public static void main(String[] args) {
        Client client = new Client(endpoint, accessId, accessKey);
        ScheduledSQL scheduledSQL = generateScheduledSQL();
        CreateScheduledSQLRequest request = new CreateScheduledSQLRequest(sourceProject, scheduledSQL);
        try {
            client.createScheduledSQL(request);
        } catch (LogException e) {
            e.printStackTrace();
        }
    }
} 

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

• ScheduledSQL

  You can call the createScheduledSQL operation to create a Scheduled SQL job. The following table describes the parameters.

  Parameter	Example	Description
  name	export-123-456	The name of the Scheduled SQL job. In the Simple Log Service console, you can click a Scheduled SQL job to view the name of the job.
  displayName	my-scheduled-sql-job	The display name of the Scheduled SQL job. In the Simple Log Service console, you can choose Job Management > Scheduled SQL to view the display names of Scheduled SQL jobs.
  description	this is a scheduled sql job.	The description of the Scheduled SQL job.
  configuration	scheduledSQLConfiguration	The basic settings of the Scheduled SQL job. For more information, see ScheduledSQLConfiguration.
  schedule	jobSchedule	The scheduling settings of the Scheduled SQL job. For more information, see JobSchedule.

• JobSchedule

  You can call JobSchedule jobSchedule = new JobSchedule(); to configure scheduling settings for a Scheduled SQL job. The following table describes the parameters.

  Parameter	Example	Description
  type	FixedRate	The frequency at which the Scheduled SQL job is scheduled. An instance is generated each time the Scheduled SQL job is scheduled. This parameter specifies the scheduled time for each instance. Valid values: FixedRate: The Scheduled SQL job is scheduled at a fixed interval. The fixed interval is specified by the interval parameter. Hourly: The Scheduled SQL job is scheduled every hour. Daily: The Scheduled SQL job is scheduled at a fixed point in time every day. Cron: The Scheduled SQL job is scheduled at an interval that is specified by a cron expression.
  interval	50m	The fixed interval at which the Scheduled SQL job is scheduled. If you set type to FixedRate, you must configure this parameter. 3s: The interval is 3 seconds. 5m: The interval is 5 minutes. 2h: The interval is 2 hours.
  cronExpression	None	The cron expression based on which the Scheduled SQL job is scheduled. If you set type to Cron, you must specify a cron expression. If you use a cron expression, the specified interval is accurate to minutes based on the 24-hour clock. For example, the expression 0 0/1 * * * indicates that the Scheduled SQL job is scheduled every hour from 00:00. If you want to specify the time zone, select Cron. For more information about common time zones, see Time zones.
  delay	10s	The number of seconds for which the instance is delayed from the scheduled time. Valid values: 0 to 120. Unit: seconds. If latency exists when data is written to the destination Logstore, you can use this parameter to ensure data integrity.

• ScheduledSQLConfiguration

  You can call ScheduledSQLConfiguration scheduledSQLConfiguration = generateConfig(); to configure the basic settings for a Scheduled SQL job. The following table describes the parameters.

  Parameter	Example	Description
  script	*|select count(1)	The query statement that you want to use.
  sqlType	searchQuery	The SQL type. Set the value to searchQuery.
  resourcePool	enhanced	The type of the resource pool. Set the value to enhanced. The resource pool that is used for data analysis. Simple Log Service provides an enhanced type of resource pool.
  roleArn	acs:ram::11111111:role/aliyunlogetlrole	The Alibaba Cloud Resource Name (ARN) of the RAM role that is used to read data from the source Logstore. For information about how to obtain the ARN, see Step 1: Grant the RAM role the permissions to analyze data in a source Logstore.
  destRoleArn	acs:ram::11111111:role/aliyunlogetlrole	The ARN of the RAM role that is used to write data to the destination Logstore. For information about how to obtain the ARN, see one of the following topics based on your business scenario: If the source Logstore and the destination Logstore belong to the same Alibaba Cloud account, obtain the ARN by following the instructions that are provided in Grant the RAM role the permissions to write data to a destination Logstore. If the source Logstore and the destination Logstore belong to different Alibaba Cloud accounts, obtain the ARN by following the instructions that are provided in Grant RAM Role B the permissions to write data to a destination Logstore.
  sourceLogstore	source-logstore	The name of the source Logstore.
  destEndpoint	http://cn-hangzhou-intranet.log.aliyuncs.com	The endpoint for the destination Logstore. Note Simple Log Service supports communication with Alibaba Cloud services over an internal network. For example, if you want to access a Simple Log Service resource from an Elastic Compute Service (ECS) instance that resides in the same region as the Simple Log Service resource, we recommend that you use an internal endpoint in the following format: http://cn-hangzhou-intranet.log.aliyuncs.com. Simple Log Service supports access over the Internet. For example, if you want to access a Simple Log Service resource by using an API or SDK from an on-premises server over the Internet, we recommend that you use a public endpoint in the following format: http://cn-hangzhou.log.aliyuncs.com. Compared with an internal endpoint, a public endpoint involves one more billable item, namely, read traffic over the Internet. For more information, see Billable items. For more information, see Endpoints.
  destProject	my-project	The name of the destination project to which data is written.
  destLogstore	my-logstore	The name of the destination Logstore to which data is written.
  dataFormat	log2log	The write mode. Valid values: log2log: The Scheduled SQL job processes data in a source Logstore and stores the processed data to a destination Logstore. log2metric: The Scheduled SQL job processes data in a source Logstore and stores the processed data to a destination Metricstore. metric2metric: The Scheduled SQL job processes data in a source Metricstore and stores the processed data to a destination Metricstore.
  fromTimeExpr	@m - 12s	The expression that is used to specify the start time of the SQL time window. For more information, see Time expression syntax.
  toTimeExpr	@m	The expression that is used to specify the end time of the SQL time window. For more information, see Time expression syntax.
  maxRetries	10	The threshold of automatic retries if the SQL analysis operation fails. If the number of retries for an instance exceeds the threshold that you specify, the instance stops retrying and enters the FAILED state.
  maxRunTimeInSeconds	60	The threshold of automatic retries if the SQL analysis operation fails. This parameter specifies the maximum amount of time that is allowed for retries. If an instance is retrying for a period that exceeds the specified time, the instance stops retrying and enters the FAILED state.
  fromTime	1653965045	The start time of the time range during which the Scheduled SQL job is scheduled. Important The instances of the Scheduled SQL job can run only within the specified time range. After the end of the time range, the Scheduled SQL job no longer generates instances.
  toTime	1653968045	The end time of the time range during which the Scheduled SQL job is scheduled. The value 0 indicates that no end time is specified for a Scheduled SQL job.
  params	None	The SQL settings. If you set dataFormat to log2metric or metric2metric, you must configure this parameter. For more information, see Log2MetricParameters and Metric2MetricParameters.

  • When you create a Scheduled SQL job to process data in a source Logstore and store the processed data to a destination Metricstore, you must also configure the following parameters.

    Table 1 Log2MetricParameters

    Parameter	Example	Description
    metricKeys	"[\"a\", \"b\", \"c\"]"	The metric columns. This parameter corresponds to the Metric Column parameter below the SQL Settings parameter in the Simple Log Service console. Simple Log Service aggregates data based on the query statement that you entered. You can select one or more columns of the numeric data type in the query results for this parameter. For more information, see Metric.
    labelKeys	"[\"d\", \"e\", \"f\"]"	The labels. This parameter corresponds to the Labels parameter below the SQL Settings parameter in the Simple Log Service console. Simple Log Service aggregates data based on the query statement that you entered. You can select one or more columns in the query and analysis results for this parameter. For more information, see Metric.
    hashLabels	"[\"d\", \"f\"]"	Specifies whether to hash data. This parameter corresponds to the Rehash parameter below the SQL Settings parameter in the Simple Log Service console. If you turn on Rehash, you can configure the Hash Column parameter to write data that has the same value for a column to the same shard. This improves data locality and query efficiency. Valid values of the Hash Column parameter vary based on the query and analysis results. You can select one or more columns in the query and analysis results as hash columns. For example, if you set Hash Column to status, data that has the same value for status is written to the same shard.
    addLabels	"[\"m\":\"h\", \"n\":\"i\"]"	This parameter corresponds to the Additional Labels parameter below the SQL Settings parameter in the Simple Log Service console. The static labels that are used to identify the attributes of a metric. Each label is a key-value pair. For example, you can set label_key to app and label_value to ingress-nginx.
    timeKey	time	The time column. This parameter corresponds to the Time Column parameter below the SQL Settings parameter in the Simple Log Service console. If you select the time column whose values are UNIX timestamps in the query and analysis results for this parameter, the system uses the values of the time column to indicate the time of metrics. Example: atime:1627025331. If you set the parameter to Null, the system uses the start time of the query statement to indicate the time of metrics.

    

  • When you create a Scheduled SQL job to process data in a source Metricstore and store the processed data to a destination Metricstore, you must also configure the following parameters.

    Table 2 Metric2MetricParameters

    Parameter	Example	Description
    metricName	my-metric	The new name of the metric that you select for analysis. If you want to change the name of the metric, you can specify a new name for the metric in this parameter. For more information, see Metric. Important If you select a single metric for analysis, we recommend that you configure this parameter to rename the metric. If you select multiple metrics for analysis and you configure this parameter, all metrics are renamed with the name that you specify.
    hashLabels	"{\"m\":\"h\", \"n\":\"i\"}"	Specifies whether to hash data. This parameter corresponds to the Rehash parameter below the SQL Settings parameter in the Simple Log Service console. If you turn on Rehash, you can configure the Hash Column parameter to write data that has the same value for a column to the same shard. This improves data locality and query efficiency. Valid values of the Hash Column parameter vary based on the existing label information of your metrics. For example, if the existing label information of your metrics is {"alert_id":"alert-1608815762-545495","alert_name":"Alert clearance disabled","status":"inactive"}, the valid values of the Hash Column parameter are alert_id, alert_name, and status. If you set Hash Column to status, the metrics that have the same value for status are written to the same shard.
    addLabels	"{\"m\":\"h\", \"n\":\"i\"}"	This parameter corresponds to the Additional Labels parameter below the SQL Settings parameter in the Simple Log Service console. The static labels that are used to identify the attributes of a metric. Each label is a key-value pair. For example, you can set label_key to app and label_value to ingress-nginx.