Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

nifi -Ingesting a file from SFTP and insert into MYSQL using nifi

avatar
Rising Star

Hi community,

I would like to leverage nifi to ingest a file from sftp and insert its data into mysql database.I currently have been unsuccessful in doing so and will require all assistance in the right direction.Much appreciated for the help in advance and below are the specifics

(1)I should be able to list the files on sftp

(2) select a particular file or files

(3)Fetch the "desired file"

(4) Ingest "desired file" into database.

Below is more detail.

(i)Sample content of sftp file to ingest into via nifi via sftp processors later to insert into MYSQL

7246-samplecontentofsftpfile.jpg

(ii)Current designed nifi workflow

7249-nifiwrkflow.jpg

(iii)Error am getting with this workflow

ConvertJSONToSQL ERROR: failed to parse standardflowfilerecord due to processor exception as JSON unexpected character (''' (code39)) expected a valid value (number,string,array,object,'true','false' or 'null')

PutSQL ERROR: failed to update a database due to a failed batch update.There were a total of 1 flow file that failed, 0 that succeeded and 0 that were not excuted and will be rerouted to retry.

(iv)Processor Configs

1. ReplaceTxext

7262-replacetextattribute.jpg

sql statement: INSERT INTO NiFiUsecase001 (Column1, Column2, Column3, Column4, Column5) VALUES ('${Column1}', '${Column2}','${Column3}','${Column4}','${Column5}')

2. ConvertJSONToSQL

7263-convertjsontosql.jpg

3.PutSQL

7264-putsql.jpg

1 ACCEPTED SOLUTION

avatar
Master Guru
hide-solution

This problem has been solved!

Want to get a detailed solution you have to login/registered on the community

Register/Login
12 REPLIES 12

avatar
Contributor

I am experiencing the same issue with the PutSQL processor, regardless of how many properly formatted SQL Insert statements I pass to the processor, it fails say that the entire batch failed because it was expecting N statements, where N = the number of SQL statements I have passed in, e.g. 30, 50, 100. It never seems to get past this point.

avatar
Expert Contributor

@David Kjerrumgaard Did you manage to get past this challenge? In my case, i get an error "invalid number" after i changed the Obtain Generated Keys property to true.

avatar
Expert Contributor

@David Kjerrumgaard I found out the issue was with my csv file. It had to do with the column names i was ingesting in the csv file. Also, the turning the Obtain Generated Keys property to false worked as well in my case.