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.
status>200 |select avg(latency),max(latency) ,count(1) as c GROUP BY method ORDER BY c DESC LIMIT 20
[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.
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.
- SELECT aggregate computing functions:
- General aggregate functions
- Security detection functions
- Map map function
- Estimating functions
- Mathematical statistics functions
- Mathematical calculation functions
- String functions
- Date and time functions
- URL functions
- Regular expression functions
- JSON functions
- Type conversion functions
- IP functions
- Binary string functions
- Bit operation
- Year-on-year comparison function and period-over-period comparison function
- Comparison functions and operators
- Lambda functions
- Logical functions
- Geospatial functions
- Geo functions
- GROUP BY syntax
- Window functions
- HAVING syntax
- ORDER BY syntax
- LIMIT syntax
- Case when and if branch syntax
- UNNEST function
- Column alias
- Nested subquery
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.
* | select count(1) as c, ip group by ip order by c desc limit 100.
Log Service has built-in fields for statistics. These built-in fields are automatically added when you configure any valid column.
||bigint||The log time.|
||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.|
||varchar||The log topic.|
- The highest concurrency of each project is 15.
- A single column varchar has the maximum length of 2048 and is truncated if the length exceeds 2048.
- 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.
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