Traces Schema - Write ClickHouse Queries & Build Dashboard Panels

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

ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp

-- Step 1: Find fingerprints matching your resource filters
WITH __resource_filter AS (
    SELECT fingerprint 
    FROM signoz_traces.distributed_traces_v3_resource 
    WHERE (simpleJSONExtractString(labels, 'service.name') = 'my-service') 
    AND seen_at_ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
)
-- Step 2: Use those fingerprints in your main query
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

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
)

-- String attribute
attributes_string['http.status_code']

-- Number attribute
attributes_number['response_time']

-- Boolean attribute
attributes_bool['is_cached']

-- Format: attribute_<dataType>_<key_with_dollars>
-- Dots in the attribute name are replaced with $$

attribute_string_http$$route        -- equivalent to attributes_string['http.route']
attribute_number_response$$time     -- equivalent to attributes_number['response.time']

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 
    fromUnixTimestamp64Milli(intDiv(toUnixTimestamp64Milli(timestamp), 100) * 100) AS interval, 
    toFloat64(count()) AS count 
FROM signoz_traces.distributed_signoz_index_v3
WHERE resource_fingerprint GLOBAL IN __resource_filter
    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 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 
    toStartOfInterval(timestamp, INTERVAL 1 MINUTE) AS interval, 
    attributes_string['http.route'] AS route,
    toFloat64(avg(duration_nano)) AS value 
FROM signoz_traces.distributed_signoz_index_v3
WHERE resource_fingerprint GLOBAL IN __resource_filter
    AND attributes_string['http.method'] != ''
    AND timestamp BETWEEN $start_datetime AND $end_datetime
    AND ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
GROUP BY route, interval 
ORDER BY route, 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) / 1000000 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 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 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 
    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
    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, 
                resource_string_service$$name = 'driver' 
                AND name = '/driver.DriverService/FindNearest'
            ) AS startTime1,
            minIf(timestamp, 
                resource_string_service$$name = 'route' 
                AND name = 'HTTP GET /route'
            ) 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 
    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 BETWEEN $start_datetime AND $end_datetime
    AND ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
GROUP BY resultArray 
ORDER BY resultArray 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 BETWEEN $start_datetime AND $end_datetime
    AND ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp
GROUP BY resultArray, interval 
ORDER BY resultArray, interval ASC;