Call the CreateScheduledSQL API to create a scheduled SQL task.
The Simple Log Service SDK no longer updates APIs related to scheduled SQL. Use the Alibaba Cloud SDK to manage scheduled SQL:
On the CreateScheduledSQL API debugging page in the Alibaba Cloud OpenAPI Explorer, click the SDK Example tab on the right panel. Select your target language (Java, TypeScript, Go, PHP, Python, .NET, C++, etc.) to view the corresponding SDK sample code. You can click Run Example for online debugging or Download Full Project.
Prerequisites
Simple Log Service is activated. For more information, see Activate Simple Log Service.
Simple Log Service SDK for Java is initialized. For more information, see Initialize Simple Log Service SDK for Java.
Background information
Simple Log Service provides scheduled SQL to periodically analyze data, store aggregate data, and perform projection and filtering. Scheduled SQL supports standard SQL92 syntax and Simple Log Service query and analysis syntax. It runs periodically based on scheduling rules and writes results to a destination database (Logstore or Metricstore).
The Simple Log Service console offers a visual interface to create scheduled SQL tasks. For details, see Create a scheduled SQL task.
In addition, Simple Log Service provides the ScheduledSQL, JobSchedule, and ScheduledSQLConfiguration classes to simplify creating scheduled SQL tasks through the Java SDK.
-
ScheduledSQL: Creates a scheduled SQL task.
-
JobSchedule: Creates a scheduling configuration for the scheduled SQL task.
-
ScheduledSQLConfiguration: Creates basic configuration for the scheduled SQL task.
Parameter descriptions
Request parameters
|
Name |
Type |
Required |
Description |
Example |
|
project |
String |
Yes |
Project name. |
ali-test-project |
|
scheduledSql |
Object |
Yes |
Scheduled SQL task configuration. |
- |
ScheduledSQL
The following table describes the parameters.
|
Parameter Name |
Type |
Required |
Description |
Example |
|
name |
String |
Yes |
Name of the scheduled SQL task. Follow these naming rules:
|
export-123-456 |
|
displayName |
String |
Yes |
Display name of the scheduled SQL task. In the Simple Log Service console, choose to view the list of display names. |
my-scheduled-sql-job |
|
description |
String |
No |
Description of the scheduled SQL task. |
this is a scheduled sql job. |
|
configuration |
Object |
Yes |
Scheduled SQL configuration. |
- |
|
schedule |
Object |
Yes |
Task scheduling configuration. |
- |
JobSchedule
Call JobSchedule jobSchedule = new JobSchedule(); to create a scheduling configuration for the scheduled SQL task. The following table describes the parameters.
|
Parameter name |
Type |
Required |
Description |
Example |
|
type |
String |
Yes |
Frequency for scheduling the scheduled SQL task. Each schedule creates one execution instance. The interval determines the scheduled time for each instance.
|
FixedRate |
|
interval |
String |
No |
Specifies the fixed interval when type is FixedRate.
|
50m |
|
cronExpression |
String |
No |
Specifies the cron expression when type is Cron. The minimum precision of a cron expression is one minute, using 24-hour format. For example, To configure a time zone, select Cron mode. For a list of common time zones, see Time zone format. |
N/A |
|
runImmediately |
boolean |
No |
Specifies whether to run the scheduled task immediately. |
False |
|
timeZone |
String |
No |
Time zone for the cron expression. Default is empty, which means UTC+8. |
+0800 |
|
delay |
int |
No |
Delay after the scheduled time before execution starts. Valid values: 0 to 120. Unit: seconds. If data written to a Logstore experiences delays or similar issues, you can use delayed execution to ensure data integrity. |
10 |
ScheduledSQLConfiguration
Call ScheduledSQLConfiguration scheduledSQLConfiguration = generateConfig(); to create configuration for the scheduled SQL task. The following table describes the parameters.
|
Parameter Name |
Type |
Required |
Description |
Example |
|
script |
String |
Yes |
Query and analysis statement. |
*|select count(1) |
|
sqlType |
String |
Yes |
SQL type. Set to searchQuery. |
searchQuery |
|
resourcePool |
String |
Yes |
Resource pool type. Set to enhanced. Simple Log Service provides enhanced resource pools for data analytics. |
enhanced |
|
roleArn |
String |
Yes |
ARN of the RAM role used to read data from the source Logstore. For instructions on how to obtain an ARN, see Grant a custom RAM role permissions to access the source LogStore. |
|
|
destRoleArn |
String |
Yes |
ARN of the RAM role used to write data to the destination Logstore. For instructions on how to obtain an ARN, see the following:
|
|
|
sourceLogstore |
String |
Yes |
Name of the source Logstore. |
source-logstore |
|
destEndpoint |
String |
Yes |
Endpoint of the destination Logstore. Note
For more information, see Endpoints. |
|
|
destProject |
String |
Yes |
Name of the destination project. |
my-project |
|
destLogstore |
String |
Yes |
Name of the destination Logstore. Warning
Do not set the destination database to the same as the source database. Doing so may cause logs to be written in a loop, resulting in extra storage and traffic charges. You are responsible for any resulting resource consumption and fees. |
my-logstore |
|
dataFormat |
String |
Yes |
Write mode.
|
log2log |
|
fromTimeExpr |
String |
Yes |
Start expression for the SQL time window. For more information, see Time expression syntax. |
@m - 12s |
|
toTimeExpr |
String |
Yes |
End expression for the SQL time window. For more information, see Time expression syntax. |
@m |
|
maxRetries |
Long |
Yes |
Maximum number of automatic retries if the SQL analysis operation fails. If the retry count exceeds this value, the execution instance ends with a failed status. |
10 |
|
maxRunTimeInSeconds |
Long |
Yes |
Maximum total retry duration in seconds if the SQL analysis operation fails. If the retry duration exceeds this value, the execution instance ends with a failed status. |
60 |
|
fromTime |
Long |
Yes |
Schedule start time. Important
Execution instances are created only within this time range. No new instances are created outside this range. |
1653965045 |
|
toTime |
Long |
Yes |
Schedule end time. Set to 0 for no end time. |
1653968045 |
|
parameters |
Object |
Yes |
When dataFormat is log2metric or metric2metric, configure SQL parameters. For details, see Log2MetricParameters and Metric2MetricParameters. |
|
parameters
-
When configuring a scheduled SQL task from a Logstore to a Metricstore, you must also configure the following additional parameters:
Table 1. Log2MetricParameters
Parameter Name
Example
Description
metricKeys
"[\"a\", \"b\", \"c\"]"Metric columns, corresponding to the metric columns in the console SQL configuration.
Simple Log Service aggregates data based on your query and analysis statement. You can select one or more numeric-type columns from the query results as metric columns. For more information, see Metrics.
labelKeys
"[\"d\", \"e\", \"f\"]"Label columns, corresponding to Labels in the console SQL configuration.
Simple Log Service aggregates data based on your query and analysis statement. You can select one or more columns from the query results as labels. For more information, see Metrics.
hashLabels
"[\"d\", \"f\"]"Corresponds to Rehash in the console SQL configuration.
After enabling the Rehash switch, you can configure hash columns to write data with the same column value into one shard. This improves data locality and query efficiency.
The available hash columns depend on your query and analysis results. You can select one or more columns from the results as hash columns. For example, if you set hash columns to status, all data with the same status value is written to the same shard.
addLabels
"[\"m\":\"h\", \"n\":\"i\"]"Corresponds to Additional Labels in the console SQL configuration.
Add static labels as key-value pairs to identify metric attributes.
For example, set label_key to app and label_value to ingress-nginx.
timeKey
time
Corresponds to Time Column in the console SQL configuration.
-
If you select a time column from the query results (with Unix timestamp values, such as
atime:1627025331), the system uses that column as the metric timestamp. -
If you select empty, the system uses the start time of the query time range as the metric timestamp.
-
-
When configuring a scheduled SQL task from a Metricstore to another Metricstore, you must also configure the following additional parameters:
Table 2. Metric2MetricParameters
Parameter Name
Example
Description
metricName
my-metric
Enter a new metric name if you want to rename the metric. For more information, see Metrics.
ImportantWe recommend renaming only when analyzing a single metric.
If you analyze multiple metrics and rename them, all metrics will share the same new name.
hashLabels
"{\"m\":\"h\", \"n\":\"i\"}"Corresponds to Rehash in the console SQL configuration.
After enabling the Rehash switch, you can configure hash columns to write data with the same label value into one shard. This improves data locality and query efficiency.
The available hash columns depend on existing labels in the metric data. For example, if the metric data includes labels
{"alert_id":"alert-1608815762-545495","alert_name":"Alert recovery closed","status":"inactive"}, valid hash columns are alert_id, alert_name, and status. If you set hash columns to status, all data with the same status value is written to the same shard.addLabels
"{\"m\":\"h\", \"n\":\"i\"}"Corresponds to Additional Labels in the console SQL configuration.
Add static labels as key-value pairs to identify metric attributes.
For example, set label_key to app and label_value to ingress-nginx.
Response parameters
For response parameter descriptions, see Create a scheduled SQL task.
Sample code
This example creates an App.java file that stores scheduled analysis results from a source Logstore into a destination Logstore. Sample code:
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 {
// This example retrieves the AccessKey ID and AccessKey secret from environment variables.
static String accessId = System.getenv("ALIBABA_CLOUD_ACCESS_KEY_ID");
static String accessKey = System.getenv("ALIBABA_CLOUD_ACCESS_KEY_SECRET");
// Set project and Logstore names.
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";
// Set the Simple Log Service endpoint. This example uses the China (Hangzhou) region. Replace it with your region.
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();
}
}
} References
-
For APIs to manage scheduled SQL tasks, see: