Aperture using workflow to export via JDBC - Issue

JenJ
JenJ Member
edited February 24 in Support

Hi,

Please can you help/advise? 

I am trying to export dataset containing 79k records (83 columns) to HDI table, which is failing. However, if we limit the record to 4 or 100 the export to HDI works perfectly fine.

Also, please find below error:

Unexpected errors have occurred during Workflow execution: 9003: An unexpected error has occurred during Workflow execution4056: Error when preparing JDBC statement parameter

image-716020dca44ac-2934.png

Answers

  • Josh Boxer
    Josh Boxer Administrator
    edited February 20

    If I understand correctly, you are able to export the first 100 rows successfully, but the full 79k records returns an error?

    There is an option in the Export step ‘Continue Workflow execution on JDBC exports error’, which will continue processing starting from the next record/batch (rather than failing completely). The step will produce a ‘Show failing rows’ output that includes the first database error message that impacted one or more rows in each failing batch. Re-exporting these failed rows with a smaller batch size can identify which row is causing the failure if this cannot be determined from the message.

    https://docs.experianaperture.io/data-quality/aperture-data-studio-v2/prepare-and-move-data/export-data/#export

    Also, if you look in the data studio log file you might be able to see a more detailed error message

  • JenJ
    JenJ Member
    edited February 20

    Hi Josh,

    Yes that's right - I am able to export few records to Hadoop table but not the 80k records with 83 columns. I have already 'checked' the option you have mentioned in the workflow setting. No luck!

    See below outcome

    1. 4 records – Successfully exported to Hadoop Table
    2. 34 records – Failed export to Hadoop Table
    3. 170 records – Failed export to Hadoop Table
    4. 79k records – Failed export to Hadoop Table

    Also, see screenshot below:

    image.png
  • Josh Boxer
    Josh Boxer Administrator

    Check the log file and the failed rows file.

    If still unclear please raise a Support ticket with the details and we will help you investigate further.

  • JenJ
    JenJ Member

    Hi Josh,

    Thanks for looking into this one - there are no failed rows created. I have not raised a support ticket before so will check with the Aperture support team and raise one.

    Thanks again.

    Best Regards,

    Jencil.

  • Henry Simms
    Henry Simms Administrator

    Given that very small exports are working it sounds like the connection either timing out or hitting some sort of data transfer size threshold, or perhaps being blocked somewhere in its route to HDI.

    I would recommend setting up the spy log, which may give more information as to what's failing from the driver's perspective:

    https://community.experianaperture.io/discussion/comment/3041#Comment_3041

    I wouldn't use the Atomic Database Update option for large volumes as it will be inefficient.

  • Uma
    Uma Member

    Hi Henry,

    As mentioned, i have added the connection properties and we are getting the below error while we are trying to export the data even for few records now.

    image.png

    2025-02-24 14:07:08,948 INFO c.e.d.n.EventProcessorImpl [pool-15-thread-1] Event received 'Event type: WORKFLOW_FAILED Event Name: Execution failed Event Description: (Workflow failed to execute)Event data / properties: WORKFLOW_ID = 8086abed-2b60-4d7c-a30b-b1ff54a1a3cc WORKFLOW_NAME = Test_write_to_HDI WORKFLOW_LABEL = Test_write_to_HDI WORKFLOW_START_TIME = Mon Feb 24 14:07:06 GMT 2025 WORKFLOW_URL = https://10.64.224.184:446/3/43/workflow/1828/0/read WORKFLOW_JOB_ID = f0990cc3-b6ff-46a6-857c-d1fc39f7bc40 CURRENT_USER_NAME = administrator CURRENT_USER_EMAIL = administrator WORKFLOW_ERROR_TIME = Mon Feb 24 14:07:08 GMT 2025 WORKFLOW_ERROR_MESSAGE = Unexpected errors have occurred during Workflow execution: 9003: An unexpected error has occurred during Workflow execution<break><break>4056: Error when preparing JDBC statement parameter'
    2025-02-24 14:07:15,507 WARN c.e.d.j.a.ExploreSessionActor [jobControllerSystem-pekko.actor.default-dispatcher-3456] [pBaWbf9QXJqBP6QAFdQDoyRHU7hj0mjaTtt12cH3QObUyNUyBba1OaR13RIknyuUuHPr-lgQvXBvlxnTaT_HSA] Still has 2 open executions waiting to close.
    2025-02-24 14:07:15,507 INFO c.e.d.j.a.ExploreSessionActor [jobControllerSystem-pekko.actor.default-dispatcher-3467] [pBaWbf9QXJqBP6QAFdQDoyRHU7hj0mjaTtt12cH3QObUyNUyBba1OaR13RIknyuUuHPr-lgQvXBvlxnTaT_HSA] All open executions closed; ending explore session.

    Can you please check and help us to resolve this.

    Regards

    Uma

  • Henry Simms
    Henry Simms Administrator

    The log excerpt above is from the application log (datastudio.log). The spy log will be a separate file that is written to by the driver itself. You can specify the path for this file, so that it appears in the same folder as your app log, rather than in the app installation directory. I typically use:

    • spyAttributes = (log=(file)C:\aperturedatastudio\data\log\Spy_HDISystem.log;timestamp=yes);

    Where

    • C:\aperturedatastudio\data\log is the path to my app log location
    • Spy_HDISystem.log is the file that will be created for this specific external system. The spy log can be configured for multiple external systems, but ideally each one would write to a different log

    More on the spyAttributes setting.

  • Uma
    Uma Member

    Hi Henry,

    As mentioned, i have updated the spyattribute location in the connection string. Please find the error below.

    spy(2025-02-24T16:26:26.812)>> x = 003P200000KzYdzIAF
    spy(2025-02-24T16:26:26.812)>> java.sql.SQLException: [ApertureDataStudio][Hive JDBC Driver]Object has been closed. ErrorCode=0 SQLState=HY000
    java.sql.SQLException: [ApertureDataStudio][Hive JDBC Driver]Object has been closed.

    Can you please check and schedule a call.The case(27672245 ) has been raised already.

  • Henry Simms
    Henry Simms Administrator

    Thanks - The UK support team have picked this up for investigation and will be in touch

Categories