To simulate the problem that I face, I have created a table (in postgres) with 4 columns - all are not null and 1 column has unique key. I have 6 rows of data. The data has an issue with 1 row violating unique key constraint and 1 row has null value for a column.
How do I handle this exception (try-catch) when I use DB insert in such a way that only those 2 rows are rejected and logged into an error table with an appropriate error message and the rest of the 4 rows are properly inserted into a target table.
@VenkataK you could try variations of Try Catch functions like demonstrated in this example:
Concerning the difference in lines loaded into the database you could try and save the no of lines before and after the insert to get an idea what was rejected. Or if you have the error table you could check that and do something if it does contain Rows from your import table.
Thanks for your reply. I am still trying to wrap my head around using try-catch. Iâve attached the flow that I am currently using. Please let me know how I should correct it.
Thanks for your reply. When I try to disable Fail on error option, it shows âError during preparationâ. When I enable it then i see a cross mark on both the output ports of DB insert. It fails with an error message that execution failed because of unique constraint or not null constraint, according to the values given.
I have no clarity on how to use try catch to get the correct 4 rows inserted into my target table. I would like to insert the error message for the rejected records into an error log table but i have to cross step-1.
Sorry. I didnât reply to your message. I though I did.
Thanks for sharing a demo. But I am still stuck with my problem.
If you scroll through the page you can see my workflow.
I have no clear understanding of how to handle the problem using try-catch.
This will only work if you set jdbc batch size to 1 which will make inserts very, very slow. OK; for 10s maybe 100s of rows but for more, this will not perform well. if you have a higher batch size, the whole batch will fail to insert and not just the faulty row. Not sure you can actually extract which row(s) failed from the error info.
If you are expecting this issue/error you should preempt it by checking the error condition (unique key violation) before the insert and split the offending rows of and insert them into the error table that way.
Exception handling is for exceptional (eg. unexpected) issues and not issues you actually forsee and can easily deal with by default.
Thanks for your reply. Unfortunately the situation is such that I have to process record size of more than 10 K. If the situation is specific to null or unique constraint I could handle it by preempting it. I presented these two cases as an example. Sometimes the batch job has been failing for various reasons because of the data quality. As you have mentioned, once one record fails then it skips all the remaining records that follows it. Thatâs exactly my problem. I want to process the rest of the records which doesnât have any issues.
As Ipazin has said you can uncheck âfail on errorâ option. My thought right now would then be to use smaller JDBC batch size, like 100, (100 vs 1000 vs 10000 doesnât make a huge difference). Then split of the batches that have an error and repeat the insert with a JDBC batch size of 1. That way you can isolate the faulty rows without having to do a batch size 1 for all inserts. Of course this only works if errors are somewhat rare. if every batch of 100 has an error, this of course wonât help.
This leads back to my previous comment to preempt the errors as much as possible by first cleaning the data so that as few batches as possible need âindividual insertionâ.
@VenkataK I tried several things to catch the error when some lines would be inserted into Postgres DB and then continue and have the ones that would be newly inserted. To no real avail.
And as @beginner has said: this is not the right way to do it. You might check first which lines would match and then store the ones that do not match.
Having said that. DB Merge and DB Update could handle inserting only certain lines under some circumstances you would have to investigate, although I would not recommend it since you might change data from entries that are already there (if you want to do that you should do so explicitly).
I share the current work in progress if anyone wants to continue:
