question Re: Loading csv files into Oracle DB with NiFi in Archives of Support Questions (Read Only)

Loading csv files into Oracle DB with NiFi

joshua_adeleke — Mon, 09 Jan 2017 18:04:08 GMT

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

Re: Loading csv files into Oracle DB with NiFi

mburgess — Mon, 09 Jan 2017 21:44:59 GMT

Can you check the logs (in logs/nifi-app.log) to see what the error was? Also, what is the content of the first flow file to go into PutSQL (that is being routed to failure)? Does that SQL statement work when executed from something like SQL Developer?

Re: Loading csv files into Oracle DB with NiFi

MattWho — Mon, 09 Jan 2017 22:15:41 GMT

@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

Re: Loading csv files into Oracle DB with NiFi

joshua_adeleke — Tue, 10 Jan 2017 18:15:34 GMT

Hi @Matt, there is no OOME error in the NiFi app log. Also, the SplitText processor is able to successfully split the files but the issue seems to be at the PutSQL processor. I can see that although i asked that SplitText processor skip 2 lines in the header line count parameter, the insert statements into the PutSQL have the csv file headers as the values.

Re: Loading csv files into Oracle DB with NiFi

joshua_adeleke — Tue, 10 Jan 2017 19:25:18 GMT

Hi @Matt Burgess, the first SQL statement is the issue. I see it's taking the headers in the csv file even though i skipped the headers with the header line count parameter in SplitText.

2017-01-10 12:07:13,538 ERROR [Timer-Driven Process Thread-40] o.apache.nifi.processors.standard.PutSQL PutSQL[id=79bf32ff-e154-1a02-b109-ebd298dfab2e] Failed to update database due to a failed batch update. There were a total of 1 FlowFiles that failed, 0 that succeeded, and 99 that were not execute and will be routed to retry;

Re: Loading csv files into Oracle DB with NiFi

MattWho — Tue, 10 Jan 2017 21:40:21 GMT

@Joshua Adeleke

You could extract the header bits from the first two lines into FlowFile attributes before the SplitText processor. All the FlowFiles that come out of the SplitText processor will all get these new FlowFile attributes as well. You can then use the FlowFile Attributes in your PutSQL.

Re: Loading csv files into Oracle DB with NiFi

joshua_adeleke — Wed, 11 Jan 2017 02:28:12 GMT

@Matt

Please how do i extract the headers without using the SplitText processor?

Re: Loading csv files into Oracle DB with NiFi

MattWho — Wed, 11 Jan 2017 02:46:34 GMT

@Joshua Adeleke

The SplitText processor simply splits the content of an incoming FlowFile into multiple FlowFiles. It gives you the ability to designate how many lines would be considered the header and ignored, but it does no extraction of content in to FlowFile attributes.

The ExtractText processor can be used to read parts of the content and assign those parts to different NiFi FlowFile attributes. It will not remove the header form the content, that would still be done during the splitText processor operation. However, every FlowFile created by SplitText will inherit the unique FlowFile attributes from the parent FlowFile.

Matt

Re: Loading csv files into Oracle DB with NiFi

MattWho — Wed, 11 Jan 2017 03:03:40 GMT

@Joshua Adeleke

If you found this information helpful in guiding you with your dataflow design, please accept the answer.

Re: Loading csv files into Oracle DB with NiFi

joshua_adeleke — Wed, 11 Jan 2017 18:01:39 GMT

@Matt

I have not found a way to remove the headers in the csv file.

Re: Loading csv files into Oracle DB with NiFi

joshua_adeleke — Wed, 11 Jan 2017 20:57:52 GMT

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

Re: Loading csv files into Oracle DB with NiFi

MattWho — Wed, 11 Jan 2017 21:29:29 GMT

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

Re: Loading csv files into Oracle DB with NiFi

prabhuu161994 — Thu, 09 Mar 2017 16:59:02 GMT

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.