This section describes the SQL statement syntax of Cloud Log Service. Structured

Query Language (SQL) is a programming language used to control database access and manage data in databases. Cloud Log Service SQL provides statements for querying structured data in log units.

In the statement or interaction mode, you can write SQL statements for statistical analysis, and Cloud Log Service will return statistical analysis results based on the SQL statements.

For specific operations, see Log Analysis.

Syntax

SELECT[ALL|DISTINCT]{*|exprs} FROM{<subquery>} [WHEREwhere_condition] [GROUPBY[col_list_name] [HAVINGexpr] [ORDERBYexpr[ASC|DESC],expr[ASC|DESC],...] [LIMITlimit]

Statement Description

Statement	Description	Example
SELECT	Select data from the table and obtain the data content that meets the retrieval conditions from the current log unit by default. The subsequent FROM log can be omitted.	SELECT visitCount FROM log
DISTINCT	Only the deduplicated results are returned.	SELECT DISTINCT visitCount
AS	Specify aliases for column names.	SELECT visitCount AS pv
FROM	Indicates the source data set of the queried data. It can be the structured data of the current log unit, where FROM can only be followed by "log", or a subset of this data. If FROM is not specified, the structured data of the current log unit is queried by default. If the data source to be queried is a subset, you need to write a subquery statement.	SELECT visitCountSELECT visitCountFROM log
WHERE	Specifies the filter condition. Arithmetic operators, relational operators, and logical operators are supported. You can enter the filtering condition in where_condition.	SELECT visitCountWHERE visitCount > 0
GROUP BY	Specifies the grouping field. Single-field grouping and multi-field grouping are supported. You can enter the structured field list in col_list_name.	SELECT host, count(*) AS pvWHERE visitCount > 0GROUP BY host
HAVING	Used only with GROUP BY. This statement specifies the structured field used to filter the GROUP BY results.	SELECT host, count(*) AS pvGROUP BY hostHAVING pv > 10
ORDER BY	Fields that follow must be used for GROUP BY. The query results of GROUP BY can be sorted by any structured field.	SELECT host, count(*) AS pvGROUP BY hostORDER BY pv
ASC/DESC	ASC (default) sorts from the lowest value to the highest value. DESC sorts from the highest value to the lowest value.	SELECT host, count(*) AS pvGROUP BY hostORDER BY pv DESC
LIMIT	Limits the number of structured logs returned in the query result. A maximum of 20000 structured logs can be returned for each query. Note: If the LIMIT statement is not used, the latest 100 records in the query result are returned by default.	SELECT hostLIMIT 100

Note:

SQL statements are not case-sensitive. For example, SELECT which is equivalent to "select".
When SELECT is used to obtain data from a log unit, a maximum of 100 rows of data can be obtained by default. If you need to obtain more data, use the LIMIT syntax to specify the number of rows to be obtained. A maximum of 20000 rows can be obtained.
Strings must be enclosed with single quotes (''). Unenclosed characters, or characters enclosed with double quotes ("") represent fields or column names. For example, 'status' represents the status string, but status or "status" represents the log field "status". When the string itself contains a single quote ('), you need to use '' (two single quotes) to represent the single quotes.
Field names in SELECT must comply with the column name specification. If it does not, it must be enclosed with double quotes ("").
Only one SQL statement can be executed at a time.

Examples of Common SQL Statements

Query Requirement	Query Statement
Standard query	SELECT "field" WHERE "field" = 'value'
Number of rows	SELECT count(*)
Column alias	SELECT count(*) AS "pv"
Deduplication	SELECT DISTINCT("field")select distinct field
Pagination Query	SELECT "field" LIMIT 100
Sorting	SELECT"ts" order by "ts"
Grouping	SELECT "field" GROUP BY "field"
Group Statistics	SELECT "field",count(*) GROUP BY "field"
Fuzzy query	SELECT * where "field" like 'value%'
Sum	SELECT sum("field")
Maximum value	SELECT max("field")
Minimum value	SELECT min("field")
Average value	SELECT avg("field")
SQL nested subquery	SELECT sum(pv) FROM (SELECT "field",count(*) AS "pv" GROUP BY "field")
HAVING clause filtering	SELECT "field",count(*) AS "pv" GROUP BY "field" HAVING "pv" > 10
Query containing GET and POST requests	SELECT * WHERE "request_method" IN ('GET', 'POST')
Query without GET and POST requests	SELECT * WHERE "request_method" NOT IN ('GET', 'POST')
Logs of non-GET requests	SELECT * WHERE "request_method" != 'GET'
Logs of successful GET requests with the 200 status code and a request time of less than 60 seconds	SELECT * WHERE "request_method" = 'GET' AND "request_time" < 60
Logs of requests whose time is greater than or equal to 60 seconds and less than 200 seconds	SELECT * WHERE "request_ time" >60 and "request_time" < 200
Logs of GET or POST requests	SELECT * WHERE "request_method" = 'GET' OR "request_method" = 'POST'