| name | signoz-writing-clickhouse-queries |
| description | 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. |
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.
