# ETL Pipelines with Node-RED?

**URL:** <https://discourse.nodered.org/t/etl-pipelines-with-node-red/84347>\
**Category:** Share Your Projects\
**Created:** [6 January 2024 19:09 UTC](https://discourse.nodered.org/t/etl-pipelines-with-node-red/84347 "2024-01-06T19:09:33Z")\
**Posts on this page:** 20\
**Page:** 3

<div class="post-metadata">

**Author:** ![jsprenkle](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/jsprenkle/32/81249_2.png) [@jsprenkle](https://discourse.nodered.org/u/jsprenkle)\
**Post date:** [10 January 2024 19:34 UTC](https://discourse.nodered.org/t/etl-pipelines-with-node-red/84347/41 "2024-01-10T19:34:59Z")

</div>

> [@dceejay](#):
>
> I rather quickly went and ruined Nick's pure vision

Darn! Lots less expensive than most middleware. Thank you 😉

---

<div class="post-metadata">

**Author:** ![GogoVega](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/gogovega/32/71313_2.png) [@GogoVega](https://discourse.nodered.org/u/GogoVega)\
**Post date:** [10 January 2024 19:38 UTC](https://discourse.nodered.org/t/etl-pipelines-with-node-red/84347/42 "2024-01-10T19:38:50Z")

</div>

And today we find ourselves with contributions in every direction that no one even you could have imagined.

Wow, this tool has become super powerful. Thanks for that 😘

Did you have a garage at that time? 😂

---

<div class="post-metadata">

**Author:** ![gregorius](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/gregorius/32/73816_2.png) [@gregorius](https://discourse.nodered.org/u/gregorius)\
**Post date:** [10 January 2024 19:49 UTC](https://discourse.nodered.org/t/etl-pipelines-with-node-red/84347/43 "2024-01-10T19:49:13Z")

</div>

> [@paulkeates](#):
>
> Node-RED started as a MQTT 'explorer'

Is there something similar for Kafka? I remember putting together some code to view messages flowing through Kafka and a minimal interface for rewinding consumers. At the time, there were no visual tools for doing this.

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [11 January 2024 06:05 UTC](https://discourse.nodered.org/t/etl-pipelines-with-node-red/84347/44 "2024-01-11T06:05:21Z")

</div>

There is a kafka client for NR and looking at the speed you are able to produce things you have this available in no-time :')

There are [kafka explorers](https://www.kafkamagic.com/howto/query/) according to google.

---

<div class="post-metadata">

**Author:** ![gregorius](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/gregorius/32/73816_2.png) [@gregorius](https://discourse.nodered.org/u/gregorius)\
**Post date:** [11 January 2024 08:43 UTC](https://discourse.nodered.org/t/etl-pipelines-with-node-red/84347/45 "2024-01-11T08:43:44Z")

</div>

> [@bakman2](#):
>
> There are [kafka explorers](https://www.kafkamagic.com/howto/query/) according to google.

The ideas in that tool are very similar to what I put together but they definitely have a far better UI!

> [@bakman2](#):
>
> produce things you have this available in no-time

Well that's because Node-RED is so easily extended and there are so many JS packages 😉

---

<div class="post-metadata">

**Author:** ![aguida79](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/aguida79/32/87228_2.png) [@aguida79](https://discourse.nodered.org/u/aguida79)\
**Post date:** [11 January 2024 12:02 UTC](https://discourse.nodered.org/t/etl-pipelines-with-node-red/84347/46 "2024-01-11T12:02:17Z")

</div>

@gregorius, a web-based Flow Based Programming tool written in Go option is Project Flogo, [http://www.flogo.io/](http://www.flogo.io/), and in theory is suitable for ETL and streaming, but their community is really small compared to NodeRED, and is really complicated to get an answer when you have questions.

---

<div class="post-metadata">

**Author:** ![gregorius](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/gregorius/32/73816_2.png) [@gregorius](https://discourse.nodered.org/u/gregorius)\
**Post date:** [11 January 2024 12:23 UTC](https://discourse.nodered.org/t/etl-pipelines-with-node-red/84347/47 "2024-01-11T12:23:11Z")

</div>

Thanks for the tip 👍

I had a quick look at the [source code](https://github.com/TIBCOSoftware/flogo) but it hasn't been updated in the last two years - do you know whether it is actively maintained?

---

<div class="post-metadata">

**Author:** ![aguida79](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/aguida79/32/87228_2.png) [@aguida79](https://discourse.nodered.org/u/aguida79)\
**Post date:** [11 January 2024 12:51 UTC](https://discourse.nodered.org/t/etl-pipelines-with-node-red/84347/48 "2024-01-11T12:51:26Z")

</div>

They changed the GitHub repo, but did't update the page. The actual GitHub repo is this one:

> **[Project Flogo](https://github.com/project-flogo)**
>
> Ecosystem of opinionated event-driven capabilities to simplify building efficient & modern serverless functions and edge microservices - Project Flogo

And the last version is 1.6.7, from November 2023.

You can see a question about this in the old GitHub repo you mention:

> <https://github.com/TIBCOSoftware/flogo/issues/575>
>
> Can't see the updates on this repo. But the community seems alive: https://githu…b.com/project-flogo

---

<div class="post-metadata">

**Author:** ![gregorius](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/gregorius/32/73816_2.png) [@gregorius](https://discourse.nodered.org/u/gregorius)\
**Post date:** [11 January 2024 13:04 UTC](https://discourse.nodered.org/t/etl-pipelines-with-node-red/84347/49 "2024-01-11T13:04:49Z")

</div>

Thanks for the info & Sorry for not checking myself 😉

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [11 January 2024 17:22 UTC](https://discourse.nodered.org/t/etl-pipelines-with-node-red/84347/50 "2024-01-11T17:22:15Z")

</div>

Broken links on the website, mostly text/code based, Needs GO for some code.

---

<div class="post-metadata">

**Author:** ![striper](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/striper/32/81114_2.png) [@striper](https://discourse.nodered.org/u/striper)\
**Post date:** [11 January 2024 20:41 UTC](https://discourse.nodered.org/t/etl-pipelines-with-node-red/84347/51 "2024-01-11T20:41:04Z")

</div>

This is something I ran into today as Kafka "explorer":  
[https://github.com/obsidiandynamics/kafdrop](https://github.com/obsidiandynamics/kafdrop)  
It seems to be used a lot. Haven't tried it myself, but I will check it out soon.

---

<div class="post-metadata">

**Author:** ![gregorius](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/gregorius/32/73816_2.png) [@gregorius](https://discourse.nodered.org/u/gregorius)\
**Post date:** [11 January 2024 21:30 UTC](https://discourse.nodered.org/t/etl-pipelines-with-node-red/84347/52 "2024-01-11T21:30:03Z")

</div>

> [@striper](#):
>
> I ran into today as Kafka "explorer"

Lucky you, that looks like a pretty nice tool for Kafka - wish I had it back in the day 🙂

---

<div class="post-metadata">

**Author:** ![gregorius](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/gregorius/32/73816_2.png) [@gregorius](https://discourse.nodered.org/u/gregorius)\
**Post date:** [11 January 2024 23:00 UTC](https://discourse.nodered.org/t/etl-pipelines-with-node-red/84347/53 "2024-01-11T23:00:54Z")

</div>

Slightly back on topic, I have completed an initial "I am happy with it" version my ETL pipeline --\> [here](https://flowhub.org/f/c520d9da20ad7f1d). For that version, I have now created an initial - RFC version of a bunch of nodes that help with doing [streaming](https://nodejs.org/dist/latest-v18.x/docs/api/stream.html) in Node-RED --\> the [pipestream](https://flows.nodered.org/node/@gregoriusrippenstein/node-red-contrib-pipestream) nodes.

My initial nodes were too specific, i.e., a streaming node that knows that the Zip file contains CSV. Instead the pipestream streaming nodes can unzip, untar, ungzip, csv, jsonl etc all as seperate nodes which are combined into a pipeline:

 ![Screen Shot 2024-01-11 at 23.51.15](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/3/435f53c556cc2a7564d16990e69c48a81f9dfede.png)

In the screenshot, the top flow retrieves and stores a `.jsonl.bz2` file using streaming, i.e., no data in memory (from web to disk without sending any messages!). The right flow streams the file from disk, unbzips the file and then parses the file using JsonL - all in a stream. What comes out at the end of the PipeEnd node are json objects, in a stream.

But wait, all these nodes are connected but they don't actually do anything? Yes, the PipeStart and PipeEnd "flow" is actually a kind of meta flow describing what the PipeEnd node should create as a pipeline. I know this isn't what Node-RED was meant to be but it can do it and all other nodes can be placed in between the stream nodes, there is no limit to what comes between a PipeStart and a PipeEnd.

But wait why not using the inbuilt CSV node and Json nodes etc? Because all the existing nodes cannot be integrated into a stream. They all need to be rebuilt to work as a scream. This is for example - don't quote me on this - not possible for Excel format since that's not a streaming format. CSV and JsonL are both streaming forms since they consist of independent lines that can be handled individually ... just as gzip, bz2 and zip are all streaming formats.

I encourage more work on streaming for Node-RED and any ideas and suggestions are very much welcome.

---

<div class="post-metadata">

**Author:** ![jsprenkle](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/jsprenkle/32/81249_2.png) [@jsprenkle](https://discourse.nodered.org/u/jsprenkle)\
**Post date:** [11 January 2024 23:24 UTC](https://discourse.nodered.org/t/etl-pipelines-with-node-red/84347/54 "2024-01-11T23:24:21Z")

</div>

Just out of curiosity what are you doing that needs streaming? Or nodes of any great size?

---

<div class="post-metadata">

**Author:** ![gregorius](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/gregorius/32/73816_2.png) [@gregorius](https://discourse.nodered.org/u/gregorius)\
**Post date:** [12 January 2024 00:06 UTC](https://discourse.nodered.org/t/etl-pipelines-with-node-red/84347/55 "2024-01-12T00:06:15Z")

</div>

Http request with 600mb file that gets passed to a write file node. That means a msg object with 600mb is generated between the http request node and the write file node. That causes NR to suffer.

So instead 600mb gets streamed to the file write directly from the http request without going through NR yet it's still built with NR.

This is quite normal for ETL pipelines were large datasets are pushed into date warehouses.

---

<div class="post-metadata">

**Author:** ![ralphwetzel](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ralphwetzel/32/53713_2.png) [@ralphwetzel](https://discourse.nodered.org/u/ralphwetzel)\
**Post date:** [12 January 2024 07:04 UTC](https://discourse.nodered.org/t/etl-pipelines-with-node-red/84347/56 "2024-01-12T07:04:21Z")

</div>

> [@gregorius](#):
>
> But wait, all these nodes are connected but they don't actually do anything? Yes, the PipeStart and PipeEnd "flow" is actually a kind of meta flow describing what the PipeEnd node should create as a pipeline. I know this isn't what Node-RED was meant to be but it can do it and all other nodes can be placed in between the stream nodes, there is no limit to what comes between a PipeStart and a PipeEnd.

Wow. Great concept! 👍

> [@gregorius](#):
>
> But wait why not using the inbuilt CSV node and Json nodes etc?

Are the standard nodes "good enough" to define the stream logic - for their particular share? If this was the case, it should be possible to (re)use their editor interface (to create & collect the logic), but not invoke their runtime code. We've done a similar thing for `node-red-mcu`. Following this path, one could integrate the streaming functionality seamlessly into NR - even red-triangle-ing nodes that cannot be part of a stream might be possible quite easily.

---

<div class="post-metadata">

**Author:** ![gregorius](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/gregorius/32/73816_2.png) [@gregorius](https://discourse.nodered.org/u/gregorius)\
**Post date:** [12 January 2024 09:34 UTC](https://discourse.nodered.org/t/etl-pipelines-with-node-red/84347/57 "2024-01-12T09:34:41Z")

</div>

> [@ralphwetzel](#):
>
> "good enough" to define the stream logic - for their particular share?

This is very good question, it goes to the heart of the unix philosophy and what is flow based programming and where are the boundaries for tools?

For those that don't know: [Unix philosophy](https://en.wikipedia.org/wiki/Unix_philosophy) says _do one thing and one thing only and do it well_. Flow based programming is the concept of an assembly line where each stage makes a minor modification to the thing on the assembly line. Each stage does the same thing but to different things that are moving along the assembly line.

Node-RED is the combination of those two concepts: data flows along the lines (errr wires) and gets modified by the rectangles (errr nodes) until the modified data flows out. Each node applies its _specific_ modifications to the data that passes through it.

What's the advantage of doing this way? Complexity is minimised because nodes have very specific and clear responsibilities, secondly focus is on data flows and not text describing algorithmic logic, i.e., code. (Amongst other advantages such better communication between techies and non-techies.)

Now the question comes: is the central responsibility of the CSV node to do all things CSV in every environment (msg based or stream based) or does adding stream support to the existing CSV node break the unix philosophy because the current CSV node _is_ a msg-oriented node and not designed for streaming applications?

CSV node being only a placeholder here but also a good example for the problems involved. So I'm using [fast-csv](https://github.com/gorenje/node-red-streaming/blob/45f037735cdaf0124c6c2891fafeb86792359d21/nodes/csvstream.js#L2) because that's the library that supports cvs streaming (at least the one I found). The library used by the current CSV node does not have that library nor streaming.

However I c&p'ed most of the [UI for the streaming CSV node](https://github.com/gorenje/node-red-streaming/blob/45f037735cdaf0124c6c2891fafeb86792359d21/nodes/csvstream.html#L45-L125) from the existing CSV node - basically to duplicate its functionality. But since the CsvStream node does not do output, I didn't take the UI for the output part.

I see just as many valid arguments for integrating streaming into the existing nodes, as there for saying that streaming should be done separately. For me though the best argument for doing it separately is that I can't go and modify the existing CSV node without hacking the core of Node-RED - remember the CSV node is [integrated](https://github.com/node-red/node-red/blob/ce5b6a8024533958f9ba68eb4df464e61c775bce/packages/node_modules/%40node-red/nodes/core/parsers/70-CSV.html) into NR.

Also integrating streaming into Node-RED would require a major rethink of the concept of Node-RED, after all, Node-RED is a pipeline just as streaming is a pipeline, so why aren't Node-RED flows by default streaming flows? Because Node-RED was built before the invention of streaming (or rather popularisation). And it doesn't really matter why or why not NR is not streaming by default, the point is that NR went down one road and streaming is a parallel road to that.

My use of "meta programming" a flow that describes a pipeline that gets executed in the PipeEnd node is a _hack_ it's not what NR is meant to be used for. That demonstrates that supporting streaming in NR should be well thought through since NR does support streaming but not in a Node-RED _way_. That for me would be another argument to keep it separated since it's possible but not in the intended way (i.e. the way Node-RED would or should do it.)

Sorry for the long rant but it's an important topic 🙂

---

<div class="post-metadata">

**Author:** ![ralphwetzel](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ralphwetzel/32/53713_2.png) [@ralphwetzel](https://discourse.nodered.org/u/ralphwetzel)\
**Post date:** [12 January 2024 10:05 UTC](https://discourse.nodered.org/t/etl-pipelines-with-node-red/84347/58 "2024-01-12T10:05:37Z")

</div>

> [@gregorius](#):
>
> Sorry for the long rant but it's an important topic 🙂

I don't understand it as a rant - rather than a valuable exchange.

> [@gregorius](#):
>
> or me though the best argument for doing it separately is that I can't go and modify the existing CSV node without hacking the core of Node-RED

My view: You don't need to hack anything in the core at all. `PipeStart` is the entry point to the streaming flow. If you never call `send()` there, no (standard) message will run down this flow. The _hacking_ part concentrates on getting the relevant logic from the nodes in-between `PipeStart` & `PipeEnd` and execute this in `PipeEnd`. That's - as you said - _meta_ programming at its best...

> [@gregorius](#):
>
> That for me would be another argument to keep it separated since it's possible but not in the intended way (i.e. the way Node-RED would or should do it.)

For me, one of the beautiful aspects of Node-RED lays in the fact that using it for tasks beyond what is/was intended to, is [part of its earliest legacy](https://discourse.nodered.org/t/etl-pipelines-with-node-red/84347/40). This might collide with the Unix philosophy - yet opens endless options, like the one you're exploring here. The key for success is - again my view - to make those as accessible for potential users as possible.

Finally - it's your call. It's not to be questioned if you decide it's _better_ to use dedicated nodes...

---

<div class="post-metadata">

**Author:** ![gregorius](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/gregorius/32/73816_2.png) [@gregorius](https://discourse.nodered.org/u/gregorius)\
**Post date:** [12 January 2024 12:54 UTC](https://discourse.nodered.org/t/etl-pipelines-with-node-red/84347/59 "2024-01-12T12:54:34Z")

</div>

> [@ralphwetzel](#):
>
> _hacking_ part concentrates on getting the relevant logic from the nodes in-between

That's what I meant, I would have to extend the existing CSV node with this logic ... that would be in the core ... then I'd wait until a release was made. I'd do that if everything was sorted and everyone would be happy with making a core change to Node-RED and having it "natively" support streaming.

Alternative is that PipeEnd has _all_ the logic builtin, i.e., it has the steaming logic for the CSV, JSON, Yaml ... etc nodes. This would bloat the PipeEnd node and also completely break the unix philosophy - which is also stateless independent codebases.

> [@ralphwetzel](#):
>
> beautiful aspects of Node-RED lays in the fact that using it for tasks beyond what is/was intended to

110% this! Can only encourage everyone to think of NR as a bunch lines connecting rectangles! Not as wires and nodes ... a step back can give a different perspective.

> [@ralphwetzel](#):
>
> This might collide with the Unix philosophy

Ironically the Unix philosophy encourages this since base applications (i.e. ed, more, awk, ...) rarely get touched or extended, so others build things such as gawk, less, sed[1][2]. I think a classic problem is knowing where to draw the line - that's where it gets hard!

For example, I started with [web2disk](https://discourse.nodered.org/t/etl-pipelines-with-node-red/84347/25) but realised that it would soon be bloated because combining two responsibilities: http request and file writing. So I split that node up and made it more useful since the stream write to file can be used independently of the http request streamer.

Having said that, I think NR should not become too bloated with functionality that can be provided by external nodes. My thinking its better to improve or extend the _extendability_ of NR than to extend its _functionality_. And this is also something that Linux is facing, hence I tend to compare Linux and NR - for better or for worse.

> [@ralphwetzel](#):
>
> it's your call. It's not to be questioned if you decide it's _better_ to use dedicated nodes...

Standing on the shoulders of giants: sure I can do what I want but better is that everyone has a say and it's clear what the ideas are. I put together a prototype to itch a scratch but perhaps there is a better way to do it or more scratches that can be itched. (scratch == use case ;))

Ironically though, having now learnt that sed is the _streaming ed_, it does seem clear that Unix would use dedicated nodes for streaming! 😉

[1] = "sed was based on the scripting features of the interactive editor ed" - [wikipedia](https://en.wikipedia.org/wiki/Sed)  
[2] = btw _stream_ ed - so unix in fact created separate applications to support streaming!

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [12 January 2024 14:25 UTC](https://discourse.nodered.org/t/etl-pipelines-with-node-red/84347/60 "2024-01-12T14:25:09Z")

</div>

> [@gregorius](#):
>
> Alternative is that PipeEnd has _all_ the logic builtin

I was wondering whether it would be better to move the actual process from PipeEnd to a config node. Then every stream capable node would have a reference to that config node and, by definition, anything that didn't have such a reference would not be a streaming node?

I think the logic of that would be more obvious to editors and flow designers. It also puts the actual processing firmly into an obvious place.

> [@gregorius](#):
>
> Ironically the Unix philosophy encourages this since base applications (i.e. ed, more, awk, ...) rarely get touched or extended, so others build things such as gawk, less, sed[1][2]. I think a classic problem is knowing where to draw the line - that's where it gets hard!

Which is why, in the other thread I talked about a single _capability_ rather than a single _function_.

[Previous page](https://discourse.nodered.org/t/etl-pipelines-with-node-red/84347.md?page=2)

[Next page](https://discourse.nodered.org/t/etl-pipelines-with-node-red/84347.md?page=4)
