edit-icon download-icon

Syntax description

Last Updated: Dec 15, 2017

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:

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

Basic syntax:

  1. [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.

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.

Field nameTypeMeaning
__time__bigintThe log time.
__source__varcharThe source IP of the log.
Note: This field is source when you query. The underlines (__) is added before and after source only in SQL.
__topic__varcharThe log topic.

Use case

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

  1. *|select date_trunc('hour',from_unixtime(__time__)) as time,
  2. count(1) as pv,
  3. approx_distinct(userid) as uv,
  4. max_by(url,latency) as top_latency_url,
  5. max(latency,10) as top_10_latency
  6. group by 1
  7. order by time
Thank you! We've received your feedback.