SQL Query Language

KubeSense lets you query your traces directly with SQL. SQL mode is powered by ClickHouse, so you get the full expressive power of ClickHouse SQL — aggregations, window functions, string and date functions, CTEs, and subqueries — over your trace data.

Open the Traces page and select the SQL tab above the search bar. Write a SELECT statement, hit Run, and visualize the result as a list, time series, bar chart, top list, or pie chart.

SELECT server, quantile(0.95)(duration_ms) AS p95
FROM traces
WHERE $__timeFilter(timestamp)
GROUP BY server
ORDER BY p95 DESC

info: Queries are read-only. Only SELECT statements are accepted — inserts, updates, and DDL are rejected.

Logs can be queried the same way — see SQL for Logs.

The `traces` Table

All trace data lives in a single table named traces. Use it directly — no database prefix is needed (or allowed).

SELECT * FROM traces WHERE $__timeFilter(timestamp) LIMIT 100

SELECT * expands to all catalog columns except the raw attribute arrays (attribute_names, attribute_values). Use @attr syntax to access those.

Time Range and Clusters

Queries are automatically scoped to the cluster(s) selected in the UI — you never need to filter by cluster yourself, and you cannot query clusters outside your selection.

The time range picker is applied through the $__timeFilter macro. Include it in your WHERE clause to scope the query to the selected time window:

SELECT * FROM traces WHERE $__timeFilter(timestamp) LIMIT 100

warn: If you omit $__timeFilter, your query scans all data regardless of the time picker. Always include it unless you intentionally want an unbounded scan.

Macros

Macro	Meaning
`$__timeFilter(timestamp)`	Expands to a span start-time range using the UI time range
`$__fromTime`	The start of the selected time range, as a scalar — usable in any expression
`$__toTime`	The end of the selected time range, as a scalar
`$__clusters`	The selected cluster filter (`cluster IN (...)`). Optional — cluster scoping is enforced automatically either way

-- Scalar macros in expressions
SELECT server, dateDiff('second', $__fromTime, timestamp) AS age_s
FROM traces
WHERE $__timeFilter(timestamp)
LIMIT 100

Trace Columns

Column	Description
`timestamp`	Span start time — use with `$__timeFilter(timestamp)`
`duration_ms`	Span duration in milliseconds
`trace_id`	Distributed trace identifier
`span_id`	Individual span identifier
`status`	Span status: `'ok'` or `'error'`
`status_code`	Response status code as a string: `'200'`, `'500'`, etc.
`protocol`	Protocol: `'HTTP'`, `'gRPC'`, `'TCP'`, `'MongoDB'`, `'Redis'`, `'MySQL'`, `'PostgreSQL'`
`method`	Request method: `'GET'`, `'POST'`, `'PUT'`, `'DELETE'`, `'PATCH'`
`role`	Span role: `'server'` or `'client'`
`request_type`	Boolean — `true` for external requests, `false` for internal
`source`	Trace source: `'eBPF'` or `'OTel'`
`client` / `client_namespace`	Calling service and its namespace
`server` / `server_namespace`	Called service and its namespace
`cluster` / `namespace` / `workload`	Kubernetes context
`instance`	Pod the span came from
`node_name`	Kubernetes node name
`container`	Container name
`service`	Application service name
`operation_name`	Span operation name
`resource`	Normalized resource path (e.g. `/api/users/:id`)
`reason`	Reason for a detected issue
`partner_cluster`	Peer cluster for cross-cluster calls
`region`	Region
`app_version`	Application version
`env_type`	Environment type

Any field not in this list is available through @attribute syntax.

info: status_code is stored as a string. Use string literals when filtering: status_code = '500', not status_code = 500. To compare ranges, cast first: toInt32OrNull(status_code) >= 500.

Dynamic Attributes (`@attr`)

Traces carry dynamic key-value attributes — OpenTelemetry span attributes and other instrumentation-supplied values. Access any of them with the @ prefix:

