trace_clickhouse_queries.md

Trace data

Supplements queries documented as part of ClickHouse exporter.

Traces over Time

SELECT toStartOfInterval(toDateTime(Timestamp), INTERVAL 60 second) as time, ServiceName, count() as ` ` 
FROM otel."otel_traces" WHERE 1=1 AND Timestamp >= '1679994670' AND Timestamp <= '1679998270'  AND 
ServiceName In ('accountingservice','adservice','cartservice','checkoutservice','currencyservice','emailservice',
'featureflagservice','frauddetectionservice','frontend','loadgenerator','paymentservice','recommendationservice',
'productcatalogservice','quoteservice','shippingservice','frontend-proxy','frontend-web') 
GROUP BY ServiceName, time ORDER BY time ASC LIMIT 100000

99th Percentile of Service Times

SELECT toStartOfInterval(toDateTime(Timestamp), INTERVAL 60 second) as time, ServiceName,
	quantile(0.99)(Duration)/1000000 AS p99 FROM otel."otel_traces" WHERE 1=1 AND Timestamp >= '1679994708' 
  AND Timestamp <= '1679998308' AND  ( Timestamp  >= toDateTime(intDiv(1679994708714,1000)) 
  AND Timestamp <= toDateTime(intDiv(1679998308714,1000)) ) AND 
  ServiceName In ('accountingservice','adservice','cartservice','checkoutservice','currencyservice',
  'emailservice','featureflagservice','frauddetectionservice','frontend','loadgenerator','paymentservice',
  'recommendationservice','productcatalogservice','quoteservice','shippingservice','frontend-proxy','frontend-web') 
  AND ServiceName != 'loadgenerator' GROUP BY time, ServiceName ORDER BY time ASC LIMIT 100000

Error Rates

SELECT toStartOfInterval(toDateTime(Timestamp), INTERVAL 60 second) as time, count(*) as ` `,
	ServiceName FROM otel_traces WHERE 1=1 AND Timestamp >= '1679994787' AND Timestamp <= '1679998387' AND 
  ServiceName In ('accountingservice','adservice','cartservice','checkoutservice','currencyservice','emailservice',
  'featureflagservice','frauddetectionservice','frontend','loadgenerator','paymentservice','recommendationservice',
  'productcatalogservice','quoteservice','shippingservice','frontend-proxy','frontend-web') 
  AND StatusCode= 'STATUS_CODE_ERROR' and ServiceName != 'loadgenerator' GROUP BY ServiceName, time 
ORDER BY time ASC LIMIT 100000

Trace lists

SELECT min(Timestamp) as timestamp, TraceId as `Trace Id`, argMin(ServiceName, Timestamp) as `Service Name`, 
sum(Duration)/1000000 as Duration from otel.otel_traces WHERE 1=1  AND ServiceName 
In ('accountingservice','adservice','cartservice','checkoutservice','currencyservice','emailservice','featureflagservice',
'frauddetectionservice','frontend','loadgenerator','paymentservice','recommendationservice','productcatalogservice',
'quoteservice','shippingservice','frontend-proxy','frontend-web') AND ServiceName != 'loadgenerator' AND 
Timestamp >= '1679994845' AND Timestamp <= '1679998445' GROUP BY TraceId ORDER BY Duration DESC LIMIT 100

Spans for Trace Panel

SELECT TraceId as traceID, SpanId as spanID, SpanName as operationName, ParentSpanId as parentSpanID, 
ServiceName as serviceName, Duration/1000000 as duration, Timestamp as startTime, 
arrayMap(key -> map('key', key, 'value',SpanAttributes[key]), mapKeys(SpanAttributes)) as tags, 
arrayMap(key -> map('key', key, 'value',ResourceAttributes[key]), mapKeys(ResourceAttributes)) as serviceTags 
FROM otel.otel_traces WHERE TraceId = 'empty' ORDER BY startTime ASC