Table & column reference
The SQL Editor and Builder run against ClickHouse. The database is `observekit` — the editor defaults to it, so you can write FROM logs (unqualified) instead of FROM observekit.logs.
> Two gotchas up front. Durations are stored in nanoseconds (duration_ns), not milliseconds — divide by 1e6 for ms. The source column is `source_id`, not source. attributes / labels / parsed_fields are JSON strings — extract with JSONExtractString(col, 'key').
logs
Every log line (agent- or OTLP-collected).
| Column | Type | Notes |
|---|---|---|
timestamp | DateTime64(9, UTC) | Nanosecond precision, UTC |
source_id | String | The source the line belongs to |
namespace | String | K8s namespace (empty for non-k8s) |
pod_name | String | |
container_name | String | |
container_id | String | |
node_name | String | |
stream | String | stdout / stderr |
level | String | debug, info, warn, error, trace, unknown (unparseable) |
message | String | The log body |
labels | String (JSON) | Pod labels / OTLP attributes |
parsed_fields | String (JSON) | Structured fields parsed from the line |
trace_id | String | Links to a span (if correlated) |
span_id | String | |
exception_fingerprint | String | Groups repeated stack traces (see Exceptions) |
workload | String | Deployment/StatefulSet/DaemonSet name |
workload_kind | String | e.g. Deployment |
Ordered by (source_id, timestamp).
spans
Trace spans from OTLP.
| Column | Type | Notes |
|---|---|---|
trace_id | String | One trace = many spans |
span_id | String | |
parent_span_id | String | Empty for the root span |
name | String | Operation name, e.g. GET /checkout |
service_name | String | From the OTLP service.name attribute |
kind | String | server, client, internal, … |
start_time | DateTime64(9, UTC) | Use this for time filters, not timestamp |
end_time | DateTime64(9, UTC) | |
duration_ns | Int64 | Nanoseconds — duration_ns/1e6 for ms |
status_code | String | OK, ERROR, UNSET |
status_message | String | |
attributes | String (JSON) | Span attributes (e.g. http.status_code) |
events | String (JSON) | Span events |
source_id | String |
Ordered by (service_name, start_time, trace_id).
metrics
Time-series points.
| Column | Type | Notes |
|---|---|---|
timestamp | DateTime64(9, UTC) | |
name | String | Metric name, e.g. container_cpu_usage_percent |
type | String | gauge, counter, … |
value | Float64 | |
source_id | String | |
labels | String (JSON) | Metric dimensions |
Ordered by (name, source_id, timestamp).
There is no cold table, and no meta database
logs, metrics and spans are the whole queryable surface. Three things people look for do not exist, and it is worth knowing why rather than hunting for them:
No `cold_logs` or `unified_logs`. ClickHouse serves hot and archived data transparently through its storage policy, so logs already spans both. A 20-day query is the same query as a 15-minute one — just slower, because it reads from object storage. You never pick a tier.
But it does not span more than retention. Rows move to the archive volume after 7 days and are deleted at 30, so a window reaching further back returns nothing — not an error, just an empty grid, which reads as a broken editor. "Transparent" means you never choose a tier; it does not mean the data is still there.
No `log_summary` or `error_summary`. There are no pre-aggregated rollup tables. Aggregate logs directly with GROUP BY toStartOfInterval(timestamp, INTERVAL 1 HOUR); ClickHouse is built for exactly this and a narrow time range keeps it cheap.
No `meta.sources`. Source, user and API-key metadata lives in SQLite, in a different engine entirely — the query editor's read-only ClickHouse user has no route to it, and is additionally restricted to the telemetry database by an explicit allowlist. To resolve a source_id to a name, use the Sources page or the source filter in the editor.
Time helpers
- There are no `$from` / `$to` variables. The editor does not substitute anything into your SQL, so write the time range literally —
WHERE timestamp >= now() - INTERVAL 1 HOUR. Earlier versions of this page documented those placeholders; pasting them produces a ClickHouse syntax error. - Or use ClickHouse directly:
WHERE timestamp > now() - INTERVAL 1 HOUR.
See Query reference for operators, JSON extraction, wildcards/regex, and limits.