Log Service supports aggregate calculation. This feature combines queries with SQL calculation capacities to calculate the query result. For more information, see Query syntax.
Syntax example:
status>200 |select avg(latency),max(latency) ,count(1) as c GROUP BY method ORDER BY c DESC LIMIT 20Basic syntax:
[search query] | [sql query]A search condition and a calculation condition are separated with a vertical bar (|). This syntax indicates that a search query is used to filter the logs you need,
and an SQL query is used to calculate these logs. The search query syntax is specific
to Log Service. For more information, see Query syntax.
Prerequisites
To use the statistical analysis feature, click Index Attributes. Turn on Enable Analytics for the target field. For more information, see Enable and configure the index feature for a Logstore.
- If you do not enable the analytics feature, only up to 10,000 rows of data records can be calculated and analyzed per shard, and the latency is long.
- After this feature is enabled, data analysis can be completed within several seconds.
- Only data generated after you enable this feature can be analyzed.
- No additional charges are incurred after this feature is enabled.
Supported SQL syntax
Log Service supports the following SQL syntax. For more information, see the following topics:
- Aggregate functions 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 syntax and functions
- GROUP BY syntax
- Window functions
- HAVING syntax
- ORDER BY syntax
- LIMIT syntax
- CASE WHEN and IF syntax
- UNNEST function
- Column aliases
- Nested subquery
Syntax
The SQL syntax is described as follows:
- The FROM and WHERE clauses are not required in SQL statements. The system uses the current Logstore for the FROM clause, and the search condition for the WHERE clause.
- 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.
Built-in fields
Log Service has built-in fields for statistical analysis. These built-in fields are automatically added when you configure a valid column.
| Field name | Type | Description |
|---|---|---|
__time__ |
bigint | The time when the log is generated. |
__source__ |
varchar | The source IP address of the log. This field is source when you query data. The underscores (_) are added before and after source only in SQL statements. |
__topic__ |
varchar | The topic of the log. |
Limits
- The maximum number of concurrent tasks for each project is 15.
- A single varchar column has the maximum length of 2048. The excess length is truncated.
- By default, 100 rows of data are returned. Paging is not supported. For more information about how to return more than 100 rows of data, see LIMIT syntax.
Examples
Count the number of page views (PVs), unique visitors (UVs), and user requests with the top 10 latencies per hour.
*|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