Description:

Executes a SQL UPDATE or INSERT command. The content of an incoming FlowFile is expected to be the SQL command to execute. The SQL command may use the ? to escape parameters. In this case, the parameters to use must exist as FlowFile attributes with the naming convention sql.args.N.type and sql.args.N.value, where N is a positive integer. The sql.args.N.type is expected to be a number indicating the JDBC Type. The content of the FlowFile is expected to be in UTF-8 format.

Tags:

sql, put, rdbms, database, update, insert, relational

Properties:

In the list below, the names of required properties appear in bold. Any other properties (not in bold) are considered optional. The table also indicates any default values.

Name	Default Value	Allowable Values	Description
JDBC Connection Pool		Controller Service API: DBCPService Implementation: DBCPConnectionPool	Specifies the JDBC Connection Pool to use in order to convert the JSON message to a SQL statement. The Connection Pool is necessary in order to determine the appropriate database column types.
Support Fragmented Transactions	true	* true * false	If true, when a FlowFile is consumed by this Processor, the Processor will first check the fragment.identifier and fragment.count attributes of that FlowFile. If the fragment.count value is greater than 1, the Processor will not process any FlowFile will that fragment.identifier until all are available; at that point, it will process all FlowFiles with that fragment.identifier as a single transaction, in the order specified by the FlowFiles' fragment.index attributes. This Provides atomicity of those SQL statements. If this value is false, these attributes will be ignored and the updates will occur independent of one another.
Transaction Timeout			If the <Support Fragmented Transactions> property is set to true, specifies how long to wait for all FlowFiles for a particular fragment.identifier attribute to arrive before just transferring all of the FlowFiles with that identifier to the 'failure' relationship
Batch Size	100		The preferred number of FlowFiles to put to the database in a single transaction
Obtain Generated Keys	false	* true * false	If true, any key that is automatically generated by the database will be added to the FlowFile that generated it using the sql.generate.key attribute. This may result in slightly slower performance and is not supported by all databases.

Relationships:

Name	Description
retry	A FlowFile is routed to this relationship if the database cannot be updated but attempting the operation again may succeed
failure	A FlowFile is routed to this relationship if the database cannot be updated and retrying the operation will also fail, such as an invalid query or an integrity constraint violation
success	A FlowFile is routed to this relationship after the database is successfully updated

Reads Attributes:

Name	Description
fragment.identifier	If the <Support Fragment Transactions> property is true, this attribute is used to determine whether or not two FlowFiles belong to the same transaction.
fragment.count	If the <Support Fragment Transactions> property is true, this attribute is used to determine how many FlowFiles are needed to complete the transaction.
fragment.index	If the <Support Fragment Transactions> property is true, this attribute is used to determine the order that the FlowFiles in a transaction should be evaluated.
sql.args.N.type	Incoming FlowFiles are expected to be parameterized SQL statements. The type of each Parameter is specified as an integer that represents the JDBC Type of the parameter.
sql.args.N.value	Incoming FlowFiles are expected to be parameterized SQL statements. The value of the Parameters are specified as sql.args.1.value, sql.args.2.value, sql.args.3.value, and so on. The type of the sql.args.1.value Parameter is specified by the sql.args.1.type attribute.

Writes Attributes:

Name	Description
sql.generated.key	If the database generated a key for an INSERT statement and the Obtain Generated Keys property is set to true, this attribute will be added to indicate the generated key, if possible. This feature is not supported by all database vendors.

How to configure?

Step 1: Drag and drop the PutSQL processor to canvas.

Step 2: Double click the processor to configure, the configuration dialog will be opened as follows,

Step 3: Check usage of each property and update those values.

Properties and usage:

JDBC Connection Pool: Enters the JDBC connection pool on which SQL connection the incoming flow file SQL statement should be executed.

Support Fragmented Transactions: Used to specify whether flow files should be queued until all the flow files are available based on fragment.count value.

Transaction Timeout: It is used to specify maximum wait time for the particular fragment.identifier attribute to arrive before transferring to failure when support fragmented transactions is set.

Batch Size: It is used to mention the size of the flow files that can be added to the database file in single transaction.

Obtain Generated Keys: Used to add auto-generated keys by the database to the flow file.

Rollback On Failure: Used to specify how to handle the errors. Continue with next flow file if error occurs or stop further processing immediately.

For example, if you want to read a json file and insert all the records into database, you should configure the PutSQL processor given as follows,

See Also:

ConvertJSONToSQL