SELECT @http.status_code, @http.method, server
FROM traces
WHERE $__timeFilter(timestamp) AND @http.status_code = '500'
LIMIT 100

Dot notation is supported (@http.status_code, @db.statement).
A missing attribute returns an empty string (not NULL).
Attribute values are always strings — cast when you need numbers:

SELECT server, toFloat64OrNull(@db.rows_returned) AS rows_returned
FROM traces
WHERE $__timeFilter(timestamp)
  AND toFloat64OrNull(@db.rows_returned) > 1000
ORDER BY rows_returned DESC

info: Attributes named from_time, to_time, or clusters cannot be accessed via @ — these names are reserved internally.

Direct attribute array access

When you need array-level operations (checking if an attribute key exists, listing all keys on a span), query attribute_names and attribute_values directly:

-- Check whether a span has a specific attribute
SELECT server, resource
FROM traces
WHERE $__timeFilter(timestamp)
  AND has(attribute_names, 'db.statement')
LIMIT 100

-- Find the most common attribute keys across all spans
SELECT arrayJoin(attribute_names) AS attr_key, count() AS cnt
FROM traces
WHERE $__timeFilter(timestamp)
GROUP BY attr_key
ORDER BY cnt DESC
LIMIT 30

Functions

Nearly all ClickHouse functions are supported, including aggregate functions, window functions, and combinators. The most useful ones for trace analysis:

Aggregations

Function	Description
`count(*)` / `count(x)`	Row count / non-null count
`sum(x)`, `avg(x)`, `min(x)`, `max(x)`	Basic aggregates
`uniq(x)`	Approximate distinct count (fast)
`uniqExact(x)`	Exact distinct count
`quantile(0.95)(x)`	Percentile (p50, p95, p99, ...) — the core of latency analysis
`topK(10)(x)`	Most frequent values
`countIf(cond)`, `sumIf(x, cond)`, `avgIf(x, cond)`	Conditional aggregates
`groupArray(x)`	Collect values into an array
`argMax(a, b)` / `argMin(a, b)`	Value of `a` at max/min of `b`

Date and Time

Function	Description
`toStartOfMinute(ts)`, `toStartOfHour(ts)`, `toStartOfDay(ts)`	Truncate to time bucket
`toStartOfInterval(ts, INTERVAL 5 MINUTE)`	Arbitrary time buckets — the building block for time series
`dateDiff('second', a, b)`	Difference between timestamps
`now()`	Current time
`formatDateTime(ts, '%Y-%m-%d %H:%M')`	Format a timestamp

Strings

Function	Description
`like(s, '%pattern%')` / `s LIKE '%pattern%'`	Substring pattern match
`ilike(s, '%pattern%')`	Case-insensitive LIKE
`position(s, 'needle')`	Substring position (0 = not found)
`match(s, 'regex')`	Regular expression match
`extract(s, 'regex')`	Extract first regex match
`extractAll(s, 'regex')`	Extract all matches as array
`lower(s)` / `upper(s)`	Case conversion
`splitByChar('/', s)`	Split into array
`replaceRegexpAll(s, 'regex', 'repl')`	Regex replace
`substring(s, start, len)`	Substring (1-indexed)
`length(s)`	String length

Type Conversion & Conditionals

Function	Description
`toFloat64OrNull(s)`, `toInt64OrNull(s)`	Safe numeric casts (NULL on failure)
`toString(x)`	Convert to string
`if(cond, then, else)`	Conditional
`multiIf(c1, v1, c2, v2, ..., default)`	Multi-branch conditional
`coalesce(a, b, ...)`	First non-NULL value

Blocked Functions

For security, functions that access external systems or affect server behavior are blocked: sleep, sleepEachRow, file, url, s3, hdfs, mysql, postgresql, sqlite, odbc, jdbc, remote, remoteSecure, cluster, clusterAllReplicas, dictGet variants, executable, and random-data generators. Using one returns an error like function "sleep" is not permitted in SQL queries.

CTEs and Subqueries

