Implementing ETL flows with Node-RED

Are you looking for an easy Extract Transform Load (ETL) tool? Consider Node-RED. In an ever-changing world of data integrations, we found Node-RED to be a low-cost, reliable, and secure platform that is capable of moving millions of records.

Node-RED is a low-code programming platform for event-driven applications (particularly for IoT apps). In essence, it’s a Node.js-based application with a browser-based editor that makes it easy to wire together flows and develop the top-level application logic using a graphical user interface.

You won't typically find Node-RED in the lists of ETL tools. Yet, we have used it as our primary ETL tool since about two years ago, and I feel it deserves the consideration.

For me, Node-RED is a low-barrier flow-based development platform because:

• It uses fewer resources than other "low-code" ETL systems such as IBM DataStage or Pentaho Data Integration.
• It is easier to find JavaScript developers to do the work.

I would even go further and call it a platform for "pretty NodeJS applications." Don't underestimate "pretty" in software development! It directly translates to lower costs of the development lifecycle. Of course, the opposite is true as well: You can still end up with spaghetti code in Node-RED.

As in any software house, developer skills come first, technology second. The GUI does help new developers to get familiar with the code quicker and speeds up maintenance, but only if the flow was written well in the first place.

Moving to Node-RED as an ETL system

We moved to Node-RED from Pentaho Data Integration. Our typical small-integration Pentaho jobs used to take at least 5 minutes to complete and had to run on a permanent 32GB RAM in a Kubernetes pod instance. The first success we experienced was when the same ETL jobs implemented in Node-RED ran in seconds and on a couple hundred megabytes of RAM.

The key to our success was to move away from a monolith ETL architecture to the use of micro-pipelines, running the jobs as temporary container instances. Dockerized jobs immediately free up all resources once finished, provide full isolation, and are measurable (assessing the resource usage of a Pentaho job is quite challenging).

The following figure shows an example process section in Pentaho.

The following figure shows an example of a full process in Node-RED.

With Node-RED, developer productivity skyrocketed, version control is fully transparent, handovers are quicker, and troubleshooting has massively improved thanks to clear logging.

How to use Node-RED as an ETL tool

Node-RED only provides the data pipeline part of an ETL system. To successfully manage thousands of integrations, you will need to add a scheduler, a source of configurations, and a management user interface.

In our case, all these components were already available in our SaaS platform (called Your Learning). Our job scheduler runs as a service and starts individual jobs as Kubernetes deployments. It provides APIs for managing the jobs (primarily through the administrator user interface), plus logging and environment integration. To make the solution more robust, we added an automatic retry framework based on the job exit status.

Each data integration, or "connector" as we call it, has its own repository and produces its own Docker image that is registered in the job scheduler. A versioning policy ensures straightforward upgrades. Connector descriptors provide configuration templates of all inputs that are expected from the configuration service. As such, any connector version can be reused among various tenants of our SaaS product.

The job management user interface then pulls all of it together and provides easy configuration, management, and monitoring.

The following figure shows an example of our job management interface.

Why is Node-RED so attractive for us?

I could say that we are able to create a production-level integration in hours. I could argue that our developers prefer Node-RED against Pentaho. I could say that the low footprint and large libraries of Node.js trump Pentaho any time. But the real hidden Node-RED gem is the ability to add custom nodes as reuseable modules.

An individual skilled developer can easily make any data pipeline work in their own tool of choice. But working as a team, it's immensely valuable to be able to drag and drop a ready-made generic node. Our nodes have not just been battle-tested but also provide an easy graphical configuration of key functionalities that are important for our business. And because we own them all, we can constantly improve our portfolio and adapt to changes easily. If there's no node for a given purpose, a function node is used (with full flexibility and libraries available in Node.js). The second time we need the same code, we create a new node or extend an existing one.

These are some examples of nodes we have collected over the past two years:

• File management nodes -- Read, convert, stream, merge, create, decrypt, decompress files (JSON, CSV, TSV, GZ, ZIP)
• Utility nodes -- Reliable integrations using re-tryable API calls
• Storage nodes -- Work with the IBM Db2 pool of connections
• Configuration nodes -- Inject configurations into a flow and exit data integration with formatted output and error handling
• Services nodes -- Work with our product-specific APIs, calculate deltas, bulk uploads

It's the best example of code reuse in its “prettiest” form.

The following figure shows an example of our custom node configuration.

