Scenario comparison between SPL and SQL

Scenario

SQL statement

SPL statement

Data filtering

select * where Type='write'

| where Type='write'

Field processing and filtering

Select a field by exact name and rename it.

select "__tag__:node" as node, path

1. Select a field by exact name and rename it.

   | project node="__tag__:node", path

2. Select fields by pattern.

   | project -wildcard "__tag__:*"

3. Rename a field without affecting other fields.

   | project-rename node="__tag__:node"

4. Remove fields by pattern.

   | project-away -wildcard "__tag__:*"

Data standardization

(SQL function calls)

Convert a data type and parse a time value.

select 
  cast(Status as BIGINT) as Status, 
  date_parse(Time, '%Y-%m-%d %H:%i') AS Time

Convert a data type and parse a time value.

| extend Status=cast(Status as BIGINT), extend Time=date_parse(Time, '%Y-%m-%d %H:%i')

Field extraction

• Regular expression-based extraction

  select 
    regexp_extract(protocol, '\w+')  as scheme,
    regexp_extract(protocol, '\d+')  as version

• JSON data extraction

  select 
    json_extract(content, '$.0.time')  as time,
    json_extract(content, '$.0.msg')  as msg

1. Regex-based extraction: one-time matching

   | parse-regexp protocol, '(\w+)/(\d+)' as scheme, version

2. JSON data extraction: full expansion

   | parse-json -path='$.0' content

3. CSV data extraction

   | parse-csv -delim='^_^' content as ip, time, host