Instructions for data transformation (new version) - Simple Log Service

pack-fields

Packages multiple fields into a new field as a JSON serialized string, suitable for structured transmission scenarios such as building API request bodies.

Important

By default, fields that are not of the Varchar type are not processed. This includes the __time__ and __time_ns_part__ fields.
By default, the source data is not retained.

Syntax

| pack-fields -keep -ltrim -include=<include> -exclude=<exclude> as <output>

Parameters

Parameter	Type	Required	Description
output	String	Yes	The name of the output field after packaging. The field value is in JSON format.
include	RegExp	No	The whitelist configuration. Fields that match the regular expression are packaged. The default value is ".*", which matches all fields. For more information, see Regular expressions.
exclude	RegExp	No	The blacklist configuration, which takes precedence over the whitelist. Fields that match the regular expression are not packaged. The default value is empty, which means no matching is performed. For more information, see Regular expressions.
ltrim	String	No	Removes the prefix from the output field name.
keep	Bool	No	Specifies whether to retain the source data after packaging. True: Retains the packaged source data in the output. False (default): Does not retain the packaged source data in the output.

Examples

Example 1: Package all log fields into the test field. By default, the original packaged fields are deleted.
- SPL statement
```
* | pack-fields -include='\w+' as test
```
- Input data
```
test1:123
test2:456
test3:789
```
- Output
```
test:{"test1": "123", "test2": "456", "test3": "789"}
```

Example 2: Package all log fields into the test field without deleting the original packaged fields.

SPL statement

* | pack-fields -keep -include='\w+' as test

Input data
```
test1:123
test2:456
test3:789
```

Output

test:{"test1": "123", "test2": "456", "test3": "789"}
test1:123
test2:456
test3:789

Example 3: Pack the test and abcd fields into the content field and retain the original fields.

SPL statement

* | pack-fields -keep -include='\w+' as content

Input data
```
abcd@#%:123
test:456
abcd:789
```

Output

abcd:789
abcd@#%:123
content:{"test": "456", "abcd": "789"}
test:456

Example 4: Package all fields except for the test and abcd fields into the content field and delete the original packaged fields.
- SPL statement
```
* | pack-fields -exclude='\w+' as content
```
- Input data
```
abcd@#%:123
test:456
abcd:789
```
- Output
```
abcd:789
content:{"abcd@#%": "123"}
test:456
```

Example 5: Extract all key-value pairs that match the specified regular expression from a field value, and then package them into the name field.

SPL statement

* | parse-kv -prefix='k_' -regexp dict, '(\w+):(\w+)' | pack-fields -include='k_.*' -ltrim = 'k_' as name

Input data
```
dict: x:123, y:456, z:789
```

Output

dict:x:123, y:456, z:789
name:{"x": "123", "y": "456", "z": "789"}

log-to-metric

Transforms logs into the format required for storing time series data.

Important

By default, log data that does not meet the requirements for Metric is ignored.
The time unit of the timestamp field in the raw log data is automatically detected. Supported units include seconds, milliseconds, microseconds, and nanoseconds.
Hash-based writing is used by default.

Syntax

| log-to-metric -wildcard -format -names=<names> -labels=<labels> -time_field=<time_field> -buckets=<buckets>

Parameters

Parameter	Type	Required	Description
wildcard	Bool	No	Specifies whether to enable wildcard matching for the field names specified by `names` and `labels`. By default, exact field matching is used. To enable wildcard matching, add this parameter.
format	Bool	No	Specifies whether to enable automatic formatting. By default, this is disabled, and invalid label data is skipped during transformation. If enabled, invalid label data is formatted. Label values cannot contain characters such as `"\|"`, `"#"`, or `"$"`. If they do, these characters are replaced with underscores (_).
names	FieldList	Yes	A list of log fields used to generate corresponding metric data points. If a field in the input data matches at least one of the specified field names or wildcard patterns, a metric data point is generated for that field. The metric name is the field name, and the metric value is the field value. For example, `["mem","mem:pct"]` generates two data points named `mem` and `mem:pct`. Important The input field name must match the regular expression `[a-zA-Z_:][a-zA-Z0-9_:]*`. Otherwise, no data point is generated for the field. The input field value must meet one of the following requirements. Otherwise, no data point is generated for the field: The field is a numeric type: TINYINT, SMALLINT, INTEGER, BIGINT, HUGEINT, REAL, or DOUBLE. The field is a VARCHAR text type, and its value can be converted to a valid DOUBLE type. For the time series data format, see Time series data (metric).
labels	FieldList	No	A list of log fields used to construct the corresponding time series label information. If a field in the input data matches at least one of the specified field names or wildcard patterns, the field is added to the data point's labels. The label name is the field name, and the label value is the field value. For example, `["host","ip"]` adds two time series labels, `host` and `ip`, with their original field values. Important The input field name must match the regular expression `[a-zA-Z_][a-zA-Z0-9_]*`. Otherwise, the generated data point label will not include this field. The input field value cannot contain a VERTICAL LINE (`\|`). Otherwise, the generated data point label will not include this field. The input field must be of the VARCHAR text type. Otherwise, the corresponding label is not generated. For the time series data format, see Time series data (metric).
time_field	String	No	The time field for the time series data. By default, the `__time__` field from the log is used as the time field for the time series data. Important The input field must be a timestamp. The corresponding data type must be BIGINT or VARCHAR. If the field is a VARCHAR text type, its value must be convertible to a valid BIGINT type.
buckets	Integer	No	The default value is 32. Set this parameter to the number of target shards. It adjusts the hash range to optimize the write performance of the log-to-metric operator. This parameter controls the number of hash buckets, which affects data distribution and query performance in the metricstore.

