Features
SQL editor
Orbit SQL runs read-only SQL over everything the product holds: the Kubernetes estate, costs,
the audit trail, logs, traces, spans and metrics. The Deck's editor is at /deck/sql. Running
a query needs the telemetry.query permission, and each table needs its own on top of it (see
"Tables and permissions" below); the built-in viewer, editor and admin roles all hold
telemetry.query (see identity.md). The routes and shapes are CONTRACT.md
Addendum v0.23.
The language
The engine is Apache DataFusion 55.1, inside orbit-api, speaking PostgreSQL-flavoured SQL:
joins, aggregates, window functions, CTEs and subqueries, with DataFusion's functions
(approx_percentile_cont, date_bin, date_trunc, regexp_like and the rest).
- It is read-only, always.
SELECT,WITHandEXPLAINrun;CREATE,COPY,INSERT,SETand every other statement are refused, and no function reads a file or a URL. - Each request gets its own session holding only the tables the caller may read. A table you
cannot read does not exist for you, so asking for it is the ordinary "table not found". The
same holds for a table whose feature is switched off:
logswithlogs,tracesandspanswithapm,recommendationsandnamespace_costswithfinops(seedocs/features.md). - A column is one of six types:
string,int,float,bool,timestampandjson. Atimestampis UTC, RFC 3339 with milliseconds; ajsonvalue is an object or an array; SQL NULL is JSON null. - Units are fixed: cores as a float (0.25 is 250m), bytes as an int, money in USD as a float.
Column names say so:
*_bytes,*_usd,*_ms, and CPU in cores. - Unquoted names are lower-case, as in PostgreSQL.
now()is the time the query started.
Using the editor
- Ctrl+Enter (Cmd+Enter on a Mac) runs the statement the cursor is in; with text selected it
runs only the selection. Statements are separated by
;, so one editor can hold several. - Completion offers keywords, tables, the columns of the tables the statement reads, and the two table functions; Ctrl+Space opens it anywhere. Tab takes the highlighted suggestion and otherwise moves focus out of the editor, so the keyboard is never trapped.
- An error comes back with its line and column when DataFusion knows them: the editor marks the spot, the gutter marks the line, and "Show line" puts the cursor there.
- The schema panel lists the tables and functions you can query, grouped as estate, cost, audit, telemetry and metrics, with each column's type and meaning. A click inserts a name at the cursor; each table has examples that open in the editor.
- The results come as a grid: every column sorts, values print in their units, timestamps in
the zone the top bar is set to, and a
jsoncell opens in full. Enter opens a row with every value, Ctrl+C copies the cell under the keyboard, and "Export CSV" downloads what the grid shows, in its order. The grid draws only the rows in view, so 10,000 rows scroll like 50. - "Chart" draws the result with the Deck's own charts: lines or stacked areas over a
timestampcolumn, bars over anything else, a pie of the rows (the 8 largest, with the total and each share) or the first row's numbers. You pick the x column, the numeric columns to plot and the column whose values split the lines. Lines, areas and bars start at zero. - "Explain" shows the plan DataFusion would run.
- "Cancel" stops a running query, in the browser and on the server.
- "Copy link" puts the SQL in the address's fragment (
#sql=), never in its query string, so it reaches no server log; opening the link runs it. The editor keeps your SQL across a reload. - History keeps the last 50 queries you ran, per user, in this browser only.
- Saved queries live in
orbit-apiunder your name. You can rename, delete and share them; a query someone shares with the team opens read-only, and "Duplicate to edit" makes it yours. - "Write with Nocturne" (with
nocturne.use) turns a sentence into SQL written from your schema and puts it in the editor with its explanation. Nocturne never runs it: you read it, then run it.
From a query to a dashboard
{{name}}in a statement is a filter, as in Metabase: a box for each name appears under the editor, and its value reaches the SQL as a quoted string (several values as a comma list, forIN ({{name}})). A clause inside[[ ]]drops while its filters are empty, soWHERE 1 = 1 [[AND country = {{country}}]]reads every row until you type a country. A value with a backslash is refused.- "Add to dashboard" (with
dashboards.write) puts the answer on a dashboard as a card: the chart as you set it on the "Chart" tab, else the grid. You pick one of your dashboards or name a new one, and the next card goes to the same dashboard by default. Cards fill the dashboard two by two. - Every
{{name}}the card reads becomes a filter at the top of the dashboard, already wired: a filter fills each card that names it, and All drops the[[ ]]clauses that read it. When the SQL compares a column with it (c.country = {{country}}, orIN ({{country}})for several values), the filter lists that column's values from its table, read withSELECT DISTINCT; otherwise it is a text box. A filter whose values cannot be read takes typed text. - A card runs its SQL with the session of whoever opens the dashboard: on Orbit SQL it needs
telemetry.query, on a database connectiondatabases.queryand a role the connection allows. Without them the card says so and runs nothing. Only a holder of that permission, on a connection their role may use, adds a SQL card or changes one's SQL; anyone withdashboards.writestill moves and resizes it. - The card menu's "Open in SQL editor" opens the card's SQL on its source. The panel editor edits the SQL, the source and the columns the chart reads (x, y, split by).
- A card draws its chart as the "Chart" tab drew it: every bar up to 30, then a line that says how many rows the answer had; a pie names its 8 largest slices and how many rows it holds; counts print with three significant digits, the exact value on hover. A time axis past four months marks month starts with their year. While a filter's new answer runs, the card waits blank instead of showing the old numbers. A dashboard of SQL cards alone hides the time range, which its cards do not read.
- Each slice and each line keeps the colour it had in the editor, whatever a filter leaves out: the card keeps the labels it was made with in their palette order. Bars name their value column above and their label column below, and print each value over its bar when "Values" is on.
- A number from an answer of many rows names the row it shows ("card · amount, first of 4 rows"). Over time it shows the newest point, named by its date, and its change from the point before, as the card does. A pie prints "Total" under its sum.
- The editor hides the time range, which no statement here reads (you write the time window in the SQL), and the auto-refresh interval, which it holds.
Tables and permissions
The estate tables cover every cluster orbit-api reads, and each row names its cluster: the
cluster column, or id in clusters itself.
| Table | Permission | What a row is |
|---|---|---|
clusters | estate.read | A cluster, with its capacity and its bill |
nodes | estate.read | A node: what it has, uses and costs |
namespaces | estate.read | A namespace |
pods | estate.read | A pod, with its owner, node, restarts, requests, limits and usage |
containers | estate.read | A container of a pod, with its state and why it last stopped |
workloads | estate.read | A Deployment, StatefulSet, DaemonSet, CronJob or Job |
events | estate.read | A Kubernetes event |
persistent_volumes | estate.read | A PersistentVolume and its claim |
alerts | estate.read | An alert firing or pending now |
recommendations | estate.read | A recommended change and what it saves a month |
namespace_costs | estate.read | A namespace's share of its cluster's bill |
audit_events | audit.read | An entry of the audit trail |
logs | logs.read | A stored log line |
traces | apm.read | A stored trace |
spans | apm.read | A stored span |
promql(), promql_instant() | telemetry.query | Samples from the metrics engine |
Every column, with its type:
clusters id string, name string, provider string, region string,
version string, nodes int, pods int, cpu_cores float,
memory_bytes int, monthly_cost_usd float
nodes cluster string, name string, node_group string,
instance_type string, capacity_type string, zone string,
status string, kubelet_version string, cpu_cores float,
cpu_used float, memory_bytes int, memory_used_bytes int,
pods int, hourly_cost_usd float, price_source string,
created_at timestamp, labels json
namespaces cluster string, name string, status string,
created_at timestamp, labels json
pods cluster string, namespace string, name string, node string,
phase string, ready bool, restarts int, owner_kind string,
owner_name string, workload string, qos_class string,
ip string, created_at timestamp, started_at timestamp,
cpu_request float, cpu_limit float, cpu_used float,
memory_request_bytes int, memory_limit_bytes int,
memory_used_bytes int, labels json
containers cluster string, namespace string, pod string, name string,
image string, ready bool, restarts int, state string,
reason string, last_termination_reason string,
cpu_request float, cpu_limit float,
memory_request_bytes int, memory_limit_bytes int
workloads cluster string, namespace string, kind string, name string,
replicas int, ready_replicas int, images json,
created_at timestamp, cpu_request float, cpu_used float,
memory_request_bytes int, memory_used_bytes int,
labels json
events cluster string, namespace string, object_kind string,
object_name string, type string, reason string,
message string, count int, first_seen timestamp,
last_seen timestamp
persistent_volumes cluster string, name string, capacity_bytes int, used_bytes int,
storage_class string, status string,
claim_namespace string, claim_name string
alerts cluster string, name string, severity string, state string,
namespace string, workload string, summary string,
since timestamp
recommendations cluster string, action string, namespace string,
workload string, kind string, monthly_savings_usd float,
summary string
namespace_costs cluster string, namespace string, monthly_cost_usd float,
share float
audit_events time timestamp, actor string, action string,
target_type string, target_name string, outcome string,
details json
logs ts timestamp, severity string, severity_number int,
service string, namespace string, pod string,
container string, node string, body string,
trace_id string, span_id string, attrs json
traces trace_id string, start_ts timestamp, duration_ms float,
root_service string, root_name string, status string,
spans int, errors int, services json
spans trace_id string, span_id string, parent_span_id string,
service string, name string, kind string, status string,
start_ts timestamp, end_ts timestamp, duration_ms float,
http_method string, http_route string,
http_status_code int, attrs json, resource jsonA few read better with their values: capacity_type is ON_DEMAND or SPOT; a workload's
kind is Deployment, StatefulSet, DaemonSet, CronJob or Job; share runs from 0 to 1;
severity on an alert is critical, warning or info, on a log line TRACE to FATAL; an
audit_events row's outcome is success, denied or failure. A volume's used_bytes is
its claim's filesystem as the kubelet measures it (kubelet_volume_stats_used_bytes), null when
no running pod mounts the claim or when that filesystem is its node's disk (a hostPath volume, such
as local-path's). The schema panel carries a sentence on every column.
logs, traces and spans read the engine's stores, and hand it what they can: a range on
ts or start_ts (the last hour when the query names none, at most 7 days), equality and IN
on their labels, comparisons on severity, body LIKE '%word%' as the engine's body search,
trace_id = and LIMIT. Anything else is filtered after the engine answers, so a narrow time
range is the cheapest filter there is.
Metrics
Two table functions read the metrics engine with PromQL:
promql(query, start, end, step)is a range query: one row per sample, withts,valueand one string column per label the answer carries.startandendtake a timestamp or an interval back from now ('1h');enddefaults to now, andstepto about 240 points.promql_instant(query)is an instant query: one row per series, its labels as columns and itsvalue.
When the query is a plain selector (container_cpu_usage_seconds_total,
kube_pod_info{namespace="shop"}), Orbit SQL adds your WHERE label = '...' and
label IN (...) filters to the selector, so the engine sends only the series that match. A
selector's columns come from the engine's index: a label column may stay null on every row.
SELECT namespace, avg(value) AS cores
FROM promql('sum by (namespace) (rate(container_cpu_usage_seconds_total[5m]))', '6h')
GROUP BY namespace
ORDER BY cores DESCLimits
- A query returns at most 10,000 rows; the page asks for 1,000 unless you raise it. Past the limit the answer stops there and says it was truncated.
- A query may run for 30 s unless you allow up to 60 s. Past it, it is cancelled on the server
and the answer is
408 timeout. - A query may use 256 MiB of memory. Past it, it is cancelled and the answer is
413 too_large. - A SQL error is
400 sql_error, with its line and column when DataFusion gives them.
Over the API
With the SESSION of a user who holds telemetry.query (identity.md →
"Signing in from a shell"):
| Route | Needs | Body and answer |
|---|---|---|
POST /api/v1/sql/query | telemetry.query | {sql, limit, timeoutMs} → {columns, rows, stats} |
POST /api/v1/sql/explain | telemetry.query | {sql} → {plan} |
GET /api/v1/sql/schema | telemetry.query | The tables and functions the caller may query, with their columns and examples |
GET, POST /api/v1/sql/queries | telemetry.query | The caller's saved queries and the ones shared with them; a new one |
GET, PUT, DELETE /api/v1/sql/queries/{id} | telemetry.query | One saved query; only its owner changes it |
POST /api/v1/sql/generate | telemetry.query, nocturne.use | {prompt} → {sql, explanation}, never run |
columns is [{name, type}], rows is an array of arrays in the same order, and stats is
{rows, truncated, elapsedMs, scannedRows, tables}. A saved query is {id, name, description, sql, shared, owner, createdAt, updatedAt}; saving one is audited as sql.query.save, deleting
it as sql.query.delete.
curl -fsS -H "Authorization: Bearer $ORBIT_AUTH_TOKEN" -H "X-Orbit-Session: $SESSION" \ -H 'Content-Type: application/json' \ -d '{"sql":"SELECT namespace, count(*) AS pod_count FROM pods GROUP BY namespace","limit":100}' \ localhost:8080/api/v1/sql/queryExamples
The page opens on these, and each runs as written.
Pods restarting, by namespace:
SELECT cluster, namespace, count(*) AS pod_count, sum(restarts) AS restart_count
FROM pods
WHERE restarts > 0
GROUP BY cluster, namespace
ORDER BY restart_count DESCSpot against on-demand spend, with each one's share of the bill:
WITH spend AS (
SELECT capacity_type,
count(*) AS nodes,
sum(cpu_cores) AS cpu_cores,
sum(hourly_cost_usd) * 730 AS monthly_cost_usd
FROM nodes
GROUP BY capacity_type
)
SELECT capacity_type, nodes, cpu_cores, monthly_cost_usd,
monthly_cost_usd / sum(monthly_cost_usd) OVER () AS share
FROM spend
ORDER BY monthly_cost_usd DESCThe last hour's errors by service, joined to the pods that wrote them:
SELECT l.service,
p.namespace,
count(*) AS errors,
count(DISTINCT p.name) AS pods,
max(p.restarts) AS max_restarts,
max(l.ts) AS last_error
FROM logs l
JOIN pods p ON p.name = l.pod AND p.node = l.node
WHERE l.severity IN ('ERROR', 'FATAL')
AND l.ts > now() - INTERVAL '1 hour'
GROUP BY l.service, p.namespace
ORDER BY errors DESCp99 latency by route:
SELECT service,
http_method,
http_route,
count(*) AS requests,
approx_percentile_cont(0.99) WITHIN GROUP (ORDER BY duration_ms) AS p99_ms,
avg(CASE WHEN http_status_code >= 500 THEN 1.0 ELSE 0.0 END) AS error_rate
FROM spans
WHERE http_route IS NOT NULL
AND start_ts > now() - INTERVAL '1 hour'
GROUP BY service, http_method, http_route
ORDER BY p99_ms DESC
LIMIT 15CPU from promql() against the pods' requests:
WITH usage AS (
SELECT namespace, pod, avg(value) AS cpu_used
FROM promql('sum by (namespace, pod) (rate(container_cpu_usage_seconds_total{container!=""}[5m]))', '6h')
GROUP BY namespace, pod
)
SELECT p.namespace,
count(*) AS pods,
sum(p.cpu_request) AS cpu_request,
sum(u.cpu_used) AS cpu_used,
sum(u.cpu_used) / sum(p.cpu_request) AS utilization
FROM usage u
JOIN pods p ON p.namespace = u.namespace AND p.name = u.pod
GROUP BY p.namespace
ORDER BY utilizationChanges in Orbit by actor, from the audit trail:
SELECT actor, action, count(*) AS changes, max(time) AS last_change
FROM audit_events
WHERE outcome = 'success'
AND action NOT LIKE 'auth.%'
AND time > now() - INTERVAL '7 days'
GROUP BY actor, action
ORDER BY changes DESCThe page holds six more: the most expensive namespaces, requests per second by service, workloads reserving idle CPU, savings against spend, containers that are not ready, and warning events by reason.
Demo data
With NEXT_PUBLIC_DEMO_MODE left on, the examples answer from the demo estate and its
telemetry, computed the way the engine would compute them, so a row agrees with the Deck page
that shows the same thing. The audit trail is never demo data: its example goes to orbit-api
when one is connected. Any other query, the plan, saved queries and Nocturne need a connected
Orbit, and the page says so.