Analytics Query Language
This page documents the SQL meta-comment language used by the Analytics / Query Explorer. The feature and its syntax are under active development and may change.
The Query Explorer uses a lightweight meta-comment language embedded in SQL comments to drive chart rendering, drill-down navigation, and time-range filtering. Any ClickHouse SQL query can become a fully interactive preset by adding a few -- @directive lines at the top.
These directives are also used by Grafana panel export. When TraceHouse runs inside Grafana, Query Explorer can turn the current query and visualization into Grafana panel JSON.
Directives
@meta - Query metadata
Defines the query's identity and display properties.
-- @meta: title='Memory Usage Trend' group='Resources' description='Memory usage over the last 24 hours' interval='1 DAY'
| Parameter | Required | Description |
|---|---|---|
title | Yes | Display name shown in the sidebar and query header |
group | Yes | Category tab (Overview, Inserts, Selects, Parts, Merges, Resources, Advanced Dashboard, Self-Monitoring, or any custom name) |
description | No | Short description shown as a tooltip / subtitle |
interval | No | Default time range, e.g. 1 DAY, 2 HOUR, 7 DAY. Used with {{time_range}} placeholders |
@chart - Chart configuration
Tells the explorer how to visualise the result set.
-- @chart: type=bar group_by=table value=bytes_size style=3d
| Parameter | Required | Description |
|---|---|---|
type | Yes | Chart type: bar, line, pie, area, grouped_bar, stacked_bar, grouped_stacked_bar, grouped_line, or radar |
group_by | Yes for non-radar charts | Column name to use for the X-axis / category grouping |
value | Yes for non-radar charts | Column name(s) for the Y-axis. Can be a single column or comma-separated list (e.g. value=p50,p95,p99) |
series | No | Column name used for series splitting (for grouped_bar, stacked_bar, grouped_stacked_bar, grouped_line). In grouped_stacked_bar this is the stacked dimension |
cluster | No | Second categorical dimension, grouped_stacked_bar only. Bars are clustered side by side by this column and stacked by series within each cluster (e.g. cluster=host series=kind) |
style | No | 2d (default SVG/recharts) or 3d (Three.js) |
unit | No | Unit suffix for value axis ticks, e.g. ms, s, MB, %. Time units (ms, s) auto-scale (e.g. 1400 ms → 1.4 s) |
orientation | No | horizontal (default for grouped/stacked bars) or vertical (shorthand v). Controls bar direction — horizontal puts labels on the left for readability |
color | No | Override the default chart color with a hex value (e.g. #f59e0b) |
render | No | Rendering variant within the chart type. overlay = many-series "spaghetti" (no legend, thin translucent lines, hover to spotlight one, click to isolate); grouped_line only |
type=grouped_stacked_bar — Clustered stacked bars
Shows three categorical dimensions at once: the X-axis (group_by, e.g. a time bucket), a set of side-by-side bars per X value (cluster, e.g. server), and a stack within each bar (series, e.g. activity kind). Colour encodes the series; the clusters are told apart by their side-by-side position and the tooltip, which groups values by cluster. Use it to answer "for each bucket, what is each server doing, broken down by kind?"
-- @chart: type=grouped_stacked_bar group_by=t value=cpu_cores cluster=host series=kind style=2d
SELECT t, host, kind, sum(cost) AS cpu_cores
FROM ...
GROUP BY t, host, kind
ORDER BY t ASC
The query returns one row per (group_by, cluster, series) combination. To stay legible, keep the number of X buckets small (the renderer keeps the most recent ~12) and the cluster/series cardinality modest — many servers × many kinds produces a dense chart. This type is 2D only.
type=radar — Multi-axis pressure chart
Radar charts render one normalized shape from several result columns. They are useful for summarizing competing resource signals such as CPU, memory, I/O, network, and query load.
-- @chart: type=radar axes=cpu:cpu_pressure,memory:memory_pressure,io:io_bytes,network:network_bytes,queries:active_queries ranges=cpu:0..1,memory:0..1,io:1Mi..10Gi,network:1Mi..10Gi,queries:1..1000 transforms=cpu:linear,memory:linear,io:log,network:log,queries:log color=profile_level
For full chart rendering, the query should return exactly one row. If a query returns multiple rows, aggregate in SQL first.
| Parameter | Required | Description |
|---|---|---|
axes | Yes for raw-column radar charts | Comma-separated axis:column pairs. AQL reads the raw result columns and normalizes them into the radar shape |
ranges | Yes with axes | Comma-separated axis:low..high ranges. Supports plain numbers and readable byte units such as 1Mi and 10Gi |
transforms | No | Comma-separated axis:linear or axis:log. Axes default to the profile/default transform when omitted |
label | No | Result column used as the radar label |
profile | No | Named radar behavior preset, e.g. query_pressure |
color | No | Hex color or computed color source such as profile_level |
values | Alternative to axes | Existing SQL result column containing normalized radar values |
labels | No with values | Existing SQL result column containing axis labels for values |
color_by | No | Result column used as the numeric color score for SQL-created radar values |
SQL-created radar values are also supported when the query already returns normalized arrays:
-- @chart: type=radar label=query_id values=pressure_values labels=pressure_labels color_by=pressure_score
@drill - Drill-down navigation
Enables click-to-drill: clicking a chart segment navigates to another query, passing the clicked value as a filter.
-- @drill: on=database into='Table Sizes'
| Parameter | Required | Description |
|---|---|---|
on | Yes | Column whose clicked value is passed to the target query |
into | Yes | Title of the target query to navigate to |
@link - Popup link to another query
Makes a table column clickable. Clicking a cell value opens a modal popup that runs a target query with the clicked value passed as a drill parameter. Unlike @drill which navigates away, @link keeps your current view and overlays the results.
-- @link: on=query_hash into='TraceHouse Query Executions'
| Parameter | Required | Description |
|---|---|---|
on | Yes | Column whose clicked value is passed to the target query |
into | Yes | Title of the target query to open in the popup |
The target query receives the clicked value via the standard {{drill:column | fallback}} or {{drill_value:column | fallback}} placeholders.
@cell - Table cell decoration
Decorates table columns with visual styles. Each @cell: line targets one column with one decoration type. Multiple @cell: lines can target the same column (e.g. gauge + rag).
| Parameter | Required | Description |
|---|---|---|
column | Yes, except synthetic radar cells | Column name to decorate |
type | Yes | Decoration type: rag, gauge, sparkline, or radar |
type=rag — Red / Amber / Green coloring
Applies conditional coloring to table cells. Only affects table view.
Numeric (ascending — lower is better):
-- @cell: column=avg_bytes_read type=rag green<2000 amber<40000
Numeric (descending — higher is better):
-- @cell: column=uptime_days type=rag green>30 amber>7
Text mode — match exact string values:
-- @cell: column=status type=rag green=ok,healthy amber=degraded red=error,down
type=gauge — Inline horizontal bar
Renders a column as a horizontal gauge bar in the table. Combine with type=rag on the same column for colored bars.
-- @cell: column=used_pct type=gauge max=100 unit=%
-- @cell: column=used_pct type=rag green<70 amber<85
-- @cell: column=disk_used type=gauge max=disk_total unit=TiB
| Parameter | Required | Description |
|---|---|---|
max | Yes | Bar's 100% value — a fixed number (e.g. 100) or another column name (e.g. disk_total) |
unit | No | Unit suffix displayed beside the value |
type=sparkline — Inline trend line
Renders a column as a tiny SVG trend in the table. The column should contain an array of numeric values (e.g. from groupArray()).
-- @cell: column=disk_delta type=sparkline ref=0
-- @cell: column=query_rate type=sparkline color=#f59e0b fill=true
| Parameter | Required | Description |
|---|---|---|
ref | No | Horizontal reference line value (e.g. 0 for delta charts) |
color | No | Hex color for the sparkline stroke |
fill | No | true to fill the area under the line |
type=radar — Compact row shape
Renders a compact radar badge in a table cell. This is intended for row scanning: for example, showing whether a query is time-heavy, memory-heavy, I/O-heavy, or expensive across several dimensions.
There are two supported forms.
Synthetic radar column from raw SQL columns:
-- @cell: type=radar radar_column=shape profile=query_pressure axes=time:query_duration_ms,memory:memory_usage,cpu:cpu_ms,io:io_bytes,scan:scan_pressure ranges=time:100..60000,memory:32Mi..8Gi,cpu:100..60000,io:1Mi..10Gi,scan:0..1 color=profile_level
radar_column creates a display-only table column. The SQL query does not need to return a shape column; it only needs to return the columns referenced by axes.
Existing SQL values column:
-- @cell: column=pressure_values type=radar labels=pressure_labels color_by=pressure_score
Use this form when SQL already returns normalized radar arrays.
| Parameter | Required | Description |
|---|---|---|
column | Yes for SQL-created values | Existing result column containing normalized radar values |
radar_column | Yes for synthetic cells | Display-only column name created by AQL. Mutually exclusive with column |
axes | Yes with radar_column | Comma-separated axis:column pairs |
ranges | Yes with radar_column | Comma-separated axis:low..high ranges |
transforms | No | Comma-separated axis:linear or axis:log overrides |
profile | No | Named radar behavior preset, e.g. query_pressure |
labels | No | SQL result column containing labels when using column |
color | No | Hex color or computed color source such as profile_level |
color_by | No | SQL result column used as a numeric color score |
color_scale | No | Named color scale for SQL-created radar values |
colors | No | Custom color stops, e.g. 0:#8b949e,0.4:#f59e0b,0.85:#ef4444 |
For type=radar, use exactly one of column or radar_column. Synthetic radar cells require both axes and ranges.
Multiple decorations on different columns:
-- @cell: column=memory_pct type=gauge max=100 unit=%
-- @cell: column=memory_pct type=rag green<60 amber<85
-- @cell: column=cpu_pct type=gauge max=100 unit=%
-- @cell: column=disk_delta type=sparkline ref=0
-- @cell: type=radar radar_column=shape axes=time:duration_ms,memory:memory_bytes ranges=time:100..60000,memory:32Mi..8Gi
Grafana export support
Grafana panel export understands radar directives with partial support:
@cell type=radarexports as a compact generated SVG image column in Grafana tables.@chart type=radaris reported as partial until TraceHouse has a dedicated Grafana radar panel export path.
Template placeholders
{{time_range}}
Replaced at execution time with a ClickHouse time expression based on the user-selected time range or the @meta interval default.
WHERE event_time > {{time_range}}
When using the time picker's custom range, {{time_range}} resolves to a toDateTime('...') expression with both start and end bounds injected automatically.
{{drill:column | fallback}}
Replaced with a filter condition when the query is reached via drill-down, or with the fallback expression otherwise.
WHERE {{drill:database | 1=1}}
- When drilled into with
database = 'nyc_taxi'→WHERE database = 'nyc_taxi' - When opened directly →
WHERE 1=1
{{drill_value:column | fallback}}
Like {{drill:column | fallback}} but resolves to just the quoted value instead of a full equality condition. Useful when the drill parameter needs a custom expression (e.g. computed column matching):
WHERE lower(hex(normalized_query_hash)) = {{drill_value:query_hash | ''}}
- When linked/drilled with
query_hash = '52794d32e666dd45'→WHERE lower(hex(normalized_query_hash)) = '52794d32e666dd45' - When opened directly →
WHERE lower(hex(normalized_query_hash)) = ''
{{cluster_aware:db.table}}
On a single-node setup, resolves to the bare table reference db.table. On a cluster, wraps it with clusterAllReplicas('cluster_name', db.table) so that per-node tables like system.query_log are fanned out to all replicas. Queries using this should deduplicate with GROUP BY since replicas may return overlapping rows.
SELECT query, count()
FROM {{cluster_aware:system.query_log}}
WHERE event_time > {{time_range}}
GROUP BY query
{{cluster_name}}
Resolves to the quoted cluster name on a cluster, or an empty string on single-node. Useful for conditional logic or display.
Source attribution
A -- Source: comment (not a directive) adds an attribution link to the query card, typically pointing to ClickHouse docs or knowledge base articles.
-- Source: https://clickhouse.com/docs/operations/system-tables/parts
Full example
-- @meta: title='Database Sizes' group='Overview' description='Total disk usage per database'
-- @chart: type=pie group_by=database value=total_bytes style=3d
-- @drill: on=database into='Table Sizes'
-- @cell: column=total_bytes type=rag green<1000000000 amber<10000000000
SELECT
database,
sum(bytes_on_disk) AS total_bytes
FROM system.parts
WHERE active
GROUP BY database
ORDER BY total_bytes DESC
This query appears in the Overview group, renders as a 3D pie chart, clicking a slice drills into the "Table Sizes" query filtered to that database, and in table view the total_bytes column is color-coded green/amber/red based on size.