Log Service provides a function similar to the SQL aggregate computing. This function integrates with the query function and the SQL computing function to compute the query results.

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]

The SEARCH condition and computing condition are separated by a vertical bar ( | ). This syntax Indicates that the required logs are filtered from the log by the search query, and SQL queries are computed for these logs. The search query syntax is specific to Log Service. For details, see Query syntax.

Prerequisites

To use the analysis function, you must click Enable of the SQL related fields in Search and Analysis config. For more information, see Overview.

  • If you do not enable analysis function, computing function of up to 10 thousand lines of data per shard is provided, and the delay is relatively high.
  • With the Enable Analytics turned on, Log Service provides the quick analysis in seconds.
  • Only works for new data when function is enabled.
  • No additional charges are incurred after the Enable Analytics is turned on.

Supported SQL syntax

Log Service supports the following SQL syntaxes. For details, click the specific links.

Syntax structure

The SQL syntax structure is as follows:

  • The FROM clause and WHERE clause are not required in the SQL statement. By default, FROM indicates to query the data of the current Logstore, and the WHERE condition is search query.
  • The supported clauses include SELECT, GROUP BY, ORDER BY [ASC,DESC], LIMIT, and HAVING.
Note By default, only the first 10 results are returned. To return more results, add limit n. For 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 statistics. These built-in fields are automatically added when you configure any valid column.

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

Limits

  1. The highest concurrency of each project is 15.
  2. A single column varchar has the maximum length of 2048 and is truncated if the length exceeds 2048.
  3. By default, 100 lines of data are returned, and page turning is not supported. If you want more data to be returned, use LIMIT syntax.

Examples

Count the hourly PV, UV, and maximum delay corresponding to a user request, with the highest delay of 10: 

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