Walkthrough for SQL queries to basic guages and multi series/line charting? Does it exist?

General
1

Hi there,

I hope you're all well in these crazy times. However, after a great deal of tinkering, I have failed to find an answer for myself and humbly ask for some help.

I'm a complete newbie when it comes to Node Red. While I'm vaguely proficient (albeit rusty) at Python and databases, my biggest weakness is that I have absolutely zero JS/JSON experience. I'm tinkering with my first Raspberry Pi based project, and it's a simple weather station.

I'd really appreciate a "walk-through" to get a feel on how to do things the "Node Red way" with one specific question in mind:

How do I "massage" my weather data from a simple MySQL select statement to a dashboard chart-friendly format. This is such a basic thing, but I struggle to find meaningful answers. Particularly when trying to get multiple database-driven lines on a chart. I'd really like to see the overall flow, then how each node is configured in series, with some indication about what to look for.

After days, I've failed to convert a MySQL query to a multi-line graph. All I want to do is to summarize old weather data to have the daily min, average, and max temperatures on a single graph.

What days of searching has taught me:

Changing the SQL statement involves something about message topics for each series, and then some seem to use a change node to somehow translate the multiple values into a series, but I have seen no working examples that go from start to finish.

I've tried to do things in almost every "wrong" way conceivable (according to Node Red, at least). Along the way, I was trying to enter my own non-standard time data and of course it failed). I managed to fix that, and got a couple of single-lined charts to work.

I managed to get a couple of gauges working after days of trawling through the web, but also found that any SQL statement that used functions like SUM(), MIN(), MAX(), or AVG() were passed to the chart in ways like "SUM(RAINFALL)" that moved the needle of the gauge to the right place, but didn't depict the value in the centre. It was fixed if I amended the SQL query to map the functions to gauge-friendly names like:

SELECT SUM(RAINFALL) AS TOTAL_RAIN FROM WEATHER...;

I can get one line on a graph, but all attempts to get MIN(), MAX(), & AVG() series have only rendered one line so far.

Having said that, please assume I know nothing, and need to start from scratch. I'm not even sure if I should create a separate SQL query for each series, or if I can extract each value from a multi-columned select statement and transmogrify it into a chart-friendly format further down the flow.

Any advice would be greatly appreciated.
Hamish.

2

SELECT SUM(RAINFALL) AS TOTAL_RAIN FROM WEATHER...;

You can query them all at once, this is also the easiest for rendering a chart with all the data at once.

select sum(RAINFALL) as sum_r, min(RAINFALL) as min_r, max(RAINFALL) as max_r, avg(RAINFALL) as avg_r from weather

Now this only gets 1 data point of all available, I assume you want to show this per day (or any timeframe), so the numbers need to be grouped by something, most likely a date (which would be a date column)

select 
sum(RAINFALL) as sum_r, 
min(RAINFALL) as min_r, 
max(RAINFALL) as max_r, 
avg(RAINFALL) as avg_r 
from weather 
GROUP BY date_column
ORDER BY date_column DESC

Replace date_column with the appropriate column

What does this output ?

This is such a basic thing

It is actually not, multi-line charts are not the easiest when you are fresh in this realm

3

I think that using Influx DB + Grafana + Node-RED is the easiest way to accomplish what you want to do. A few months ago I followed the video in the middle of this thread and got everything doing pretty much exactly what you are wanting to do.

4

Hi Bakman2,

Thank you for the prompt reply, I appreciate it. It's also nice of you to say that it's not so easy when starting out. However, I meant that taking some data from a database and charting it should definitely be in the "bread and butter" category for something as powerful as Node Red.

I appreciate your help with the SQL, and for confirming that I can do the SQL statement in one hit for the dashboard chart's stringent formatting requirements. However, when I do so, I only get one line on the chart.

The SQL I have created so far is:

