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 leverages Dedicated SQL computing power, provides sufficient concurrency, and isolates resources from your console SQL operations. You are charged based on CPU time consumed. 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. 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. Select one or more columns with numeric values from the results as metric columns. For more information, see time series data.
      Labels	Simple Log Service aggregates data based on your query and analysis statement. Select one or more columns from the results as labels. For more information, see time series data.
      Rehash	Enable Rehash to configure a Hash Column. Data with the same hash column value is written to a single shard, improving data locality and query efficiency. Select one or more columns from the query and analysis results 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	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	Grant the Scheduled SQL job 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	Grant the Scheduled SQL job permission to read data from the source Logstore and run SQL analysis 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 run generates an execution instance. 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. 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. The system retries automatically on failure. If retries exceed this timeout or the maximum retry count, the instance status changes to Failed. You can then manually retry 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. Set a delay to ensure data completeness when ingestion into the Metricstore lags behind.

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

SDK examples

Create a Scheduled SQL job