Writing traces based ClickHouse queries for building dashboard panels

(
    `ts_bucket_start` UInt64 CODEC(DoubleDelta, LZ4),
    `resource_fingerprint` String CODEC(ZSTD(1)),
    `timestamp` DateTime64(9) CODEC(DoubleDelta, LZ4),
    `trace_id` FixedString(32) CODEC(ZSTD(1)),
    `span_id` String CODEC(ZSTD(1)),
    `trace_state` String CODEC(ZSTD(1)),
    `parent_span_id` String CODEC(ZSTD(1)),
    `flags` UInt32 CODEC(T64, ZSTD(1)),
    `name` LowCardinality(String) CODEC(ZSTD(1)),
    `kind` Int8 CODEC(T64, ZSTD(1)),
    `kind_string` String CODEC(ZSTD(1)),
    `duration_nano` UInt64 CODEC(T64, ZSTD(1)),
    `status_code` Int16 CODEC(T64, ZSTD(1)),
    `status_message` String CODEC(ZSTD(1)),
    `status_code_string` String CODEC(ZSTD(1)),
    `attributes_string` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    `attributes_number` Map(LowCardinality(String), Float64) CODEC(ZSTD(1)),
    `attributes_bool` Map(LowCardinality(String), Bool) CODEC(ZSTD(1)),
    `resources_string` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    `resource` JSON(max_dynamic_paths = 100) CODEC(ZSTD(1)),
    `events` Array(String) CODEC(ZSTD(2)),
    `links` String CODEC(ZSTD(1)),
    `response_status_code` LowCardinality(String) CODEC(ZSTD(1)),
    `external_http_url` LowCardinality(String) CODEC(ZSTD(1)),
    `http_url` LowCardinality(String) CODEC(ZSTD(1)),
    `external_http_method` LowCardinality(String) CODEC(ZSTD(1)),
    `http_method` LowCardinality(String) CODEC(ZSTD(1)),
    `http_host` LowCardinality(String) CODEC(ZSTD(1)),
    `db_name` LowCardinality(String) CODEC(ZSTD(1)),
    `db_operation` LowCardinality(String) CODEC(ZSTD(1)),
    `has_error` Bool CODEC(T64, ZSTD(1)),
    `is_remote` LowCardinality(String) CODEC(ZSTD(1)),
    `resource_string_service$$name` LowCardinality(String) DEFAULT resources_string['service.name'] CODEC(ZSTD(1)),
    `attribute_string_http$$route` LowCardinality(String) DEFAULT attributes_string['http.route'] CODEC(ZSTD(1)),
    `attribute_string_messaging$$system` LowCardinality(String) DEFAULT attributes_string['messaging.system'] CODEC(ZSTD(1)),
    `attribute_string_messaging$$operation` LowCardinality(String) DEFAULT attributes_string['messaging.operation'] CODEC(ZSTD(1)),
    `attribute_string_db$$system` LowCardinality(String) DEFAULT attributes_string['db.system'] CODEC(ZSTD(1)),
    `attribute_string_rpc$$system` LowCardinality(String) DEFAULT attributes_string['rpc.system'] CODEC(ZSTD(1)),
    `attribute_string_rpc$$service` LowCardinality(String) DEFAULT attributes_string['rpc.service'] CODEC(ZSTD(1)),
    `attribute_string_rpc$$method` LowCardinality(String) DEFAULT attributes_string['rpc.method'] CODEC(ZSTD(1)),
    `attribute_string_peer$$service` LowCardinality(String) DEFAULT attributes_string['peer.service'] CODEC(ZSTD(1))
)
ORDER BY (ts_bucket_start, resource_fingerprint, has_error, name, timestamp)

(
    `labels` String CODEC(ZSTD(5)),
    `fingerprint` String CODEC(ZSTD(1)),
    `seen_at_ts_bucket_start` Int64 CODEC(Delta(8), ZSTD(1))
)

