Docs / Dashboards

Writing widget queries

The Query Workbench, the semantic Builder, the Metrics Builder in Data Explorer, query conventions, and time macros.

dashboardssqlclickhousequeryworkbenchbuildersemanticmacrosmetrics

Building widget queries

Widget queries are written in SQL against ClickHouse. The editor has syntax highlighting and schema-aware autocomplete (table and column suggestions), plus a schema browser for discovering the available tables and columns. Below the editor, every ClickHouse-backed query gets a Query Workbench — an inline panel for running, debugging, and previewing the query without leaving the widget editor. For metrics, a semantic Builder composes correct SQL for you from a metric, aggregation, and label pickers.

Query Workbench

The workbench appears under the SQL editor for the ClickHouse Metrics, Logs, and Traces sources.

  • Run — click Run or press Ctrl+Enter (⌘+Enter on macOS). The query executes through the same pipeline a live dashboard uses: dashboard variable defaults are substituted, and the server injects the tenant filter, the active time filter, and a row limit.
  • Result tabs — Table (sortable results, first 500 rows), Raw (the response JSON), and Chart preview (renders the result in the widget's actual visualization type).
  • Status row — row count, execution time, and the response type. A cached chip means the result came from the 30-second server-side result cache; a warning appears if you edit the SQL after the last run.
  • Distilled errors — ClickHouse errors are trimmed to the core message and error code, with hints for the usual causes: referencing a label as a bare column instead of labels['key'], querying a table that isn't allowed, a query that doesn't start with SELECT/WITH, exceeding the resource budget, or a $variable with no default.
  • Final SQL — expands the query after variable substitution, so you can see exactly what your $variables resolved to. The server additionally injects the time filter, the tenant filter, and a row limit before execution; those are described, not shown.
  • Insert chips — one-click snippets inserted at the cursor: fixed and automatic time buckets (toStartOfFiveMinutes(...), $__time(...)), label group-bys (labels['host_name']), top-K expressions, and $__interval.
  • Starter templates — when the query is blank, Timeseries, Top list, and Single stat starters appear. For metrics they open the Builder prefilled; for logs and traces they insert runnable SQL directly.
  • Variables bar — when the query references $variables, a chip row shows the dashboard default each one resolves to in the preview, and flags any variable that has no default yet.

Builder tab (metrics)

For the ClickHouse Metrics source, the workbench has a second tab — Builder — that composes a query semantically instead of by hand:

  1. Pick a Metric from a popularity-ranked autocomplete (each entry shows its label keys and sample volume).
  2. Choose an Aggregation (avg, sum, min, max, count, uniq, p50, p95, p99) and an optional Time bucket (1 minute to 1 hour; no bucket produces a top list or single value).
  3. Add Group by labels, and filters (= / !=) with value autocomplete scoped to the selected metric. You can also group or filter by curated properties attached to a label's values.
  4. Click Compile → SQL. The compiled query is inserted into the SQL editor, fully editable — the Builder is a starting point, not a cage.
Prefer the Builder for metrics widgets. Compiled queries come out in the shape the platform expects — a metric_name filter, labels[...] map access, a top-level WHERE, and no time or tenant predicates. Compile first, then hand-tune.

Metrics Builder in the Data Explorer

The same semantic building is available outside the designer. In Data Explorer, click Builder to open the Metrics Builder dialog: a searchable metric catalog on the left, the query builder in the middle (aggregation, time bucket, dimensions, filters, limit, and a live Compiled SQL panel), and results on the right. Run it directly (an Auto-run switch re-runs on every change), then either:

  • Save as widget — choose a widget type (line, bar, pie, top list, or table — a suggested type is preselected) and a destination: a new dashboard (created in the Explorer folder) or any existing dashboard.
  • Insert into editor — load the compiled SQL into the Data Explorer's SQL tab to keep working on it by hand.

The semantic catalog

The Builder's pickers are powered by a semantic catalog that VerOps maintains automatically from incoming metric samples (a ClickHouse materialized view — no configuration needed). It tracks every metric name, its label keys, and their values, together with sample counts and last-seen times. Autocomplete is therefore instant and popularity-ranked — the values you actually use most come first — and never scans your raw metric data.

The catalog is also available programmatically under /api/v1.0/semantic/catalog — list metrics, the dimensions of a metric, the values of a dimension, and compile a query spec to SQL.

Writing widget queries: conventions

Widget SQL runs inside a managed pipeline: the platform substitutes your dashboard variables, then injects the tenant filter and the dashboard's time filter into the query before execution. A few conventions keep queries correct and fast.

  • Query only the allowed tables. Widget SQL can read metrics, logs, spans, service_operations, rum_events, rum_sessions, and rum_replays. Queries must be a single statement starting with SELECT or WITH.
  • Always filter by metric_name. The metrics table holds every metric in one wide table. Constrain it first — without it, the query scans all metrics:
SELECT $__time(timestamp), avg(value) AS cpu
FROM metrics
WHERE metric_name = 'system_cpu_usage_total'
GROUP BY ts
ORDER BY ts
  • Keep WHERE at the top level. The injected tenant and time predicates land in the outer query. If your only WHERE sits inside a subquery, the time filter can attach to the wrong scope. Structure queries so the outermost SELECT carries the WHERE; a top-level UNION ALL is fine (each branch is scoped independently).
  • Never add your own time or tenant predicates. Tenant scoping is always injected — you can't (and shouldn't) write it. The time filter comes from the dashboard's time picker; hardcoding a window defeats the picker. If you need the range bounds explicitly, use the $__from / $__to macros instead.
  • Reference labels as map keys. Metric labels live in a map column — write labels['host_name'], not a bare host_name. The Builder tab lists the label keys that actually exist on a metric.
  • Double backslashes in string literals. ClickHouse treats \ as an escape character inside '...', so a Windows path is written 'C:\\Program Files'. This applies only to literals you type — values substituted from dashboard variables are escaped automatically.
  • Use $variables for anything a viewer should switch. $host and ${host} are both substituted with the dashboard variable's value. Strings arrive quoted and escaped; numbers and booleans arrive raw; a multi-value selection becomes a comma-separated list, so write it as labels['host'] IN ($hosts). An unknown variable is left as-is and fails loudly rather than silently matching nothing. Names starting with $__ are reserved for macros.

