Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: Table output - Batch insert Error handling

  1. #1
    Join Date
    Jan 2018
    Posts
    5

    Default Table output - Batch insert Error handling

    Hi,

    I am having some trouble with a Table Output step in PDI 7.1

    I want to have "Error handling" for the case when something happens, a message is sent to a third party via POST. I have one wrapper Job which executes a transformation containing the "Table Output" step.

    As I was testing, I deliberately misconfigured the connection and made it fail. In spite of logging the error on the console, the error handling step does not go through and thus no message is sent to my third party.

    I saw some replies about this being due to the DB (lack of) error handling, but isn't there a way of capturing, for example the error logged on the console and sending that ? My idea would be to use a script or java step. Would that be possible? Is there a function I can use or Any alternative ideas ?

    The error shown is as follows:
    "Error handling in combination with batch processing is not fully supported on the used database because of driver limitations. Proceed with caution at your own risk."

  2. #2
    Join Date
    May 2016
    Posts
    282

    Default

    Do you want to send a message only if Table Output step fails or if the transformation in general fails? You could put the transformation in a job and if the transformation fails (in general, not only if a certain step fails) generate a logic to send your message.
    Name:  20180110_001.jpg
Views: 683
Size:  15.2 KB
    OS: Ubuntu 16.04 64 bits
    Java: Openjdk 1.8.0_131
    Pentaho 6.1 CE

  3. #3
    Join Date
    Jan 2018
    Posts
    5

    Default

    Thanks for the reply Ana. Ideally I was looking into doing this on the step inside the same transformation because this is a process which already has more than a layer of Transformation and Job executors, and wouldn't like to add another layer just to handle this.
    In this particular case the structure already has a Job, calling a Transformation, which in turn calls this last Transformation. It seems already a bad practice to be doing this.
    However, since it seems there is no other way I'll probably will add another complexity layer, just like you suggested.

    Thanks,

  4. #4
    Join Date
    Nov 2013
    Posts
    382

    Default

    The Table Output step should deal with error handling without major problems. Duplicate keys, incorrect fields, whatever. Can you provide some detailed info about what database/driver are you using? And/or show us your transformation?

  5. #5
    Join Date
    Jan 2018
    Posts
    5

    Default

    Hi DepButi,
    I am connecting to an Oracle 12c db using native JDBC.

    The steps are pretty simple:

    Name:  fail step.png
Views: 615
Size:  14.3 KB

    I'm purposely misconfiguring the schema name so that this fails. A warning message with an error shows up but no action is triggered to the notification system. Thisis working with insert/update steps, but not whole table outputs. However, due to the volume of data inserts are impracticable.

    Name:  fail step 1.jpg
Views: 614
Size:  12.4 KB

  6. #6
    Join Date
    Nov 2013
    Posts
    382

    Default

    Let me understand it clearly ... you configure your table output step with an inexistent schema/table name?

    In this situation the step cannot even initialize and the whole transaction is aborted. There is no opportunity for Table Output step to deal with row errors.

    The error hop is meant to deal with any faulty row, not with a general step failure.

    To test it, configure a correct, existent schema/table bame and try to insert an erroneous row (duplicate key, null value for a non nullable field, ...). Clicking the red hop you can capture the error into a new field on your pipe and display it or switch to diferent actions.

  7. #7
    Join Date
    Jan 2018
    Posts
    5

    Default

    Thanks for the explanation, that makes sense.

    I've tried inserting rows with incorrect information, data types and forcing the errors but it simply does not write into the db, without going through the "Error" path.

    I'll probably try containing this transformation in a wrapping job, as previously suggested.

  8. #8
    Join Date
    Nov 2013
    Posts
    382

    Default

    Quote Originally Posted by nva View Post
    I've tried inserting rows with incorrect information, data types and forcing the errors but it simply does not write into the db, without going through the "Error" path.
    That makes no sense to me although never worked with an Oracle db/driver ... maybe someone else can find an explanation.

    I'll probably try containing this transformation in a wrapping job, as previously suggested.
    Worthless. A wrapping job will detect a Transformation error, but in your case it seems there is no error to trap as the Transformation will end succesfully.

  9. #9
    Join Date
    May 2016
    Posts
    282

    Default

    In my experience with Oracle and the default configuration, if you try to insert a row in which a column has incorrect information, the Table Output Step should throw an error and don't insert anything, maybe if you have an error table configured in the database, it doesn't get to throw the error and the conflicted row is inserted in the error table, but I thought you had to specify the error table when doing the insert, maybe it works different (I looked at the error table feature a long time ago and haven't used it, so you better check it) and it is silently handling it, check if it is your case.
    Regards
    OS: Ubuntu 16.04 64 bits
    Java: Openjdk 1.8.0_131
    Pentaho 6.1 CE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.