[笔记]玩一玩在 ClickHouse 里面查询 Jaeger 的 tracing 数据

作者:张富春(ahfuzhang)，转载时请注明作者和引用链接，谢谢！

• cnblogs博客
• zhihu
• Github
• 公众号:一本正经的瞎扯
  

---

1. 连接服务

• 使用 k9s 连接到 k8s 群集
• 找到 clickhouse 服务，按 shift + f 进行 port forward
• 然后可以通过 127.0.0.1:9000 进行访问
• 使用 docker 中的客户端来连接：

 docker run -it --rm clickhouse/clickhouse-server clickhouse-client -h host.docker.internal --port 9000

2. 表结构

• show databases
  • 查看数据库。Jaeger 的数据放在了 default 数据库
  • use default
• show tables
  • 查看表
• desc jaeger_index
  • 查看 traceID 相关的表
  • 字段含义如下：
    • timestamp 时间戳
    • traceID
    • service 服务名
    • operation 调用的接口
    • tags.key / tags.value, 字段名和字段的值

3. 常见查询

查询某个服务调用了哪些服务

select tags.value[indexOf(tags.key, 'peer.service')] as callee, count(1) as times
from jaeger_index
where timestamp>='2025-09-22 00:00:00' and timestamp<'2025-09-23 00:00:00'
  and service='my_caller_service'
  and has(tags.key, 'peer.service')
group by tags.value[indexOf(tags.key, 'peer.service')]
order by count(1) desc

查询主调和被调两个服务之间都有哪些接口

select operation, count(1) as times, floor(avg(durationUs)) as latency_us,max(durationUs),min(durationUs)
from jaeger_index
where timestamp>='2025-09-22 00:00:00' and timestamp<'2025-09-23 00:00:00'
  and service='my_caller_service'
  and has(tags.key, 'peer.service')
  and tags.value[indexOf(tags.key, 'peer.service')]='callee_service'
group by operation
order by floor(avg(durationUs)) desc

查询某条接口，在每分钟的延迟分布

select formatDateTime(timestamp,'%Y-%m-%d %H:%i') as t, count(1) as times, floor(avg(durationUs)) as latency_us
from jaeger_index
where timestamp>='2025-09-22 00:00:00' and timestamp<'2025-09-23 00:00:00'
  and service='my_caller_service'
  and has(tags.key, 'peer.service')
  and tags.value[indexOf(tags.key, 'peer.service')]='callee_service'
  and operation='Interface_name'
group by formatDateTime(timestamp,'%Y-%m-%d %H:%i')
order by formatDateTime(timestamp,'%Y-%m-%d %H:%i') asc

查询每个 span

# jaeger_index 表中的数据，与 jaeger_spans 中的数据是一一对应的
select *
from jaeger_index
where timestamp>='2025-09-22 00:00:00' and timestamp<'2025-09-23 00:00:00'
  and traceID='c3372673c468991d72c00f049795cb99';

# operation_name 与 jaeger_index 表中的 operation 对应
select JSONExtractString(model, 'span_id') as span_id,JSONExtractString(model, 'operation_name') as operation_name from jaeger_spans 
where timestamp='2025-09-22 09:49:42' and traceID='c3372673c468991d72c00f049795cb99';