question Re: nifi -Ingesting a file from SFTP and insert into MYSQL using nifi in Support Questions

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

kdossou — Mon, 19 Aug 2019 09:21:02 GMT

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

(ii)Current designed nifi workflow

(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

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

2. ConvertJSONToSQL

3.PutSQL

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

mburgess — Fri, 02 Sep 2016 01:21:31 GMT

After your FetchSFTP, the bar-delimited content will be in the content of the flow file, not the attributes. That is followed by an AttributesToJson processor which will overwrite the flow file content with a JSON document containing attributes such as sftp.remote.host, sftp.remote.port, etc. (see the doc for AttributesToJson).

I think you may want a SplitText processor after your FetchSFTP processor, to create one flow file for each line in your file. Then you could have an ExtractText processor which could use a regex (with grouping) to create attributes such as column.1, column.2, etc. Then your ReplaceText can use those attributes.

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

kdossou — Mon, 19 Aug 2019 09:20:32 GMT

Hi @Matt Burgess thanks a lot for responding I updated my workflow with the specifics you suggested but I still cannot insert into mysql db, am getting the same error as mentioned previously.I also attached my configs as well.

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.

(i)Updated Workflw

(ii)SplitText Config

(iii)ExtractText Config

(iv) Both ReplaceText and PutSQL remain unchanged.

ps: What is the tiny number "1" that appears when processors are running.

Cheers

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

kdossou — Fri, 02 Sep 2016 23:41:02 GMT

Hi @Matt Burgess could there other reasons such as available RAM on this nifi compute? Am currently using a t2 medium in AWS .

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

kdossou — Sat, 03 Sep 2016 00:27:52 GMT

Hi All, @mclark, @Bryan Bende, @Brandon Wilson, @jfrazee, @Pierre Villard, @Andrew Grande I would appreciate if you could get assist me on the above.Please refer to the above for details and explanation.Thanks a lot!

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

pvillard — Sat, 03 Sep 2016 01:15:07 GMT

Could you share the information you will find in the application log file? (./logs/nifi-app.log)

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

kdossou — Mon, 19 Aug 2019 09:20:13 GMT

Hi All,

@Pierre Villard ,@mclark, @Bryan Bende, @Brandon Wilson, @jfrazee, @Andrew Grande , @Matt Burgess I have been able to insert into MYSQL by setting "Obtain Generated Keys =true" in PutSQL configuration (pics below) but the problem now is, there is an insane number of duplicates that got ingested into the mysql table(pics below). I would like to know what might be going on with my flow to cause this and how to fix it.Thanks a lot!!

(i)PutSql Configuration

(ii)ExtractText

(iii)Original data to be ingested into MYSQL

(iv)Table count after PutSQL nifi ingest

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

kdossou — Thu, 08 Sep 2016 00:35:41 GMT

Hi All,

@Pierre Villard ,@mclark, @Bryan Bende, @Brandon Wilson, @jfrazee, @Andrew Grande , @Matt Burgess could you please assist me with the above.Greatly appreciate the help please.I have explained everything thoroughly.

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

pvillard — Thu, 08 Sep 2016 01:32:54 GMT

Are you sure that you don't ingest multiple times the same file with List/FetchSFTP?

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

kdossou — Wed, 21 Sep 2016 08:35:45 GMT

@Pierre Villard sorry for the late reply. How do I verify am not ingesting multiple times the same file with List/FetchSFTP?

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

dkjerrumgaard1 — Wed, 16 Nov 2016 04:56:53 GMT

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.

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

joshua_adeleke — Thu, 22 Dec 2016 20:17:52 GMT

@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.

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

joshua_adeleke — Thu, 22 Dec 2016 21:35:29 GMT

@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.