How to backup and restore chart data in Node-RED Dashboard

Using Node-RED Dashboard you can quickly build useful and attractive user interfaces for IoT applications. Unfortunately, all diagrams are empty again when Node-RED or Node.js are restarted. I'll show you how to save and restore the chart data so that you will not be facing empty charts.

What you need

In this tutorial, I use Node-RED on Bluemix, the data will be stored in a DashDB instance. You can also transfer my example to a local running Node-RED with a local database or even data storage in a file. The pattern is basically the same as what I will show you.

Prepare the database

We simply need a small table in our database which is going to hold the data from various charts. Our table will be named CHARTDATA and contain two columns. Column CHART must contain values because this will be the identifier of our chart. CHARTDATA will be stored as CLOB because the chart node will give us a large JSON structure containing the chart data. And finally we need to organize this table by row because otherwise we are not allowed to use CLOB. Create it in DashDB as follows:

CREATE TABLE "CHARTDATA"
(
"CHART" VARCHAR(256) NOT NULL,
"CHARTDATA" CLOB
) ORGANIZE BY ROW;

Now we create a new entry in this table for our chart (which in the following example stores wind data from a weather station). Simply connect an inject node with a DashDB node.  If Node.js and DashDB are bound to the same application in Bluemix, you can directly select your database instance in the DashDB node without having to enter any further configurations. As payload of type string for the inject node enter

INSERT INTO CHARTDATA (CHART, CHARTDATA) VALUES ( 'Wind',  '');

Inject SQL insert statement into DashDB

Now after deploying this small flow, press once the inject button and a new row is inserted into your table. Later on we will update this row to hold the chart data. If you want to store data for other charts simply modify the SQL insert statement and add further rows for your other charts.

Save the chart data to the database

The node ui_chart already comes with the necessary capabilities so that we can easily store and reload chart data. In my example flow, wind data (average speed and gust) come from a weather station. In a function node I convert the values in I msg.topic from meter/s in km/h and set msg.topic to wind or gust, so that the two lines in the diagram will be correctly labeled. In order to not have too many data points in the chart, I use the delay node to limit to only one value every 5 minutes. The diagram shows the last 24 hours.

Node-RED flow including line chart (Image source: Internet-of-Things.blog)

In the function "check chartdata" I simply check if at least 5 data points are present in the diagram. If it is less, the flow ends here, it is 5 or more, the data will be stored. I have introduced this threshold to prevent accidentally overwriting a record in the database that already contained many chart data. Whereas when Node-RED is started, the chart contains by chance only one record which might have arrived before I have read all the records from the database. Check chartdata looks like this:

//only proceed if some chart values are already present
if(msg.payload[0].values.length < 5) {
    return null;
}
return msg;

Then I convert the JSON object stored in msg.payload to a string. This JSON object is used in an SQL statement within the node construct SQL statement:

var pl = msg.payload;
msg.payload = "UPDATE CHARTDATA SET CHARTDATA = '" + pl + "' WHERE CHART = 'Wind'";
return msg;

This SQL satement I then execute in the DashDB node.

Restore diagramm data

The ui_chart node has a second output, via which a trigger is sent when reloading of the diagram data becomes necessary. However, for me it has not been triggered, for example, when Node-RED was rebooted, so that no graph data has been loaded from the database. Therefore, I have built my own trigger, which fires only once at the start of Node-RED. To build this you take an inject node, which provides any value (I am using timestamp). Configure the node as follows:

Configuration of inject-node (Image source: Internet-of-Things.blog)

After the trigger has been fired, the flow waits 5 seconds (less could probably also work). This is because I could not access the database without a short wait. We have to give Node-RED a short time to establish the database connection. Then we read our previously stored diagram values ​​with the following SQL statement from the database

SELECT CHARTDATA FROM CHARTDATA WHERE CHART = 'Wind';

The result is returned in msg.payload.chartdata as a string. We convert the string into an JSON object and place it in msg.payload where it is expected by the diagram node. The parse chartdata node contains the following code:

msg.payload = JSON.parse(msg.payload.CHARTDATA);
return msg;

If you wired everything as in my example, you can now safely restart Node-RED or Node.js without having to sit in front of an empty diagram afterwards.

Node-RED line chart (Image source: Internet-of-Things.blog)

And here is the complete sample flow. Just copy and paste it into Node-RED via import> clipboard. You will have to adjust the configuration in the DashDB node to your database instance:

