While working with [Cube.js framework](https://github.com/statsbotco/cube.js) we've seen a lot of diffrent ETL tools used by data engineers nowadays. Most of them require writing code. But there are some  visual ETL you can try as well. We asked Dmitry Dorofeev, Head of R&amp;D at Luxms Group, to tell us about his experience with comparing Apache NiFi and Streamsets.

***

Our team at [Luxms Inc.](https://www.luxms.com/) has recently faced a boring data integration problem: when some data is stored in Hadoop, some in Oracle, and a little bit is in Excel. The goal was to ETL all that data into Greenplum and finally provide some BI on top of it. 

We quickly found 2 mainstream open source ETL projects: Apache NiFi and Streamsets, and it seemed an easy task to choose one product out of the two. **In no way was it easy.** I know that better than anyone since I was responsible for the product evaluation and the final choice. In this post, I’d like to share my experience and maybe save days of your life.

<i>Spoiler:</i> There is no silver bullet. Apache NiFi is not necessarily better than Streamsets, nor Streamsets better than NiFi. Everything has its pros and cons. This post is my personal experience with these tools as a novice user without any introductory training.

## Dataflow Programming

Programmers, analysts, and even managers often draw a box and arrow diagram to illustrate some flows. You can even use these boxes and arrows to create programs. We can track such attempts back to the 1960s when the [Dataflow Programming](https://en.wikipedia.org/wiki/Dataflow_programming) paradigm was born in MIT.

_[ETL Tools List: Overview &amp; Pricing](https://statsbot.co/blog/etl-tools-list/)_

Today, we have tens of Dataflow Programming tools where you can visually assemble programs from boxes and arrows, writing zero lines of code. Some of them are open source and some are suitable for ETL.

Yes, you don’t have to know any programming language. You just use ready-made “processors” represented with boxes, connect them with arrows, which represent exchange of data between “processors,” and that’s it. 

> There are three main types of boxes: sources, processors, and sinks. Think Extract for sources, Transform for processors, and Load for sinks.

Almost anything can be a source, for example, files on the disk or AWS, JDBC query, Hadoop, web service, MQTT, RabbitMQ, Kafka, Twitter, or UDP socket.

A processor can enhance, verify, filter, join, split, or adjust data. If ready-made processor boxes are not enough, you can code on Python, Shell, Groovy, or even Spark for data transformation.

Sinks are basically the same as sources, but they are designed for writing data.

## Apache NiFi vs StreamSets

When we faced yet another customer with complicated ETL requirements I decided to try visual dataflow tools. Visual might be attractive even if you use [Singer](http://www.singer.io), [data build tool](https://www.getdbt.com/), or other handy open source ETL tools, right?

Luckily, there are two open source visual tools with the web interface: [Apache NiFi](https://nifi.apache.org/) and [StreamSets Data Collector](https://streamsets.com/products/sdc) (SDC). NiFi was donated by the NSA to the Apache Foundation in 2014 and current development and support is provided mostly by Hortonworks. SDC was started by a California-based startup in 2014 as an open source ETL project available on [GitHub](https://github.com/streamsets/datacollector-oss). The first release was published in June 2015.

Both products are written in Java and distributed under the Apache 2.0 license.

Here are some stats from GitHub for early 2018:

<table>
	<tr>
		<th>Metric</th>
		<th><a href="https://github.com/apache/nifi">Apache NiFi</a></th>
		<th><a href="https://streamsets.com/products/data-collector-engine/">StreamSets</a></th>
	</tr>
	<tr>
		<td>First release year</td>
		<td>2007</td>
		<td>2015</td>
	</tr>
	<tr>
		<td>Forks</td>
		<td>783</td>
		<td>914</td>
	</tr>
	<tr>
		<td>Releases</td>
		<td>57</td>
		<td>113</td>
	</tr>
	<tr>
		<td>Stars</td>
		<td>811</td>
		<td>405</td>
	</tr>
	<tr>
		<td>Version</td>
		<td>1.5</td>
		<td>3.1.0.0</td>
	</tr>
</table>

## Architecture and features

Both tools encourage creation of long-running jobs which work with either streaming data or regular periodic batches. You can create manually managed jobs, but they might be tricky to set up. This is the greatest surprise and mind-shifting feature I personally had with these tools.

### Apache NiFi

Apache NiFi has a well-thought-out architecture. Once data is fetched from external sources, it is represented as **FlowFile** inside Apache NiFi dataflows. FlowFile is basically original data with meta-information attached to it. You can easily process not only CSV or other record-based data, but also pictures, videos, audio, or any binary data.

![](https://media.graphcms.com/w4DLooFSLOBsZMtxeauy")

A processor usually will have 3 outputs:

- Failure. If a FlowFile cannot be processed correctly, the original FlowFile will be routed to this output.
- Original. Once an incoming FlowFile has been processed, the original FlowFile is routed to this output.
- Success. FlowFiles that are successfully processed will be routed to this relationship.

You can terminate outputs with checkboxes, so Apache NiFi will ignore terminated outputs and will not send any FlowFiles there.

Another handy feature is **Process Groups**. When a dataflow becomes complex, you can combine your dataflow elements into Process Group, which is graphically represented in the UI in the same way as standard processors. It behaves as a processor, so you can build very complex dataflows recursively.

**Controller Service** is something that exists outside of your dataflows, but provides some useful information for your processors. It might be SSL certificates, JDBC connection and pool settings, schema definition, and so on. The idea is that rather than configure this information in every processor that might need it, the controller service provides it for any processor to use.

Processors are connected with... well, connections. Usually connections are just arrows, but not so with Apache NiFi. Every connection arrow has a small widget attached to it, representing a queue with back pressure and can be configured individually.

![](https://media.graphcms.com/5hGEEssaT6WuQOMlKd1p)

For example, if the LogAttribute processor becomes slow or freezes for some reason, FlowFiles generated by the GenerateFlowFile processor will be queued in the connection. After some time, back pressure will pause the GenerateFlowFile processor until the queue goes below the configured threshold.

![](https://media.graphcms.com/BH5LMEAvSnGgfNkmkHW9")

If you are not yet impressed, how about different queue policies like FIFO, LIFO, and others you can apply to queues in connections?

**Data Provenance** is a Big Brother service which records almost everything in your dataflows as they process data. This is very handy, as you have recorded history of how your dataflow performed, including saved content of the FlowFiles. But that comes with a price, you should have enough disk space to keep the required backlog of provenance data. 

Even with these awesome features and great architecture, I was not very comfortable with the Apache NiFi user interface. It is definitely usable, but not sexy.

### StreamSets Data Collector

Then, I tried Streamsets. 

![](https://media.graphcms.com/kSAtWIITHGwHibqCWkUv)

Processors in Streamsets exchange **records**. That means that everything you ingest into Streamsets is converted automatically into the standard record-oriented format and all processors can handle it as a stream of records. There are no queues in between processors, at least, they are not represented visually, like we saw it in Apache NiFi.

One nice thing about **Streamsets is that it can process binary data**. Some sources, such as Kafka Consumer, can read messages from the Kafka topic and pass them to other processors or external systems without parsing the structure of the binary message into the record format. This allows us to forward the efficient data to some other destination with minimum overhead. 

The more powerful option is the [whole file data format](https://streamsets.com/documentation/datacollector/latest/help/index.html#datacollector/UserGuide/Data_Formats/WholeFile.html#concept_nfc_qkh_xw), supported by several origins, including S3, directory, FTP, and more. With the whole file format, the file is not parsed, but file metadata and a reference to the content is sent along the pipeline. Processors can optionally act on the content – script evaluators and custom processors can get an input stream to the content. But in the default case, once the whole file record arrives at the destination, the data is streamed directly from its source.

Even though there are some complaints about lack of binary data support in Streamsets, the whole file support has been there since version 1.6.0.0, released in September 2016. 

In Apache NiFi the same processor **should have different versions of itself to handle different formats**. One version for CSV, one for JSON, and another for Avro, for example. You might guess that it is not very user and developer friendly. This was addressed in Apache NiFi 1.5, where most processors are using the Avro format, so you should convert to Avro early and it will be almost the same experience as in Streamsets after that.

> To change processor settings in Apache NiFi you must stop the processor, while in Streamsets you must stop the whole dataflow.

That means that you always start your dataflow from the beginning after you make any changes in it with Streamsets. With Apache NiFi you have a chance to stop a misbehaving processor, fix it, and start again. Hopefully, queued FlowFiles will be sent to the fixed processor and you will not miss the data.

But that doesn’t mean that Streamsets dataflows are harder to debug. Actually it is easier, you have a nice-looking live dashboard displaying a lot of statistics for every processor while your dataflow is running. Errors are cleanly presented as red numbers on the processor icon and you can see individual errors for every faulty record with a mouse click. You may even put record filters on the connections between processors to inspect records in question. Filters can be applied while your dataflow is running, so I used it as live debugging tool.

Streamsets has 4 processor types:

- Origins: they get data from the external sources. You may have only one Origin Processor in your dataflow.
- Processors: data transformers.
- Destinations: they save data to the external systems or files.
- Executors: they process events, generated by other processors.

Some of the Streamsets processors may generate events, including errors. You should use special processors called **Executors** to handle that. For example, there is Email Executor, which can send emails when an error has occurred. 

I am definitely more happy with the clean Apache NiFi architecture with just Processors and Controller Services, but the Streamsets design is also fine and can be quickly picked up.

## UI

### Apache NiFi

There is not much to say about the Apache NiFi UI. It feels spartan, and it is very easy to follow, thanks to the great architecture with minimum concepts. Probably the only drawback I discovered is that Apache NiFi will not autosize text fields for your long SQL queries, so you will have to manually resize popup text fields every time you want to edit it.

![](https://media.graphcms.com/4kwaYmwVTvCsZkeVCthA)

### Streamsets

Streamsets has a more attractive UI, but it is not perfect as well.

The first thing I quickly get annoyed with is the absence of Controller Services, especially for JDBC settings. You need to fill in all JDBC settings for every processor that reads data from the same JDBC source. There is just no user-friendly way to reuse such information.

Before you can run your dataflow, Streamsets will check each processor inside your dataflow to make sure all processors are correctly configured. **It sounds like a good thing, and it helped me sometimes, but other times – harmed me.** In Apache NiFi you can have disconnected processors and I usually leave them so for debugging purposes. In Streamsets you can not do the same, since all the processors must be connected to make dataflow pass validation.

Another annoyance is that you can not select several processors at once. At least I was unable to do so. Moving a dozen processors and reorganizing them one by one on the screen can make you mad.

Streamsets has syntax highlighting for SQL which is a nice feature, but not always useful. Our data engineer creates heavy SQL queries which can easily be a hundred lines long. The syntax highlighting process becomes slow and that results in another annoyance. If you edit the last lines of the long SQL query the caret unexpectedly moves to the beginning and what you type appears on the first line.

## Conclusion

I made a very brief introduction to Apache NiFi and Streamsets. They have plenty of useful features not covered in this blog post. Even if you do not find the required built-in or third-party processors, you can always use Python, Javascript, R, or even Apache Spark to program your complex data transformation logic in the Apache NiFi or Streamsets dataflows.

Both Apache NiFi and Streamsets are mature, open source ETL tools. They have very similar functionality and the only way to make a concise choice is to try both! That’s what I did. Even after 3 months of running both products I can not see a clear winner. 

For me, live monitoring is the single feature in Streamsets that outweighs all its small glitches. 

### Apache NiFi

Pros: 

- Clean and well-thought-out implementation of the dataflow programming concept
- Can handle binary data
- Data Provenance

Cons:

- Spartan User Interface
- No live monitoring/debugging features with per-record statistics

### Streamsets

Pros:

- Live monitoring/debugging features with visual per-record statistics for every processor
- Sexy UI
- Well-suited for record-based data and streaming

Cons:

- You need to stop the whole dataflow to edit a single processor configuration
- No reusable JDBC configuration for processors

Dmitry Dorofeev

Extract, Load, Transform (ETL)

Choosing between mainstream open source ETL projects

Open Source ETL: Apache NiFi vs Streamsets

<i>We're developing an open source framework, which helps to run transformations in the data warehouses - <a href="https://github.com/statsbotco/cube.js">Cube.js</a></i>

ETL stands for Extract, Transform, Load. It has been a traditional way to manage analytics pipelines for decades. With the advent of modern cloud-based data warehouses, such as BigQuery or Redshift, the traditional concept of ETL is changing towards ELT – when you’re running transformations right in the data warehouse. Let’s see why it’s happening, what it means to have ETL vs ELT, and what we can expect in the future.

<h1>ETL is hard and outdated</h1>

ETL arose to solve a problem of providing businesses with clean and ready-to-analyze data. We remove dirty and irrelevant data and transform, enrich, and reshape the rest. The example of this could be sessionization – <a href="https://statsbot.co/blog/event-analytics-define-user-sessions-sql/">the process of creating sessions out of raw pageviews and users’ events</a>.

ETL is complicated, especially the transformation part. It requires <strong>at least several months</strong> for a small-sized (less than 500 employees) company to get up and running. Once you have the initial transform jobs implemented, never-ending changes and updates will begin, because data always evolves with business.

The other problem of ETL is that during the transformation we <strong>reshape data into some specific form</strong>. This form usually lacks some data’s resolution and does not include data which is useless for that time or for that particular task. Often, “useless” data becomes “useful.” For example, if business users request daily data instead of weekly, then you will have to fix your transformation process, reshape data, and reload it. That would take a few weeks more.

> The more data you have, the longer the transformation process is.

The transformation rules are very complex, and even if you have only a few terabytes of data, <b>loading time can take hours</b>. Given the time it takes to transform and load a big dataset, your end users will almost never get fresh data. “Today” will mean today last week, and “yesterday” – a week ago yesterday. Sometimes it takes several weeks or months to get a new update to rollup.

To summarize some of the cons of ETL:
<ul>
 	<li>ETL is expensive to implement, especially for small and medium businesses.</li>
 	<li>ETL is expensive to maintain.</li>
 	<li>ETL eliminates access to raw data.</li>
 	<li>ETL is time consuming. Users have to wait for transformation to be finished.</li>
</ul>

<h1>Why have we been doing it for decades?</h1>

A prior generation of data warehouses weren’t able to work with the size and complexity of raw data. So, we had to transform data before loading and querying it.

<aside><h2 class="ui header"><a href="https://statsbot.co/blog/data-collection-tools/">Data Collection Tools for Events Analytics</a></h2></aside>

The latest advances in database technologies made warehouses much faster and cheaper. Storage is getting more accessible all the time, some data warehouses even separate pricing for storage and computation. For example, BigQuery <a href="https://cloud.google.com/bigquery/pricing">storage pricing</a> is quite cheap and you can just store all your raw data there.

The most important changes in recent years to data warehouses, which made the comparison of ETL vs ELT possible:
<ul>
 	<li><strong>Optimized for analytical operations.</strong> Modern analytical warehouses tend to be columnar and optimized for aggregating and processing huge datasets.</li>
 	<li><strong>Cheap storage.</strong> No worries about what to store, you can just dump all your raw data into a warehouse.</li>
 	<li><strong>Cloud based.</strong> It scales infinitely and on-demand, so you can get the performance you need the moment you need it.</li>
</ul>

<h1>ETL vs ELT: running transformations in a data warehouse</h1>

What exactly happens when we switch “L” and “T”? With new, fast data warehouses some of the transformation can be done at query time. But there are still a lot of cases where it would take quite a long time to perform huge calculations. So instead of doing these transformations at query time you can perform them in the warehouse, but in the background, after loading data.

<a href="https://statsbot.co/form-trial?utm_source=statsbotblog&amp;utm_medium=article&amp;utm_campaign=elt"><img src="https://media.graphcms.com/h0z3UKZcT7CuLBe3vx0t" /></a>

Once raw data is loaded into a warehouse, heavy transformations can be performed. It makes sense to have <strong>both real-time and background transformations in the BI platform</strong>. Users consume and operate on the business definitions level when querying data, and BI is either performing transformation on-the-fly or querying data already transformed in the background.

> This approach gives flexibility and agility for development of a transformation layer.

<aside><h2 class="ui header"><a href="https://statsbot.co/blog/postgres-to-bigquery-etl/">Loading Terabytes of Data from Postgres into BigQuery</a></h2></aside>

Software engineers nowadays deploy several times a day and praise continuous delivery. The same principle should be adopted for how we approach a transformation. If metric definition changes or some new data is required, one can easily make this changes in hours, not weeks or months. It is especially valuable for fast-growing startups, where changes happen daily and data teams have to be flexible and agile to keep up with product development and business needs.

As data warehouses advance more and more, I’m sure we will see how query time transformations will entirely replace background transformations. Before that happens, we can run some transformations in the background with ELT. Since they are already SQL based and run in the data warehouses, the final switch would be easy and painless.

<em>Statsbot is designed to work with raw data stored in modern data warehouses and run both query time and background transformations. Don’t hesitate to <a href="https://statsbot.co/?utm_source=statsbotblog&amp;utm_medium=article&amp;utm_campaign=elt">contact us and try it for free</a>.</em>

Artyom Keydunov

How the evolution of data warehouses is changing ETL

ETL vs ELT: Considering the Advancement of Data Warehouses

Despite the fact that an ETL task is pretty challenging when it comes to loading Big Data, there’s still the scenario in which you can load terabytes of data from Postgres into BigQuery relatively easy and very efficiently. This is the case when you have a lot of immutable data distributed in tables by some timestamp. For example, a `transactions` table with a `created_at` timestamp column. BigQuery and Postgres have great tools in order to do this pretty fast and conveniently.

<h1>Preparing Postgres Tables</h1>

<p>BigQuery <a href="https://cloud.google.com/blog/products/bigquery/inside-capacitor-bigquerys-next-generation-columnar-storage-format" target="_blank" rel="noopener">Capacitor</a> storage format, as many other Big Data formats, is optimized for a one-time write of an entire table. To distribute data between tables, BigQuery heavily relies on the <a href="https://cloud.google.com/bigquery/docs/querying-wildcard-tables" target="_blank" rel="noopener">wild card tables</a> pattern. Typical usage is to create tables with names suffixed by some field value. Usually, date suffixes are used for this purpose. For example, the table `transactions20180301` can contain transactions created on March 1, 2018. This approach allows for both the creation of very efficient storage on the BigQuery side, and for ease of loading the data source on the storage side.</p>

In order to implement this loading approach we need to ensure we have the right indexes in the Postgres database before we start loading processes from Postgres into BigQuery. In order to create this, you can use psql to connect to your database. Let’s create an index for a `created_at` timestamp in the `transactions` table. Assuming we’re dealing with an ubuntu machine that hosts the db instance, it can be as simple as:

```
$ sudo su - postgres
$ psql yourdbname

yourdbname=>create index transactions_created_at on transactions (created_at);
```

Index creation can take a while if you have a lot of data. You can track process execution using `top` utility. You should repeat index creation for each table you’re going to load into BigQuery.

<h1>Loading Data into BigQuery</h1>

To load data into BigQuery we’re going to use BigQuery CLI, which is a very versatile tool. You can install it using <a href="https://cloud.google.com/sdk/docs/#deb" target="_blank" rel="noopener">these</a> instructions. As we’re on Linux, we’ll be using bash script in order to perform all the work. I assume BigQuery CLI is installed and authorized.

Let’s create `bigquery-upload.sh` and add the following function in order to upload a single day from a specific table:

```
#!/bin/bash

function upload_day {
  table=$1
  sel=$2
  day=$3
  next_day=$(date -d "$day+1 days" +%Y-%m-%d)
  bq_suffix=$(date -d "$day" +%Y%m%d)
  echo "Uploading $table: $day..."
  psql  -c "\\copy (select $sel from $table where created_at >= '$day' and created_at < '$next_day') TO '$table-$day.csv' WITH CSV HEADER"
  gzip $table-$day.csv
  bq load --allow_quoted_newlines --project_id  --replace --source_format=CSV --autodetect --max_bad_records 100 .$table$bq_suffix $table-$day.csv.gz
  rm $table-$day.csv.gz
};
```

This function has three arguments: table, columns for selection, and date to upload. As you can see, it uses the `\copy` operation to download a CSV from Postgres and then compresses it. BigQuery docs says loading of a compressed CSV is slower than uncompressed, but uploading uncompressed data almost always seems slower.

You can call this function as simply as adding a line at the end of the script:

```
upload_day 'transactions' '*' '2018-03-01'
```

This will create the `transactions20180301` table with one day of data. To automate the uploading process we can introduce another function:

```
function upload_table {
  t=$1
  s=$2
  start_date=$3
  end_date=$4
  while [ "$start_date" != "$end_date" ]; do
       upload_day "$t" "$s" "$start_date"
       start_date=$(date -d "$start_date+1 days" +%Y-%m-%d)
  done
}
```

This `upload_table` function has 4 arguments: table name, columns for select, start dates, and end dates.

You can make this script parametrized by adding `upload_table "$1" '*' "$2" "$3"` as the last line so that entire script will be:

```
#!/bin/bash

function upload_day {
  table=$1
  sel=$2
  day=$3
  next_day=$(date -d "$day+1 days" +%Y-%m-%d)
  bq_suffix=$(date -d "$day" +%Y%m%d)
  echo "Uploading $table: $day..."
  psql  -c "\\copy (select $sel from $table where created_at >= '$day' and created_at < '$next_day') TO '$table-$day.csv' WITH CSV HEADER"
  gzip $table-$day.csv
  bq load --allow_quoted_newlines --project_id  --replace --source_format=CSV --autodetect --max_bad_records 100 .$table$bq_suffix $table-$day.csv.gz
  rm $table-$day.csv.gz
};

function upload_table {
  t=$1
  s=$2
  start_date=$3
  end_date=$4
  while [ "$start_date" != "$end_date" ]; do
       upload_day "$t" "$s" "$start_date"
       start_date=$(date -d "$start_date+1 days" +%Y-%m-%d)
  done
}

upload_table "$1" '*' "$2" "$3"
```

Don’t forget to `$ chmod +x bigquery-upload.sh` your script. After it you can run it as

```
$ ./bigquery-upload.sh "transactions" "2017-01-01" "2018-03-01"
```

The great thing about this script of loading data from Postgres into BigQuery is you can use it to upload just a single day and it can be put in the `/etc/cron.daily` or `/etc/cron.hourly` directory so that you can get your data in nearly real time. The `--replace` option script can be run more than once for the same day to replace data as it is refreshed.

If all is done right, it should load your terabyte of data in a day or so. Please do not hesitate to <a href="https://statsbot.co/?utm_source=statsbotblog&amp;utm_medium=article&amp;utm_campaign=postgres_to_bigquery">contact us</a> and we would love to advice you.

Extract, Load, Transform (ETL)

Open Source ETL: Apache NiFi vs Streamsets

ETL vs ELT: Considering the Advancement of Data Warehouses

Loading Terabytes of Data from Postgres into BigQuery

Get Cube updates to your inbox

Upgrade your data stack today