Ali Cloud Log service is committed to providing users with a unified observability platform, and supports query and storage of logs, time series and Trace data. Based on the collected data, users can build unified monitoring and service trays to detect system exceptions and service trends in a timely manner. However, as the volume of data collected continues to grow, especially during business peaks, there will be significant delays in large report presentations and the inability to view important data in a timely manner. Scheduled SQL supports Scheduled analysis of data, storage of aggregated data, projection and filtering of data, and storing the results of executed analysis in a user-specified log or timing library for subsequent analysis. Since the amount of data after aggregation will be much smaller than before, it is ideal for real-time data analysis and mass presentation.

background

Ali Cloud log service is committed to providing users with a unified observability platform, and supports query and storage of logs, time series and Trace data. Based on the collected data, users can build unified monitoring and service trays to detect system exceptions and service trends in a timely manner. However, as the volume of data collected continues to grow, especially during business peaks, there will be significant delays in large report presentations and the inability to view important data in a timely manner. Scheduled SQL supports Scheduled analysis of data, storage of aggregated data, projection and filtering of data, and storing the results of executed analysis in a user-specified log or timing library for subsequent analysis. Since the amount of data after aggregation will be much smaller than before, it is ideal for real-time data analysis and mass presentation. Using the success rate of requests for a service as an example, we’ll show you how to speed up bulk reporting based on Scheduled SQL.

plan

Suppose we need to look at the success rate of requests in one minute, in one hour. When constructing a report, you need to configure a real-time report based on the current part of the data less than one minute, and configure a historical report based on the historical data that has been more than one minute. Of course, if the user feels that a one-minute data delay is acceptable, he can configure only the historical report and not the real-time report. If the current time is 11:09:47, you need to view the success rate of minute-level requests from 10:11:00 to 11:09:00 and the success rate of second-level requests from 11:09:00 to 11:09:47.

Log contents

History report

The following figure shows the minute-level request success rate. You can configure the minute-level ScheduledSQL task to calculate the success rate per minute and display it in a historical report. Because only the aggregation results need to be directly pulled, there is no need for immediate calculation, so the display speed is greatly improved.

Real-time reports

The figure shows the success rate of requests at the second level, and the speed is increased because less than a minute of data is computed instead of an hour of data.

configuration

The success rate of requests is still used as an example to introduce how to accelerate reports through ScheduledSQL.

Create the target timing library

The first step is to create the target timing library to store the aggregated data for ScheduledSQL.

Create Scheduled SQL tasks

On the LogStore query screen for the data stored in the service request, enter the query statement and click the Query/Analyze button. After the query analysis has been successfully executed, click the Create Scheduled SQL button.

*| select  (__time__ - __time__ % 60) as time , sum(IF(status = 200, 1, 0)) * 1.0 / count(*) AS success_ratio  from log group by time  order by time
Copy the code

Calculate the configuration

  1. Fill in the corresponding job name and job description, and select log library to import the timing library in writing mode.
  2. Metric column indicates the sequential result column, where Success_ratio is selected.
  3. Labels refer to which columns in the selection result should be used as the Labels of time series data, which can be left blank.
  4. Time column refers to the time of time series data, time is selected here;
  5. Target library Selects the target timing library that you just created.

Scheduling configuration

Because we need to look at the success rate of service requests at the minute level, scheduling intervals and SQL time Windows both need to be minute grained. Users can also adjust according to their own needs.

  1. The scheduling interval is 1 minute.
  2. SQL time window fill @m-1m ~ @m;
  3. Click ok to create the task

Viewing Task Details

A list of Scheduled SQL tasks is available by clicking Scheduled SQL from the Jobs menu. Click the created task name to view the task execution details. After the task is successfully executed, we can create the history report.

Configuring historical Reports

In the query interface of the target timing sequence library, execute the query statement and click Add to Dashboard to create the historical report.

* | select promql_query_range('success_ratio') from metrics limit 1000
Copy the code

Configuring real-time Reports

On the LogStore query page, enter the query statement, set the time range to 1 minute, and click Add to Dashboard to create a real-time report.

*| select  __time__ as time , sum(IF(status = 200, 1, 0)) * 1.0 / count(*) AS success_ratio  from log group by time  order by time
Copy the code

conclusion

Scheduled SQL provides great convenience for periodically analyzing, storing aggregate, projecting, and filtering data. Users can also use Scheduled SQL to perform Scheduled aggregation tasks on a Scheduled basis, reducing the amount of data required for real-time queries and speeding up bulk presentations.

The original link

This article is the original content of Aliyun and shall not be reproduced without permission.