Time macros

Macro Expands to
$__time(col) toStartOfInterval(col, INTERVAL n SECOND) AS ts — a bucket sized automatically from the time range and chart width
$__interval the automatic bucket as an interval literal, for example 60 SECOND
$__bucket_seconds the automatic bucket size as a bare number of seconds
$__from / $__to the active range bounds as ClickHouse timestamp expressions (exact instants for absolute ranges)
$__app('code') a service filter for an application code, using the right service column for the table ($__appOn('code', 'column') picks the column explicitly)
Warning: $__timeFilter(...) is a Data Explorer macro and is not expanded in widget queries — it would reach ClickHouse verbatim and error. In widgets, time filtering is injected automatically; use $__time(col) for bucketing and $__from/$__to for explicit bounds.

Resource limits

Every widget query is capped server-side: execution time is limited to 30 seconds and results to 10,000 rows (a LIMIT 10000 is appended when a query has none). Results are cached server-side for 30 seconds, so a burst of identical requests — several viewers of the same board, for example — costs one query.

Troubleshooting

A widget shows "No data"

Cause: the widget's query has a typo, its data source has no data in the range, or a dashboard variable resolved to a value that matches nothing.

  1. Open the widget's query and press Run in the Query Workbench — the results table shows whether it returns rows, and errors come back distilled with hints.
  2. Check Final SQL to confirm every $variable resolved to the value you expect.
  3. Widen the dashboard time range.

The query errors with "Unknown identifier"

Cause: metric labels live in a map column, not as bare columns. Fix: reference the label as labels['host_name'], not host_name. The workbench's Builder tab lists the label keys that actually exist on the selected metric.

The dashboard is slow to load

Cause: too many widgets, wide time ranges, high-cardinality group-by clauses, or an aggressive auto-refresh. Fix: reduce the number of widgets per board, narrow the default time range, simplify group-by dimensions, and lengthen the refresh interval.