Getting started with PPL
Run a query
On a file, without a server:
lynxdb query --file access.log 'source=main | stats count() by level'
The CLI loads the file into an in-process index named main, detects common
log formats, extracts key=value pairs and the fields of combined, syslog
and docker lines, and runs the query. Piped input works the same way:
cat access.log | lynxdb query 'source=main | where status >= 500 | top 5 path'
On a server (default http://localhost:3100), the same text queries the
stored indexes:
lynxdb query 'source=main | stats count() by level' --since 1h
curl -s -X POST localhost:3100/api/v1/query \
-H 'Content-Type: application/json' \
-d '{"q": "source=main | stats count() by level", "from": "-1h"}'
The examples on this page run over testdata/logs/access.log of the
LynxDB repository, 1000 lines such as:
2025-01-15T08:00:00.105Z host=web-03 level=ERROR method=GET path=/api/v1/users status=502 user=grace response_time=786 bytes=32228 msg="GET /api/v1/users completed"
Anatomy of a query
source=main level=ERROR | stats count() as errors by host | sort - errors
source=mainnames the index to read.search source=mainandindex=mainmean the same.level=ERRORis a search term of the source command. Terms after the source keep only matching events; see Search semantics.| stats ...,| sort ...are commands. Each one reads the rows of the command before it.
Command names and keywords are case-insensitive; field names are case-sensitive.
Filter
Free-text and field terms in the source command:
source=main "auth/login" status>=500 earliest=-1h
Boolean expressions with where:
source=main | where status >= 500 AND host LIKE 'web%'
source=main | where path IN ('/api/v1/users', '/api/v1/orders')
Shape the rows
source=main
| where response_time > 500
| eval kb = bytes / 1024
| fields _time, host, path, response_time, kb
| sort _time
| head 3
_time host path response_time kb
2025-01-15T08:00:00.105Z web-03 /api/v1/users 786 31
2025-01-15T08:00:03.072Z web-02 /api/v1/orders 588 21
2025-01-15T08:00:03.556Z web-01 /api/v1/health 776 48
Write spaces around - and *: bytes-1 and bytes*8 are field names in
PPL, so arithmetic is bytes - 1 and bytes * 8. See Syntax.
Aggregate
source=main status>=500
| stats count() as errors, p95(response_time) as p95 by host
| sort - errors
host errors p95
api-02 48 781
web-02 43 782
web-03 41 784
web-01 38 793
api-01 34 768
An aggregate without an alias produces a column named after its text, such
as count(). Give it an alias when a later command refers to it.
Over time
source=main | timechart span=5m count() by level
source=main | stats count() by span(_time, 1h), status
See Time series.
Inspect a plan
explain before a query, lynxdb query --explain or lynxdb explain
print the plan instead of running the query:
lynxdb query --explain 'source=main error | where host = "web-01" | stats count() by status'
Logical plan:
Project(`count()`, status)
└─ Aggregate(`count()` = count() BY status)
└─ Filter(host = "web-01")
└─ Filter(SEARCH_WORD(_raw, "error"))
└─ Scan(main)
Optimized plan:
Project(`count()`, status)
└─ Aggregate(`count()` = count() BY status)
└─ Filter(SEARCH_WORD(_raw, "error") AND (host = "web-01"))
└─ Scan(main) columns=[_raw, _time, host, status] prune=SEARCH_WORD(_raw, "error") prune=host = "web-01"
Optimizer rules:
merge-filters x1
prune-scan x1
When a query fails
Errors carry a kind and a position:
source=main | stats count() by host | where nope = 1
^
semantic error at line 1, column 45: field nope not found (available fields: count(), host)
See Error handling. Next: the command guide and the examples.