Repository navigation
Macro for aggregating per-second rates - need to simplified SQL query which allows get rates with dimensions but show only one time series with sum/avg #386
Description
One common pattern in our metrics is that we want to calculate the rate of a large number of counters and then aggregate those series, typically using a sum. A good example of this would be the total transmit bandwidth for all interfaces grouped by datacenter.
There may be hundreds of series here so we really need to push down the query to ClickHouse so doing workarounds with transforms would not really help. It is doable but
I've included below the InfluxDB and ClickHouse versions of the raw query. Having a macro would simplify the CH version considerably. I was thinking this could be a $perSecond* variant with 2 extra parameters the aggregation function (typically sum) and the columns to aggregate over.
InfluxDB:
SELECT sum(x) FROM (SELECT non_negative_derivative(max("PortXmitData_bits"), 1s) AS x FROM "netstats" WHERE ("datacenter" =~ /^$datacenter$/) AND env = 'prod' AND $timeFilter GROUP BY time($__interval),host,interface) GROUP BY time($__interval) fill(null)
ClickHouse:
SELECT
t,
datacenter,
groupArray((datacenter, max_0_Rate_aggregated)) AS groupArr
FROM
(
SELECT
t,
datacenter,
sum(max_0_Rate) AS max_0_Rate_aggregated
FROM (
SELECT
t,
datacenter,
perSecondColumns,
if(runningDifference(max_0) < 0, nan, runningDifference(max_0) / runningDifference(t / 1000)) AS max_0_Rate
FROM
(
SELECT
$timeSeries as t,
concat(host, interface) AS perSecondColumns,
max(PortXmitData_bits) AS max_0,
datacenter
FROM default.netstats
WHERE
$timeFilter
AND datacenter in ($datacenter) AND env = 'prod'
GROUP BY
t,
datacenter,
perSecondColumns
ORDER BY
perSecondColumns,
datacenter,
t
)
)
GROUP BY datacenter, t
ORDER BY datacenter, t
)
GROUP BY datacenter, t
ORDER BY datacenter, t
Activity
Slach commentedon Nov 23, 2021
Why
$perSecondColumnsis not applicable for you?oplehto commentedon Nov 23, 2021
Can I do this with $perSecondColumns? I may have missed something.
I have a counter that has the identifying columns
datacenter,hostandinterface. So I want to do the following:datacenterThe first part I can do with
$perSecondColumnresulting in 1 graph per counter but how do I do the second level aggregation to get the sum of the counters?Slach commentedon Nov 24, 2021
How many
time seriesdo you need?Is only one series per datacenter or one per datacenter plus one per datacenter+interface?
oplehto commentedon Nov 24, 2021
In this particular case the time series would be per datacenter but I have queries that I'm planning to migrate to ClickHouse where we need the top-level GROUP BY based on multiple columns.
Slach commentedon Jun 1, 2022
maybe
GROUP BY ... WITH ROLLUP ..will good solution for your use case?https://clickhouse.com/docs/en/sql-reference/statements/select/group-by/#with-rollup-modifier
[-]Macro for aggregating per-second rates[/-][+]Macro for aggregating per-second rates - need to simplified SQL query which allows get rates with dimensions but show only one time series with sum/avg[/+]Slach commentedon Feb 28, 2024
main PAIN
we need to simplify SQL query in ClickHouse or create macros like
$aggregatedRatewhich allow get analog of prometheus
rate/increasefunction with dimensionbut show only one time series in the result with sum/avg/min/max results of internal
ratefirst implementation $rateColumnsAggregate macro, see #386
improve implementation $rateColumnsAggregate macro for properly lines…