Real-time log analysis - Index and query| Alibaba Cloud Documentation Center

Overview

Note To use the real-time analysis feature, you must turn on the Enable Analytics switch for one or more fields in the field index. For more information, see Enable and configure the indexing feature for a Logstore.

After you turn on the Enable Analytics switch, analysis within seconds is supported and incurs no additional charges.

The real-time analysis feature of Log Service allows you to search for log data and then analyze the log data based on SQL syntax. In addition, the feature returns the results within seconds.

You can execute a query statement to search and analyze log data. Each query statement consists of a search statement and an analytic statement. They are separated by a vertical bar (|). The search statement uses proprietary syntax. For more information, see Search syntax.

Format of a query statement
```
Search statement|Analytic statement
```

Example

status>200 |select avg(latency),max(latency),count(1) as c GROUP BY  method  ORDER BY c DESC  LIMIT 20

SQL syntax

This section lists the SQL syntax that Log Service supports.

Aggregate functions that can be used in SELECT statements
GROUP BY syntax
Window functions
HAVING syntax
ORDER BY syntax
LIMIT syntax
CASE WHEN and IF syntax
UNNEST function
Column aliases
Nested subquery
INSERT syntax

Additional considerations about SQL syntax

Do not specify the FROM or WHERE clause in an analytic statement. This is because logs are queried from the current Logstore and the WHERE clause is replaced by the search statement in Log Service.
An analytic statement can include the following clauses: SELECT, GROUP BY, ORDER BY [ASC,DESC], LIMIT, and HAVING.

Scenarios

Interactive analysis
Visual chart generation
Alerting

Limits

You can perform a maximum of 15 concurrent queries in each project.
By default, you can analyze only the log data that is collected after the Enable Analytics switch is turned on.
The maximum size of a field value is 2 KB. If the size of a field value exceeds the maximum, the value is truncated.
By default, a maximum of 100 rows of data is returned for each query. For information about how to retrieve more rows for a query, see LIMIT syntax.

System fields

Log service provides some system fields to facilitate log analysis. For more information, see Reserved fields.


System field	Data type	Description
__time__	bigint	The timestamp of a log entry.
__source__	varchar	The source of a log entry. Note To reference this field, use source in a search statement and __source__ in an analytic statement.
__topic__	varchar	The topic of a log entry.

Example

To count the number of page views (PVs) and unique visitors (UVs) per hour and query the user requests with the 10 longest latency periods in each hour, you can use the following statement:

*|select date_trunc('hour',from_unixtime(__time__)) as time, 
     count(1) as pv, 
     approx_distinct(userid)  as uv,
     max_by(url,latency) as top_latency_url,
     max(latency,10) as top_10_latency
     group by  1
     order by time