SPL instructions and functions - Simple Log Service - Alibaba Cloud Documentation Center

This topic describes SPL instructions.

Parameter types

The following table describes the parameter types used in SPL instructions.

Parameter type	Description
Bool	A Boolean parameter. When you use SPL, this parameter works as a switch.
Char	An ASCII character parameter. It must be enclosed in single quotation marks (''). For example, `'a'` represents the character a, `'\t'` represents the tab character, `'\11'` represents the ASCII character corresponding to the octal number 11, and `'\x09'` represents the ASCII character corresponding to the hexadecimal number 09.
Integer	An integer parameter.
String	A string parameter. It must be enclosed in single quotation marks (''). For example, `'this is a string'`.
RegExp	An RE2 regular expression parameter. It must be enclosed in single quotation marks (''). For example, `'([\d.]+)'`. For more information about the syntax, see Syntax.
JSONPath	A JSON path parameter. It must be enclosed in single quotation marks (''). For example, `'$.body.values[0]'`. For more information about the syntax, see JsonPath.
Field	A field name parameter. For example, `\| project level, content`. If a field name contains special characters other than letters, digits, and underscores, enclose the field name in double quotation marks (""). For example, `\| project "a:b:c"`. Note For more information about the case sensitivity of field names, see General reference.
FieldPattern	A combination of a field name and a wildcard character, or a field name parameter. The wildcard character () is supported, which matches zero or more characters. It must be enclosed in double quotation marks (""). For example, `\| project "__tag__:"`. Note For more information about the case sensitivity of field names, see General reference.
SPLExp	An SPL expression parameter.
SQLExp	An SQL expression parameter.

SPL instruction list

Instruction category	Instruction name	Description	Data transformation	Write processor	Consume processor	Scan mode (SCAN)	Logtail collection
Control instructions	.let	Defines a named dataset. For more information about SPL datasets, see General reference.	√	×	×	√	×
Field operation instructions	project	Retains fields that match a specified pattern and renames specified fields. During execution, all field retention expressions are processed before renaming expressions.	√	√	√	√	√
	project-away	Removes fields that match a specified pattern and retains all other fields.	√	√	√	√	√
	project-rename	Renames specified fields and retains all other fields.	√	√	√	√	√
	expand-values	Expands the first-layer JSON object of a specified field to generate multiple results.	√	×	√	×	×
SQL calculation instructions for structured data	extend	Creates new fields by calculating the results of SQL expressions. For a list of supported SQL functions, see List of SQL functions supported by SPL.	√	√	√	√	√
SQL calculation instructions for structured data	where	Filters data based on an SQL expression and retains data entries that meet the expression. For a list of SQL functions supported by the where instruction, see List of SQL functions supported by SPL.	√	√	√	√	√
Semi-structured data extraction instructions	parse-regexp	Extracts information that matches regular expression groups from a specified field.	√	√	√	√	√
	parse-csv	Extracts information in CSV format from a specified field.	√	√	√	√	√
	parse-json	Extracts first-layer JSON information from a specified field.	√	√	√	√	√
	parse-kv	Extracts key-value pair information from a specified field.	√	√	√	√	√
New data transformation instructions	pack-fields	Packs log fields and outputs them to a new field through JSON serialization. This instruction is suitable for scenarios that require structured transmission, such as building an API request body.	√	×	√	√	×
	log-to-metric	Converts the log format to the metric storage format.	√	×	×	×	×
	metric-to-metric	Further processes existing metric data, such as adding, modifying, or removing tags.	√	×	×	×	×
Aggregation instructions	stats	An instruction for statistical analysis of logs, similar to SQL aggregate functions such as `COUNT`, `SUM`, and `AVG`. It performs statistics, grouping, and aggregation operations on specific fields in log data.	×	×	×	√	×
	sort	Sorts query results. It supports sorting field values or statistical results in ascending (`asc`) or descending (`desc`) order. It is an important tool for quickly locating key data and generating ordered reports in log analysis.	×	×	×	√	×
	limit	Limits the number of log rows returned in query results. This is a core instruction for controlling data volume. Using the `limit` instruction prevents performance issues or resource waste from large query results. It is suitable for scenarios such as log analysis and real-time monitoring.	×	×	×	√	×

