SQL syntax and function overview - Simple Log Service - Alibaba Cloud Documentation Center

Simple Log Service projects support the use of SQL statements for query result analysis. This topic provides an overview of the basic SQL syntax for analytic statements.

Basic syntax

Query statements and analytic statements are delineated by |. The structure is as follows:

query statement|analytic statement

Query statements can function independently, whereas analytic statements must accompany query statements. The log analysis feature enables data analysis within search results or across all data in a logstore.

Important

We recommend that you limit a query statement to 30 conditions or fewer.
In the absence of a FROM or WHERE clause in an analytic statement, the current logstore's data is analyzed by default. Analytic statements are case-insensitive, do not support the use of offset, and do not require a semicolon to conclude.

Statement type

Description

Query statement

A query statement defines one or more search conditions. It can consist of keywords, numeric values, ranges, a space, or an asterisk (*).

Specifying a space or asterisk (*) implies no conditions.

Analytic statement

Analytic statements are used to aggregate or analyze data from search results or all logs. For detailed information on analysis functions and syntax supported by Simple Log Service, see the following topics:

Examples of SQL analytic statements:

* | SELECT status, count(*) AS PV GROUP BY status

SQL functions

SQL functions are typically embedded within SQL clauses to perform calculations or transformations on data during the query process, such as using aggregate or string functions in the SELECT clause.

SQL functions are commonly used for various operations, including summing, averaging, string manipulation, and date processing.

Examples:

Query logs from the previous day:

* |
SELECT
  *
FROM  log
WHERE
  __time__ < to_unixtime(current_date)
  AND __time__ > to_unixtime(date_add('day', -1, current_date))

Calculate the number of JSON elements in the Results field value:
- Sample fields
```
Results:[{"EndTime":1626314920},{"FireResult":2}]
```
- Query and analysis statements
```
* | SELECT json_array_length(Results)
```

SQL clauses

SQL syntax provide a structured framework for SQL statements, while SQL functions carry out specific operations on the data within those clauses.

SQL clauses are essential for constructing complete SQL queries or data manipulation statements, determining characteristics such as data sources, conditions, grouping, and sorting.

Examples:

Assess whether the latency for reading and writing data in a logstore exceeds 1000. If such latency is present, return the consumer group details.

* |
SELECT
  consumer_group
FROM  "internal-diagnostic_log"
WHERE
  EXISTS (
    SELECT
     Latency
    FROM     internal-operation_log
    WHERE
      "internal-diagnostic_log".LogStore = "internal-operation_log".logstore and latency >1000
  )

Retrieve the first 200 rows from the query and analysis results.
```
* | SELECT request_time LIMIT 200
```