Creating Task
Download
フォーカスモード
フォントサイズ
This document describes how to create a scheduled SQL analysis task.
Prerequisites
CLS has been activated, and key-value indexing is enabled for the source log topic key-value indexing.
Ensure that your current operating account has the permission to configure scheduled SQL analysis. For details, see CLS Access Policy Template.
Operation Steps
1. Log in to the CLS console, select Scheduled SQL Analysis in the left sidebar, and click Create.
2. Configure a scheduled SQL analysis task. The write target can be categorized into metric topics and log topics.
Write target is a metric topic: Use a query statement to convert logs into metrics and store them in a metric topic.
Write target is a log topic: It is used for scenarios such as log filtering and log pre-aggregation.
Scenarios:
Pre-aggregation. For example, logs are reported every 10 seconds. If your business needs to query 60 days of data:
*| select count(*) group by ip, request_method, the number of IP address entries may exceed 100 million within 60 days. Due to the high dimensionality, the query will fail. In this case, you can use log-to-metric conversion to pre-aggregate logs into minute-level metric data. Then, querying the aggregated metric topic will no longer time out.Advantages of metric storage:
Metric queries have lower costs: Only storage and write traffic fees are charged for metrics, with no index traffic or index storage fees (which apply to logs). For the same data volume, metric query costs are lower. For details, see Product Pricing.
Storage costs: For businesses that need to query data over long time ranges, after logs are converted to metrics, you can archive the original logs to STANDARD_IA storage (which does not support SQL but allows search), further reducing log storage costs.
Query speed is faster. For details, see Log-to-Metric Conversion.
Configuration Items:
Configuration Item | Description |
Task Name | The name of the scheduled SQL task, with a maximum length of 255 characters, consisting of underscores, English letters, and numbers. |
Enabling Status | Enabled by default, enabling/disabling the task. |
Service Logs | Enable/Disable. After enabled, monitoring metrics of the scheduled SQL task run will be written to the service log, such as single query results and query time. |
Source Log Topic | The input for the scheduled SQL task, namely, the log topic where the original logs to be converted into metrics are located. |
Target Topic | Metric Topic/Log Topic. The output of the scheduled SQL task, select metric topic to store the converted metrics in the specified metric topic. If there is no metric topic currently, you can create a metric topic. |
Query statement | * | select avg(cpu_usage)as cpu_usage ,avg(mem_usage) as mem_usage,namespace,cluster group by namespace,cluster limit 10000Note: If you modify the SQL statement, click Preview to refresh the SQL query results. The number of query results will be returned according to the limit you specified. To return all data, click the configuration button on the right side of the execution statement, disable the SQL Result Default Limit switch, and then delete the limit in the execution statement. |
Metric Name | Multiple selection, typically of numeric type. Select fields from the dropdown list as metric names. For example, in the example above, cpu_usage and mem_usage are metric names, and their values are metric values. |
Metric Dimension | Multiple selection. Do not select fields of time type as metric dimensions. Select fields from the dropdown list as metric dimensions. For example, in the example above, namespace and cluster are dimensions, and their values are dimension values. This can be understood as viewing cpu_usage and mem_usage according to different combinations of cluster and namespace. Metric dimensions are generally the dimension fields in your SQL statement's group by clause (excluding fields of time type). |
Custom Dimension | Adding dimensions to metrics, where the dimension values are static. For example: Your original metric dimensions are. go_gc_duration_seconds{job="prometheus",quantile="0"}After the custom dimension tcloud_region_name="ap-guangzhou" is added, the dimensions become:go_gc_duration_seconds{job="prometheus", quantile="0",tcloud_region_name="ap-guangzhou" } |
Timestamp | Default value: The default timestamp for a metric is the left boundary time of the query time window. For example, if the query time window is May 7, 00:00:00 - May 7, - 00:10:00, then the metric timestamp generated by this calculation is May 7, 00:00:00. Custom timestamp: If you have defined a time-type field in the SQL statement, you can select that field as the metric timestamp from the dropdown list. If you do not see that time-type field, click Preview below the SQL statement to refresh the field values in the dropdown list. For example, you use a search and analysis statement. * | select count(*) as cnt,histogram(__TIMESTAMP__,INTERVAL 15 SECOND) as time group by time limit 100 Counts the number of log entries every 15 seconds. You can select the time field as your custom timestamp. In this way, a metric data point will be generated every 15 seconds in the metric topic.Note: The minimum interval for a custom timestamp is 15 seconds.Note: Custom timestamps support selection of UNIX time and TimeStamp types, with millisecond precision. Examples: 1629876543.123, 2021-08-25T12:22:23.123+08:00. The minimum interval for a custom timestamp is 15 seconds. Examples: 15:00:00, 15:00:15, 15:00:30. |
Duration (Scope) | The time range of log data processed by the scheduled SQL task, for example, logs from 2023-01-01 00:00:00 to 2023-03-31 00:00:00. If no end time is selected, the task will run continuously, converting newly collected logs into metrics in real time. |
Scheduling cycle | The value range is 1 minute - 1440 minutes. If you configure it as X, it means that a log query will be initiated every X minutes, and the results of the log query will be saved as metrics. It is recommended to configure 1 minute to convert logs into metrics more promptly. |
Query Time Window (Query Window) | It is recommended to configure @m-1m to @m, which means the most recent 1 minute. Each time, it queries one minute of log data and converts it into metrics. Note: When logs are converted to metrics, do not set this parameter to exceed 30 minutes, otherwise the conversion may fail. Quick configuration: If you select "Last 5 minutes" and the current time is 17:30, the query time window is 17:25 - 17:30. Time expression: This is used for relative time expressions. If you specify a time expression as @m-10m to @m-5m and the current time is 17:30, the query time window is 17:20 - 17:25. |
Advanced Settings | Query delay time, configurable in the console Advanced Settings with a value range of 60 seconds to 120 seconds. Index generation for logs typically has a delay. Data cannot be queried before the index is generated. Therefore, a 60-second query delay is set, by which time the index has been generated (99.9% of the index data will be generated within 5 seconds). |
Scenarios:
Filter logs :
*| SELECT ip where region="guangzhou" Note:
Scheduled SQL execution has a 1-minute delay. For example, to query data from 11:01-11:02, the query is initiated at 11:03 and returns a maximum of 1 million data entries. Evaluate whether this meets your scenario requirements.
Pre-aggregation: For example, logs are reported every 10 seconds. If your business needs to query 60 days of data:
*| SELECT ip , AVG(request_time) AS avg_request_time GROUP BY ip, the number of IP address entries may exceed 100 million within 60 days. Due to the high dimensionality, the query will fail. In this case, you can use Scheduled SQL to pre-aggregate second-level logs into minute-level data. Then, querying the aggregated data will no longer time out.Configuration Items:
Configuration Item | Description |
Task Name | The name of the scheduled SQL task, with a maximum length of 255 characters, consisting of underscores, English letters, and numbers. |
Enabling Status | Enabled by default, enabling/disabling the task. |
Service Logs | Enable/Disable. After enabled, monitoring metrics of the scheduled SQL task run will be written to the service log, such as single query results and query time. |
Source Log Topic | The input for the scheduled SQL task, namely, the original logs to be processed. |
Target Topic | The output of the scheduled SQL task (1), select log topic, and it is recommended to configure the index of the target topic in advance. |
Query statement | Use SQL statements to statistically analyze original logs. For example: if the scheduling cycle is configured as 1 minute and the query time window is configured as 1 hour, the SQL statement * | select sum(active_user) as hourly_active_user limit 10000 means counting the number of active customers in the last hour once per minute.Note: If you modify the SQL statement, click Preview to refresh the SQL query results. The number of query results will be returned according to the limit you specified. To return all data, click the configuration button on the right side of the execution statement, disable the SQL Result Default Limit switch, and then delete the limit in the execution statement. |
Duration (Scope) | The time range of log data processed by the scheduled SQL task, for example, logs from 2023-01-01 00:00:00 to 2023-03-31 00:00:00. If no end time is specified, the task runs continuously by default. |
Scheduling cycle | The value range is 1 minute - 1440 minutes. If you configure it as 1 minute, it means that a log query will be initiated every 1 minute. |
Query Time Window (Query Window) | Query Time Window, the time window for a single log query. Quick configuration: If you select "Last 5 minutes" and the current time is 17:30, the query time window is 17:25 - 17:30. Time expression: This is used for relative time expressions. If you specify a time expression as @m-10m to @m-5m and the current time is 17:30, the query time window is 17:20 - 17:25. |
Advanced Settings | Query delay time, configurable in the console Advanced Settings with a value range of 60 seconds to 120 seconds. Index generation for logs typically has a delay. Data cannot be queried before the index is generated. Therefore, a 60-second query delay is set, by which time the index has been generated (99.9% of the index data will be generated within 5 seconds). |
Time Expression Syntax
When creating a Scheduled SQL analysis, you can specify an SQL time window. When the Scheduled SQL task runs, only logs within that SQL time window are queried. The following content describes the time expression syntax related to SQL time windows.
Operator | Note: |
+ | plus sign |
- | minus sign |
@ | The rounding operator rounds down based on time. For example, rounding down the time 01:40 to the hour yields 01:00. |
Time Unit | Note: |
h | Time |
m | Score |
s | Second |
Example
Time Expression | Note: |
@m-15m to @m-5m | First subtract 15 minutes and then round down to the minute. For example, when a scheduled SQL task is created, if you configure the scheduling interval to be daily at 00:00, the execution delay to be 30 seconds, and the SQL time window to be [@m-15m,@m-5m), it means the SQL task is executed at 00:00:30, analyzing data from the period [23:45~23:55). |
@h-1h to @h | First subtract 1 hour and then round down to the hour. For example, when you create a scheduled SQL task, if you configure the scheduling interval to be daily at 00:00, the execution delay to be 30 seconds, and the SQL time window to be [@h-1h@h,@m-5m), it means the SQL task is executed at 00:00:30, analyzing data from the period [23:00~23:55). |
@h-12h+5m to @m-5m | First subtract 12 hours and then add 5 minutes, which is equivalent to subtracting 11 hours and 55 minutes. For example, when a scheduled SQL task is created, if you configure the scheduling interval to be daily at 00:00, the execution delay to be 30 seconds, and the SQL time window to be [@h-12h+5m,@m-5m), it means the SQL task is executed at 00:00:30, analyzing data from the period [12:05~23:55). |
フィードバック