---
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

- Logs: read
  [`references/clickhouse-logs-reference.md`](./references/clickhouse-logs-reference.md)
  before writing any query.
- Traces: read
  [`references/clickhouse-traces-reference.md`](./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`.
- 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:

```sql
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

1. Detect the signal: logs or traces.
2. Read the matching reference file before writing the query.
3. Pick the panel type: timeseries, value, or table.
4. Build the query using the required patterns from the reference.
5. Append the `SETTINGS log_comment` attribution clause.
6. Validate the result with the checklist in the reference.