Allow to use top function with spread function

[Kaauw]

Hello,

I want to propose a new feature. It's the possibility to create a top of a spread (or other) function.

I found an other method to do this thing, but it's a bit hard to set it up. I used Continuous Queries like this :

CREATE CONTINUOUS QUERY spread_ifInDiscards on QLAB RESAMPLE EVERY 5m BEGIN SELECT spread("ifInDiscards") INTO spread_ifInDiscards from interface group by description, hostname, time(31d) END
CREATE CONTINUOUS QUERY spread_ifOutDiscards on QLAB RESAMPLE EVERY 5m BEGIN SELECT spread("ifOutDiscards") INTO spread_ifOutDiscards from interface group by description, hostname, time(31d) END
CREATE CONTINUOUS QUERY spread_ifInErrors on QLAB RESAMPLE EVERY 5m BEGIN SELECT spread("ifInErrors") INTO spread_ifInErrors from interface group by description, hostname, time(31d) END
CREATE CONTINUOUS QUERY spread_ifOutErrors on QLAB RESAMPLE EVERY 5m BEGIN SELECT spread("ifOutErrors") INTO spread_ifOutErrors from interface group by description, hostname, time(31d) END

And after to make a top of theses values :

> select top("spread", 10), description, hostname from spread_ifInDiscards
name: spread_ifInDiscards
time                    top     description                                                             hostname
----                    ---     -----------                                                             --------
1483833600000000000     118186  Avaya Ethernet Routing Switch 4548GT PWR Module - Unit 1 Port 46        BAT1-LT00-SW01
1483833600000000000     24602   Avaya Ethernet Routing Switch 4548GT PWR Module - Unit 2 Port 45        BAT1-LT00-SW01
1483833600000000000     22889   Avaya Ethernet Routing Switch 4548GT PWR Module - Unit 1 Port 47        BAT1-LT00-SW01
1483833600000000000     19890   Avaya Ethernet Routing Switch 4548GT PWR Module - Unit 2 Port 46        BAT1-LT00-SW01
1483833600000000000     17983   Avaya Ethernet Routing Switch 4548GT PWR Module - Unit 2 Port 47        BAT1-LT00-SW01
1483833600000000000     15158   Avaya Ethernet Routing Switch 4548GT PWR Module - Unit 1 Port 41        BAT1-LT00-SW01
1483833600000000000     6723    Avaya Ethernet Routing Switch 4548GT PWR Module - Unit 1 Port 48        BAT1-LT00-SW01
1483833600000000000     4445    Avaya Ethernet Routing Switch 4548GT PWR Module - Unit 1 Port 42        BAT1-LT00-SW01
1483833600000000000     4030    Avaya Ethernet Routing Switch 4548GT PWR Module - Unit 1 Port 25        BAT1-LT00-SW01
1483833600000000000     2900    Avaya Ethernet Routing Switch 4548GT PWR Module - Unit 2 Port 11        BAT1-LT00-SW01

Proposal: [Description of the feature]

The possibility to use the top function with spread (or other function). Maybe : select top(spread("ifInErrors"), 10), description, hostname from interface

Current behavior: [What currently happens]

> select top(spread("ifInErrors"), 10), description, hostname from interface ERR: error parsing query: only fields or tags are allowed in top(), found spread(ifInErrors)

Desired behavior: [What you would like to happen]

time                    top     description                                                             hostname
----                    ---     -----------                                                             --------
1483833600000000000     118186  Avaya Ethernet Routing Switch 4548GT PWR Module - Unit 1 Port 46        BAT1-LT00-SW01
1483833600000000000     24602   Avaya Ethernet Routing Switch 4548GT PWR Module - Unit 2 Port 45        BAT1-LT00-SW01
1483833600000000000     22889   Avaya Ethernet Routing Switch 4548GT PWR Module - Unit 1 Port 47        BAT1-LT00-SW01
1483833600000000000     19890   Avaya Ethernet Routing Switch 4548GT PWR Module - Unit 2 Port 46        BAT1-LT00-SW01
1483833600000000000     17983   Avaya Ethernet Routing Switch 4548GT PWR Module - Unit 2 Port 47        BAT1-LT00-SW01
1483833600000000000     15158   Avaya Ethernet Routing Switch 4548GT PWR Module - Unit 1 Port 41        BAT1-LT00-SW01
1483833600000000000     6723    Avaya Ethernet Routing Switch 4548GT PWR Module - Unit 1 Port 48        BAT1-LT00-SW01
1483833600000000000     4445    Avaya Ethernet Routing Switch 4548GT PWR Module - Unit 1 Port 42        BAT1-LT00-SW01
1483833600000000000     4030    Avaya Ethernet Routing Switch 4548GT PWR Module - Unit 1 Port 25        BAT1-LT00-SW01
1483833600000000000     2900    Avaya Ethernet Routing Switch 4548GT PWR Module - Unit 2 Port 11        BAT1-LT00-SW01

Use case: [Why is this important (helps with prioritizing requests)]

I'm working in network metrology, and i need to make a top of interface who are making errors and discards. It's very helpful when you want to diagnostic a network issue.

Regards,

Benjamin

[desa]

@Kaauw this should be possible with subqueries in the 1.2 release. The code is currently on master if you'd like to try it out.

The query would look something like

