Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

NiFi: How to enter CSV file content and meta data into single table of postgresql database by NiFi

Solved Go to solution

NiFi: How to enter CSV file content and meta data into single table of postgresql database by NiFi

Hi,

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) thru 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

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted

Re: NiFi: How to enter CSV file content and meta data into single table of postgresql database by NiFi

Master Collaborator

@vikrant_kumar24 

 

There are many ways to do this.   I have added a template to my NiFI templates for you.  This way takes a csv Input, splits the lines, extracts two columns, builds an insert statement, and executes that statement (requires database connection pool controller service).

 

The only real tricky part to this is the regex for mapping the columns in ExtractText Processor.

 

https://github.com/steven-dfheinz/NiFi-Templates

 

Once you are able to parse the csv to attributes, adding more attributes for metadata, and adding those details to the insert query should be very easy. 

 

Hope this helps get you started.   Additionally, if you search here, you will find loads of posts with all the other suggested methods for processing csv to sql.

 


 


If this answer resolves your issue or allows you to move forward, please choose to ACCEPT this solution and close this topic. If you have further dialogue on this topic please comment here or feel free to private message me. If you have new questions related to your Use Case please create separate topic and feel free to tag me in your post.  


 


Thanks,



Steven

View solution in original post

Highlighted

Re: NiFi: How to enter CSV file content and meta data into single table of postgresql database by NiFi

Hi @vikrant_kumar24

 

Looking over your most recent post it appears that @stevenmatison  solved your issue. Once you've had a chance to try out the files he's provided, can you confirm by using the Accept as Solution button which can be found at the bottom of his reply so it can be of assistance to others?

 

cloudera community accept solution button created 2019-12-14_19-46-50.jpg

Bill Brooks, Community Manager
Was your question answered? Make sure to mark the answer as the accepted solution.
If you find a reply useful, say thanks by clicking on the thumbs up button.

View solution in original post

3 REPLIES 3
Highlighted

Re: NiFi: How to enter CSV file content and meta data into single table of postgresql database by NiFi

Master Collaborator

@vikrant_kumar24 

 

There are many ways to do this.   I have added a template to my NiFI templates for you.  This way takes a csv Input, splits the lines, extracts two columns, builds an insert statement, and executes that statement (requires database connection pool controller service).

 

The only real tricky part to this is the regex for mapping the columns in ExtractText Processor.

 

https://github.com/steven-dfheinz/NiFi-Templates

 

Once you are able to parse the csv to attributes, adding more attributes for metadata, and adding those details to the insert query should be very easy. 

 

Hope this helps get you started.   Additionally, if you search here, you will find loads of posts with all the other suggested methods for processing csv to sql.

 


 


If this answer resolves your issue or allows you to move forward, please choose to ACCEPT this solution and close this topic. If you have further dialogue on this topic please comment here or feel free to private message me. If you have new questions related to your Use Case please create separate topic and feel free to tag me in your post.  


 


Thanks,



Steven

View solution in original post

Highlighted

Re: NiFi: How to enter CSV file content and meta data into single table of postgresql database by NiFi

Thank you very much

Highlighted

Re: NiFi: How to enter CSV file content and meta data into single table of postgresql database by NiFi

Hi @vikrant_kumar24

 

Looking over your most recent post it appears that @stevenmatison  solved your issue. Once you've had a chance to try out the files he's provided, can you confirm by using the Accept as Solution button which can be found at the bottom of his reply so it can be of assistance to others?

 

cloudera community accept solution button created 2019-12-14_19-46-50.jpg

Bill Brooks, Community Manager
Was your question answered? Make sure to mark the answer as the accepted solution.
If you find a reply useful, say thanks by clicking on the thumbs up button.

View solution in original post

Don't have an account?
Coming from Hortonworks? Activate your account here