postgresqlcsvapache-nifi

Nifi : How to move CSV content and its meta data to single table in Postgresdatabase using NiFi

I have csv files and I want to move the content of files along with its meta data (File name, source (To be hard coded), control number (Part of file name - to be extracted from file name itself) using NiFi. So here is the sample File name and layout -

File name - 12345_user_data.csv (control_number_user_data.csv)

source - Newyork

CSV File Content/columns - 

Fields - abc1, abc2, abc3, abc4 

values - 1,2,3,4

Postgres Database table layout

Table name - User_Education

fields name  -

control_number, file_name, source, abc1, abc2, abc3, abc4

Values - 

12345, 12345_user_data.csv, Newyork, 1,2,3,4

I am planning to use below processors - 

ListFile

FetchFile

UpdateAttributes

PutDatabaseRecords

LogAttributes

But I am not sure how to combine the actual content with the meta data to load into one single table. Please help

Solution

  • You can use UpdateRecord before PutDatabaseRecord to add the control_number, file_name, and source fields to each record, setting the populating the "Replacement Value Strategy" property to "Literal Value" and use Expression Language to set the values to the corresponding attributes.

    For example, you could have a user-defined property /file_name set to ${filename}, that will add the file_name field to each record and set the value to whatever is in the "filename" attribute of the FlowFile.