Exploring data: SQL, PromQL and VRL

1. Searching with SQL #

The common language for logs and traces.

Useful queries #

All these queries were run on OpenObserve 1.1.2.

-- Filter on a field
SELECT * FROM commandes WHERE level = 'error'

-- Search text in a field
SELECT * FROM commandes WHERE str_match(message, 'banque')

-- Search all full-text fields
SELECT * FROM commandes WHERE match_all('banque')

-- Aggregate
SELECT service, count(*) AS n, avg(duree_ms) AS moy
FROM commandes GROUP BY service ORDER BY n DESC

-- Per-minute chart
SELECT histogram(_timestamp, '1 minute') AS t, count(*) AS n
FROM commandes GROUP BY t ORDER BY t

-- Percentile
SELECT approx_percentile_cont(duree_ms, 0.95) AS p95 FROM commandes

2. PromQL for metrics #

Reusing your Prometheus queries.

A compatible API #

OpenObserve exposes the Prometheus query API under /api/<organization>/prometheus, so Grafana can connect to it like a Prometheus.

curl -s -u 'root@example.com:Complexpass#123' \
  'http://localhost:5080/api/default/prometheus/api/v1/query?query=cpu_usage' | jq .data.result

3. Optimizing a stream #

Indexes, partitions and retention.

Stream settings #

curl -u 'root@example.com:Complexpass#123' -X PUT \
  -H 'Content-Type: application/json' \
  'http://localhost:5080/api/default/streams/commandes/settings?type=logs' \
  -d '{
    "data_retention": 30,
    "full_text_search_keys": {"add": ["message"]},
    "index_fields": {"add": ["service"]},
    "partition_keys": {"add": [{"field": "level", "types": "value"}]}
  }'

Partitioning on a highly varied field (request ID, user) creates a multitude of small files and slows everything down. Keep partitions for fields with few distinct values.

4. Transforming with VRL #

Functions at read or write time.

A function #

Functions are written in VRL, the Vector language. They can run at read time, or be applied at ingestion through a pipeline. A function must end with ., which returns the event.

curl -u 'root@example.com:Complexpass#123' \
  -H 'Content-Type: application/json' \
  http://localhost:5080/api/default/functions \
  -d '{"name":"marque_grave","function":".grave = true\n.","params":"row","transType":0}'
# {"code":200,"message":"Function saved successfully"}

To try it at read time, pass it base64-encoded in query_fn: the grave field then appears in results without being stored.

5. Dashboards and traces #

Exploring in the UI.

Dashboards #

Panels rely on a SQL or PromQL query, with variables to filter by service or environment. Dashboards export to JSON: keep them under version control.

Traces #

The Traces menu lists requests and shows their timeline. To go from a log to its trace, have your logs carry trace_id: the OpenTelemetry SDK does it for you when the call runs within a trace.