kugarocks

English

简体中文

English

简体中文

Appearance

ClickHouse Schema ​

otel_traces ​

sql
CREATE TABLE default.otel_traces
(
    `Timestamp` DateTime64(9) CODEC(Delta(8), ZSTD(1)),
    `TraceId` String CODEC(ZSTD(1)),
    `SpanId` String CODEC(ZSTD(1)),
    `ParentSpanId` String CODEC(ZSTD(1)),
    `TraceState` String CODEC(ZSTD(1)),
    `SpanName` LowCardinality(String) CODEC(ZSTD(1)),
    `SpanKind` LowCardinality(String) CODEC(ZSTD(1)),
    `ServiceName` LowCardinality(String) CODEC(ZSTD(1)),
    `ResourceAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    `ScopeName` String CODEC(ZSTD(1)),
    `ScopeVersion` String CODEC(ZSTD(1)),
    `SpanAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    `Duration` UInt64 CODEC(ZSTD(1)),
    `StatusCode` LowCardinality(String) CODEC(ZSTD(1)),
    `StatusMessage` String CODEC(ZSTD(1)),
    `Events.Timestamp` Array(DateTime64(9)) CODEC(ZSTD(1)),
    `Events.Name` Array(LowCardinality(String)) CODEC(ZSTD(1)),
    `Events.Attributes` Array(Map(LowCardinality(String), String)) CODEC(ZSTD(1)),
    `Links.TraceId` Array(String) CODEC(ZSTD(1)),
    `Links.SpanId` Array(String) CODEC(ZSTD(1)),
    `Links.TraceState` Array(String) CODEC(ZSTD(1)),
    `Links.Attributes` Array(Map(LowCardinality(String), String)) CODEC(ZSTD(1)),
    INDEX idx_trace_id TraceId TYPE bloom_filter(0.001) GRANULARITY 1,
    INDEX idx_res_attr_key mapKeys(ResourceAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_res_attr_value mapValues(ResourceAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_span_attr_key mapKeys(SpanAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_span_attr_value mapValues(SpanAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_duration Duration TYPE minmax GRANULARITY 1
)
ENGINE = MergeTree
PARTITION BY toDate(Timestamp)
ORDER BY (ServiceName, SpanName, toDateTime(Timestamp))
TTL toDate(Timestamp) + toIntervalDay(1)
SETTINGS index_granularity = 8192, ttl_only_drop_parts = 1;

otel_traces_trace_id_ts ​

  • Store the start and end time of each TraceId.
  • ts is for Timestamp.
sql
CREATE TABLE default.otel_traces_trace_id_ts
(
    `TraceId` String CODEC(ZSTD(1)),
    `Start` DateTime CODEC(Delta(4), ZSTD(1)),
    `End` DateTime CODEC(Delta(4), ZSTD(1)),
    INDEX idx_trace_id TraceId TYPE bloom_filter(0.01) GRANULARITY 1
)
ENGINE = MergeTree
PARTITION BY toDate(Start)
ORDER BY (TraceId, Start)
TTL toDate(Start) + toIntervalDay(1)
SETTINGS index_granularity = 8192, ttl_only_drop_parts = 1;

otel_traces_trace_id_ts_mv ​

  • mv is for Materialized View.
sql
CREATE MATERIALIZED VIEW default.otel_traces_trace_id_ts_mv
TO default.otel_traces_trace_id_ts
(
    `TraceId` String,
    `Start` DateTime64(9),
    `End` DateTime64(9)
) AS
SELECT
    TraceId,
    min(Timestamp) AS Start,
    max(Timestamp) AS End
FROM default.otel_traces
WHERE TraceId != ''
GROUP BY TraceId;

otel_traces_tags ​

sql
CREATE TABLE default.otel_traces_tags
(
    `Timestamp` DateTime CODEC(Delta(4), ZSTD(1)),
    `TagKey` LowCardinality(String) CODEC(ZSTD(1)),
    `TagVal` String CODEC(ZSTD(1)),
    `Count` UInt64 CODEC(ZSTD(1)),
    INDEX idx_tag_key TagKey TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_tag_val TagVal TYPE bloom_filter(0.01) GRANULARITY 1
)
ENGINE = SummingMergeTree()
PARTITION BY toDate(Timestamp)
ORDER BY (Timestamp, TagKey, TagVal)
TTL toDate(Timestamp) + toIntervalDay(7)
SETTINGS index_granularity = 8192, ttl_only_drop_parts = 1;

otel_traces_tags_mv ​

  • Store field value occurrence statistics aggregated by minute.
  • Support flexible field addition and handle missing fields gracefully.
sql
CREATE MATERIALIZED VIEW default.otel_traces_tags_mv
TO default.otel_traces_tags
(
    Timestamp DateTime,
    TagKey LowCardinality(String),
    TagVal String,
    Count UInt64
) AS
SELECT
    toStartOfMinute(Timestamp) AS Timestamp,
    tag.1 AS TagKey,
    tag.2 AS TagVal,
    count() AS Count
FROM default.otel_traces
ARRAY JOIN [
    ('span.status.code', StatusCode),
    ('span.service.name', ServiceName),
    ('span.attributes.http.method', SpanAttributes['http.method']),
    ('span.attributes.http.status_code', SpanAttributes['http.status_code']),
    ('span.attributes.http.url', SpanAttributes['http.url'])
] AS tag
WHERE tag.2 IS NOT NULL AND tag.2 != ''
GROUP BY
    Timestamp,
    TagKey,
    TagVal

Insert Data ​

sql
INSERT INTO default.otel_traces (
    Timestamp,
    TraceId,
    SpanId,
    ParentSpanId,
    TraceState,
    SpanName,
    SpanKind,
    ServiceName,
    ResourceAttributes,
    ScopeName,
    ScopeVersion,
    SpanAttributes,
    Duration,
    StatusCode,
    StatusMessage,
    `Events.Timestamp`,
    `Events.Name`,
    `Events.Attributes`,
    `Links.TraceId`,
    `Links.SpanId`,
    `Links.TraceState`,
    `Links.Attributes`
) VALUES (
    now64(),                                -- Timestamp
    'trace-abc',                            -- TraceId
    'span-abc',                             -- SpanId
    '',                                     -- ParentSpanId
    '',                                     -- TraceState
    'getUser',                              -- SpanName
    'SERVER',                               -- SpanKind
    'user-service',                         -- ServiceName
    map(),                                  -- ResourceAttributes
    'otel.scope',                           -- ScopeName
    '1.0.0',                                -- ScopeVersion
    map('http.method', 'GET', 'http.status_code', '200'), -- SpanAttributes
    123456,                                 -- Duration
    'OK',                                   -- StatusCode
    '',                                     -- StatusMessage
    [], [], [], [], [], [], []              -- Events & Links fields
);

Test Queries ​

Basic Statistics ​

View all field statistics for the last hour
sql
SELECT 
    TagKey,
    TagVal,
    sum(Count) as TotalCount
FROM default.otel_traces_tags
WHERE Timestamp >= now() - INTERVAL 1 HOUR
GROUP BY TagKey, TagVal
ORDER BY TagKey, TotalCount DESC;

Top Values by Field ​

Get top HTTP status codes
sql
SELECT 
    TagVal as HttpStatusCode,
    sum(Count) as TotalCount,
    round(TotalCount * 100.0 / (
        SELECT sum(Count) 
        FROM default.otel_traces_tags 
        WHERE TagKey = 'span.attributes.http.status_code' 
          AND Timestamp >= now() - INTERVAL 1 HOUR
    ), 2) as Percentage
FROM default.otel_traces_tags
WHERE Timestamp >= now() - INTERVAL 1 HOUR
  AND TagKey = 'span.attributes.http.status_code'
GROUP BY TagVal
ORDER BY TotalCount DESC;

Error Rate Analysis ​

Calculate error rate by service
sql
SELECT 
    s.TagVal as ServiceName,
    countIf(st.TagVal = 'Error') as ErrorCount,
    sum(st.Count) as TotalCount,
    round(ErrorCount * 100.0 / TotalCount, 2) as ErrorRatePercent
FROM default.otel_traces_tags s
JOIN default.otel_traces_tags st 
    ON s.Timestamp = st.Timestamp
WHERE s.Timestamp >= now() - INTERVAL 1 HOUR
  AND s.TagKey = 'span.service.name'
  AND st.TagKey = 'span.status.code'
GROUP BY s.TagVal
HAVING TotalCount > 0
ORDER BY ErrorRatePercent DESC;
HTTP status code trends over time (5-minute intervals)
sql
SELECT 
    toStartOfInterval(Timestamp, INTERVAL 5 MINUTE) as TimeBucket,
    TagVal as StatusCode,
    sum(Count) as RequestCount
FROM default.otel_traces_tags
WHERE Timestamp >= now() - INTERVAL 6 HOUR
  AND TagKey = 'span.attributes.http.status_code'
GROUP BY TimeBucket, TagVal
ORDER BY TimeBucket DESC, RequestCount DESC;

Service Performance Overview ​

Service performance summary
sql
SELECT 
    service.TagVal as ServiceName,
    sum(CASE WHEN status.TagVal = 'Error' THEN status.Count ELSE 0 END) as ErrorCount,
    sum(status.Count) as TotalRequests,
    round(ErrorCount * 100.0 / TotalRequests, 2) as ErrorRate
FROM default.otel_traces_tags service
LEFT JOIN default.otel_traces_tags status 
    ON service.Timestamp = status.Timestamp 
    AND status.TagKey = 'span.status.code'
WHERE service.Timestamp >= now() - INTERVAL 1 HOUR
  AND service.TagKey = 'span.service.name'
GROUP BY service.TagVal
ORDER BY TotalRequests DESC;

Field Coverage Analysis ​

Check which fields are most commonly populated
sql
SELECT 
    TagKey,
    count(DISTINCT TagVal) as UniqueValues,
    sum(Count) as TotalOccurrences,
    round(avg(Count), 2) as AvgPerValue
FROM default.otel_traces_tags
WHERE Timestamp >= now() - INTERVAL 1 DAY
GROUP BY TagKey
ORDER BY TotalOccurrences DESC;

Teletrace Queries ​

Get All Tags ​

sql
SELECT DISTINCT key
FROM (
    SELECT 'SpanAttributes.' || arrayJoin(mapKeys(SpanAttributes)) AS key
    FROM default.otel_traces
    WHERE Timestamp >= 1748401258 AND Timestamp <= 1748487748

    UNION ALL

    SELECT 'ResourceAttributes.' || arrayJoin(mapKeys(ResourceAttributes)) AS key
    FROM default.otel_traces
    WHERE Timestamp >= 1748401258 AND Timestamp <= 1748487748
)

Get Predefined Tags ​

sql
SELECT
    TagVal,
    sum(Count) AS TotalCount
FROM default.otel_traces_tags
WHERE
    TagKey = 'span.service.name'
    AND Timestamp >= 1748422457
    AND Timestamp <= 1748508857
GROUP BY TagVal
ORDER BY TotalCount DESC;

API Test ​

Get Tags ​

bash
curl 'http://localhost:3000/api/v1/trace/tags' \
  -X POST \
  -H 'Accept: application/json, text/plain, */*' \
  -H 'Accept-Language: en-US,en;q=0.9' \
  -H 'Connection: keep-alive' \
  -H 'Content-Type: application/json' \
  -b 'JSESSIONID=6e2c5f33a8b6cb36409435edfecc9382; EnvironmentID=1' \
  -H 'Referer: http://localhost:3000/' \
  -H 'Sec-Fetch-Dest: empty' \
  -H 'Sec-Fetch-Mode: cors' \
  -H 'Sec-Fetch-Site: same-origin' \
  -H 'User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/136.0.0.0 Safari/537.36' \
  -H 'sec-ch-ua: "Chromium";v="136", "Google Chrome";v="136", "Not.A/Brand";v="99"' \
  -H 'sec-ch-ua-mobile: ?0' \
  -H 'sec-ch-ua-platform: "macOS"' \
  -d '{
    "envId": 1,
    "timeFrame": {
      "startTime": 1748701109,
      "endTime": 1748787509
    }
  }'

Get Tag Values ​

bash
curl 'http://localhost:3000/api/v1/trace/tags/values' \
  -X POST \
  -H 'Accept: application/json, text/plain, */*' \
  -H 'Accept-Language: en-US,en;q=0.9' \
  -H 'Connection: keep-alive' \
  -H 'Content-Type: application/json' \
  -b 'JSESSIONID=6e2c5f33a8b6cb36409435edfecc9382; EnvironmentID=1' \
  -H 'Referer: http://localhost:3000/' \
  -H 'Sec-Fetch-Dest: empty' \
  -H 'Sec-Fetch-Mode: cors' \
  -H 'Sec-Fetch-Site: same-origin' \
  -H 'User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/136.0.0.0 Safari/537.36' \
  -H 'sec-ch-ua: "Chromium";v="136", "Google Chrome";v="136", "Not.A/Brand";v="99"' \
  -H 'sec-ch-ua-mobile: ?0' \
  -H 'sec-ch-ua-platform: "macOS"' \
  -d '{
    "envId": 1,
    "tagName": "span.service.name",
    "timeFrame": {
      "startTime": 1748701109,
      "endtime": 1748787509
    }
  }'

Get Spans ​

Time Frame Only
bash
curl 'http://localhost:3000/api/v1/trace/spans' \
  -X POST \
  -H 'Accept: application/json, text/plain, */*' \
  -H 'Accept-Language: en-US,en;q=0.9' \
  -H 'Connection: keep-alive' \
  -H 'Content-Type: application/json' \
  -b 'JSESSIONID=6e2c5f33a8b6cb36409435edfecc9382; EnvironmentID=1' \
  -H 'Referer: http://localhost:3000/' \
  -H 'Sec-Fetch-Dest: empty' \
  -H 'Sec-Fetch-Mode: cors' \
  -H 'Sec-Fetch-Site: same-origin' \
  -H 'User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/136.0.0.0 Safari/537.36' \
  -H 'sec-ch-ua: "Chromium";v="136", "Google Chrome";v="136", "Not.A/Brand";v="99"' \
  -H 'sec-ch-ua-mobile: ?0' \
  -H 'sec-ch-ua-platform: "macOS"' \
  -d '{
    "envId": 1,
    "timeFrame": {
      "startTime": 1748701109,
      "endtime": 1748787509
    }
  }'
Sort by ASC
bash
curl 'http://localhost:3000/api/v1/trace/spans/search' \
  -X POST \
  -H 'Accept: application/json, text/plain, */*' \
  -H 'Accept-Language: en-US,en;q=0.9' \
  -H 'Connection: keep-alive' \
  -H 'Content-Type: application/json' \
  -b 'JSESSIONID=6e2c5f33a8b6cb36409435edfecc9382; EnvironmentID=1' \
  -H 'Referer: http://localhost:3000/' \
  -H 'Sec-Fetch-Dest: empty' \
  -H 'Sec-Fetch-Mode: cors' \
  -H 'Sec-Fetch-Site: same-origin' \
  -H 'User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/136.0.0.0 Safari/537.36' \
  -H 'sec-ch-ua: "Chromium";v="136", "Google Chrome";v="136", "Not.A/Brand";v="99"' \
  -H 'sec-ch-ua-mobile: ?0' \
  -H 'sec-ch-ua-platform: "macOS"' \
  -d '{
    "envId": 1,
    "timeFrame": {
      "startTime": 1748701109,
      "endtime": 1748787509
    },
    "sort": {
      "field": "timestamp",
      "order": "asc"
    }
  }'
Specify Service Name
bash
curl 'http://localhost:3000/api/v1/trace/spans/search' \
  -X POST \
  -H 'Accept: application/json, text/plain, */*' \
  -H 'Accept-Language: en-US,en;q=0.9' \
  -H 'Connection: keep-alive' \
  -H 'Content-Type: application/json' \
  -b 'JSESSIONID=6e2c5f33a8b6cb36409435edfecc9382; EnvironmentID=1' \
  -H 'Referer: http://localhost:3000/' \
  -H 'Sec-Fetch-Dest: empty' \
  -H 'Sec-Fetch-Mode: cors' \
  -H 'Sec-Fetch-Site: same-origin' \
  -H 'User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/136.0.0.0 Safari/537.36' \
  -H 'sec-ch-ua: "Chromium";v="136", "Google Chrome";v="136", "Not.A/Brand";v="99"' \
  -H 'sec-ch-ua-mobile: ?0' \
  -H 'sec-ch-ua-platform: "macOS"' \
  -d '{
    "envId": 1,
    "timeFrame": {
      "startTime": 1748701109,
      "endtime": 1748787509
    },
    "searchFilters": [
      {
        "keyValueFilter": {
          "key": "span.service.name",
          "operator": "=",
          "value": "user-service"
        }
      }
    ]
  }'

Get Trace Spans ​

bash
curl 'http://localhost:3000/api/v1/1/trace/spans/dcaba06623f298e6e0a55ad021cf4f65' \
  -H 'Accept: application/json, text/plain, */*' \
  -H 'Accept-Language: en-US,en;q=0.9' \
  -H 'Connection: keep-alive' \
  -H 'Content-Type: application/json' \
  -b 'JSESSIONID=67ecf6fa511a81420831d753b2cff948; EnvironmentID=1' \
  -H 'Referer: http://localhost:3000/' \
  -H 'Sec-Fetch-Dest: empty' \
  -H 'Sec-Fetch-Mode: cors' \
  -H 'Sec-Fetch-Site: same-origin' \
  -H 'User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/136.0.0.0 Safari/537.36' \
  -H 'sec-ch-ua: "Chromium";v="136", "Google Chrome";v="136", "Not.A/Brand";v="99"' \
  -H 'sec-ch-ua-mobile: ?0' \
  -H 'sec-ch-ua-platform: "macOS"'

Get Traces ​

bash
curl 'http://localhost:3000/api/v1/trace/traces' \
  -X POST \
  -H 'Accept: application/json, text/plain, */*' \
  -H 'Accept-Language: en-US,en;q=0.9' \
  -H 'Connection: keep-alive' \
  -H 'Content-Type: application/json' \
  -b 'EnvironmentID=1; JSESSIONID=0e109f32f9ec577c26d9894067e7d8a4' \
  -H 'Referer: http://localhost:3000/' \
  -H 'Sec-Fetch-Dest: empty' \
  -H 'Sec-Fetch-Mode: cors' \
  -H 'Sec-Fetch-Site: same-origin' \
  -H 'User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/136.0.0.0 Safari/537.36' \
  -H 'sec-ch-ua: "Chromium";v="136", "Google Chrome";v="136", "Not.A/Brand";v="99"' \
  -H 'sec-ch-ua-mobile: ?0' \
  -H 'sec-ch-ua-platform: "macOS"' \
  -d '{
    "envId": 1,
    "timeFrame": {
      "startTime": 1749723135,
      "endtime": 1749726735
    }
  }'