Examples

Example 1: Transform a log that contains the rt field into the time series data format.

SPL statement
```
* | log-to-metric -names='["rt"]'
```
Input data
```
__time__: 1614739608
rt: 123
```

Output

__labels__:
__name__:rt
__time_nano__:1614739608
__value__:123

Example 2: Transform a log that contains the rt field into the time series data format, and use the host field as a new label.

SPL statement

* | log-to-metric -names='["rt"]' -labels='["host"]'

Input data

__time__: 1614739608
rt: 123
host: myhost

Output

__labels__:host#$#myhost
__name__:rt
__time_nano__:1614739608
__value__:123

Example 3: Transform a log with the rt and qps fields into the time series data format and use the host field as a label.

SPL statement

* | log-to-metric -names='["rt", "qps"]' -labels='["host"]'

Input data

__time__: 1614739608
rt: 123
qps: 10
host: myhost

Output

__labels__:host#$#myhost
__name__:rt
__time_nano__:1614739608
__value__:123

__labels__:host#$#myhost
__name__:qps
__time_nano__:1614739608
__value__:10

Example 4: Use wildcard matching to transform a log that contains the rt1 and rt2 fields into the time series data format, and use the host field as a new label.

SPL statement

* | log-to-metric -wildcard -names='["rt*"]' -labels='["host"]'

Input data

__time__: 1614739608
rt1: 123
rt2: 10
host: myhost

Output

__labels__:host#$#myhost
__name__:rt1
__time_nano__:1614739608
__value__:123

__labels__:host#$#myhost
__name__:rt2
__time_nano__:1614739608
__value__:10

Example 5: Transform a log that contains the rt and qps fields into the time series data format, use the host field as a new label, and automatically format the new label value.

SPL statement

* | log-to-metric -format -names='["rt", "qps"]' -labels='["host"]'

Input data

__time__: 1614739608
rt: 123
qps: 10
host: myhost1|myhost2

Output

__labels__:host#$#myhost1_myhost2
__name__:rt
__time_nano__:1614739608
__value__:123

__labels__:host#$#myhost1_myhost2
__name__:qps
__time_nano__:1614739608
__value__:10

Example 6: Transform a log that contains the rt and qps fields into the time series data format. Replace the field names with max_rt and total_qps, and use the host field as a new label.

SPL statement

* | project-rename max_rt = rt, total_qps = qps| log-to-metric -names='["max_rt", "total_qps"]' -labels='["host"]'

Input data

__time__: 1614739608
rt: 123
qps: 10
host: myhost

Output

__labels__:host#$#myhost
__name__:max_rt
__time_nano__:1614739608
__value__:123

__labels__:host#$#myhost
__name__:total_qps
__time_nano__:1614739608
__value__:10

Example 7: Transform a log containing the rt and qps fields into the time series data format. The fields are renamed to max_rt and total_qps, and the host field is renamed to hostname to serve as a new label.

SPL statement

* | project-rename max_rt = rt, total_qps = qps, hostname=host| log-to-metric -names='["max_rt", "total_qps"]' -labels='["hostname"]'

Input data

__time__: 1614739608
rt: 123
qps: 10
host: myhost

Output

__labels__:hostname#$#myhost
__name__:max_rt
__time_nano__:1614739608
__value__:123

__labels__:hostname#$#myhost
__name__:total_qps
__time_nano__:1614739608
__value__:10

