跳至正文
OpenAgentSkill
浏览已开放 · 目录记录日期 2026-11-21 · 自动更新和账户服务暂时关闭。
数据与分析

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.

SigNoz · 已有 AI 审核记录

安装与使用

运行命令前先阅读来源与依赖说明。复制命令不会自动安装或执行。

npx skills add SigNoz/agent-skills --skill signoz-writing-clickhouse-queries

技能说明


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

  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.