Hello,
I am using Grafana with InfluxDB. i batch measurements resulting in a typical write:
measurement,substation=substation_name,component=transformer,component_name=T1,quantity=voltage,type=magnitude,<other tags here> a=123,b=124,c=125
measurement,substation=substation_name,component=transformer,component_name=T2,quantity=voltage,type=magnitude,<other tags here> a=111,b=112,c=113
...
This works fine but the batched messages result in a very large bandwidth. A batch contains hundreds/thousand of lines and it is sent every second. The tags are all constant, only the value changes.
At the moment, In Grafana, the user selects a certain tags from pop up menus populated via template variables. This results in a typical query to InfluxDB:
SELECT mean(a), mean(b), mean(c) FROM "$RP"."measurement" WHERE ("substation" =~ /^$sub_name$/ AND "component"='transformer' AND "quantity"='voltage' AND "type" = 'magnitude') AND $timeFilter GROUP BY time($__interval),component_name
My idea is to leverage on the fact that tags are constant and I am thinking of associating a UUID to a specific set of tags so that the write results in a much smaller message
measurement,UUID=xyz1 a=123,b=124,c=125
measurement,UUID=xyz2 a=111,b=112,c=113
...
I am thinking of using template variables in Grafana to query a PostgreSQL database in which I map
substation=substation_name,component=transformer,component_name=T1,quantity=voltage,type=magnitude,<other tags here> ==> UUID=xyz1 .No idea how to do the PostgreSQL side yet, but I guess it is feasible.
The UUID(s) assigned to the template variables is(are) then used to query the InfluxDB to get the proper values to plot.
-
Is there a better way to achieve what I am trying to do? My way seems a bit complex to setup and to maintain.
-
If this is the way to go, is PostgreSQL the database to use?
-
Am I overestimating the possibility of using the results of a query to a relational database to query a time series database?
Thanks for reading!
Marco