Migrate Relational Database to InfluxDB

A question I'm regularly asked is "I'm currently using a relational database for my time-series data, but the data has got too large and the database is going too slow, so how to I migrate all my historic data to InfluxDB?".

Previous blog posts here have introduced Node-RED. Node-RED is designed to link things together without needing lots of coding, and we can use a Node-RED flow to read data from a SQL source and write to InfluxDB.

As a simple example of this, let's take my table of Xiaomi sensors, and see how to extract that data from MySQL. This isn't the best example, because it's static reference data, but the concept is identical for time-series data.

This is the data in my Xiaomi table:

I need to create a SELECT statement to read the data, run it against that table, format it to be time-series friendly, and write to InfluxDB.

This is the flow I created to do that with this data:

The first node is just a button to start the migration process.

The second node contains the SELECT statement I'll apply to the database:

I want to apply that SELECT statement to my MySQL database. For this, I edited the MySQL config to accept connections from my LAN. The MySQL node in Node-RED takes the msg.payload of the flow (my SELECT statement) and sends it to the database:

It returns an array of objects containing the selected data:

The Node-RED InfluxDB node requires data to be formatted in a different way, so the "Parse & Translate" function step does this:

 msg.precision = "ms";  
   
 $measurement = "xiaomi_devices";  
   
 var data = msg.payload;  
 // The response to the MySQL query is an array of objects.  
 // Each object is a returned row.  
 // Each element in the bject is a field value  
   
 // There are many ways to transform the data. I don't know which  
 // is computationally efficient.  
 // Choices include:  
 //  Use a JSON node to convert the objects into strings and deal with them from there  
 //  Use a "Change" node and put a JSONata expression in there  
 //  Use an array iterator  
 var output = [];  
 for (var i in data)  
 {  
    var $id = data[i].id;  
    var $did = data[i].did;  
    var $name = data[i].name.replace(/\s+/g, '');  
    var $type = data[i].type.replace(/\s+/g, '');  
    var $room = data[i].room.replace(/\s+/g, '');  
      
    var timestamp = data[i].timestamp;  
    var $msec = Date.parse(timestamp);  
      
   output.push({  
     measurement:$measurement,  
     fields:{  
       did:$did  
     },  
     tags:{  
       id:$id,  
       name:$name,  
       type:$type,  
       room:$room  
     },  
     timestamp:$msec  
   });  
 }  
 msg.payload = output;  
 return msg;  

Note that this isn't a particularly good example of data to be transformed into time-series, because it's not actually providing metrics or events. I selected "did" as the column to translate into a value, and all other columns become tags.

The data is transformed into this:

Finally, the "Batch write" node sends the data to my database:

Has the data reached InfluxDB? Let's have a look:

Success! So this another use case for Node-RED -> InfluxDB