[
 {
 "id": "1463aac.751bfd5",
 "type": "function",
 "z": "4d244ff7.cda44",
 "name": "mps in kmh",
 "func": "msg.payload = Math.round(msg.payload * 3,6);\nmsg.topic = \"Böen\";\nreturn msg;",
 "outputs": 1,
 "noerr": 0,
 "x": 323.20001220703125,
 "y": 147.90000915527344,
 "wires": [
 [
 "17ab898e.5cb24e"
 ]
 ]
 },
 {
 "id": "d859540.d0528b",
 "type": "ui_chart",
 "z": "4d244ff7.cda44",
 "name": "Wind 24h",
 "group": "82d4ad11.96f7a8",
 "order": 4,
 "width": "12",
 "height": "4",
 "label": "Windgeschwindigkeit (km/h)",
 "chartType": "line",
 "legend": "true",
 "xformat": "%H:%M",
 "interpolate": "basis",
 "nodata": "",
 "ymin": "",
 "ymax": "",
 "removeOlder": 1,
 "removeOlderUnit": "86400",
 "x": 687.7000122070312,
 "y": 119.14999389648438,
 "wires": [
 [
 "53222927.1e6128"
 ],
 [
 "a196d223.48f058"
 ]
 ]
 },
 {
 "id": "53e2717c.2a2ea",
 "type": "dashDB in",
 "z": "4d244ff7.cda44",
 "dashDB": "",
 "service": "dashDB-if",
 "query": "",
 "params": "",
 "name": "",
 "x": 1409.25,
 "y": 109.34991455078125,
 "wires": [
 []
 ]
 },
 {
 "id": "659fc8c0.c1dbd",
 "type": "json",
 "z": "4d244ff7.cda44",
 "name": "",
 "x": 1019.5003051757812,
 "y": 111.15003967285156,
 "wires": [
 [
 "5d5faa4e.49d904"
 ]
 ]
 },
 {
 "id": "5d5faa4e.49d904",
 "type": "function",
 "z": "4d244ff7.cda44",
 "name": "construct SQL statement",
 "func": "var pl = msg.payload;\nmsg.payload = \"UPDATE CHARTDATA SET CHARTDATA = '\" + pl + \"' WHERE CHART = 'Wind'\";\n\n//{};\n//msg.payload.CHART = \"Luftfeuchte\";\n//msg.payload.CHARTDATA = pl;\nreturn msg;",
 "outputs": 1,
 "noerr": 0,
 "x": 1211.500244140625,
 "y": 109.35005187988281,
 "wires": [
 [
 "53e2717c.2a2ea"
 ]
 ]
 },
 {
 "id": "17ab898e.5cb24e",
 "type": "delay",
 "z": "4d244ff7.cda44",
 "name": "",
 "pauseType": "rate",
 "timeout": "5",
 "timeoutUnits": "seconds",
 "rate": "1",
 "nbRateUnits": "5",
 "rateUnits": "minute",
 "randomFirst": "1",
 "randomLast": "5",
 "randomUnits": "seconds",
 "drop": true,
 "x": 495.50006103515625,
 "y": 111.15000915527344,
 "wires": [
 [
 "d859540.d0528b"
 ]
 ]
 },
 {
 "id": "a196d223.48f058",
 "type": "dashDB in",
 "z": "4d244ff7.cda44",
 "dashDB": "",
 "service": "dashDB-if",
 "query": "SELECT CHARTDATA FROM CHARTDATA WHERE CHART = 'Wind';",
 "params": "",
 "name": "",
 "x": 695,
 "y": 189.1999969482422,
 "wires": [
 [
 "e3f01a4e.c6328"
 ]
 ]
 },
 {
 "id": "53222927.1e6128",
 "type": "function",
 "z": "4d244ff7.cda44",
 "name": "check chartdata",
 "func": "//only proceed if some chart values are already present\nif(msg.payload[0].values.length < 5) {\n return null;\n}\n\nreturn msg;",
 "outputs": 1,
 "noerr": 0,
 "x": 862.6002197265625,
 "y": 114.59998321533203,
 "wires": [
 [
 "659fc8c0.c1dbd"
 ]
 ]
 },
 {
 "id": "c586f172.035428",
 "type": "delay",
 "z": "4d244ff7.cda44",
 "name": "",
 "pauseType": "delay",
 "timeout": "5",
 "timeoutUnits": "seconds",
 "rate": "1",
 "nbRateUnits": "1",
 "rateUnits": "second",
 "randomFirst": "1",
 "randomLast": "5",
 "randomUnits": "seconds",
 "drop": false,
 "x": 529.9000457763673,
 "y": 195.99995727539067,
 "wires": [
 [
 "a196d223.48f058"
 ]
 ]
 },
 {
 "id": "e3f01a4e.c6328",
 "type": "function",
 "z": "4d244ff7.cda44",
 "name": "parse chartdata",
 "func": "msg.payload = JSON.parse(msg.payload.CHARTDATA);\nreturn msg;",
 "outputs": 1,
 "noerr": 0,
 "x": 860.4000854492188,
 "y": 184.3999481201172,
 "wires": [
 [
 "d859540.d0528b"
 ]
 ]
 },
 {
 "id": "b77c2b1d.9800b8",
 "type": "inject",
 "z": "4d244ff7.cda44",
 "name": "",
 "topic": "",
 "payload": "",
 "payloadType": "date",
 "repeat": "",
 "crontab": "",
 "once": true,
 "x": 388.2501220703125,
 "y": 198.45001983642578,
 "wires": [
 [
 "c586f172.035428"
 ]
 ]
 },
 {
 "id": "1dbc08c2.5e5707",
 "type": "function",
 "z": "4d244ff7.cda44",
 "name": "mps in kmh",
 "func": "msg.payload = Math.round(msg.payload * 3,6);\nmsg.topic = \"Wind\";\nreturn msg;",
 "outputs": 1,
 "noerr": 0,
 "x": 300.79998779296875,
 "y": 51,
 "wires": [
 [
 "c1c9bc77.aa31"
 ]
 ]
 },
 {
 "id": "c1c9bc77.aa31",
 "type": "delay",
 "z": "4d244ff7.cda44",
 "name": "",
 "pauseType": "rate",
 "timeout": "5",
 "timeoutUnits": "seconds",
 "rate": "1",
 "nbRateUnits": "5",
 "rateUnits": "minute",
 "randomFirst": "1",
 "randomLast": "5",
 "randomUnits": "seconds",
 "drop": true,
 "x": 482.29998779296875,
 "y": 70.60003662109375,
 "wires": [
 [
 "d859540.d0528b"
 ]
 ]
 },
 {
 "id": "82d4ad11.96f7a8",
 "type": "ui_group",
 "z": "",
 "name": "Wetter letzte 24 Stunden",
 "tab": "7c4a7d5b.6f0c64",
 "order": 2,
 "disp": true,
 "width": "12"
 },
 {
 "id": "7c4a7d5b.6f0c64",
 "type": "ui_tab",
 "z": "",
 "name": "Details Haus und Umgebung",
 "icon": "dashboard",
 "order": 2
 }
]
(Visited 5,121 times, 1 visits today)