Function overview

SPL instructions support most SQL functions. However, the availability and applicable scenarios for specific functions are described in the corresponding function documentation.

Classification	Description	Common functions
Aggregate functions	Perform summary calculations on the target dataset to generate a single statistical result.	avg function count function max function min function sum function
String functions	Process text data, including search, replace, substring, concatenate, and format.	concat function length function position function replace function split function
Date and time functions	Perform format conversion, grouping, and aggregation on dates and times in logs.	current_date function date function day function date_trunc function time_series function
JSON functions	Process JSON objects, including extraction, transformation, and statistics.	json_array_contains function json_array_get function json_array_length function json_extract function json_extract_scalar function
Regular expression functions	Pattern matching and text processing.	regexp_extract_all function regexp_extract function regexp_extract_bool function regexp_replace function regexp_split function
Year-on-year and month-on-month functions	Calculate relative changes in time series data.	compare function ts_compare function
Array functions and operators	Perform addition, deletion, modification, query, traversal, and transformation on arrays.	Subscript operator array_distinct function array_intersect function array_join function reverse function
Map mapping functions and operators	Operate on key-value pairs.	cardinality function element_at function histogram function histogram_u function map function
Mathematical calculation functions	Numerical calculations, rounding, random numbers, trigonometric functions, and more.	abs function ceil function log function mod function random function
Mathematical statistics functions	Data distribution analysis and numerical calculations.	corr function covar_pop function regr_intercept function beta_cdf function inverse_chi_squared_cdf function
Type conversion functions	Handle conversions between data types.	cast function try_cast function typeof function
Window functions	Aggregation or sorting based on data windows.	Aggregate functions dense_rank function lag function
IP functions	Parse and calculate IP addresses.	ip_to_city function ip_prefix function ipv6_to_city function
URL functions	Parse URL structures.	url_encode function url_decode function url_extract_fragment function url_extract_path function url_extract_query function
Estimation functions	Predict data or fill in missing values.	approx_distinct function approx_percentile function numeric_histogram function numeric_histogram_u function
Binary functions	Process binary data types.	from_base64 function from_big_endian_64 function from_hex function to_hex function sha256 function
Bitwise operation functions	Directly operate on binary bits.	bit_count function bitwise_and function bitwise_not function bitwise_or function bitwise_xor function
Spatial geometry functions	Process spatial geometries.	ST_AsText function ST_GeometryFromText function ST_Boundary function ST_Buffer function ST_Contains function
Geographic functions	Geographic location analysis and map calculations.	geohash function
Color functions	Color representation and conversion.	bar function color function render function rgb function
HyperLogLog functions	Perform statistical processing on large datasets, sacrificing accuracy to save memory.	approx_set function cardinality function empty_approx_set function merge function
Comparison operators	Determine the size relationship of parameters, applicable to any comparable data type (double, bigint, varchar, timestamp, and date).	Basic operators ALL operator ANY operator BETWEEN operator DISTINCT operator
Logical operators	Combine multiple Boolean conditions to control logical flow.	AND operator OR operator NOT operator
Unit conversion functions	Convert units of data size or time intervals.	convert_data_size function format_duration function
Window funnel functions	Analyze user behavior, app traffic, product goal conversion, and other data.	window_funnel function
Lambda expressions	Define lambda expressions in SQL analytic statements and SPL statements, and pass them to specified functions to enrich the expression of functions.	filter function reduce function transform function
Conditional expressions	Return different values based on conditional branches.	CASE WHEN expression IF expression COALESCE expression NULLIF expression TRY expression