Standard WITH clauses and subqueries work as expected:

WITH failed AS (
  SELECT server, resource, duration_ms
  FROM traces
  WHERE $__timeFilter(timestamp) AND status = 'error'
)
SELECT server, count(*) AS failures, avg(duration_ms) AS avg_dur
FROM failed
GROUP BY server
ORDER BY failures DESC

Practical Examples

Latency percentiles by service

SELECT
  server,
  count(*) AS requests,
  quantile(0.5)(duration_ms) AS p50,
  quantile(0.95)(duration_ms) AS p95,
  quantile(0.99)(duration_ms) AS p99
FROM traces
WHERE $__timeFilter(timestamp)
GROUP BY server
ORDER BY p95 DESC
LIMIT 20

Error rate between services

SELECT
  client,
  server,
  count(*) AS calls,
  countIf(status = 'error') AS errors,
  round(errors / calls, 4) AS error_rate
FROM traces
WHERE $__timeFilter(timestamp) AND protocol = 'HTTP'
GROUP BY client, server
HAVING calls > 50
ORDER BY error_rate DESC

Slowest endpoints

SELECT
  resource,
  method,
  count(*) AS hits,
  quantile(0.95)(duration_ms) AS p95
FROM traces
WHERE $__timeFilter(timestamp) AND role = 'server'
GROUP BY resource, method
HAVING hits > 20
ORDER BY p95 DESC
LIMIT 25

Throughput over time (time series)

SELECT
  toStartOfInterval(timestamp, INTERVAL 5 MINUTE) AS t,
  status,
  count(*) AS cnt
FROM traces
WHERE $__timeFilter(timestamp)
GROUP BY t, status
ORDER BY t

Visualize this with Time Series to see request volume and error trends together.

HTTP 5xx errors by endpoint

SELECT
  resource,
  status_code,
  count(*) AS hits
FROM traces
WHERE $__timeFilter(timestamp)
  AND protocol = 'HTTP'
  AND toInt32OrNull(status_code) >= 500
GROUP BY resource, status_code
ORDER BY hits DESC
LIMIT 25

Database call performance

SELECT
  server,
  protocol,
  count(*) AS queries,
  avg(duration_ms) AS avg_dur,
  quantile(0.99)(duration_ms) AS p99
FROM traces
WHERE $__timeFilter(timestamp)
  AND protocol IN ('MySQL', 'PostgreSQL', 'MongoDB', 'Redis')
GROUP BY server, protocol
ORDER BY p99 DESC

External dependency latency

SELECT
  server,
  count(*) AS calls,
  quantile(0.95)(duration_ms) AS p95,
  countIf(status = 'error') AS errors
FROM traces
WHERE $__timeFilter(timestamp) AND request_type = true
GROUP BY server
ORDER BY p95 DESC
LIMIT 20

Spans slow due to a specific DB query

SELECT
  server,
  @db.statement AS statement,
  count(*) AS calls,
  quantile(0.95)(duration_ms) AS p95
FROM traces
WHERE $__timeFilter(timestamp)
  AND has(attribute_names, 'db.statement')
  AND duration_ms > 500
GROUP BY server, statement
ORDER BY p95 DESC
LIMIT 20

Spans for a single trace

SELECT timestamp, span_id, service, resource, duration_ms, status
FROM traces
WHERE $__timeFilter(timestamp) AND trace_id = '<trace-id>'
ORDER BY timestamp

Limits and Behavior

Default row limit: 1000. Add an explicit LIMIT N to return more (or fewer) rows.
Read-only. Only SELECT queries run; the underlying connection is read-only, so mutations are impossible.
Cluster scoping is enforced. Queries can only see data from clusters you have selected and are authorized for.
Timeouts. Very expensive queries are cancelled — narrow the time range or aggregate instead of scanning raw rows.

Querying via API

The same SQL can be executed programmatically against the KubeSense API — see API Access for authentication and the /api/traces/sql/execute endpoint.

SQL Query Language

ON THIS PAGE