clickhouse-grafana: $columns + ORDER BY ... WITH FILL ... not works

Hello everyone,

I have an issue while trying to create alert rule on Grafana using this plugin. Issue is that different graphs are plotted using same query on “Create alert rule” and “Explore” sections. Because of this i am unable to create multi dimensional rule as described here.

In Explore section graph is plotted correctly and requests are grouped by status code as thay should.

But, using same query in Create alert rule section i got strange grouping which i don’t understand.

I have tried the same thing with InfluxDB as a source and graphs were indentical.

I am not sure why it is behaving like this so any insight is welcome.

Query:

SELECT 
    $timeSeries as t,
    sum(request) as request,
    status_code
FROM $table
WHERE
    $timeFilter
    AND status_code != 201 AND status_code != 0
GROUP BY t, status_code
ORDER BY t WITH FILL STEP 60000

ClickHouse: 21.11.6.7 (official build) Grafana version: v8.4.1 (53f5c6a44c) Plugin version: v2.4.2 Plugin was installed using grafana-cli command. ClickHouse and Grafana are installed on Debian 11

About this issue

Original URL
State: open
Created 2 years ago
Comments: 23

Most upvoted comments

@Slach I’ve written an example below.

Create a timeseries with some category that we want to group on:

DROP TABLE IF EXISTS test_data

CREATE TABLE IF NOT EXISTS test_data
(
    timestamp DateTime,
    category LowCardinality(String),
    value UInt64
) ENGINE = MergeTree ORDER BY timestamp

INSERT INTO test_data VALUES
    (now(), 'A', 1),
    (now(), 'B', 2),
    (now() - INTERVAL 10 MINUTE, 'A', 2),
    (now() - INTERVAL 15 MINUTE, 'B', 5),
    (now() - INTERVAL 60 MINUTE, 'A', 4)

Produce a query:

$columns(category, sum(value) AS c)
FROM test_data
WHERE $timeFilter(timestamp)

Generated SQL:

SELECT t, groupArray((category, c)) AS groupArr FROM ( SELECT (intDiv(toUInt32(timestamp), 15) * 15) * 1000 AS t, category, sum(value) AS c FROM test_data
WHERE timestamp >= toDateTime(1646641819) AND timestamp <= toDateTime(1646663419) AND timestamp >= toDateTime(1646641819) AND timestamp <= toDateTime(1646663419)(timestamp) GROUP BY t, category ORDER BY t, category) GROUP BY t ORDER BY t

I’d like to create an alert for if the total of value over the last 30 minutes is >5 for any one of the series. I did this with a reduce + math expression.

The sum by series appears to work correctly, but I notice that they’re each displayed by the query name rather than by the category. When I try to evaluate this, I get an error that @Enton-72 hit:

When using a classic condition the alerting does work as expected. However, there are some limitations to that approach (any single label triggering the alert will prevent new alert updates when a second label would trigger it). I’m running plugin version 2.4.3, grafana 8.4.3.

kleemaj on Mar 7, 2022