This topic describes the real-time analysis feature of Log Service and the limits and SQL syntax of this feature.

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 index 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 Query 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.

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

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,048 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