Simple Log Service:Create a Scheduled SQL job to process data in a source Logstore and store the processed data to a destination Logstore

Procedure

1. Log on to the Simple Log Service console of the source Logstore by using an Alibaba Cloud account or a Resource Access Management (RAM) user that can create a Scheduled SQL job.

2. In the Projects section, click the project to which the source Logstore belongs.

3. In the left-side navigation pane, click Log Storage. In the Logstores list, click the source Logstore whose data you want to analyze.

4. Enter a query statement and click Last 15 Minutes to specify a query time range.

   Note

   This step allows you to preview data before you create a Scheduled SQL job. You can check whether the entered query statement is valid and whether the query and analysis results contain data.

5. On the Graph tab, click Save as Scheduled SQL job.

   

6. Create a Scheduled SQL job.

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

      Parameter	Description
      Job Name	The unique name of the Scheduled SQL job. Retain the default value.
      Display Name	The display name of the Scheduled SQL job.
      Job Description	Optional. 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 pools. The enhanced type of resource pools reuse the computing capability of Dedicated SQL. The enhanced type of resource pools can meet concurrent analysis requirements and isolate resources between Scheduled SQL and your SQL analysis operations in the console. You are charged for the enhanced type of resource pools 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 feature processes data in the source Logstore and stores the processed data to the destination Logstore.
      SQL Code	The query statement. By default, the system displays the query statement that you entered in Step 4. If you want to use a different query statement, you can enter the code for the query statement. After you enter the query statement, specify a query time range and click Preview to verify the results. 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. You cannot modify this parameter.
      Target Within the same Alibaba Cloud account Parameter Description Destination Region The region where the destination project resides. Destination Project The name of the destination project. You can select a project from the Destination Project drop-down list. Target Store The name of the destination Logstore. You can select a Logstore from the Target Store drop-down list. Write Authorization The Scheduled SQL job can assume a default role or custom role to write data to the destination Logstore. Valid values: Default Role: The AliyunLogETLRole role has the permissions to perform SQL analysis operations in a source Logstore or Metricstore and write the SQL analysis results to a destination Logstore or Metricstore. For more information, see Use the default role to create a scheduled SQL job. Custom Role: You can create a custom role and a custom policy to perform fine-grained permission management. For more information, see Use a custom RAM role to create a Scheduled SQL job. Within different Alibaba Cloud accounts Parameter Description Destination Region Select Other Regions and read and select the Compliance Warranty on Cross-border Data Transfer agreement. Then, enter the public endpoint of the destination project. Example: cn-hangzhou.log.aliyuncs.com. Destination Project The name of the destination project. Example: test-project. Target Store The name of the destination Logstore. Example: test-logstore. Write Authorization Select Custom Role. For more information, see Grant the RAM role the permissions to write data to a destination Logstore.
      SQL Execution Authorization	The Scheduled SQL job can assume a default role or custom role to query and analyze data in the source Logstore. Valid values: Default Role: The AliyunLogETLRole role has the permissions to perform SQL analysis operations in a source Logstore or Metricstore and write the SQL analysis results to a destination Logstore or Metricstore. For more information, see Use the default role to create a scheduled SQL job. Custom Role: You can create a custom role and a custom policy to perform fine-grained permission management. For more information, see Grant a custom RAM role the permissions to analyze logs in a source Logstore.

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

      Parameter	Description
      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 determines the scheduled time for each instance. Valid values: 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 the minute. The cron expression is based on the 24-hour clock. For example, 0 0/1 * * * specifies that the Scheduled job runs at an interval of 1 hour from 00:00. If you want to specify a time zone, select Cron. For more 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. Specific Time Range: If you select this option, you must specify a time range within which the instances of the Scheduled SQL job can run. Note If you specify a time range, the instances of the Scheduled SQL job can run only within the specified time range. If the scheduled time of the instances falls outside the specified time range, the Scheduled SQL job no longer generates instances. The __time__ field specifies the time range. For more information, see Reserved fields.
      SQL Time Window	The time window of logs that are analyzed when the Scheduled SQL job runs. This parameter and the Scheduling Interval parameter take effect at a time. The duration specified by this parameter can be as long as five times the duration specified by the Scheduling Interval parameter. The start time and the end time of the time window must be within 24 hours. For more information, see Time expression syntax. For example, the Scheduling Interval parameter is set to Fixed Interval 10 Minutes, the Start At 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 are generated within the [23:50:00~00:00:00) time range. For more information, see Scheduling and running scenarios. Note The __time__ field specifies the SQL time window. For more information, see Reserved fields. If you do not specify the __time__ field in the SQL Code field, the time at which the Scheduled SQL job starts to write the processed data to the destination Logstore is the start time of the scheduling by default.
      SQL Timeout	The threshold of automatic retries if the SQL analysis operation fails. If an instance is retried for a period of time that exceeds the maximum time that you specify or the number of retries for an instance exceeds the upper limit that you specify, the instance stops retrying and enters the FAILED state. You can manually retry the instance based on the cause of the failure. For more information, see Manage a Scheduled SQL job.
      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 configure this parameter to ensure data integrity.

7. After you create a Scheduled SQL job, choose Job Management > Scheduled SQL to view the Scheduled SQL job. For more information, see Manage a Scheduled SQL job.

SDK example

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