(
    `timestamp` DateTime64(9) CODEC(DoubleDelta, LZ4),
    `errorID` FixedString(32) CODEC(ZSTD(1)),
    `groupID` FixedString(32) CODEC(ZSTD(1)),
    `traceID` FixedString(32) CODEC(ZSTD(1)),
    `spanID` String CODEC(ZSTD(1)),
    `serviceName` LowCardinality(String) CODEC(ZSTD(1)),
    `exceptionType` LowCardinality(String) CODEC(ZSTD(1)),
    `exceptionMessage` String CODEC(ZSTD(1)),
    `exceptionStacktrace` String CODEC(ZSTD(1)),
    `exceptionEscaped` Bool CODEC(T64, ZSTD(1)),
    `resourceTagsMap` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
    INDEX idx_error_id errorID TYPE bloom_filter GRANULARITY 4,
    INDEX idx_resourceTagsMapKeys mapKeys(resourceTagsMap) TYPE bloom_filter(0.01) GRANULARITY 64,
    INDEX idx_resourceTagsMapValues mapValues(resourceTagsMap) TYPE bloom_filter(0.01) GRANULARITY 64
)

(
    `name` LowCardinality(String) CODEC(ZSTD(1)),
    `serviceName` LowCardinality(String) CODEC(ZSTD(1))
)

(
    `tagKey` LowCardinality(String) CODEC(ZSTD(1)),
    `tagType` Enum8('tag' = 1, 'resource' = 2) CODEC(ZSTD(1)),
    `dataType` Enum8('string' = 1, 'bool' = 2, 'float64' = 3) CODEC(ZSTD(1)),
    `isColumn` Bool CODEC(ZSTD(1))
)

(
    `timestamp` DateTime CODEC(DoubleDelta, ZSTD(1)),
    `tagKey` LowCardinality(String) CODEC(ZSTD(1)),
    `tagType` Enum8('tag' = 1, 'resource' = 2) CODEC(ZSTD(1)),
    `dataType` Enum8('string' = 1, 'bool' = 2, 'float64' = 3) CODEC(ZSTD(1)),
    `stringTagValue` String CODEC(ZSTD(1)),
    `float64TagValue` Nullable(Float64) CODEC(ZSTD(1)),
    `isColumn` Bool CODEC(ZSTD(1))
)

WITH __resource_filter AS (
    SELECT fingerprint 
    FROM signoz_traces.distributed_traces_v3_resource 
    WHERE (simpleJSONExtractString(labels, 'service.name') = 'myservice') 
    AND seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)

WITH __resource_filter AS (
    SELECT fingerprint 
    FROM signoz_traces.distributed_traces_v3_resource 
    WHERE (simpleJSONExtractString(labels, 'service.name') = 'myservice') 
    AND seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)
SELECT count(*) 
FROM signoz_traces.distributed_signoz_index_v3 
WHERE resource_fingerprint GLOBAL IN __resource_filter
    AND ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
    AND timestamp >= $start_datetime AND timestamp <= $end_datetime

`attribute_string_http$$method` LowCardinality(String) DEFAULT attributes_string['http.method'] CODEC(ZSTD(1)),
`attribute_string_http$$method_exists` Bool MATERIALIZED if(mapContains(attributes_string, 'http.method') != 0, true, false) CODEC(ZSTD(1)),

WITH __resource_filter AS (
    SELECT fingerprint 
    FROM signoz_traces.distributed_traces_v3_resource 
    WHERE seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)
SELECT fromUnixTimestamp64Milli(intDiv( toUnixTimestamp64Milli ( timestamp ), 100) * 100) AS interval, 
toFloat64(count()) AS count 
FROM (
 SELECT timestamp 
 FROM signoz_traces.distributed_signoz_index_v3
 WHERE resource_string_service$$name='frontend' 
 AND duration_nano>=50*exp10(6) 
 AND timestamp BETWEEN $start_datetime AND $end_datetime
 AND ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp)
 GROUP BY interval ORDER BY interval ASC;

WITH __resource_filter AS (
    SELECT fingerprint 
    FROM signoz_traces.distributed_traces_v3_resource 
    WHERE (simpleJSONExtractString(labels, 'service.name') = 'frontend') 
    AND seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)

SELECT 
    avg(duration_nano) / 1000_000 as duration_ms
FROM 
    signoz_traces.distributed_signoz_index_v3
WHERE 
    timestamp BETWEEN $start_datetime AND $end_datetime AND
    ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp AND
    http_method = 'POST' AND 
    resource_fingerprint GLOBAL IN __resource_filter;

WITH __resource_filter AS (
    SELECT fingerprint 
    FROM signoz_traces.distributed_traces_v3_resource 
    WHERE (simpleJSONExtractString(labels, 'service.name') = 'frontend') 
    AND (simpleJSONExtractString(labels, 'environment') = 'production')
    AND seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)
