Syntax description

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.

To use the analysis function, turn on the Enable Analytics switch for the corresponding SQL fields in Search & Analysis settings. For details, see Create an index. If the Enable Analytics switch is turned off, Log Service only provides the computing function for part of the data by default, and the latency is high. With the Enable Analytics switch turned on, Log Service provides the quick analysis in seconds.

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 to filter the logs you need from the logs by using the search query and perform SQL query calculation for these logs. The search query syntax is specific to Log Service. For details, see Query syntax.

Limits

1. The highest concurrency of each project is 5.
2. A single column varchar has the maximum length of 512 and will be truncated if the length exceeds 512.

Supported SQL syntax

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

• SELECT aggregate computing functions:
  • General aggregate functions
  • Mapping functions
  • Estimating functions
  • Mathematical statistics functions
  • Mathematical calculation functions
  • String functions
  • Date and time functions
  • URL conversion functions
  • Regular expression functions
  • JSON functions
  • Type and type conversion
  • IP recognition functions
• GROUP BY syntax
• HAVING syntax
• ORDER BY syntax
• LIMIT syntax
• CASE WHEN syntax
• Column alias
• Nested subquery

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.
• 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 some built-in fields for statistics. These built-in fields are automatically added when you configure any valid column.

Use case

Make statistics on the hourly PV and UV, the user request of the highest latency, and the top 10 highest latencies:

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