#support

Grouping Fields in Clickhouse Query for Graph

TLDR Romario had difficulty displaying http_status in a graph using a clickhouse query. Srikanth suggested using toString to resolve the issue.

Powered by Struct AI
10
4mo
Solved
Join the chat
May 30, 2023 (4 months ago)
Romario
Photo of md5-6c7925cd4df7e39b036d2b26fb21013d
Romario
08:00 PM
Hello, how can I select 2 fields and group them with a clickhouse query?

I'm trying this but the status is not shown in the graph
SELECT
      toStartOfInterval(timestamp, INTERVAL 1 MINUTE) AS interval,
      stringTagMap['net.host.name'] AS host,
      numberTagMap['http.status_code'] as http_status,
      count() AS value 
FROM signoz_traces.distributed_signoz_index_v2
WHERE timestamp > {{.start_datetime}} AND timestamp < {{.end_datetime}}
      AND (stringTagMap['net.host.name'] = 'some_host' OR stringTagMap['net.host.name'] = 'other_host')
      AND stringTagMap['http.target'] = '/api/webhooks/1'
GROUP BY (host, http_status), interval
ORDER BY host ASC, http_status ASC, interval

I'm not seeing the http_status in the graph altough I'm selecting it
08:07
Romario
08:07 PM
I'm trying to use this field from the trace:
Image 1 for I'm trying to use this field from the trace:
08:09
Romario
08:09 PM
Seems that the http_status is not a tag? 🤔 not sure
May 31, 2023 (4 months ago)
Srikanth
Photo of md5-ce04a9988e2fd758a659dc55be6f2543
Srikanth
01:55 AM
SELECT
      count() AS value,
      toStartOfInterval(timestamp, INTERVAL 1 MINUTE) AS interval,
      stringTagMap['net.host.name'] AS host,
      numberTagMap['http.status_code'] as http_status
FROM signoz_traces.distributed_signoz_index_v2
WHERE timestamp > {{.start_datetime}} AND timestamp < {{.end_datetime}}
      AND (stringTagMap['net.host.name'] = 'some_host' OR stringTagMap['net.host.name'] = 'other_host')
      AND stringTagMap['http.target'] = '/api/webhooks/1'
GROUP BY (host, http_status), interval
ORDER BY host ASC, http_status ASC, interval

Try this instead
Romario
Photo of md5-6c7925cd4df7e39b036d2b26fb21013d
Romario
05:46 PM
What's the difference? Just the order of the selecte fields?
05:48
Romario
05:48 PM
Didn't work
Image 1 for Didn't work
Jun 01, 2023 (4 months ago)
Srikanth
Photo of md5-ce04a9988e2fd758a659dc55be6f2543
Srikanth
01:32 AM
I think there is conflict between using the value vs http status code for the chart value since both are numbers. The reorder should have worked. I will check. You can also try toString explicitly since you are not doing anything with status code.
Romario
Photo of md5-6c7925cd4df7e39b036d2b26fb21013d
Romario
01:33 AM
The reorder left me with the graph I shared. How do I use the toString thing?
Srikanth
Photo of md5-ce04a9988e2fd758a659dc55be6f2543
Srikanth
01:34 AM
SELECT
      count() AS value,
      toStartOfInterval(timestamp, INTERVAL 1 MINUTE) AS interval,
      stringTagMap['net.host.name'] AS host,
      toString(numberTagMap['http.status_code']) as http_status
FROM signoz_traces.distributed_signoz_index_v2
WHERE timestamp > {{.start_datetime}} AND timestamp < {{.end_datetime}}
      AND (stringTagMap['net.host.name'] = 'some_host' OR stringTagMap['net.host.name'] = 'other_host')
      AND stringTagMap['http.target'] = '/api/webhooks/1'
GROUP BY (host, http_status), interval
ORDER BY host ASC, http_status ASC, interval
Romario
Photo of md5-6c7925cd4df7e39b036d2b26fb21013d
Romario
01:42 AM
Yes! That worked