SELECT avg(value) as avg_count FROM (
    SELECT 
        toStartOfInterval(timestamp, INTERVAL 1 MINUTE) AS ts,
        toFloat64(count()) AS value 
    FROM 
        signoz_traces.distributed_signoz_index_v3
    WHERE 
        resource_fingerprint GLOBAL IN __resource_filter AND
        timestamp BETWEEN $start_datetime AND $end_datetime AND
        ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
    GROUP BY ts 
    ORDER BY ts ASC
);

WITH __resource_filter AS (
    SELECT fingerprint 
    FROM signoz_traces.distributed_traces_v3_resource 
    WHERE seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)
SELECT attributes_string['example_string_attribute'] AS attribute_name, 
    toFloat64(avg(duration_nano)) AS value 
FROM signoz_traces.distributed_signoz_index_v3  
WHERE timestamp BETWEEN $start_datetime AND $end_datetime
    AND ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
GROUP BY attribute_name 
ORDER BY attribute_name ASC;

WITH __resource_filter AS (
    SELECT fingerprint 
    FROM signoz_traces.distributed_traces_v3_resource 
    WHERE seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)
SELECT 
    resource.service.name::String as `service.name`,
    toFloat64(count()) AS value 
FROM 
    signoz_traces.distributed_signoz_index_v3
WHERE 
    resource_fingerprint GLOBAL IN __resource_filter AND
    timestamp BETWEEN $start_datetime AND $end_datetime AND
    ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp AND
    `service.name` IS NOT NULL
GROUP BY `service.name`
ORDER BY value DESC;

WITH __resource_filter AS (
    SELECT fingerprint 
    FROM signoz_traces.distributed_traces_v3_resource 
    WHERE (simpleJSONExtractString(labels, 'service.name') = 'api-gateway') 
    AND seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)
SELECT 
    http_method,
    toFloat64(avg(duration_nano)) AS avg_duration_nano 
FROM 
    signoz_traces.distributed_signoz_index_v3
WHERE 
    resource_fingerprint GLOBAL IN __resource_filter AND
    timestamp BETWEEN $start_datetime AND $end_datetime AND
    ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp AND
    http_method IS NOT NULL AND http_method != ''
GROUP BY http_method
ORDER BY avg_duration_nano DESC;

WITH __resource_filter AS (
    SELECT fingerprint
    FROM signoz_traces.distributed_traces_v3_resource
    WHERE (simpleJSONExtractString(labels, 'service.name') = 'checkout-service-production')
    AND seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)
SELECT
    toStartOfInterval(timestamp, INTERVAL 1 MINUTE) AS ts,
    quantile(0.95)(duration_nano / 1000000) AS p95_latency_ms,
    quantile(0.99)(duration_nano / 1000000) AS p99_latency_ms,
    max(duration_nano / 1000000) AS max_latency_ms,
    toFloat64(count()) AS call_count
FROM
    signoz_traces.distributed_signoz_index_v3
WHERE
    resource_fingerprint GLOBAL IN __resource_filter AND
    timestamp BETWEEN {{.start_datetime}} AND {{.end_datetime}} AND
    ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp AND
    kind_string = 'Client' AND
    http_url LIKE '%cart-service.prod%'
GROUP BY ts
ORDER BY ts ASC;

WITH __resource_filter AS (
    SELECT fingerprint 
    FROM signoz_traces.distributed_traces_v3_resource 
    WHERE seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)
SELECT 
    toStartOfInterval(timestamp, INTERVAL 1 MINUTE) AS ts,
    resource.service.name::String as `service.name`,
    toFloat64(count()) AS value 
FROM 
    signoz_traces.distributed_signoz_index_v3  
WHERE 
    resource_fingerprint GLOBAL IN __resource_filter AND
    timestamp BETWEEN $start_datetime AND $end_datetime AND 
    `service.name` IS NOT NULL AND
    ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
GROUP BY `service.name`, ts
ORDER BY ts ASC;

WITH __resource_filter AS (
    SELECT fingerprint 
    FROM signoz_traces.distributed_traces_v3_resource 
    WHERE seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)
SELECT 
    resource.service.name::String as `service.name`,
    toFloat64(count()) AS value 
FROM 
    signoz_traces.distributed_signoz_index_v3  
WHERE 
    resource_fingerprint GLOBAL IN __resource_filter AND
    timestamp BETWEEN $start_datetime AND $end_datetime AND 
    has_error = true AND
    `service.name` IS NOT NULL AND
    ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
