SPL Grammar

SPL (SLS Processing Language) — A pipeline-based data processing language for Log Service. Statements are separated by |: Query Statement | Analysis Statement.

1. Basic Syntax

SPL Statement Structure

<data-source> | <spl-expr> | <spl-expr> ;

• * = All LogStore Data
• | = Pipe Operator; Output from the Previous Stage Serves as Input to the Next Stage
• ; = Statement Terminator (Optional at the End)

Syntax Symbols

Symbol	Description
*	LogStore Data Placeholder
.	SPL Keyword Prefix (e.g., .let)
\|	Pipe Symbol
'...'	String Literal
"..."	Field Name / Field Name Pattern
-- / /*...*/	Single-Line / Multi-Line Comments
$	Named Dataset Reference ($name)

Data Types

Type	Description
VARCHAR	Variable-Length Character String (Except for Time Fields, the Initial Type of All SPL Input Fields Is VARCHAR)
BOOLEAN / TINYINT / SMALLINT / INTEGER / BIGINT / HUGEINT	Integer Types
REAL / DOUBLE	Floating-Point Types
TIMESTAMP	Nanosecond-precision UNIX Timestamp
DATE	YYYY-MM-DD
ARRAY	Array, with Indexing Starting at 1, a[1]
MAP	Dictionary, a['foo']
JSON	JSON Type

---

2. SPL Command Quick Reference

Command Expression Syntax: cmd -option=<value> -flag <expression> as <output>, ...

2.1 Control Commands

.let — Define a Named Dataset

.let <name> = <spl-expr>

.let err = * | where status >= 500 | extend msg='ERR';
$err;   -- Output Named Dataset

---

2.2 Field Operations

project — Preserve/Rename Fields

| project -wildcard <pattern>, <new>=<old>, ...

* | project level, err_msg
* | project log_level=level
* | project -wildcard "__tag__:*"

By default, __time__ and __time_ns_part__ are retained and cannot be renamed or overwritten.

project-away — Remove Fields

| project-away -wildcard <pattern>, ...

* | project-away -wildcard "__tag__:*"

project-rename — Rename Field

| project-rename <new>=<old>, ...

* | project-rename log_level=level

2.3 SQL Computation Commands

extend — Generate New Fields Using SQL Expressions (Directly Overwrite Existing Fields with the Same Name)

| extend <output>=<sql-expr>, ...

* | extend Duration = EndTime - StartTime
* | extend a = json_extract(content, '$.body.a')
* | extend status = cast(status as BIGINT)
* | extend k1='v1', k2='v2', k3='v3'          -- Merge Multiple extend Statements

where — Filter Data Based on SQL Expressions

| where <sql-expr>

* | where userId = '123'
* | where cast(status as BIGINT) >= 500
* | where regexp_like(server_protocol, '\d+')
* | where a = 'x' and b = 'y'                  -- Merge Multiple where

---

2.4 Aggregation Commands

stats — Aggregated Statistics

stats <alias>=<aggFunc> by <group>, ...

Aggregate Functions
count, count_if, min, max, sum, avg, skewness, kurtosis, approx_percentile, approx_distinct, bool_and, bool_or, every, arbitrary, array_agg

* | stats pv = count(*) by ip
* | stats pv = count(*)                         -- Ungrouped Global Statistics
* | extend lat = cast(latencyMs as bigint) | stats minLat=min(lat), maxLat=max(lat) by ip

By default, returns the first 100 entries，use with limit to retrieve more。

sort — Sorting

sort <field> [asc|desc], ...

* | sort latencyMs desc

limit — Limit the Number of Lines

limit (<offset>,) <size>

* | sort latencyMs | limit 1
* | limit 5, 10                                 -- Skip 5 Records and Retrieve 10 Records

When Not Used with sort, the Output Order of limit Is Random.

---

3. SQL Analysis Syntax

Query Statements | Analysis Statements

• Query statements can be used independently; analysis statements must be used together with query statements
• The analytical statement does not require FROM/WHERE (the current LogStore is analyzed by default), is case-insensitive, and does not end with a semicolon
• The analytical statement does not support offset

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

---

4. Query Statement Syntax

4.1 Full-Text Query

keywords1 [ and | or | not ] keywords2 ...

• Multiple keywords are joined by and by default
• Wildcards * (0+ characters) and ? (1 character); cannot be used at the beginning of a word

GET
GET or POST
Jo?                    -- Joe, Jon, etc.
cn*                    -- Words Beginning with cn

4.2 Field Query

field [ : | > | >= | < | <= | = | in ] value [ and | or | not ... ]

Operator Quick Reference

Operator	Description	Example
:	Key:Value	status:200
and / or / not	Logical Operators	status:200 and method:GET
( )	Precedence	(GET or POST) and status:200
""	Enclose Special Characters/Keywords	"file info":apsara, "and"
* / ?	Wildcard	host:www*com, host:aliyund?c
> >= < <=	Comparison (long/double)	request_time>100
=	Equal to (long/double)	request_time=100
in [a b] / in (a b)	Closed/Open Interval	status in [200 299]
__source__	Log Source	__source__:192.0.2.*
__tag__	Metadata	__tag__:__receive_time__:1609...
__topic__	Log Topic	__topic__:nginx_access_log

Operator Precedence (High → Low)

:, "", (), and/not, or

Field Types and Available Operators

Type	Available Operators
text	and, or, not, (), :, "", *, ?
long/double	and, or, not, (), >, >=, <, <=, =, in
JSON	Based on the Internal Field Type

Special Queries

Requirement	Query
Field Value Is Not Empty	not remote_user:""

