Create a JDBC Output

To create a JBDC Output, you must first create a JDBC Connection. 

JDBC Connections available:

  • MS Sql
  • MySql
  • Oracle
  • PostgreSQL
  • Qubole
  • Snowflake

Create a Output to a JDBC in Hero Platform_

    1. Open Hero Platform_.

    2. Open Integration from the navigation menu and select Outputs.

        

    3) Click Create New Output.

        

    4) Give the Output a name and select your JDBC Connection. 

         Complete the JDBC Output configuration fields:

  • BatchMode
    • Yes: Recommend for most cases.

      • Enter a batch size for the number of queries that execute in one transaction. 

        • The batch size value depends on the content of the query being sent. Large text OR binary fields require a smaller batch size that will not step over data size limits. For smaller payloads, such as numbers or small text fields, a larger batch size is more efficient.
        • If the amount of records exceeds a single batch, additional batches are prepared and run until all queued records have been processed.
    • No: Execute one query for each tuple.
  • Batch size:
    • The number of database rows in the batch.
  • Batch memory limit:
    • Limits the amount of memory available for batched tuples.
      • The batch size and limit are counted toward requirements for running the Flow execution. If the memory limit is reached, the batch operation is executed immediately. 
  • Enter the query timeout value. (Seconds)
    • Query statements that are not completed by the timeout value are canceled.
    • The value of "0" disables the query timeout feature.
  • Enter an SQL query used for inserting, updating, or removing data.
  • From the field mapping table:
    • Confirm or change field names.
    • Confirm or change field data types.
    • Remove or confirm the arrangement of fields.
  • Click OK to save the Output.

Preparation

A database table (MySQL/PostgreSQL) needs to be created in your database where the data is to be written.

Below is an example of how to create a table in MySQL using a next script:

create table test_url_download ( name varchar(255), data blob);

        

        Learn more about variable resolution.

    6) Click OK to finish saving the Output in Hero Platform_.

A JDBC Output can be used as a passthrough Output in a Flow.

Troubleshooting

If a Flow is using a JDBC Output and encounters the following error:

<x> tuples dropped and gracefully ignored

- 100 tuples dropped and gracefully ignored.
java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction

This error is caused because the same record is trying to be updated by two runners at the same time. The reason for dropping 100 tuples in this example is that the JDBC Output buffer is configured to 100 records.

Resolution:

  • Reduce the buffer to 50 records to remove the deadlock. The downside is the writing on the JDBC database will be slower.
  • If speed is a requirement, use an aggregation of the records to be written. Select the last record based on the timestamp to avoid updating the same record by two runners in parallel.
    After implementing the aggregation, it is possible to increase the output buffer size. (e.g., to 200) Now, no records are updated in parallel and the writing speed is increased.


Search