Process and save data from a Logstore to another Logstore - Simple Log Service

Simple Log Service provides the Scheduled SQL feature. You can use the feature to analyze data at a scheduled time and aggregate data for storage. You can also use the feature to project and filter data. Simple Log Service allows you to process data in a source Logstore by configuring a Scheduled SQL job and save the processed data to a destination Logstore.

Prerequisites

Data is collected to a source Logstore. For more information, see Data collection overview.
A destination Logstore is created. For more information, see Create a Logstore.
Indexes are configured for the source and destination Logstores. For more information, see Create indexes.

Important The Logstores that are described in this topic are Standard Logstores. For more information, see Manage a Logstore.

Procedure

Important

The Scheduled SQL feature is in public preview. If you enable the feature, you are charged only for the computing resources that are consumed by Dedicated SQL. For more information, see Billable items of pay-by-feature.

Log on to the Log Service console.
In the Projects section, click the project that you want to manage.
On the Log Storage > Logstores tab, click the Logstore that you want to manage.
Enter a query statement in the search box, click 15 Minutes(Relative), and then specify a time range for the query.
For more information, see Procedure.
Note
This step allows you to preview data before you create a Scheduled SQL job. You can check whether the query statement that you entered is valid and whether the query and analysis result contains data.
On the Graph tab, click Save as Scheduled SQL Job.

Create a Scheduled SQL job.

In the Compute Settings step, configure the parameters and click Next. The following table describes the parameters.

Parameter	Description
Job Name	The name of the Scheduled SQL job.
Job Description	The description of the Scheduled SQL job.
Resource Pool	The resource pool that is used for data analysis. Simple Log Service provides an enhanced type of resource pool. The enhanced type of resource pool utilizes the computing capability of Dedicated SQL to meet concurrent analysis requirements. It isolates resources between Scheduled SQL and your SQL analysis operations in the console. You are charged for the enhanced type of resource pool based on the CPU time that is consumed by your SQL analysis operations. For more information, see Enable Dedicated SQL.
Write Mode	Select Import Data from Logstore to Logstore. The Scheduled SQL job processes the data in the source Logstore and saves the processed data to the destination Logstore.
SQL Code	The query statement. By default, the system displays the statement that you entered in 4. The preview operation that is provided for this parameter has the same effect as the preview operation in 4. You can click Preview to check whether the query statement is valid and whether the query and analysis result contains data. When the Scheduled SQL job runs, Simple Log Service executes the query statement to analyze data.
Source Project/Logstore	The project and Logstore to which the source data belongs.
Target
Target Region	The region where the destination project resides.
Target Project	The name of the destination project that stores the result of the query statement.
Target Store	The name of the destination Logstore that stores the result of the query statement.
Write Authorization	The method that is used to authorize the Scheduled SQL job to write data to the destination Logstore. Valid values: Default Role: The Scheduled SQL job assumes the AliyunLogETLRole system role to write the analysis result to the destination Logstore. Important The first time that you create a Scheduled SQL job, authorization is required and must be completed by using the Alibaba Cloud account to which the destination project belongs. You do not need to perform authorization when you create subsequent Scheduled SQL jobs. Custom Role: The Scheduled SQL job assumes a custom role to write the analysis result to the destination Logstore. You must grant the custom role the permissions to write data to the destination Logstore. Then, enter the Alibaba Cloud Resource Name (ARN) of the custom role in the Role ARN field. 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 Step 2: 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 Step 2: Grant RAM Role B the permissions to write data to a destination Logstore.
SQL Execution Authorization	The method that is used to authorize the Scheduled SQL job to read data from the source Logstore and analyze the data by using query statements in the current project. Valid values: Default Role: The Scheduled SQL job assumes the AliyunLogETLRole system role to perform the required operations. Important The first time that you create a Scheduled SQL job, authorization is required and must be completed by using the Alibaba Cloud account to which the destination project belongs. You do not need to perform authorization when you create subsequent Scheduled SQL jobs. Custom Role: The Scheduled SQL job assumes a custom role to perform the required operations. You must grant the custom role the required permissions. Then, enter the ARN of the custom role in the Role ARN field. For more information, see Step 1: Grant the RAM role the permissions to analyze log data in a source Logstore.

In the Scheduling Settings step, configure the parameters and click OK. The following table describes the parameters.

Parameter	Description
Specify Scheduling Interval	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: Hourly: The Scheduled SQL job is scheduled every hour. Daily: The Scheduled SQL job is scheduled at a fixed point in time every day. Weekly: The Scheduled SQL job is scheduled at a fixed point in time on a fixed day of each week. Fixed Interval: The Scheduled SQL job is scheduled at a fixed interval. Cron: The Scheduled SQL job is scheduled at an interval that is specified by a cron expression. A cron expression can specify an interval that is accurate to minutes. The cron expression is based on the 24-hour clock. For example, 0 0/1 * * * indicates that the Scheduled SQL job is scheduled at an interval of 1 hour from 00:00. If you want to specify the time zone, select Cron. For information about common time zones, see Time zones.
Scheduling Time Range	The time range during which the Scheduled SQL job is scheduled. Valid values: From Specific Time: If you select this option, you must specify the point in time at which the first instance of the Scheduled SQL job starts to run. Within Specific Period: If you select this option, you must specify a time range within which the instances of the Scheduled SQL job can run. 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.
SQL Time Window	The time window of logs that are analyzed when the Scheduled SQL job runs. This parameter must be configured together with the Scheduling Time Range parameter. The duration that is specified by this parameter can be up to five times the duration that is specified by the Specify Scheduling Interval parameter. The start time and end time of the time window must be within 24 hours. For more information, see Time expression syntax. For example, the Specify Scheduling Interval parameter is set to Fixed Interval 10 Minutes, the Start Time parameter is set to 2021-04-01 00:00:00, the Delay Task parameter is set to 30 Seconds, and the SQL Time Window parameter is set to [@m-10m,@m). In this example, the first instance of the Scheduled SQL job is generated at 00:00:30 to analyze the logs that fall within the time range `[23:50:00,00:00:00)`. For more information, see Scheduling and running scenarios.
SQL Timeout	The threshold of automatic retries if the SQL analysis operation fails. If the retry period exceeds the specified maximum period or the number of retries exceeds the upper limit, the instance stops and the instance status changes to FAILED. You can retry the instance based on the failure cause. For more information, see Retry a scheduled SQL instance.
Delay Task	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.

Sample SDKs

Use Log Service SDK for Java to create a Scheduled SQL task