Skip to content

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, WITH and EXPLAIN run; CREATE, COPY, INSERT, SET and 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: logs with logs, traces and spans with apm, recommendations and namespace_costs with finops (see docs/features.md).
  • A column is one of six types: string, int, float, bool, timestamp and json. A timestamp is UTC, RFC 3339 with milliseconds; a json value 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 json cell 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 timestamp column, 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-api under 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, for IN ({{name}})). A clause inside [[ ]] drops while its filters are empty, so WHERE 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}}, or IN ({{country}}) for several values), the filter lists that column's values from its table, read with SELECT 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 connection databases.query and 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 with dashboards.write still 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.

TablePermissionWhat a row is
clustersestate.readA cluster, with its capacity and its bill
nodesestate.readA node: what it has, uses and costs
namespacesestate.readA namespace
podsestate.readA pod, with its owner, node, restarts, requests, limits and usage
containersestate.readA container of a pod, with its state and why it last stopped
workloadsestate.readA Deployment, StatefulSet, DaemonSet, CronJob or Job
eventsestate.readA Kubernetes event
persistent_volumesestate.readA PersistentVolume and its claim
alertsestate.readAn alert firing or pending now
recommendationsestate.readA recommended change and what it saves a month
namespace_costsestate.readA namespace's share of its cluster's bill
audit_eventsaudit.readAn entry of the audit trail
logslogs.readA stored log line
tracesapm.readA stored trace
spansapm.readA stored span
promql(), promql_instant()telemetry.querySamples from the metrics engine

Every column, with its type:

text
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 json

A 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, with ts, value and one string column per label the answer carries. start and end take a timestamp or an interval back from now ('1h'); end defaults to now, and step to about 240 points.
  • promql_instant(query) is an instant query: one row per series, its labels as columns and its value.

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.

sql
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 DESC

Limits

  • 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"):

RouteNeedsBody and answer
POST /api/v1/sql/querytelemetry.query{sql, limit, timeoutMs} → {columns, rows, stats}
POST /api/v1/sql/explaintelemetry.query{sql} → {plan}
GET /api/v1/sql/schematelemetry.queryThe tables and functions the caller may query, with their columns and examples
GET, POST /api/v1/sql/queriestelemetry.queryThe caller's saved queries and the ones shared with them; a new one
GET, PUT, DELETE /api/v1/sql/queries/{id}telemetry.queryOne saved query; only its owner changes it
POST /api/v1/sql/generatetelemetry.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.

bash
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/query

Examples

The page opens on these, and each runs as written.

Pods restarting, by namespace:

sql
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 DESC

Spot against on-demand spend, with each one's share of the bill:

sql
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 DESC

The last hour's errors by service, joined to the pods that wrote them:

sql
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 DESC

p99 latency by route:

sql
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 15

CPU from promql() against the pods' requests:

sql
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 utilization

Changes in Orbit by actor, from the audit trail:

sql
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 DESC

The 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.