msg.topic = "SELECT * FROM (SELECT CREATED, MIN(AMBIENT_TEMP) AS MIN_TEMP, AVG(AMBIENT_TEMP) AS AVG_TEMP, MAX(AMBIENT_TEMP) AS MAX_TEMP FROM WEATHER_MEASUREMENT GROUP BY DAY(CREATED) ORDER BY CREATED DESC LIMIT 31) DAILY_TEMPS ORDER BY CREATED;"

return msg;

This is piped into my MySQL node. With the debug node piped directly into the MySQL node, the debug console spits out some lovely data from my existing data (only a week, hence 7 daily arrays, despite 31 days being requested) such as:

array[7]

0: object

CREATED: "2020-07-27T05:38:19.000Z"

MIN_TEMP: 18.4

AVG_TEMP: 18.558

MAX_TEMP: 18.75

1: object

CREATED: "2020-07-28T11:17:00.000Z"

MIN_TEMP: 15.53

AVG_TEMP: 15.714583

MAX_TEMP: 15.88

Side note: When I type this directly into MySQL on the terminal the daily results are as expected, but when I do this in Node Red, I get two arrays from the 28th which is somewhat weird. I have no idea why this would happen.

Back to the problem at hand...

This is all good an well, but then when I link the database to a chart, I get one line only, (MAX_TEMP). Clearly I need to convert the SQL output so multiple lines are displayed (perhaps into multiple X/Y values?) ... but I'm not sure how to do this.

If you or anyone else knows how to do this, please feel free to let me know!

Kind Regards,
Hamish.

5

I do not have access to my system atm, but indeed it is correct that you only see one value, because as i said, it is not that simple. See the documentation - what is expected as input to show multiseries:

See “stored data -> line charts”.

6

Hi Bakman2,

I appreciate the help, but this documentation isn't at all clear to a newbie like myself with no Node Red experience or JS/JSONata knowledge. Clearly the level of assumed knowledge is above my own. Hence my humble request for a detailed walk-through.

The situation so far...

Ok, so the SQL is good and gathers all the info we need from a single query, great.

But then this documentation says I need to split it into topics?

No information is provided on where to do that in the flow when working with a database. This is why I thought might have had to split the SQL query into three parts (min, average and max) and assign them to differing topics, but my tinkering hasn't worked that way so far.

Speaking of the linked documentation, the importable flow structure at the bottom of the documentation page has no database at all (for obvious reasons) but the data is already formatted from the start of the flow, and as such doesn't help me much when dealing with database query output.

So I am guessing I need to run a change node between the MySQL node and the chart to format the data, but my attempts to do so never provide more than one line.

My change node has the following code:

[
   {
       "series": ["MIN_TEMP", "AVG_TEMP", "MAX_TEMP"],
       "labels": ["MIN_TEMP", "AVG_TEMP", "MAX_TEMP"],
       "data": [
           [
             payload.{  "x": CREATED, "y": $."MIN_TEMP"},
            {  "x": CREATED, "y": $."AVG_TEMP"},
            {  "x": CREATED, "y": $."MAX_TEMP"}
           ]
       ]
}
]

... and now I get the min temp line with little else. I get the feeling I am getting close, but I'm just not seeing what I need to change... or how.

Grant1's suggestion to switch out from MySQL to Influx + Grafana, is intriguing. However at this point, the sensor data is being collected by a Python script, Node Red is not involved in collecting or managing the data at all. It just provides a dash board and graphs the results. I really don't want to have to gut this thing and start from scratch.

Anyway, I really appreciate that you took the time to respond. Thanks.

Hamish.

7

Hello harmo
In Your query where Your work with time, You do not have the same dimensions in SELECT, GROUP BY and ORDER BY. In GROUP BY You use the function DAY, which might only return weekdays. I think it is vital, when You make Your query, that You use same dimensions in SELECT and GROUP BY. At least when testing. Dimensions are those fields in GROUP BY and SELECT, which does not aggregate.

In the field Created there is also a timestamp. So it might work with SELECT DAY(CREATED), .... in the subquery, which is qouted below.

Best regard
Brian

8

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.