Agent Skill · SigNoz

signoz-writing-clickhouse-queries

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.

View SKILL.md on GitHub → Source repository Provider profile

Provider: SigNoz Path in repo: plugins/signoz/skills/signoz-writing-clickhouse-queries/SKILL.md

Skill body

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

Logs: read references/clickhouse-logs-reference.md before writing any query.
Traces: read references/clickhouse-traces-reference.md before writing any query.

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.
Using plain IN instead of GLOBAL IN on the resource fingerprint subquery.
Adding a resource CTE when there is no resource attribute filter.
Logs query with $start_datetime or $end_datetime.
Traces query with $start_timestamp_nano or $end_timestamp_nano.
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.