Sum of non_negative_derivative with group by

Hi,

I planned to use InfluxDB to monitor our HBase cluster with region-level metrics, where my data looks like:
> select * from “regionserver.Regions.readRequestCount” limit 5
name: regionserver.Regions.readRequestCount
time cluster host namespace region table value
---- ------- ---- --------- ------ ----- -----
1492152641000000000 sh host1 default 00461c76de8093dd93c31ba95bce8f7f table1 4.4121848e+07
1492152641000000000 sh host2 default 00e563b860ecc360f4dd686e4629b9f7 table1 1.3915744e+07
1492152641000000000 sh host3 default 03d503204f5a4307bc0c8a12926fd2bb table3 0
1492152641000000000 sh host4 default 08b463b37012bb4aae44bac78eda7aa2 table2 3.189501e+06
1492152641000000000 sh host5 default 118dbc704b724f0668f4cd2bf825afac table3 0

I would like to find out how many reads a table has during the last week.
So I should probably have something like
select sum(non_negative_derivative(“regionserver.Regions.readRequestCount”) from “regionserver.Regions.readRequestCount” group by table, time(1m) where “table”=‘table1’

But there are two problems:

  1. sum can only apply to field, instead of functions
  2. If i write like derivative(sum(“regionserver.Regions.readRequestCount”)), then it will first sum and then calculate derivative, which will have bad results when some point is lost, interpolation seems not working here.

Can anybody help with this? Thanks.

Your query is close…do something like this:

select sum(non_negative_derivative) from (select last(<fieldname>) from regionserver.Regions.readRequestCount where time > now() - <sometime> and table = 'table1' group by *,time(1m)) where time > now() - <sometime> group by time(1m),table

You have to be careful when using these aggregate queries, as you can’t just do a select * operation on the whole data and expect a rate to be calculated.

This is a really, really good reference point: https://docs.influxdata.com/influxdb/v1.2/query_language/data_exploration/#subqueries