Simple Log Service:Create a scheduled SQL job to import Logstore data to a Metricstore

Procedure

1. Log on to the Simple Log Service console.

2. In the Project list, click the target Project.

3. On the Log Storage > Logstore tab, click the target Logstore.

4. Enter a query and analysis statement, and then click Last 15 Minutes to set a time range for the query and analysis.

   For more information, see Step 1: Configure an index.

   Note

   Use this step to preview the Scheduled SQL job and verify that your query and analysis statement is valid and returns data.

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

6. Create a Scheduled SQL job.

   1. In the Compute Settings wizard, configure the following parameters and click Next.

      Parameter	Description
      Task Name	The unique name of the Scheduled SQL job.
      Display Name	The display name of the Scheduled SQL job.
      Job Description	The description of the Scheduled SQL job.
      Resource Pool	Simple Log Service provides enhanced resource pools for data analysis. An enhanced resource pool reuses the computing power of Dedicated SQL, provides sufficient concurrency for analysis, and isolates resources from your SQL analysis operations in the console. You are charged for an enhanced resource pool based on the CPU time consumed by SQL analysis. For more information, see High-performance and high-accuracy query and analysis (Dedicated SQL).
      Write Mode	Select Import Data from Logstore to Metricstore to process data from the source Logstore and store it in the destination Metricstore.
      SQL Code	Displays the query and analysis statement that you entered in Step 4. You can preview the results to verify that the statement is valid and returns data. When the job runs, Simple Log Service executes this statement to analyze the data.
      SQL Settings
      Metric Column	Simple Log Service aggregates data based on your query and analysis statement. You can select one or more columns with numeric values from the query and analysis results as metric columns. For more information, see time series data.
      Labels	Simple Log Service aggregates data based on your query and analysis statement. You can select one or more columns from the query and analysis results as labels. For more information, see time series data.
      Rehash	Enabling Rehash allows you to configure a Hash Column. This writes data with the same value in the hash column to a single shard, improving data locality and query efficiency. The value of the Hash Column depends on the query and analysis results. You can select one or more columns from the query and analysis results to use as the hash column. For example, if you set the Hash Column to status, data with the same status field value is written to the same shard.
      Time Column	If you select a column that contains Unix timestamps (for example, atime:1627025331), the system uses these values as the timestamps for the time series data. If you select Null, the system uses the start time of the query and analysis time range as the timestamp of the time series data. For more information, see time series data.
      Additional Labels	Add static labels in key-value pairs to identify related attributes of the metrics. For example, set label_key to app and label_value to ingress-nginx.
      Source Project/Logstore	Displays the Project and Logstore where the source data resides.
      Target
      Destination Region	The region where the destination Project is located.
      Destination Project	The name of the destination Project where the SQL analysis results are stored.
      Target Store	The name of the destination Metricstore where the SQL analysis results are stored. Warning Do not configure the destination store as the current source store (same-source configuration). Otherwise, logs may be written in a loop, which incurs additional storage and traffic costs. You are responsible for the resource consumption and costs incurred.
      Write Authorization	Use one of the following methods to grant the Scheduled SQL job the permission to write data to the destination Metricstore. Default Role: Authorizes the Scheduled SQL job to use the system role AliyunLogETLRole to write the execution results to the destination Metricstore. Custom Role: Authorizes the Scheduled SQL job to use a custom role to write the execution results to the destination Metricstore. You must first grant the required permissions to the custom RAM role, and then enter its ARN in the Role ARN field. For more information, see Grant a custom RAM role the permission to write data to a destination Logstore.
      SQL Execution Authorization	Use one of the following methods to grant the Scheduled SQL job the permission to read data from the source Logstore and run SQL analysis operations in the current Project. Default Role: Authorizes the Scheduled SQL job to use the system role AliyunLogETLRole to perform the required operations. Custom Role: Authorizes the Scheduled SQL job to use a custom role to perform the required operations. You must first grant the required permissions to the custom role, and then enter its ARN in the Role ARN field. For more information, see Grant a custom RAM role the permission to analyze data in a source Logstore.

   2. In the Scheduling Settings wizard, configure the following parameters and click OK.

      Parameter	Description
      Scheduling Interval	How often the job runs. Each scheduled run generates an execution instance, and the interval determines when each instance is scheduled. Fixed Interval: Schedules the job at a fixed interval. Cron: Schedules the job based on a cron expression. The minimum precision for a cron expression is one minute. The time is in the 24-hour format. For example, 0 0/1 * * * indicates that the job runs every hour, starting from 00:00. Select Cron if you need to configure a time zone. For a list of common time zones, see Time zone formats.
      Scheduling Time Range	The time range during which the job is scheduled. The following options are available: From Specific Time: Specify the start time for the first execution instance. Specific Time Range: Specify a start and end time for scheduling. The job can be executed only within this time range. Note The scheduled time of an execution instance must be within this range. The job stops generating new instances if the scheduled time falls outside this range. The scheduling time range corresponds to the __time__ reserved field. For more information, see Reserved fields.
      SQL Time Window	The time range of logs to be analyzed when the Scheduled SQL job runs. The SQL time window works in conjunction with the scheduled time of the execution instance. The time range cannot be more than five times the Scheduling Interval and cannot exceed one day. For more information, see Time expression syntax. For example, if the Scheduling Interval is a fixed interval of 10 minutes, the Start At is 2021-04-01 00:00:00, the Delay Task is 30 seconds, and the SQL Time Window is [@m-10m,@m), the first execution instance is generated at 00:00:30. It analyzes logs from the time range [23:50:00, 00:00:00). For more information, see Scheduling and execution scenarios. Note The SQL time window corresponds to the __time__ reserved field. For more information, see Reserved fields. If __time__ is not defined in the SQL code, the __time__ of the data written to the destination Metricstore defaults to the start time of the SQL time window.
      SQL Timeout	The timeout for retrying failed SQL analysis operations. If an operation fails, the system retries automatically. The instance stops and its status changes to Failed if retries exceed this timeout or the maximum retry count. You can then manually retry the instance based on the failure cause. For more information, see Retry a Scheduled SQL job instance.
      Delay Task	The time to delay execution after the scheduled time, in seconds. The value must be an integer from 0 to 120. Use an execution delay to ensure data completeness when data ingestion into the Metricstore is delayed.

      After you create the Scheduled SQL job, you can view the execution results in the destination Metricstore.

SDK examples

Create a Scheduled SQL job