GROUP BY `service.name`;

SELECT toStartOfInterval(timestamp, INTERVAL 1 MINUTE) AS interval, 
    attributes_string['http.route'] AS route,
    toFloat64(avg(durationNano)) AS value 
FROM signoz_traces.distributed_signoz_index_v3
WHERE attributes_string['http.method']!=''
    AND timestamp > now() - INTERVAL 30 MINUTE
    AND ts_bucket_start >= toUInt64(toUnixTimestamp(now() - toIntervalMinute(30))) - 1800
GROUP BY (route, interval) order by (route, interval) ASC;

WITH arrayFilter(x -> JSONExtractString(x, 'name')='Getting customer', events) AS filteredEvents
SELECT toFloat64(count()) AS count, 
    arrayJoin(arrayMap(x -> JSONExtractString(JSONExtractString(x, 'attributeMap'), 'customer_id'), filteredEvents)) AS resultArray 
FROM signoz_traces.distributed_signoz_index_v3
WHERE not empty(filteredEvents) 
    AND timestamp > toUnixTimestamp(now() - INTERVAL 30 MINUTE) 
GROUP BY resultArray 
ORDER BY resultArray ASC;

SELECT
    interval,
    round(avg(time_diff), 2) AS result
FROM
(
    SELECT
        interval,
        traceID,
        if(startTime1 != 0, if(startTime2 != 0, (toUnixTimestamp64Nano(startTime2) - toUnixTimestamp64Nano(startTime1)) / 1000000, nan), nan) AS time_diff
    FROM
    (
        SELECT
            toStartOfInterval(timestamp, toIntervalMinute(1)) AS interval,
            traceID,
            minIf(timestamp, if(resource_string_service$$name='driver', if(name = '/driver.DriverService/FindNearest', if((resources_string['component']) = 'gRPC', true, false), false), false)) AS startTime1,
            minIf(timestamp, if(resource_string_service$$name='route', if(name = 'HTTP GET /route', true, false), false)) AS startTime2
        FROM signoz_traces.distributed_signoz_index_v3
        WHERE (timestamp BETWEEN $start_datetime AND $end_datetime) AND (ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp) AND (resource_string_service$$name IN ('driver', 'route'))
        GROUP BY (interval, traceID)
        ORDER BY (interval, traceID) ASC
    )
)
WHERE isNaN(time_diff) = 0
GROUP BY interval
ORDER BY interval ASC;

WITH arrayFilter(x -> JSONExtractString(x, 'name')='Getting customer', events) AS filteredEvents
SELECT toStartOfInterval(timestamp, INTERVAL 1 MINUTE) AS interval, toFloat64(sum(toInt32(resultArray))) AS sum, 
arrayJoin(arrayMap(x -> JSONExtractString(JSONExtractString(x, 'attributeMap'), 'customer_id'), filteredEvents)) AS resultArray 
FROM signoz_traces.distributed_signoz_index_v3
WHERE not empty(filteredEvents) and timestamp > toUnixTimestamp(now() - INTERVAL 30 MINUTE) 
AND ts_bucket_start >= toUInt64(toUnixTimestamp(now() - toIntervalMinute(30))) - 1800
GROUP BY (resultArray, interval) order by (resultArray, interval) ASC;

SELECT fromUnixTimestamp64Milli(intDiv( toUnixTimestamp64Milli ( timestamp ), 100) * 100) AS interval, 
    toFloat64(count()) AS count 
FROM (
    SELECT timestamp 
    FROM signoz_traces.distributed_signoz_index_v3
    WHERE resource_string_service$$name='frontend' 
        AND durationNano>=50*exp10(6) 
        AND timestamp > now() - INTERVAL 1 MINUTE AND ts_bucket_start >= toUInt64(toUnixTimestamp(now() - toIntervalMinute(1))) - 1800)
    GROUP BY interval 
    ORDER BY interval ASC
);

SELECT toStartOfInterval(fromUnixTimestamp64Nano(timestamp), INTERVAL 1 MINUTE) AS interval, 
    toFloat64(count()) AS value 
FROM signoz_logs.distributed_logs_v2
WHERE timestamp > toUnixTimestamp64Nano(now64() - INTERVAL 30 MINUTE) 
    AND ts_bucket_start >= toUInt64(toUnixTimestamp(now() - toIntervalMinute(30))) - 1800
GROUP BY interval 
ORDER BY interval ASC;