This topic describes the real-time analysis feature of Log Service and the limits and SQL syntax of this feature.
After you turn on the Enable Analytics switch, analysis within seconds is supported and incurs no additional charges.
The real-time analysis feature of Log Service allows you to search for log data and then analyze the log data based on SQL syntax. In addition, the feature returns the results within seconds.
- Format of a query statement
Search statement|Analytic statement
status>200 |select avg(latency),max(latency),count(1) as c GROUP BY method ORDER BY c DESC LIMIT 20
This section lists the SQL syntax that Log Service supports.
- Aggregate functions that can be used in SELECT statements
- General aggregate functions
- Security check functions
- Map functions
- Approximate functions
- Mathematical statistic functions
- Mathematical calculation functions
- String functions
- Date and time functions
- URL functions
- Regular expression functions
- JSON functions
- Type conversion functions
- IP functions
- Array functions
- Binary string functions
- Bitwise functions
- Interval-valued comparison and periodicity-valued comparison function
- Comparison functions and operators
- Lambda functions
- Logical functions
- Geospatial functions
- Geo functions
- Machine learning functions
- GROUP BY syntax
- Window functions
- HAVING syntax
- ORDER BY syntax
- LIMIT syntax
- CASE WHEN and IF syntax
- UNNEST function
- Column aliases
- Nested subquery
- INSERT syntax
Additional considerations about SQL syntax
- Do not specify the FROM or WHERE clause in an analytic statement. This is because logs are queried from the current Logstore and the WHERE clause is replaced by the search statement in Log Service.
- An analytic statement can include the following clauses: SELECT, GROUP BY, ORDER BY [ASC,DESC], LIMIT, and HAVING.
- You can perform a maximum of 15 concurrent queries in each project.
- By default, you can analyze only the log data that is collected after the Enable Analytics switch is turned on.
- The maximum size of a field value is 2 KB. If the size of a field value exceeds the maximum, the value is truncated.
- By default, a maximum of 100 rows of data is returned for each query. For information about how to retrieve more rows for a query, see LIMIT syntax.
Log service provides some system fields to facilitate log analysis. For more information, see Reserved fields.
|System field||Data type||Description|
|__time__||bigint||The timestamp of a log entry.|
|__source__||varchar||The source of a log entry.
Note To reference this field, use source in a search statement and __source__ in an analytic statement.
|__topic__||varchar||The topic of a log entry.|
To count the number of page views (PVs) and unique visitors (UVs) per hour and query the user requests with the 10 longest latency periods in each hour, you can use the following statement:
*|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