Don't want to miss anything?

Newsletter Subscribe Content

* This field is required

About René Auberger 12 Articles
René is an IT Architect for Internet of Things, Industry 4.0, Analytics and Cognitive Computing. In his spare time he makes his house smarter step by step. In 2017 René founded the Internet-of-Things.blog. He is also frequently tweeting. View René's full profile...

4 Comments

  1. Great post, very helpfull. I'm able to save to a MySL database, but I'm not able to restore it. I do get data back from DB and it looks like this:

    object
    topic: "SELECT CHARDATA FROM `CHARTDATA` WHERE CHART="DEVTEMP""
    payload: array[1]
    0: object
    CHARDATA: "[{"key":"Température DEV","values":[[1505593970325,23.1],[1505594030635,23.1],[1505594033658,23.1],[1505594041230,23.1],[1505594061457,23.1],...

    A pipe to a dedicated graph doesnt work. Graph is not populated. Tried several JSON/paylaod extract with no luck. Any hint on what I have missed ? Thanks.

  2. You are missing a function node which converts the CHARTDATA string into a JSON object and puts this into msg.payload as follows:

    msg.payload = JSON.parse(msg.payload.CHARTDATA);

    In the example above this is done in the node "parse chartdata".

  3. Many thanks. I had tried this , but it yielded an error so I investigated and I finally found the good syntax for my flow :

    msg.payload = JSON.parse(msg.payload[0].CHARDATA);

    Works perfectly now ! thanks again

Leave a Reply

Your email address will not be published.


*


twenty four + = 30