[NIFI-5788] Introduce batch size limit in PutDatabaseRecord processor

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.9.0
• Component/s: Core Framework
• Labels: pull-request-available
• Environment: Teradata DB
• Flags: Important
• Assignee: Unassigned
• Reporter: Vadim
• Created: 05/Nov/18 10:52
• Updated: 15/Nov/18 15:47
• Resolved: 15/Nov/18 15:46

Description

Certain JDBC drivers do not support unlimited batch size in INSERT/UPDATE prepared SQL statements. Specifically, Teradata JDBC driver (https://downloads.teradata.com/download/connectivity/jdbc-driver) would fail SQL statement when the batch overflows the internal limits.

Dividing data into smaller chunks before the PutDatabaseRecord is applied can work around the issue in certain scenarios, but generally, this solution is not perfect because the SQL statements would be executed in different transaction contexts and data integrity would not be preserved.

The solution suggests the following:

• introduce a new optional parameter in PutDatabaseRecord processor, max_batch_size which defines the maximum batch size in INSERT/UPDATE statement; the default value zero (INFINITY) preserves the old behavior
• divide the input into batches of the specified size and invoke PreparedStatement.executeBatch()  for each batch

Pull request: https://github.com/apache/nifi/pull/3128

 

[EDIT] Changed batch_size to max_batch_size. The default value would be zero (INFINITY) 

Issue Links

• links to: GitHub Pull Request #3128