Writing ClickHouse Queries for SigNoz Dashboards
When to Use
Use this skill when the user asks for SigNoz queries involving:
- Logs: severity, body text, log volume, structured fields, containers,
services, or environments.
- Traces: spans, latency, duration, p95 or p99, HTTP operations, DB
operations, or error spans.
- Dashboard panels: timeseries charts, value widgets, and table breakdowns.
If the user asks for a dashboard panel but does not mention ClickHouse, still
use this skill.
Signal Detection
Identify whether the request is about logs or traces.
- Logs: log lines, severity, body text, log volume, container logs, or
structured log fields.
- Traces: spans, latency, duration, p99, trace analysis, HTTP operations, DB
operations, or error spans.
If the request is ambiguous, ask the user to clarify.
Reference Routing
Each reference covers table schemas, optimization patterns, attribute access
syntax, dashboard templates, query examples, and a validation checklist.
Quick Reference
- Timeseries panel: return rows of
(ts, value) for a chart over time.
- Value panel: return a single
value for a stat or counter widget.
- Table panel: return labelled columns for a grouped breakdown.
Key Variables by Signal
Logs
- Timestamp type:
UInt64 in nanoseconds.
- Time filter:
$start_timestamp_nano and $end_timestamp_nano.
- Bucket filter:
$start_timestamp and $end_timestamp.
- Display conversion:
fromUnixTimestamp64Nano(timestamp).
- Main table:
signoz_logs.distributed_logs_v2.
- Resource table:
signoz_logs.distributed_logs_v2_resource.
Traces
- Timestamp type:
DateTime64(9).
- Time filter:
$start_datetime and $end_datetime.
- Bucket filter:
$start_timestamp and $end_timestamp.
- Display conversion: use the timestamp directly.
- Main table:
signoz_traces.distributed_signoz_index_v3.
- Resource table:
signoz_traces.distributed_traces_v3_resource.
Top Anti-Patterns
- Missing
ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp.
- Plain
IN / JOIN whose subquery reads a distributed table: with
distributed_product_mode='deny' it fails. Prefer the time-bounded fingerprint
GLOBAL IN pattern or a local subquery table. Use GLOBAL JOIN only for a
demonstrably small, bounded RHS; it broadcasts that dataset to every shard.
- Adding a resource CTE when there is no resource attribute filter.
- Omitting a non-aggregated projection from
GROUP BY, including computed
projections such as JSONExtractString(body, ...).
- Logs query with
$start_datetime or $end_datetime.
- Traces query with
$start_timestamp_nano or $end_timestamp_nano.
- Logs query against
signoz_logs.logs, bare logs, or distributed_logs;
always use signoz_logs.distributed_logs_v2.
- Traces query with
resources_string['service.name'] instead of
resource_string_service$$name.
Query Attribution
Every generated query MUST end with a SETTINGS clause for monitoring:
SELECT ...
FROM ...
WHERE ...
SETTINGS log_comment = 'signoz-writing-clickhouse-queries skill | YYYY-MM-DD'
Replace YYYY-MM-DD with today's date (e.g., 2026-04-03). If the query
already has a SETTINGS clause, append log_comment to it with a comma.
Workflow
- Detect the signal: logs or traces.
- Read the matching reference file before writing the query.
- Pick the panel type: timeseries, value, or table.
- Build the query using the required patterns from the reference.
- Append the
SETTINGS log_comment attribution clause.
- Validate the result with the checklist in the reference.
1---2name: signoz-writing-clickhouse-queries3description: Write raw ClickHouse SQL for a SigNoz dashboard panel: timeseries, value, or table widgets that the builder UI cannot express (custom joins, window functions, regex extraction over log bodies, aggregations beyond builder syntax). Trigger when the user explicitly asks for a "ClickHouse query", a "raw SQL panel", a "custom SQL widget", or describes a SigNoz dashboard panel whose query needs SQL the builder cannot produce. Anchored to dashboard-panel SQL specifically. For ad-hoc data exploration that does not need to land in a panel, use `signoz-generating-queries` instead.4---56# Writing ClickHouse Queries for SigNoz Dashboards78## When to Use910Use this skill when the user asks for SigNoz queries involving:1112- Logs: severity, body text, log volume, structured fields, containers,13 services, or environments.14- Traces: spans, latency, duration, p95 or p99, HTTP operations, DB15 operations, or error spans.16- Dashboard panels: timeseries charts, value widgets, and table breakdowns.1718If the user asks for a dashboard panel but does not mention ClickHouse, still19use this skill.2021## Signal Detection2223Identify whether the request is about logs or traces.2425- Logs: log lines, severity, body text, log volume, container logs, or26 structured log fields.27- Traces: spans, latency, duration, p99, trace analysis, HTTP operations, DB28 operations, or error spans.2930If the request is ambiguous, ask the user to clarify.3132## Reference Routing3334- Logs: read35 [`references/clickhouse-logs-reference.md`](./references/clickhouse-logs-reference.md)36 before writing any query.37- Traces: read38 [`references/clickhouse-traces-reference.md`](./references/clickhouse-traces-reference.md)39 before writing any query.4041Each reference covers table schemas, optimization patterns, attribute access42syntax, dashboard templates, query examples, and a validation checklist.4344## Quick Reference4546- Timeseries panel: return rows of `(ts, value)` for a chart over time.47- Value panel: return a single `value` for a stat or counter widget.48- Table panel: return labelled columns for a grouped breakdown.4950## Key Variables by Signal5152### Logs5354- Timestamp type: `UInt64` in nanoseconds.55- Time filter: `$start_timestamp_nano` and `$end_timestamp_nano`.56- Bucket filter: `$start_timestamp` and `$end_timestamp`.57- Display conversion: `fromUnixTimestamp64Nano(timestamp)`.58- Main table: `signoz_logs.distributed_logs_v2`.59- Resource table: `signoz_logs.distributed_logs_v2_resource`.6061### Traces6263- Timestamp type: `DateTime64(9)`.64- Time filter: `$start_datetime` and `$end_datetime`.65- Bucket filter: `$start_timestamp` and `$end_timestamp`.66- Display conversion: use the timestamp directly.67- Main table: `signoz_traces.distributed_signoz_index_v3`.68- Resource table: `signoz_traces.distributed_traces_v3_resource`.6970## Top Anti-Patterns7172- Missing `ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp`.73- Plain `IN` / `JOIN` whose subquery reads a distributed table: with74 `distributed_product_mode='deny'` it fails. Prefer the time-bounded fingerprint75 `GLOBAL IN` pattern or a local subquery table. Use `GLOBAL JOIN` only for a76 demonstrably small, bounded RHS; it broadcasts that dataset to every shard.77- Adding a resource CTE when there is no resource attribute filter.78- Omitting a non-aggregated projection from `GROUP BY`, including computed79 projections such as `JSONExtractString(body, ...)`.80- Logs query with `$start_datetime` or `$end_datetime`.81- Traces query with `$start_timestamp_nano` or `$end_timestamp_nano`.82- Logs query against `signoz_logs.logs`, bare `logs`, or `distributed_logs`;83 always use `signoz_logs.distributed_logs_v2`.84- Traces query with `resources_string['service.name']` instead of85 `resource_string_service$$name`.8687## Query Attribution8889Every generated query MUST end with a `SETTINGS` clause for monitoring:9091```sql92SELECT ...93FROM ...94WHERE ...95SETTINGS log_comment = 'signoz-writing-clickhouse-queries skill | YYYY-MM-DD'96```9798Replace `YYYY-MM-DD` with today's date (e.g., `2026-04-03`). If the query99already has a `SETTINGS` clause, append `log_comment` to it with a comma.100101## Workflow1021031. Detect the signal: logs or traces.1042. Read the matching reference file before writing the query.1053. Pick the panel type: timeseries, value, or table.1064. Build the query using the required patterns from the reference.1075. Append the `SETTINGS log_comment` attribution clause.1086. Validate the result with the checklist in the reference.
SigNoz