Support Questions

joshua_adeleke · ‎01-09-2017

I have a flow diagram that picks zipped csv files and loads into a table in Oracle 12c. This has been tested successfully for 20 records but when i try it with a larger csv file (over 40k records), i get an error message "Failed to update DB due to a failed batch update. There were a total of 1 flowfiles that failed, 0 that succeeded and 99 that were not executed and will be routed to retry;". I currently have the batch size set to 100 for the PutSQL processor. Is there any setting i am missing?

GetSFTP---UnpackContent---SplitText---ExtractText---ReplaceText---PutSQL

MattWho · ‎01-09-2017

@Joshua Adeleke

Also check your NiFi app log for any Out Of Memory Errors (OOME). The SplitText processor may be having memory issues trying to split over 40k records. You could try using two splitText processors in series with the first splitting on a 10,000 "Line Split Count" and the second then splitting those 10,000 line FlowFiles with a 1 "Line Split Count". This will greatly reduce the heap memory footprint.

In addition, if you do a listing on the queue feeding your putSQL processor, do you see any listed FlowFiles with an unexpected size?

Matt

View solution in original post

joshua_adeleke · ‎01-11-2017

I used the fragment.index attribute to remove the headers and successfully loaded into Oracle DB. Thank you @Matt and @Matt Burgess.

MattWho · ‎01-11-2017

@Joshua Adeleke

Another option might be to use the ReplaceText processor to find the first two lines and replace them with nothing. Glad to hear you got things working for you.

prabhuu161994 · ‎03-09-2017

you just copy the query generated in any one of the output flowfile of the ReplaceText processor and execute that in Oracle DB.If it is executed successfully in DB here also will execute.

Cloudera Community

Support Questions

Loading csv files into Oracle DB with NiFi