| Field Does Not Exist | not remote_user:* |
| Field Exists | remote_user:* |
| Full-Text Search in Raw Logs | * \| where __line__ like '%error%' |
| Field Name with Spaces | "request method":PUT |
| Reserved Word as a Keyword | "and" |
| Exact Phrase Match | #"redo_index/1" |

---

5. SQL Clauses

Clause	Description
SELECT	Select Columns, Supporting AS Aliases
FROM log	Specify the LogStore (Must Be Explicitly Specified in Nested Subqueries)
WHERE	Filter Conditions
GROUP BY	Group and Aggregate Data, Supporting ROLLUP, CUBE, and GROUPING SETS
HAVING	Filter Grouped/Aggregated Results
ORDER BY	Sort by ASC/DESC
LIMIT	Limit the Number of Rows (Default: 100)
JOIN	Join Multiple Tables (Across Logstore / MySQL / OSS)
UNION	Combine Multiple SELECT Results
INTERSECT	Return the Intersection
EXCEPT	Return the set difference
EXISTS	Check whether the subquery returns any results
UNNEST	Expand array/map fields
WITH	Save the subquery as a temporary table
VALUES	Construct temporary data
INSERT INTO	Write computed results to other Logstores

---

6. SQL Reserved Keywords

AND, AS, BETWEEN, BY, CASE, CAST, CROSS, CUBE,
CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP,
DISTINCT, ELSE, END, ESCAPE, EXCEPT, EXISTS,
FROM, GROUP, GROUPING, HAVING, IN, INNER,
INSERT, INTERSECT, INTO, IS, JOIN, LEFT, LIKE,
LIMIT, LOCALTIME, LOCALTIMESTAMP, NATURAL, NOT,
NULL, ON, OR, ORDER, OUTER, RIGHT, ROLLUP,
SELECT, THEN, TRUE, UNION, UNNEST, VALUES,
WHEN, WHERE, WITH

---

7. Nested Subqueries

Subqueries are enclosed in () and must contain FROM log.

-- Baseline
* | SELECT key FROM (sub_query)


-- Find Minimum PV
* | SELECT min(PV) FROM (
    SELECT count(1) as PV FROM log GROUP BY request_method
)


-- Year-over-Year Comparison
* | SELECT diff[1] AS today, diff[2] AS yesterday, diff[3] AS ratio
FROM (
    SELECT compare(PV, 86400) AS diff FROM (
        SELECT count(*) AS PV FROM log
    )
)


-- Percentage (Window Function)
* | SELECT uri, c, round(c * 100.0 / sum(c) over(), 2) AS "Percentage%"
FROM (SELECT request_uri AS uri, count(*) AS c FROM log GROUP BY uri)

---

8. Function Classification Quick Reference

Category	Common Functions
Aggregation	count, count_if, min, max, sum, avg, array_agg
String	concat, length, position, replace, split, split_part, chr
Date and Time	current_date, date_parse, date_format, date_trunc, date_add, to_unixtime, day
JSON	json_extract, json_extract_scalar, json_array_get, json_array_length, json_parse
Regular Expressions	regexp_extract, regexp_extract_all, regexp_like, regexp_replace, regexp_split
Type Conversion	cast, try_cast, typeof
Conditional Expressions	CASE WHEN ... THEN ... ELSE ... END, IF, COALESCE, NULLIF, TRY
Array Functions	array_distinct, array_join, reverse, filter, reduce, transform, []
Map	cardinality, element_at, histogram, map
Mathematical Functions	abs, ceil, round, log, mod, random
Statistical Functions	corr, covar_pop, regr_intercept, beta_cdf
Window Functions	dense_rank, lag + Aggregate Functions
Year-over-Year and Month-over-Month Comparisons	compare, ts_compare
Forecasting	ts_predicate_simple
Comparison	>, >=, <, <=, =, !=, BETWEEN, IS NULL, IS NOT NULL, LIKE, IN, ALL, ANY
Logic	AND, OR, NOT
IP	ip_to_province, ip_to_city, ip_prefix, ipv6_to_city
URL	url_encode, url_decode, url_extract_path, url_extract_query
Estimation	approx_distinct, approx_percentile, numeric_histogram
Binary	from_base64, from_hex, to_hex, sha256
Bitwise Operations	bitwise_and, bitwise_or, bitwise_not, bit_count
Geography	geohash, ST_AsText, ST_Contains, ST_Buffer
HyperLogLog	approx_set, cardinality, merge
Window Funnel	window_funnel
Unit Conversion	convert_data_size, format_duration

---

9. Common Scenario Quick Reference

9.1 SPL vs SQL

Scenario	SQL	SPL
Filtering	WHERE Type='write'	\| where Type='write'
Select and Rename Fields	SELECT a AS x, b	\| project x=a, b
Exclude Fields	—	\| project-away -wildcard "tag:*"
Calculate New Fields	SELECT cast(s AS BIGINT)	\| extend s = cast(s as BIGINT)
Aggregation	SELECT count(*) PV GROUP BY ip	\| stats pv=count(*) by ip

9.2 Key Considerations

• Type Conversion: Except for time fields, SPL input fields are initially VARCHAR; cast is required before comparison:

  * | where cast(status as BIGINT) >= 500
  

• Type Preservation After extend: Subsequent pipelines use the new type after conversion
• Pipeline Merging: Multiple where/extend statements should be merged into one to reduce the number of pipelines
• Time Fields: __time__ and __time_ns_part__ are retained by default and can only be modified through extend
• NULL: The value is null when the field does not exist or a calculation returns an abnormal result; use COALESCE as a fallback
• Case Sensitivity: Field names are case-insensitive in query scenarios and case-sensitive in other scenarios
• Character Escaping: Escape single quotation marks in strings by using ''; \\ is not an escape character; concatenate special characters using chr()