Hi,
I have some time series (energy data in seconds) stored in an InfluxDB. I made the experience that the standard graphs in Grafana like time series, x-y-plot etc. reach their limits when you start calculating and customizing.
So I wanted to have some custom plots and tried Plotly and Apache E-Charts Plug-In. In works for now, even coding in JavaScript (ChatGPT ftw ). But the calculations and transformations of my raw data from InfluxDB I could either do in the query (or using Grafana transformations) or in the JavaScript section of the Plug-in. The latter seems faster and you get more help because JavaScript is more common than Influx Query Language.
As for the current status, I have some calculations here and some there, which doesnât seem very consistent. I do calculations like grouping by weekday and certain time periods per weekday and then showing average values or sorting the power values and giving them time shares in %. In the future I would like to work more with graphs where the user can choose variables himself like the time period for average values.
So my questions are: 1. Is it better to do the calculations in the query or in the visualization script e.g. in JavaScript and why either one or the other way is preferable? 2. If the script way is preferable, is there a way to write and test the code somewhere else with a real debugging option?
In the query, because TSDB (InfluxDB) is designated for this kind of computations + it is running on the server, where you have more resources usually.
Letâs imagine dummy user selects long time range, which returns huge dataset - e. g. 100M rows. What will happen if you load this huge dataset into your browser (to be processed in javascript)? I guess your browser will be unresponsive.
Design your solution for edge cases, e. g. huge datasets should be processed.
Thanks for the food for thought⌠I wouldnât have thought of that myself and it shows, that I donât really understand the structure and how, for example, the Plug-in with JavaScript works.
For now, the operations with querys take longer than the ones in JavaScript. Does that mean, that I donât have enough capacity on my InfluxDB Server or could there be any other reason?
Is there another way to operate on the InfluxDB data in Grafana besides using the user interface in Grafana with the Influx Query Language?
Only you know your data, query, DB sizing, so only you should be able to find a reason.
It can be anything, e.g. small dataset - overhead to compute 2 records is probably bigger on the InfluxDB side, than Javascript. It can be inefficient query, e.g. filtering on fields, not on tags, high cardinality tags, âŚ
These can be continuous or temporary measurements for a specific period of time.Yes, the granularity in seconds is necessary. Even if I donât need them for every analysis I often need it to see the behavior of a machine which you canât see in aggregated values.
Mostly, for the visualisations in Grafana I filter the values by the field âegy_wattâ (power):
Thanks for your assessment! I didnât build the database myself but I think I can comprehend the thoughts of the builder. All numeric values are stored in the fields and the tags store additional information. The field keys are the default names from the device sent via MQTT.
I understand your comments on the scheme and the best practice tips but itâs really hard for me to transfer it to my data. Could you please help me with that?
Sure, I have to think about the names a little longer (nevertheless itâs phase a,b,c or 1,2,3). If Iâm likely to use phase=total most often, i.e. regularly filter by total, option 1 is preferable, right?
Hi grandma These are the original names sent by the electrical measuring device. The addition in the designation stands for the measured variable, like watt for the power or vrms for voltage root mean square. a, b, c stands for one of the three current phases, No suffix means the âtotalâ, e.g. the sum or the average of the values (depending on the measured parameter).
So, that approach is tribal knowledge, you had to explain it implicit and does not have grandma stamp of approval . Good schema design explains itself explicitly
What Iâm still struggeling about is the question if the values for total and phase a,b,c go in the same measurement or are different measurements itself. I found arguments for both ways⌠Right now I feel better with putting them as own measurements. I could still filter for the tag aggregation then when I want to use the aggregated value (which will be mostly the case).
). But the calculations and transformations of my raw data from InfluxDB I could either do in the query (or using Grafana transformations) or in the JavaScript section of the Plug-in. The latter seems faster and you get more help because JavaScript is more common than Influx Query Language.
These are the original names sent by the electrical measuring device. The addition in the designation stands for the measured variable, like watt for the power or vrms for voltage root mean square. a, b, c stands for one of the three current phases, No suffix means the âtotalâ, e.g. the sum or the average of the values (depending on the measured parameter).
. Good schema design explains itself explicitly