It is best to document the nodes well, set sensible defaults, and make iterative changes rather than shoot for perfection at the start. We took extra care to make the logging minimal but clear and consistent so that the job output log reads well.

Key implementation considerations

From our experience, these are key implementation considerations:

• Set up back pressure
• Implement unit testing
• Develop locally using Docker

Set up back pressure to fight out-of-memory errors

It's far too easy to get out of memory when working with JSON arrays in Node.js. Creating back pressure (controlling the flow rate such as by streaming data) is critical to processing larger data sets. Node-RED offers some limited capability out of the box, such as the rate-limit node or linked loops. Embedding RED.events listeners (an internal Node-RED functionality) into our custom nodes proved to be the cleanest option for us for creating back pressure. And, of course, you can also use any publish-subscribe library of your choice (such as pubsub-js).

Implement unit testing

The easiest way to test your flows is to branch the flows where data is retrieved and exported, then provide static checking of the transformations that happen in between. A more advanced way would be to provide mock configurations and inputs directly in your custom nodes so flows don't have to split unnecessarily. Either way, decide on your test mode trigger convention early in the process, ideally as an attribute in the global space.

Develop locally using Docker

Working with a Docker container enables us to be sure that our locally developed code behaves exactly the same in the test or production Kubernetes cluster. Developers start the Node-RED instance in a Docker container with source directories mapped to the local file system.

Limitations of Node-RED as an ETL tool

The key limitation of Node-RED is that code reviews are difficult. Pull requests are hard to verify because all application code is embedded in a one-line JSON file. Initially, we have partially addressed this issue by using an extension that keeps the file format as YAML. This year I wrote an extension that splits the JSON file into individual JavaScript files. More complex changes still require a reviewer to run the application but static code analysis tools, like SonarQube, can now be used to check your source code.

I should add that before V3 of Node-RED, there was no code linting provided by the UI. Make sure you start with the latest version where you can use the built-in formatting function when you write code in your nodes. Linted code is an important practice to produce more maintainable code.

What about “xyz” platform?

Data pipelines are a massive industry, and there were a lot of alternatives from big data processors to smaller systems that we evaluated before we made our decision to use Node-RED as our ETL tool. These ETL systems are what we came up with in our initial research:

ETL as a service:

• AWS Glue, Amazon Kinesis Data Streams, IBM Cloud Code Engine

Java-based ETL systems:

• Logstash, Apache NiFi, Pentaho, Talend, Oracle Data Integrator
• Alooma, Blendo, Heva, Matillion, Xplenty, Microsoft SQL Server Integration Services, DataFlux
• Apache Spark (Scala JVM), Apache Flume, Apache Chukwa, Apache Flink, Splunk
• Apache Hive, Apache Pig, Cascading, Embulk

Python-based ETL systems:

• Apache Airflow, Singer, GitLab Meltano (Singer EL + DBT), Spotify Luigi, Dagster, Digdag, Bonobo, Mara

GO-based ETL systems:

• Reflow, SciPipe, Telegraf, Cadence (used by Uber), Flogo, Mozilla Heka (deprecated)

Other notable systems:

• C: Mozilla Hindsight, Rust: Vector
• Node.js: NoFlo
• Multi-language: Apache Beam (Java, Python, Go), Pachyderm (K8S cluster – pipelines in Python, GO, Scala)

Other approaches to ETL:

• ELT (Extract, Load, and Transform - makes use of SQL for transformations, for instance in the dbt framework)

Although this is not a comprehensive list, I hope it can be useful to any readers standing at a similar crossroads. If Python or GO skills are readily available in your group, I'd recommend a deeper look at Singer and Flogo, respectively.

You already know the result of our quest to find a lightweight, flexible, low cost-of-ownership ETL tool that fits our organization’s skills. Now after two years of production use of Node-RED as our ETL tool, we still think we made the right choice. This does not mean we won't change in the future. The most likely next shift will lean toward real-time processing, so fewer batch jobs. Whether it will influence our ETL choices is a question for another article and another time.

Summary

Node-RED deserves a spot in the lists of ETL tools. It can be used to extract data from any source, transform the data, and load it to any destination. And it does it well.

In this article, I tried to cover all key aspects of building Node-RED ETL pipelines and hopefully inspired some of you to give Node-RED a try. If you have any questions or would like to provide feedback, feel free to contact me at ondrej.lehota@uk.ibm.com.