Skip to content

Instantly share code, notes, and snippets.

@ijokarumawak

ijokarumawak/0.NIFI-3704-BatchUpdateException.md

Created April 25, 2017 12:33
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save ijokarumawak/d2ae2d582472780cc5769c19dd204033 to your computer and use it in GitHub Desktop.
Save ijokarumawak/d2ae2d582472780cc5769c19dd204033 to your computer and use it in GitHub Desktop.
Download ZIP
Contains NiFi templates and SQLs to test NIFI-3704.
Raw
0.NIFI-3704-BatchUpdateException.md

MySQL table

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);

This is an example table from MySQL document.

Schema

{
  "type": "record",
  "name": "animals",
  "fields" : [
    {"name": "id", "type": "int"},
    {"name": "name", "type": "string"}
  ]
}

How to reproduce BatchUpdateException

Leave 2nd row so that row 2 can fail:

delete from animals where id in (1, 3);

Then pass this input FlowFile again, id 1 and 3 rows will be inserted but 2 will fail because there is an existing row with the same id.

[{"id":1,"name":"one"},{"id":2,"name":"two"},{"id":3,"name":"three"}]
Raw
NIFI-3704_Animals.xml
<?xml version="1.0" ?>
<template encoding-version="1.1">
<description></description>
<groupId>a4198a48-015b-1000-8ceb-99b777a4aec0</groupId>
<name>NIFI-3704 Animals</name>
<snippet>
<connections>
<id>97d32883-0700-3e22-0000-000000000000</id>
<parentGroupId>89442448-8fb4-30d1-0000-000000000000</parentGroupId>
<backPressureDataSizeThreshold>1 GB</backPressureDataSizeThreshold>
<backPressureObjectThreshold>10000</backPressureObjectThreshold>
<destination>
<groupId>89442448-8fb4-30d1-0000-000000000000</groupId>
<id>8b311b17-b61f-3a6d-0000-000000000000</id>
<type>PROCESSOR</type>
</destination>
<flowFileExpiration>0 sec</flowFileExpiration>
<labelIndex>1</labelIndex>
<name></name>
<selectedRelationships>success</selectedRelationships>
<source>
<groupId>89442448-8fb4-30d1-0000-000000000000</groupId>
<id>eb805050-41d2-3353-0000-000000000000</id>
<type>PROCESSOR</type>
</source>
<zIndex>0</zIndex>
</connections>
<connections>
<id>0d9d1c1e-4453-3820-0000-000000000000</id>
<parentGroupId>89442448-8fb4-30d1-0000-000000000000</parentGroupId>
<backPressureDataSizeThreshold>1 GB</backPressureDataSizeThreshold>
<backPressureObjectThreshold>10000</backPressureObjectThreshold>
<bends>
<x>458.25299072265625</x>
<y>440.33477783203125</y>
</bends>
<destination>
<groupId>89442448-8fb4-30d1-0000-000000000000</groupId>
<id>511f4122-b889-3531-0000-000000000000</id>
<type>PROCESSOR</type>
</destination>
<flowFileExpiration>0 sec</flowFileExpiration>
<labelIndex>1</labelIndex>
<name></name>
<selectedRelationships>retry</selectedRelationships>
<source>
<groupId>89442448-8fb4-30d1-0000-000000000000</groupId>
<id>8b311b17-b61f-3a6d-0000-000000000000</id>
<type>PROCESSOR</type>
</source>
<zIndex>0</zIndex>
</connections>
<connections>
<id>0ffa0110-4520-340f-0000-000000000000</id>
<parentGroupId>89442448-8fb4-30d1-0000-000000000000</parentGroupId>
<backPressureDataSizeThreshold>1 GB</backPressureDataSizeThreshold>
<backPressureObjectThreshold>10000</backPressureObjectThreshold>
<bends>
<x>0.0</x>
<y>441.5136413574219</y>
</bends>
<destination>
<groupId>89442448-8fb4-30d1-0000-000000000000</groupId>
<id>511f4122-b889-3531-0000-000000000000</id>
<type>PROCESSOR</type>
</destination>
<flowFileExpiration>0 sec</flowFileExpiration>
<labelIndex>1</labelIndex>
<name></name>
<selectedRelationships>failure</selectedRelationships>
<source>
<groupId>89442448-8fb4-30d1-0000-000000000000</groupId>
<id>8b311b17-b61f-3a6d-0000-000000000000</id>
<type>PROCESSOR</type>
</source>
<zIndex>0</zIndex>
</connections>
<connections>
<id>27ca99b4-4d39-39ac-0000-000000000000</id>
<parentGroupId>89442448-8fb4-30d1-0000-000000000000</parentGroupId>
<backPressureDataSizeThreshold>1 GB</backPressureDataSizeThreshold>
<backPressureObjectThreshold>10000</backPressureObjectThreshold>
<destination>
<groupId>89442448-8fb4-30d1-0000-000000000000</groupId>
<id>511f4122-b889-3531-0000-000000000000</id>
<type>PROCESSOR</type>
</destination>
<flowFileExpiration>0 sec</flowFileExpiration>
<labelIndex>1</labelIndex>
<name></name>
<selectedRelationships>success</selectedRelationships>
<source>
<groupId>89442448-8fb4-30d1-0000-000000000000</groupId>
<id>8b311b17-b61f-3a6d-0000-000000000000</id>
<type>PROCESSOR</type>
</source>
<zIndex>0</zIndex>
</connections>
<controllerServices>
<id>0c2d2cc6-568d-3848-0000-000000000000</id>
<parentGroupId>89442448-8fb4-30d1-0000-000000000000</parentGroupId>
<bundle>
<artifact>nifi-dbcp-service-nar</artifact>
<group>org.apache.nifi</group>
<version>1.2.0-SNAPSHOT</version>
</bundle>
<comments></comments>
<descriptors>
<entry>
<key>Database Connection URL</key>
<value>
<name>Database Connection URL</name>
</value>
</entry>
<entry>
<key>Database Driver Class Name</key>
<value>
<name>Database Driver Class Name</name>
</value>
</entry>
<entry>
<key>database-driver-locations</key>
<value>
<name>database-driver-locations</name>
</value>
</entry>
<entry>
<key>Database User</key>
<value>
<name>Database User</name>
</value>
</entry>
<entry>
<key>Password</key>
<value>
<name>Password</name>
</value>
</entry>
<entry>
<key>Max Wait Time</key>
<value>
<name>Max Wait Time</name>
</value>
</entry>
<entry>
<key>Max Total Connections</key>
<value>
<name>Max Total Connections</name>
</value>
</entry>
<entry>
<key>Validation-query</key>
<value>
<name>Validation-query</name>
</value>
</entry>
</descriptors>
<name>DBCPConnectionPool</name>
<persistsState>false</persistsState>
<properties>
<entry>
<key>Database Connection URL</key>
<value>jdbc:mysql://192.168.99.100:3306/nifi_test</value>
</entry>
<entry>
<key>Database Driver Class Name</key>
<value>com.mysql.jdbc.Driver</value>
</entry>
<entry>
<key>database-driver-locations</key>
<value>/Users/koji/Downloads/mysql-connector-java-5.1.41/mysql-connector-java-5.1.41-bin.jar</value>
</entry>
<entry>
<key>Database User</key>
<value>root</value>
</entry>
<entry>
<key>Password</key>
</entry>
<entry>
<key>Max Wait Time</key>
</entry>
<entry>
<key>Max Total Connections</key>
</entry>
<entry>
<key>Validation-query</key>
</entry>
</properties>
<state>ENABLED</state>
<type>org.apache.nifi.dbcp.DBCPConnectionPool</type>
</controllerServices>
<controllerServices>
<id>43103a0e-45c4-3935-0000-000000000000</id>
<parentGroupId>89442448-8fb4-30d1-0000-000000000000</parentGroupId>
<bundle>
<artifact>nifi-record-serialization-services-nar</artifact>
<group>org.apache.nifi</group>
<version>1.2.0-SNAPSHOT</version>
</bundle>
<comments></comments>
<descriptors>
<entry>
<key>Schema Access Strategy</key>
<value>
<name>Schema Access Strategy</name>
</value>
</entry>
<entry>
<key>Schema Registry</key>
<value>
<identifiesControllerService>org.apache.nifi.schemaregistry.services.SchemaRegistry</identifiesControllerService>
<name>Schema Registry</name>
</value>
</entry>
<entry>
<key>Schema Name</key>
<value>
<name>Schema Name</name>
</value>
</entry>
<entry>
<key>schema-text</key>
<value>
<name>schema-text</name>
</value>
</entry>
<entry>
<key>Date Format</key>
<value>
<name>Date Format</name>
</value>
</entry>
<entry>
<key>Time Format</key>
<value>
<name>Time Format</name>
</value>
</entry>
<entry>
<key>Timestamp Format</key>
<value>
<name>Timestamp Format</name>
</value>
</entry>
<entry>
<key>id</key>
<value>
<name>id</name>
</value>
</entry>
<entry>
<key>name</key>
<value>
<name>name</name>
</value>
</entry>
</descriptors>
<name>JsonPathReader</name>
<persistsState>false</persistsState>
<properties>
<entry>
<key>Schema Access Strategy</key>
</entry>
<entry>
<key>Schema Registry</key>
<value>5ac82c1d-f60f-37c7-0000-000000000000</value>
</entry>
<entry>
<key>Schema Name</key>
</entry>
<entry>
<key>schema-text</key>
</entry>
<entry>
<key>Date Format</key>
</entry>
<entry>
<key>Time Format</key>
</entry>
<entry>
<key>Timestamp Format</key>
</entry>
<entry>
<key>id</key>
<value>$.id</value>
</entry>
<entry>
<key>name</key>
<value>$.name</value>
</entry>
</properties>
<state>ENABLED</state>
<type>org.apache.nifi.json.JsonPathReader</type>
</controllerServices>
<controllerServices>
<id>5ac82c1d-f60f-37c7-0000-000000000000</id>
<parentGroupId>89442448-8fb4-30d1-0000-000000000000</parentGroupId>
<bundle>
<artifact>nifi-registry-nar</artifact>
<group>org.apache.nifi</group>
<version>1.2.0-SNAPSHOT</version>
</bundle>
<comments></comments>
<descriptors>
<entry>
<key>animals</key>
<value>
<name>animals</name>
</value>
</entry>
</descriptors>
<name>AvroSchemaRegistry</name>
<persistsState>false</persistsState>
<properties>
<entry>
<key>animals</key>
<value>{
"type": "record",
"name": "animals",
"fields" : [
{"name": "id", "type": "int"},
{"name": "name", "type": "string"}
]
}</value>
</entry>
</properties>
<state>ENABLED</state>
<type>org.apache.nifi.schemaregistry.services.AvroSchemaRegistry</type>
</controllerServices>
<processors>
<id>8b311b17-b61f-3a6d-0000-000000000000</id>
<parentGroupId>89442448-8fb4-30d1-0000-000000000000</parentGroupId>
<position>
<x>50.3165283203125</x>
<y>213.19921875</y>
</position>
<bundle>
<artifact>nifi-standard-nar</artifact>
<group>org.apache.nifi</group>
<version>1.2.0-SNAPSHOT</version>
</bundle>
<config>
<bulletinLevel>WARN</bulletinLevel>
<comments></comments>
<concurrentlySchedulableTaskCount>1</concurrentlySchedulableTaskCount>
<descriptors>
<entry>
<key>put-db-record-record-reader</key>
<value>
<identifiesControllerService>org.apache.nifi.serialization.RecordReaderFactory</identifiesControllerService>
<name>put-db-record-record-reader</name>
</value>
</entry>
<entry>
<key>put-db-record-statement-type</key>
<value>
<name>put-db-record-statement-type</name>
</value>
</entry>
<entry>
<key>put-db-record-dcbp-service</key>
<value>
<identifiesControllerService>org.apache.nifi.dbcp.DBCPService</identifiesControllerService>
<name>put-db-record-dcbp-service</name>
</value>
</entry>
<entry>
<key>put-db-record-catalog-name</key>
<value>
<name>put-db-record-catalog-name</name>
</value>
</entry>
<entry>
<key>put-db-record-schema-name</key>
<value>
<name>put-db-record-schema-name</name>
</value>
</entry>
<entry>
<key>put-db-record-table-name</key>
<value>
<name>put-db-record-table-name</name>
</value>
</entry>
<entry>
<key>put-db-record-translate-field-names</key>
<value>
<name>put-db-record-translate-field-names</name>
</value>
</entry>
<entry>
<key>put-db-record-unmatched-field-behavior</key>
<value>
<name>put-db-record-unmatched-field-behavior</name>
</value>
</entry>
<entry>
<key>put-db-record-unmatched-column-behavior</key>
<value>
<name>put-db-record-unmatched-column-behavior</name>
</value>
</entry>
<entry>
<key>put-db-record-update-keys</key>
<value>
<name>put-db-record-update-keys</name>
</value>
</entry>
<entry>
<key>put-db-record-field-containing-sql</key>
<value>
<name>put-db-record-field-containing-sql</name>
</value>
</entry>
<entry>
<key>put-db-record-quoted-identifiers</key>
<value>
<name>put-db-record-quoted-identifiers</name>
</value>
</entry>
<entry>
<key>put-db-record-quoted-table-identifiers</key>
<value>
<name>put-db-record-quoted-table-identifiers</name>
</value>
</entry>
<entry>
<key>put-db-record-query-timeout</key>
<value>
<name>put-db-record-query-timeout</name>
</value>
</entry>
</descriptors>
<executionNode>ALL</executionNode>
<lossTolerant>false</lossTolerant>
<penaltyDuration>30 sec</penaltyDuration>
<properties>
<entry>
<key>put-db-record-record-reader</key>
<value>43103a0e-45c4-3935-0000-000000000000</value>
</entry>
<entry>
<key>put-db-record-statement-type</key>
<value>INSERT</value>
</entry>
<entry>
<key>put-db-record-dcbp-service</key>
<value>0c2d2cc6-568d-3848-0000-000000000000</value>
</entry>
<entry>
<key>put-db-record-catalog-name</key>
</entry>
<entry>
<key>put-db-record-schema-name</key>
</entry>
<entry>
<key>put-db-record-table-name</key>
<value>animals</value>
</entry>
<entry>
<key>put-db-record-translate-field-names</key>
<value>true</value>
</entry>
<entry>
<key>put-db-record-unmatched-field-behavior</key>
<value>Ignore Unmatched Fields</value>
</entry>
<entry>
<key>put-db-record-unmatched-column-behavior</key>
<value>Fail on Unmatched Columns</value>
</entry>
<entry>
<key>put-db-record-update-keys</key>
</entry>
<entry>
<key>put-db-record-field-containing-sql</key>
</entry>
<entry>
<key>put-db-record-quoted-identifiers</key>
<value>false</value>
</entry>
<entry>
<key>put-db-record-quoted-table-identifiers</key>
<value>false</value>
</entry>
<entry>
<key>put-db-record-query-timeout</key>
<value>0 seconds</value>
</entry>
</properties>
<runDurationMillis>0</runDurationMillis>
<schedulingPeriod>0 sec</schedulingPeriod>
<schedulingStrategy>TIMER_DRIVEN</schedulingStrategy>
<yieldDuration>1 sec</yieldDuration>
</config>
<name>PutDatabaseRecord</name>
<relationships>
<autoTerminate>false</autoTerminate>
<name>failure</name>
</relationships>
<relationships>
<autoTerminate>false</autoTerminate>
<name>retry</name>
</relationships>
<relationships>
<autoTerminate>false</autoTerminate>
<name>success</name>
</relationships>
<style></style>
<type>org.apache.nifi.processors.standard.PutDatabaseRecord</type>
</processors>
<processors>
<id>eb805050-41d2-3353-0000-000000000000</id>
<parentGroupId>89442448-8fb4-30d1-0000-000000000000</parentGroupId>
<position>
<x>49.6785888671875</x>
<y>0.0</y>
</position>
<bundle>
<artifact>nifi-standard-nar</artifact>
<group>org.apache.nifi</group>
<version>1.2.0-SNAPSHOT</version>
</bundle>
<config>
<bulletinLevel>WARN</bulletinLevel>
<comments></comments>
<concurrentlySchedulableTaskCount>1</concurrentlySchedulableTaskCount>
<descriptors>
<entry>
<key>File Size</key>
<value>
<name>File Size</name>
</value>
</entry>
<entry>
<key>Batch Size</key>
<value>
<name>Batch Size</name>
</value>
</entry>
<entry>
<key>Data Format</key>
<value>
<name>Data Format</name>
</value>
</entry>
<entry>
<key>Unique FlowFiles</key>
<value>
<name>Unique FlowFiles</name>
</value>
</entry>
<entry>
<key>generate-ff-custom-text</key>
<value>
<name>generate-ff-custom-text</name>
</value>
</entry>
<entry>
<key>schema.name</key>
<value>
<name>schema.name</name>
</value>
</entry>
</descriptors>
<executionNode>ALL</executionNode>
<lossTolerant>false</lossTolerant>
<penaltyDuration>30 sec</penaltyDuration>
<properties>
<entry>
<key>File Size</key>
<value>0B</value>
</entry>
<entry>
<key>Batch Size</key>
<value>1</value>
</entry>
<entry>
<key>Data Format</key>
<value>Text</value>
</entry>
<entry>
<key>Unique FlowFiles</key>
<value>false</value>
</entry>
<entry>
<key>generate-ff-custom-text</key>
<value>[{"id":1,"name":"one"},{"id":2,"name":"two"},{"id":3,"name":"three"}]</value>
</entry>
<entry>
<key>schema.name</key>
<value>animals</value>
</entry>
</properties>
<runDurationMillis>0</runDurationMillis>
<schedulingPeriod>1d</schedulingPeriod>
<schedulingStrategy>TIMER_DRIVEN</schedulingStrategy>
<yieldDuration>1 sec</yieldDuration>
</config>
<name>GenerateFlowFile</name>
<relationships>
<autoTerminate>false</autoTerminate>
<name>success</name>
</relationships>
<style></style>
<type>org.apache.nifi.processors.standard.GenerateFlowFile</type>
</processors>
<processors>
<id>511f4122-b889-3531-0000-000000000000</id>
<parentGroupId>89442448-8fb4-30d1-0000-000000000000</parentGroupId>
<position>
<x>58.7330322265625</x>
<y>541.0090942382812</y>
</position>
<bundle>
<artifact>nifi-update-attribute-nar</artifact>
<group>org.apache.nifi</group>
<version>1.2.0-SNAPSHOT</version>
</bundle>
<config>
<bulletinLevel>WARN</bulletinLevel>
<comments></comments>
<concurrentlySchedulableTaskCount>1</concurrentlySchedulableTaskCount>
<descriptors>
<entry>
<key>Delete Attributes Expression</key>
<value>
<name>Delete Attributes Expression</name>
</value>
</entry>
<entry>
<key>Store State</key>
<value>
<name>Store State</name>
</value>
</entry>
<entry>
<key>Stateful Variables Initial Value</key>
<value>
<name>Stateful Variables Initial Value</name>
</value>
</entry>
</descriptors>
<executionNode>ALL</executionNode>
<lossTolerant>false</lossTolerant>
<penaltyDuration>30 sec</penaltyDuration>
<properties>
<entry>
<key>Delete Attributes Expression</key>
</entry>
<entry>
<key>Store State</key>
<value>Do not store state</value>
</entry>
<entry>
<key>Stateful Variables Initial Value</key>
</entry>
</properties>
<runDurationMillis>0</runDurationMillis>
<schedulingPeriod>0 sec</schedulingPeriod>
<schedulingStrategy>TIMER_DRIVEN</schedulingStrategy>
<yieldDuration>1 sec</yieldDuration>
</config>
<name>UpdateAttribute</name>
<relationships>
<autoTerminate>false</autoTerminate>
<name>success</name>
</relationships>
<style></style>
<type>org.apache.nifi.processors.attributes.UpdateAttribute</type>
</processors>
</snippet>
<timestamp>04/25/2017 21:22:35 JST</timestamp>
</template>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment