Traces Schema

CREATE TABLE otel_logsdefault.otel_traces
(
    `Timestamp` DateTime64(9) CODEC(Delta(8), ZSTD(1)),
    `TimestampTime` DateTime DEFAULT toDateTime(Timestamp),
    `TraceId` String CODEC(ZSTD(1)),
    `SpanId` String CODEC(ZSTD(1)),
    `TraceFlags`ParentSpanId` UInt8,String CODEC(ZSTD(1)),
    `SeverityText`TraceState` String CODEC(ZSTD(1)),
    `SpanName` LowCardinality(String) CODEC(ZSTD(1)),
    `SeverityNumber` UInt8,
    `ServiceName`SpanKind` LowCardinality(String) CODEC(ZSTD(1)),
    `Body` String CODEC(ZSTD(1)),
    `ResourceSchemaUrl`ServiceName` LowCardinality(String) CODEC(ZSTD(1)),
    `ResourceAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    `ScopeSchemaUrl` LowCardinality(String) CODEC(ZSTD(1)),
    `ScopeName` String CODEC(ZSTD(1)),
    `ScopeVersion` LowCardinality(String)String CODEC(ZSTD(1)),
    `ScopeAttributes`SpanAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    `LogAttributes`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_scope_attr_keyidx_span_attr_key mapKeys(ScopeAttributes)SpanAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_scope_attr_valueidx_span_attr_value mapValues(ScopeAttributes)SpanAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_log_attr_keyidx_duration mapKeys(LogAttributes)Duration TYPE bloom_filter(0.01)minmax GRANULARITY 1,
    INDEX idx_log_attr_value mapValues(LogAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
    INDEX idx_body Body TYPE tokenbf_v1(32768, 3, 0) GRANULARITY 81
)
ENGINE = MergeTree
PARTITION BY toDate(TimestampTime)
PRIMARY KEY (ServiceName, TimestampTime)Timestamp)
ORDER BY (ServiceName, TimestampTime, Timestamp)
TTLSpanName, toDateTime(Timestamp) + toIntervalDay(7)
SETTINGS index_granularity = 8192, ttl_only_drop_parts = 1;

CREATE TABLE otel_logs_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;

Store the start and end time of each TraceId.

CREATEts MATERIALIZEDis VIEWfor otel_logs_tags_mvTimestamp.
TO otel_logs_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 otel_logs
ARRAY JOIN [
    ('log.service.name', ServiceName),
    ('log.severity.text', SeverityText),
    ('log.severity.number', toString(SeverityNumber)),
    ('log.attributes.namespace', LogAttributes['namespace']),
    ('log.attributes.container_image', LogAttributes['container_image']),
    ('log.attributes.container_name', LogAttributes['container_name']),
    ('log.attributes.pod_name', LogAttributes['pod_name']),
    ('log.attributes.pod_ip', LogAttributes['pod_ip'])
] AS tag
WHERE tag.2 IS NOT NULL AND tag.2 != ''
GROUP BY
    Timestamp,
    TagKey,
    TagVal

CREATE TABLE otel_logs_stats_minutedefault.otel_traces_trace_id_ts
(
    `Timestamp`TraceId` String CODEC(ZSTD(1)),
    `Start` DateTime CODEC(Delta(4), ZSTD(1)),
    `ServiceName`End` LowCardinality(String)DateTime CODEC(Delta(4), ZSTD(1)),
    `Count`INDEX UInt64idx_trace_id CODEC(ZSTD(1))TraceId TYPE bloom_filter(0.01) GRANULARITY 1
)
ENGINE = SummingMergeTreeMergeTree
PARTITION BY toDate(Timestamp)Start)
ORDER BY (ServiceName,TraceId, Timestamp)Start)
TTL toDate(Timestamp)Start) + toIntervalDay(7)1)
SETTINGS index_granularity = 8192, ttl_only_drop_parts = 1;

CREATE MATERIALIZED VIEW otel_logs_stats_minute_mvdefault.otel_traces_trace_id_ts_mv
TO otel_logs_stats_minutedefault.otel_traces_trace_id_ts
(
    Timestamp`TraceId` DateTime,String,
    ServiceName`Start` LowCardinality(String)DateTime64(9),
    Count`End` UInt64DateTime64(9)
) AS
SELECT
    toStartOfMinute(TraceId,
    min(Timestamp) AS Timestamp,Start,
    ServiceName,
    count()max(Timestamp) AS CountEnd
FROM otel_logsdefault.otel_traces
WHERE ServiceNameTraceId != ''
GROUP BY Timestamp,
    ServiceNameTraceId;

curl 'http://localhost:3000/api/v1/logs/query' \
  -X POST \
  -H 'Content-Type: application/json' \
  -b 'EnvironmentID=1; JSESSIONID=48facfbe9f23884ca89611dbffa62c6c' \
  -d '{
    "envId": 1,
    "timeFrame": {
      "startTime": 1750405400,
      "endTime": 1750405445
    },
    "whereClause": "ServiceName = '\''one'\''",
    "paginator": {
      "lastTimestamp": ""
    }
  }'

curl 'http://localhost:3000/api/v1/logs/histogram' \
  -X POST \
  -H 'Content-Type: application/json' \
  -b 'EnvironmentID=1; JSESSIONID=48facfbe9f23884ca89611dbffa62c6c' \
  -d '{
    "envId": 1,
    "timeFrame": {
      "startTime": 1750405400,
      "endTime": 1750405445
    }
  }'