While default nodes are sufficient to upload small datasets to a database via Node-RED, they do not suffice when handling files of tens of megabytes, let alone gigabytes, which cannot fit in memory.
We take advantage of two special nodes:
- node-red-contrib-ui-upload, able to upload (using streaming) large files from a Node-RED Dashboard.
- node-red-contrib-chunks-to-lines able to convert large upload streams into text lines
For this example, we assume the input file is in CSV format – but it could be something else – and we use the default CSV node to parse the text lines.
For this example, we use a PostgreSQL database (with optional Timescale extension for better handling of time-series) – but it could be another database – thanks to the node-red-contrib-postgresql node.
- The ui-upload node is set to chunks of 256kB in binary mode. Our tests show that this provides good speed and stability.
- The chunks-to-lines is set to CSV node (for compatibility with the default CSV node), UTF-8 encoding (should be the same than the uploaded file), and outputting 4096 lines at a time. Inserting many lines at a time in database is much faster than one at a time.
- The CSV node is set to “first row contains column names”, and “output a single message (array)”. Obviously, the separator (comma by default) must be the same than your uploaded file.
- The postgres node is set to “receive query output”, in order to know when the insertion of a batch of lines in database is done and to trigger the next one.
- Adapt to your database parameters (e.g. database name, column names)
- Browse to the Node-RED Dashboard
- Click on the Prepare SQL table button to create a blank table in database
- In the Upload to SQL section, select a local CSV file on your computer (with columns: time, sensor, data). See sample.csv for an example of mini dataset.
- Wait for upload
- The number of uploaded lines appears in the Dashboard
- You can inspect your database using another tool