Example 8: Transform a log that contains the remote_user field into the time series data format. Use the status field as a new label and the time field as the timestamp for the time series data.

SPL statement

* | log-to-metric -names='["remote_user"]' -labels='["status"]' -time_field='time'

Input data

time:1652943594
remote_user:89
request_length:4264
request_method:GET
status:200

Output

__labels__:status#$#200
__name__:remote_user
__time_nano__:1652943594
__value__:89

metric-to-metric

Further transforms existing time series data by adding, modifying, or removing labels.

Important

The input field name must match the regular expression [a-zA-Z_][a-zA-Z0-9_]*. Otherwise, the generated time series point label will not include this field.
If the three option parameters contain the same field, the priority is as follows: add_labels > del_labels > rename_labels.

For more information about the output time series data format, see Time series data (metric).

Syntax

| metric-to-metric -format -add_labels=<add_labels> -del_labels=<del_labels> -rename_labels=<rename_labels> -buckets=<buckets>

Parameters

Parameter	Type	Required	Description
add_labels	Array	No	A list of label fields to add. This is used to construct new time series label information. Adds the raw data to the labels of the time series point. Only the VARCHAR type is supported. For example, if the raw data is `{"host":"http://www.xxx.com", "ip":"127.0.0.1"}` and you enter `["host", "ip"]`, `\|host#$#http://www.xxx.com\|ip#$#127.0.0.1` is added to the original labels. If the original labels already contain a host field, its value is overwritten.
del_labels	Array	No	A list of label fields to remove. This is used to construct new time series label information. If a field name in the input matches a field name in the original labels, the corresponding field is deleted from the original labels. For example, if the original label value is `host#$#http://www.xxx.com\|ip#$#127.0.0.1`, inputting `["ip"]` specifies the deletion of a time series label and updates the original label to `host#$#http://www.xxx.com`. If the original label contains the `ip` field, no action is taken.
rename_labels	Map	No	A list of label fields to rename. This is used to construct new time series label information. Updates the labels of the original time series point based on the map information. The key is the original field name, and the value is the new field name. For example, `{"host":"host_new", "ip":"ip_new"}` renames `"host"` to `"host_new"` and `"ip"` to `"ip_new"`. If the original labels do not contain the corresponding field, no operation is performed.
format	Bool	No	Specifies whether to enable automatic formatting. By default, this is disabled, and invalid data is skipped during transformation. If enabled: The __labels__ are sorted. The LabelKey and LabelValue are formatted. LabelKey: Must match the regular expression "[a-zA-Z_][a-zA-Z0-9_]". Invalid characters are replaced with a space (" "). LabelValue: Cannot contain characters such as "\|", "#", or "$". If they do, these characters are replaced with an underscore (_). Labels in __labels__ with an empty string as the LabelValue are dropped. However, the data entry is retained. Duplicate labels in the __labels__ field are removed. The label with the lexicographically first LabelValue is retained.
buckets	Integer	No	The default value is 32. Set this parameter to the number of target shards. It adjusts the hash range to optimize the write performance of the metric-to-metric operator. This parameter controls the number of hash buckets, which affects data distribution and query performance in the metricstore.

Examples

Example 1: Add a label.

SPL statement

* | extend qps = '10'|metric-to-metric -add_labels='["qps"]'

Input data

__labels__:host#$#myhost
__name__:rt
__time_nano__:1614739608
__value__:123

Output

__labels__:host#$#myhost|qps#$#10
__name__:rt
__time_nano__:1614739608
__value__:123

Example 2: Remove a label.

SPL statement

* | metric-to-metric -del_labels='["qps"]'

Input data

__labels__:host#$#myhost|qps#$#10
__name__:rt
__time_nano__:1614739608
__value__:123

Output

__labels__:host#$#myhost
__name__:rt
__time_nano__:1614739608
__value__:123

Example 3: Rename a label.

SPL statement

* | metric-to-metric -rename_labels='{"host":"etl_host"}'

Input data

__labels__:host#$#myhost|qps#$#10
__name__:rt
__time_nano__:1614739608
__value__:123

Output

__labels__:etl_host#$#myhost|qps#$#10
__name__:rt
__time_nano__:1614739608
__value__:123

Example 4: Format invalid data.

SPL statement
```
* | metric-to-metric -format
```

Input data

__labels__:host#$#myhost|qps#$#10|asda$cc#$#j|ob|schema#$#|#$#|#$#xxxx
__name__:rt
__time_nano__:1614739608
__value__:123

Output

__labels__:asda_cc#$#j|host#$#myhost|qps#$#10
__name__:rt
__time_nano__:1614739608
__value__:123