SELECT top("spread",10) FROM (SELECT spread("ifInErrors") FROM interface

[Kaauw]

Updated influxDB to 1.2, i have tried to use subqueries but it don't work.

> select top(spread, 10), hostname, description from (select spread("ifInDiscards") from interface group by description, hostname)
ERR:

I use continuous queries to do this things for the moment. But the table is erased each time the continuous query is executed, so i can't go back in the past to show which interfaces have dropped in a defined period.

When i try without hostname and description and just with the group by clause, it work, but i can't identify interfaces and host. And the result is too different compared to the continuous query

> select top(spread, 10) from (select spread("ifInDiscards") from interface group by description, hostname)
name: interface
time top
---- ---
0    1417680
0    415961
0    248773
0    213407
0    202220
0    173550
0    172271
0    76731
0    56442
0    33230

[jsternberg]

@Kaauw when you get that error, does it result in the server panicking? It might just be a bug that was found in #7875 (1 on the list).

[gkuchta]

@jsternberg I've run into pretty much the exact same problem; in my case it results in:

Jan 25 19:42:39 ip-10-48-106-231 influxd: panic: runtime error: index out of range
Jan 25 19:42:39 ip-10-48-106-231 influxd: goroutine 29555 [running]:
Jan 25 19:42:39 ip-10-48-106-231 influxd: panic(0xa03980, 0xc4200100c0)
Jan 25 19:42:39 ip-10-48-106-231 influxd: /usr/local/go/src/runtime/panic.go:500 +0x1a1
Jan 25 19:42:39 ip-10-48-106-231 influxd: github.com/influxdata/influxdb/influxql.filterIntegerByUniqueTags(0xc422a3d000, 0x5f, 0x80, 0xc428dfb110, 0x1, 0x1, 0xbac6f8, 0x0,
 0x0, 0x1100000000)

[Kaauw]

Sorry for time to respond

Here the log just after request, server is panicking

JJan 26 09:26:34 Projet-SNMP influxd[7285]: [I] 2017-01-26T08:26:34Z SELECT top(spread, 10), hostname, description FROM (SELECT spread(ifInDiscards) FROM QLAB.autogen.interface GROUP BY description, hostname) service=query
Jan 26 09:26:38 Projet-SNMP influxd[7285]: panic: runtime error: index out of range
Jan 26 09:26:38 Projet-SNMP influxd[7285]: goroutine 8329 [running]:
Jan 26 09:26:38 Projet-SNMP influxd[7285]: panic(0xa03980, 0xc4200100b0)
Jan 26 09:26:38 Projet-SNMP influxd[7285]: /usr/local/go/src/runtime/panic.go:500 +0x1a1
Jan 26 09:26:38 Projet-SNMP influxd[7285]: github.com/influxdata/influxdb/influxql.(*auxIteratorFields).send(0xc431de2720, 0xe334c0, 0xc42a7e7560, 0x0)
Jan 26 09:26:38 Projet-SNMP influxd[7285]: /root/go/src/github.com/influxdata/influxdb/influxql/iterator.go:510 +0x7b0
Jan 26 09:26:38 Projet-SNMP influxd[7285]: github.com/influxdata/influxdb/influxql.(*integerAuxIterator).stream(0xc420275b40)
Jan 26 09:26:38 Projet-SNMP influxd[7285]: /root/go/src/github.com/influxdata/influxdb/influxql/iterator.gen.go:3404 +0xd2
Jan 26 09:26:38 Projet-SNMP influxd[7285]: created by github.com/influxdata/influxdb/influxql.(*integerAuxIterator).Start
Jan 26 09:26:38 Projet-SNMP influxd[7285]: /root/go/src/github.com/influxdata/influxdb/influxql/iterator.gen.go:3379 +0x3f
Jan 26 09:26:38 Projet-SNMP systemd[1]: influxdb.service: main process exited, code=exited, status=2/INVALIDARGUMENT
Jan 26 09:26:38 Projet-SNMP systemd[1]: Unit influxdb.service entered failed state.
Jan 26 09:26:38 Projet-SNMP systemd[1]: influxdb.service holdoff time over, scheduling restart.

[Kaauw]

Server panic Fixed in 1.2.1 but it return empty data

> select top(spread, 10), interface_name, device_info_hostname from (select spread("interface_issue_input_discards") from interface_issue group by interface_name, device_info_hostname)
name: interface_issue
time top     interface_name device_info_hostname
---- ---     -------------- --------------------
0    3553099
0    523061
0    506666
0    423990
0    367496
0    294807
0    204215
0    183145
0    125342
0    93071

[jsternberg]

I think your subquery might be wrong. There's no interface_name or device_info_hostname field or tag in the subquery so it's not finding anything to query. spread works because it's in the subquery.

[Kaauw]

@jsternberg It's no possible, this is why i use the group by statement. It's working fine with continuous queries

> select top(spread, 10), interface_name, device_info_hostname from (select spread("interface_issue_input_discards"), interface_name, device_info_hostname from interface_issue group by interface_name, device_info_hostname)
ERR: error parsing query: mixing aggregate and non-aggregate queries is not supported

I also tried this

select top(spread, 10) from (select spread("interface_issue_input_discards") from interface_issue) group by interface_name, device_info_hostname

It give me a lot of data where values are 0. I don't need it, so tried te request below.

> select top(spread, 10) from (select spread("interface_issue_input_discards") from interface_issue) where top > 0 group by interface_name, device_info_hostname
> select top(spread, 10) from (select spread("interface_issue_input_discards") from interface_issue) where spread > 0 group by interface_name, device_info_hostname

I need to spread discards values by interface, not the entire table. Maybe the best way is to use continuous queries insted of subqueries. But i need to know "What is the interface who dropped the most between monday and thuesday" and i'm not sure if i can do that with CQ.

[jsternberg]

This should be fixed by #8081.

[jsternberg]

Fixed by #8081.