Log Service supports aggregate calculation. This feature combines queries with SQL calculation capacities to calculate the query result. For more information, see Query syntax.

Syntax example:

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

Basic syntax:

[search query] | [sql query]

A search condition and a calculation condition are separated with a vertical bar (|). This syntax indicates that a search query is used to filter the logs you need, and an SQL query is used to calculate these logs. The search query syntax is specific to Log Service. For more information, see Query syntax.

Prerequisites

To use the statistical analysis feature, click Index Attributes. Turn on Enable Analytics for the target field. For more information, see Enable and configure the index feature for a Logstore.

  • If you do not enable the analytics feature, only up to 10,000 rows of data records can be calculated and analyzed per shard, and the latency is long.
  • After this feature is enabled, data analysis can be completed within several seconds.
  • Only data generated after you enable this feature can be analyzed.
  • No additional charges are incurred after this feature is enabled.

Supported SQL syntax

Log Service supports the following SQL syntax. For more information, see the following topics:

Syntax

The SQL syntax is described as follows:

  • The FROM and WHERE clauses are not required in SQL statements. The system uses the current Logstore for the FROM clause, and the search condition for the WHERE clause.
  • The supported clauses include SELECT, GROUP BY, ORDER BY [ASC,DESC], LIMIT, and HAVING.
Note By default, only the first 100 results are returned. To return more than 100 results, you must add limit n to the statement. Example: * | select count(1) as c, ip group by ip order by c desc limit 100.

Built-in fields

Log Service has built-in fields for statistical analysis. These built-in fields are automatically added when you configure a valid column.

Field name Type Description
__time__ bigint The time when the log is generated.
__source__ varchar The source IP address of the log. This field is source when you query data. The underscores (_) are added before and after source only in SQL statements.
__topic__ varchar The topic of the log.

Limits

  1. The maximum number of concurrent tasks for each project is 15.
  2. A single varchar column has the maximum length of 2048. The excess length is truncated.
  3. By default, 100 rows of data are returned. Paging is not supported. For more information about how to return more than 100 rows of data, see LIMIT syntax.

Examples

Count the number of page views (PVs), unique visitors (UVs), and user requests with the top 10 latencies per hour.

*|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