How to join two CSVs with Apache Nifi

nifi join two flow files
nifi merge attributes
nifi mergecontent json
nifi merge content based on attribute
apache nifi example
nifi csv lookup service
nifi merge avro files

I'm looking into ETL tools (like Talend) and investigating whether Apache Nifi could be used. Could Nifi be used to perform the following:

  1. Pick up two CSV files that are placed on local disk
  2. Join the CSVs on a common column
  3. Write the joined CSV to disk

I've tried setting up a job in Nifi, but couldn't see how to perform the join of two separate CSV files. Is this task possible in Apache Nifi?

It looks like the QueryDNS processor could be used to perform enrichment of one CSV file using the other, but that seems to be over-complicated for this use case.

Here's an example of the input CSVs, which need to be joined on state_id:

Input files

customers.csv

id | name | address      | state_id
---|------|--------------|---------
1  | John | 10 Blue Lane | 100
2  | Bob  | 15 Green St. | 200

states.csv

state_id | state
---------|---------
100      | Alabama
200      | New York
Output file

output.csv

id | name | address      | state
---|------|--------------|---------
1  | John | 10 Blue Lane | Alabama
2  | Bob  | 15 Green St. | New York

Apache NiFi is more of a dataflow tool and not really made to perform arbitrary joins of streaming data. Typically those types of operations are better suited to stream processing systems like Storm, Flink, Apex, etc, or ETL tools.

The types of joins that NiFi can do well are enrichment look ups where there is a fixed size lookup dataset, and for each record in the incoming data you use the lookup dataset to retrieve some value. For example, in your case there could be a processor called LookUpState which has a property "State Data" which points to a file containing all the states, then the customers.csv could be the input to this processor.

A community member started a project to make a generic lookup service for NiFi: https://github.com/jfrazee/nifi-lookup-service

etl - How to join two CSVs with Apache Nifi, I am having two csv files and want to merge them into single csv file using Id column. ExecuteSQL (you write SQL query to join all the required tables) -> ConvertRecord -> PutFile (This will give you the join of all the CSVs) 3. After that again, GetFile -> UpdateAttribute -> PutDatabaseRecord (If you again want to put the aggregated/joined CSV into the DB, then you can do it again) Hope it would help.

The typical pattern one follows for this is to load the reference set into a map cache controller service in NiFi. In this case that is the states.csv data. Then the live feed of customer data comes in and is enriched with this reference data using something like ReplaceText or you could even write a custom processor in Groovy. There are a lot of ways to slice this. There is also a JIRA/PR coming for making this even easier. There are elements of live stream joins that are best done in processing systems like Apache Storm, Spark, and Flink, but for the case you mention it can be done well in NiFi.

merge too csv files in nifi, NiFi example on how to join CSV files to create a merged result t, v 1, a 2, b 3, c <identifiesControllerService>org.apache.nifi.schemaregistry.services. Nifi is not an ETL tool but more a flow manager, it allow to move data accros system and to do some very simple transformation like csv to avro. You should not do computation or join with Nifi. For you usecase it would be better to use another tools like hive, spark,

workflow

I also tried to join two CSV files using the common column and did it sucessfully using the lookup record attribute in nifi lookup record config

Here, I used simplecsvlookup service as my lookup service and I am also attaching it's configuaration simplecsvlookup configuration

The first thing we should learn is how to use the lookup record attribute. Here, I have two csv files:

sample.csv: id,msisdn,recharge_amount 1,9048108594,399

new1: msisdn,type 9048108594,1

output: id,msisdn,recharge_amount,type 1,9048108594,399,1

The most important thing to notice is that result record path and key in this case key is msisdn (because this one is the common one in both files) and, for the result record path, we should use the column name which we need to merge with us, which in this case is "type:"

result record path--->> /type
key----->> /msisdn

And, in the lookup service, give the respective key and value names.

It will work.

NiFi example on how to join CSV files to create a merged result , My best guess is that to best accomplish this it would require custom coding to handling the merging logic. Apache NiFi is more of a dataflow tool and not really​  Advanced XML Processing With Apache NiFi 1.9.1. With the latest version of Apache NiFi, you can now directly convert XML to JSON or Apache AVRO, CSV, or any other format supported by RecordWriters.

Is Apache NIFI capable of joining multiple CSV files?, Nifi Join two CSV flowfiles on specific common headers - hadoop. Apache NiFi doesn't typically do these kinds of streaming joins. The joins it can do are more  I tried to join two csv file based on id with respect to the below reference. How to join two CSVs with Apache Nifi i'm using NiFi-1.3.0 Now i have two csv files.

Nifi Join two CSV flowfiles on specific common headers, The Processor creates several 'bins' to put the FlowFiles in. The Merge Strategy can be set to one of two options: Bin Packing Algorithm, or Defragment. we will use CSV-formatted data and write the merged data as CSV-formatted data,  If not specified, Date fields will be assumed to be number of milliseconds since epoch (Midnight, Jan 1, 1970 GMT). If specified, the value must match the Java Simple Date Format (for example, MM/dd/yyyy for a two-digit month, followed by a two-digit day, followed by a four-digit year, all separated by '/' characters, as in 01/01/2017). Time Format

MergeRecord, How to join two CSVs with Apache Nifi. For now I have tried with MergeContent processor, but it appends two flowfiles. Not adding as new columns with unique  The Apache NiFi template demonstrate how to Merge the content of two json incoming flow files into a single flowfile