signoz-writing-clickhouse-queries

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Writing ClickHouse Queries for SigNoz Dashboards

为SigNoz仪表盘编写ClickHouse查询

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.

当用户需要以下类型的SigNoz查询时，使用本技能：

日志：日志级别、正文内容、日志量、结构化字段、容器、服务或环境相关查询。
追踪：链路跨度（spans）、延迟、时长、p95或p99分位值、HTTP操作、数据库操作或错误链路跨度相关查询。
仪表盘组件：时序图表、数值组件和表格细分组件。

若用户要求创建仪表盘组件但未提及ClickHouse，仍可使用本技能。

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.

判断请求是关于日志还是追踪：

日志相关关键词：日志行、日志级别、正文内容、日志量、容器日志或结构化日志字段。
追踪相关关键词：链路跨度（spans）、延迟、时长、p99分位值、追踪分析、HTTP操作、数据库操作或错误链路跨度。

若请求模糊，请要求用户明确说明。

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.

日志查询：编写任何查询前，请阅读
```
references/clickhouse-logs-reference.md
```
。
追踪查询：编写任何查询前，请阅读
```
references/clickhouse-traces-reference.md
```
。

每份参考文档涵盖表结构、优化模式、属性访问语法、仪表盘模板、查询示例及验证清单。

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.

时序面板：返回
```
(ts, value)
```
格式的行数据，用于生成时间趋势图表。
数值面板：返回单个
```
value
```
值，用于统计或计数器组件。
表格面板：返回带标签的列数据，用于分组细分展示。

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

时间戳类型：
```
UInt64
```
（纳秒级）

时间过滤器：

$start_timestamp_nano

和

$end_timestamp_nano

时间桶过滤器：
```
$start_timestamp
```
和
```
$end_timestamp
```
显示转换：
```
fromUnixTimestamp64Nano(timestamp)
```
主表：
```
signoz_logs.distributed_logs_v2
```

资源表：

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

时间戳类型：
```
DateTime64(9)
```
时间过滤器：
```
$start_datetime
```
和
```
$end_datetime
```
时间桶过滤器：
```
$start_timestamp
```
和
```
$end_timestamp
```
显示转换：直接使用时间戳即可

主表：

signoz_traces.distributed_signoz_index_v3

资源表：

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

$end_timestamp_nano

Traces query with

resources_string['service.name']

instead of

resource_string_service$$name

遗漏

ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp

条件。

在资源指纹子查询中使用普通
```
IN
```
而非
```
GLOBAL IN
```
。
无资源属性过滤器时仍添加资源CTE。
日志查询中使用
```
$start_datetime
```
或
```
$end_datetime
```
。

追踪查询中使用

$start_timestamp_nano

或

$end_timestamp_nano

。

追踪查询中使用

resources_string['service.name']

而非

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.

生成的每个查询必须以

SETTINGS

子句结尾，用于监控：

sql

SELECT ...
FROM ...
WHERE ...
SETTINGS log_comment = 'signoz-writing-clickhouse-queries skill | YYYY-MM-DD'

将

YYYY-MM-DD

替换为当前日期（例如：

2026-04-03

）。若查询已包含

SETTINGS

子句，需用逗号追加

log_comment

参数。

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.

识别信号类型：日志或追踪。
编写查询前阅读对应的参考文档。
选择面板类型：时序图、数值或表格。
使用参考文档中的要求模式构建查询。
追加
```
SETTINGS log_comment
```
归属子句。
使用参考文档中的验证清单验证结果。