<!DOCTYPE html><html lang="en"><head><meta charset="utf-8"></meta><title>PutDatabaseRecord</title><link rel="stylesheet" href="../../../../../css/component-usage.css" type="text/css"></link></head><script type="text/javascript">window.onload = function(){if(self==top) { document.getElementById('nameHeader').style.display = "inherit"; } }</script><body><h1 id="nameHeader" style="display: none;">PutDatabaseRecord</h1><h2>Description: </h2><p>The PutDatabaseRecord processor uses a specified RecordReader to input (possibly multiple) records from an incoming flow file. These records are translated to SQL statements and executed as a single transaction. If any errors occur, the flow file is routed to failure or retry, and if the records are transmitted successfully, the incoming flow file is routed to success.  The type of statement executed by the processor is specified via the Statement Type property, which accepts some hard-coded values such as INSERT, UPDATE, and DELETE, as well as 'Use statement.type Attribute', which causes the processor to get the statement type from a flow file attribute.  IMPORTANT: If the Statement Type is UPDATE, then the incoming records must not alter the value(s) of the primary keys (or user-specified Update Keys). If such records are encountered, the UPDATE statement issued to the database may do nothing (if no existing records with the new primary key values are found), or could inadvertently corrupt the existing data (by changing records for which the new values of the primary keys exist).</p><h3>Tags: </h3><p>sql, record, jdbc, put, database, update, insert, delete</p><h3>Properties: </h3><p>In the list below, the names of required properties appear in <strong>bold</strong>. Any other properties (not in bold) are considered optional. The table also indicates any default values, and whether a property supports the <a href="../../../../../html/expression-language-guide.html">NiFi Expression Language</a>.</p><table id="properties"><tr><th>Display Name</th><th>API Name</th><th>Default Value</th><th>Allowable Values</th><th>Description</th></tr><tr><td id="name"><strong>Record Reader</strong></td><td>put-db-record-record-reader</td><td></td><td id="allowable-values"><strong>Controller Service API: </strong><br/>RecordReaderFactory<br/><strong>Implementations: </strong><a href="../../../nifi-record-serialization-services-nar/1.19.1/org.apache.nifi.grok.GrokReader/index.html">GrokReader</a><br/><a href="../../../nifi-record-serialization-services-nar/1.19.1/org.apache.nifi.xml.XMLReader/index.html">XMLReader</a><br/><a href="../../../nifi-record-serialization-services-nar/1.19.1/org.apache.nifi.avro.AvroReader/index.html">AvroReader</a><br/><a href="../../../nifi-record-serialization-services-nar/1.19.1/org.apache.nifi.syslog.Syslog5424Reader/index.html">Syslog5424Reader</a><br/><a href="../../../nifi-record-serialization-services-nar/1.19.1/org.apache.nifi.cef.CEFReader/index.html">CEFReader</a><br/><a href="../../../nifi-record-serialization-services-nar/1.19.1/org.apache.nifi.syslog.SyslogReader/index.html">SyslogReader</a><br/><a href="../../../nifi-record-serialization-services-nar/1.19.1/org.apache.nifi.json.JsonTreeReader/index.html">JsonTreeReader</a><br/><a href="../../../nifi-record-serialization-services-nar/1.19.1/org.apache.nifi.csv.CSVReader/index.html">CSVReader</a><br/><a href="../../../nifi-scripting-nar/1.19.1/org.apache.nifi.record.script.ScriptedReader/index.html">ScriptedReader</a><br/><a href="../../../nifi-parquet-nar/1.19.1/org.apache.nifi.parquet.ParquetReader/index.html">ParquetReader</a><br/><a href="../../../nifi-record-serialization-services-nar/1.19.1/org.apache.nifi.json.JsonPathReader/index.html">JsonPathReader</a><br/><a href="../../../nifi-record-serialization-services-nar/1.19.1/org.apache.nifi.lookup.ReaderLookup/index.html">ReaderLookup</a><br/><a href="../../../nifi-record-serialization-services-nar/1.19.1/org.apache.nifi.windowsevent.WindowsEventLogReader/index.html">WindowsEventLogReader</a></td><td id="description">Specifies the Controller Service to use for parsing incoming data and determining the data's schema.</td></tr><tr><td id="name">Database Type</td><td>db-type</td><td id="default-value">Generic</td><td id="allowable-values"><ul><li>Generic <img src="../../../../../html/images/iconInfo.png" alt="Generates ANSI SQL" title="Generates ANSI SQL"></img></li><li>Oracle <img src="../../../../../html/images/iconInfo.png" alt="Generates Oracle compliant SQL" title="Generates Oracle compliant SQL"></img></li><li>Oracle 12+ <img src="../../../../../html/images/iconInfo.png" alt="Generates Oracle compliant SQL for version 12 or greater" title="Generates Oracle compliant SQL for version 12 or greater"></img></li><li>MS SQL 2012+ <img src="../../../../../html/images/iconInfo.png" alt="Generates MS SQL Compatible SQL, for version 2012 or greater" title="Generates MS SQL Compatible SQL, for version 2012 or greater"></img></li><li>MS SQL 2008 <img src="../../../../../html/images/iconInfo.png" alt="Generates MS SQL Compatible SQL for version 2008" title="Generates MS SQL Compatible SQL for version 2008"></img></li><li>MySQL <img src="../../../../../html/images/iconInfo.png" alt="Generates MySQL compatible SQL" title="Generates MySQL compatible SQL"></img></li><li>PostgreSQL <img src="../../../../../html/images/iconInfo.png" alt="Generates PostgreSQL compatible SQL" title="Generates PostgreSQL compatible SQL"></img></li></ul></td><td id="description">The type/flavor of database, used for generating database-specific code. In many cases the Generic type should suffice, but some databases (such as Oracle) require custom SQL clauses. </td></tr><tr><td id="name"><strong>Statement Type</strong></td><td>put-db-record-statement-type</td><td></td><td id="allowable-values"><ul><li>UPDATE</li><li>INSERT</li><li>UPSERT</li><li>INSERT_IGNORE</li><li>DELETE</li><li>Use statement.type Attribute</li><li>Use Record Path</li></ul></td><td id="description">Specifies the type of SQL Statement to generate. Please refer to the database documentation for a description of the behavior of each operation. Please note that some Database Types may not support certain Statement Types. If 'Use statement.type Attribute' is chosen, then the value is taken from the statement.type attribute in the FlowFile. The 'Use statement.type Attribute' option is the only one that allows the 'SQL' statement type. If 'SQL' is specified, the value of the field specified by the 'Field Containing SQL' property is expected to be a valid SQL statement on the target database, and will be executed as-is.</td></tr><tr><td id="name"><strong>Statement Type Record Path</strong></td><td>Statement Type Record Path</td><td></td><td id="allowable-values"></td><td id="description">Specifies a RecordPath to evaluate against each Record in order to determine the Statement Type. The RecordPath should equate to either INSERT, UPDATE, UPSERT, or DELETE.<br/><br/><strong>This Property is only considered if </strong><strong>the [Statement Type] Property has a value of "Use Record Path".</strong></td></tr><tr><td id="name">Data Record Path</td><td>Data Record Path</td><td></td><td id="allowable-values"></td><td id="description">If specified, this property denotes a RecordPath that will be evaluated against each incoming Record and the Record that results from evaluating the RecordPath will be sent to the database instead of sending the entire incoming Record. If not specified, the entire incoming Record will be published to the database.</td></tr><tr><td id="name"><strong>Database Connection Pooling Service</strong></td><td>put-db-record-dcbp-service</td><td></td><td id="allowable-values"><strong>Controller Service API: </strong><br/>DBCPService<br/><strong>Implementations: </strong><a href="../../../nifi-hadoop-dbcp-service-nar/1.19.1/org.apache.nifi.dbcp.HadoopDBCPConnectionPool/index.html">HadoopDBCPConnectionPool</a><br/><a href="../../../nifi-dbcp-service-nar/1.19.1/org.apache.nifi.dbcp.DBCPConnectionPoolLookup/index.html">DBCPConnectionPoolLookup</a><br/><a href="../../../nifi-dbcp-service-nar/1.19.1/org.apache.nifi.dbcp.DBCPConnectionPool/index.html">DBCPConnectionPool</a><br/><a href="../../../nifi-dbcp-service-nar/1.19.1/org.apache.nifi.dbcp.HikariCPConnectionPool/index.html">HikariCPConnectionPool</a></td><td id="description">The Controller Service that is used to obtain a connection to the database for sending records.</td></tr><tr><td id="name">Catalog Name</td><td>put-db-record-catalog-name</td><td></td><td id="allowable-values"></td><td id="description">The name of the catalog that the statement should update. This may not apply for the database that you are updating. In this case, leave the field empty. Note that if the property is set and the database is case-sensitive, the catalog name must match the database's catalog name exactly.<br/><strong>Supports Expression Language: true (will be evaluated using flow file attributes and variable registry)</strong></td></tr><tr><td id="name">Schema Name</td><td>put-db-record-schema-name</td><td></td><td id="allowable-values"></td><td id="description">The name of the schema that the table belongs to. This may not apply for the database that you are updating. In this case, leave the field empty. Note that if the property is set and the database is case-sensitive, the schema name must match the database's schema name exactly.<br/><strong>Supports Expression Language: true (will be evaluated using flow file attributes and variable registry)</strong></td></tr><tr><td id="name"><strong>Table Name</strong></td><td>put-db-record-table-name</td><td></td><td id="allowable-values"></td><td id="description">The name of the table that the statement should affect. Note that if the database is case-sensitive, the table name must match the database's table name exactly.<br/><strong>Supports Expression Language: true (will be evaluated using flow file attributes and variable registry)</strong></td></tr><tr><td id="name">Translate Field Names</td><td>put-db-record-translate-field-names</td><td id="default-value">true</td><td id="allowable-values"><ul><li>true</li><li>false</li></ul></td><td id="description">If true, the Processor will attempt to translate field names into the appropriate column names for the table specified. If false, the field names must match the column names exactly, or the column will not be updated</td></tr><tr><td id="name">Unmatched Field Behavior</td><td>put-db-record-unmatched-field-behavior</td><td id="default-value">Ignore Unmatched Fields</td><td id="allowable-values"><ul><li>Ignore Unmatched Fields <img src="../../../../../html/images/iconInfo.png" alt="Any field in the document that cannot be mapped to a column in the database is ignored" title="Any field in the document that cannot be mapped to a column in the database is ignored"></img></li><li>Fail on Unmatched Fields <img src="../../../../../html/images/iconInfo.png" alt="If the document has any field that cannot be mapped to a column in the database, the FlowFile will be routed to the failure relationship" title="If the document has any field that cannot be mapped to a column in the database, the FlowFile will be routed to the failure relationship"></img></li></ul></td><td id="description">If an incoming record has a field that does not map to any of the database table's columns, this property specifies how to handle the situation</td></tr><tr><td id="name">Unmatched Column Behavior</td><td>put-db-record-unmatched-column-behavior</td><td id="default-value">Fail on Unmatched Columns</td><td id="allowable-values"><ul><li>Ignore Unmatched Columns <img src="../../../../../html/images/iconInfo.png" alt="Any column in the database that does not have a field in the document will be assumed to not be required.  No notification will be logged" title="Any column in the database that does not have a field in the document will be assumed to not be required.  No notification will be logged"></img></li><li>Warn on Unmatched Columns <img src="../../../../../html/images/iconInfo.png" alt="Any column in the database that does not have a field in the document will be assumed to not be required.  A warning will be logged" title="Any column in the database that does not have a field in the document will be assumed to not be required.  A warning will be logged"></img></li><li>Fail on Unmatched Columns <img src="../../../../../html/images/iconInfo.png" alt="A flow will fail if any column in the database that does not have a field in the document.  An error will be logged" title="A flow will fail if any column in the database that does not have a field in the document.  An error will be logged"></img></li></ul></td><td id="description">If an incoming record does not have a field mapping for all of the database table's columns, this property specifies how to handle the situation</td></tr><tr><td id="name">Update Keys</td><td>put-db-record-update-keys</td><td></td><td id="allowable-values"></td><td id="description">A comma-separated list of column names that uniquely identifies a row in the database for UPDATE statements. If the Statement Type is UPDATE and this property is not set, the table's Primary Keys are used. In this case, if no Primary Key exists, the conversion to SQL will fail if Unmatched Column Behaviour is set to FAIL. This property is ignored if the Statement Type is INSERT<br/><strong>Supports Expression Language: true (will be evaluated using flow file attributes and variable registry)</strong><br/><br/><strong>This Property is only considered if </strong><strong>the [Statement Type] Property is set to one of the following values: [Use statement.type Attribute], [Use Record Path], [UPSERT], [UPDATE]</strong></td></tr><tr><td id="name">Field Containing SQL</td><td>put-db-record-field-containing-sql</td><td></td><td id="allowable-values"></td><td id="description">If the Statement Type is 'SQL' (as set in the statement.type attribute), this field indicates which field in the record(s) contains the SQL statement to execute. The value of the field must be a single SQL statement. If the Statement Type is not 'SQL', this field is ignored.<br/><strong>Supports Expression Language: true (will be evaluated using flow file attributes and variable registry)</strong><br/><br/><strong>This Property is only considered if </strong><strong>the [Statement Type] Property is set to one of the following values: [Use statement.type Attribute], [Use Record Path]</strong></td></tr><tr><td id="name"><strong>Allow Multiple SQL Statements</strong></td><td>put-db-record-allow-multiple-statements</td><td id="default-value">false</td><td id="allowable-values"><ul><li>true</li><li>false</li></ul></td><td id="description">If the Statement Type is 'SQL' (as set in the statement.type attribute), this field indicates whether to split the field value by a semicolon and execute each statement separately. If any statement causes an error, the entire set of statements will be rolled back. If the Statement Type is not 'SQL', this field is ignored.<br/><br/><strong>This Property is only considered if </strong><strong>the [Statement Type] Property is set to one of the following values: [Use statement.type Attribute], [Use Record Path]</strong></td></tr><tr><td id="name">Quote Column Identifiers</td><td>put-db-record-quoted-identifiers</td><td id="default-value">false</td><td id="allowable-values"><ul><li>true</li><li>false</li></ul></td><td id="description">Enabling this option will cause all column names to be quoted, allowing you to use reserved words as column names in your tables.</td></tr><tr><td id="name">Quote Table Identifiers</td><td>put-db-record-quoted-table-identifiers</td><td id="default-value">false</td><td id="allowable-values"><ul><li>true</li><li>false</li></ul></td><td id="description">Enabling this option will cause the table name to be quoted to support the use of special characters in the table name.</td></tr><tr><td id="name"><strong>Max Wait Time</strong></td><td>put-db-record-query-timeout</td><td id="default-value">0 seconds</td><td id="allowable-values"></td><td id="description">The maximum amount of time allowed for a running SQL statement , zero means there is no limit. Max time less than 1 second will be equal to zero.<br/><strong>Supports Expression Language: true (will be evaluated using variable registry only)</strong></td></tr><tr><td id="name"><strong>Rollback On Failure</strong></td><td>rollback-on-failure</td><td id="default-value">false</td><td id="allowable-values"><ul><li>true</li><li>false</li></ul></td><td id="description">Specify how to handle error. By default (false), if an error occurs while processing a FlowFile, the FlowFile will be routed to 'failure' or 'retry' relationship based on error type, and processor can continue with next FlowFile. Instead, you may want to rollback currently processed FlowFiles and stop further processing immediately. In that case, you can do so by enabling this 'Rollback On Failure' property.  If enabled, failed FlowFiles will stay in the input relationship without penalizing it and being processed repeatedly until it gets processed successfully or removed by other means. It is important to set adequate 'Yield Duration' to avoid retrying too frequently.</td></tr><tr><td id="name"><strong>Table Schema Cache Size</strong></td><td>table-schema-cache-size</td><td id="default-value">100</td><td id="allowable-values"></td><td id="description">Specifies how many Table Schemas should be cached</td></tr><tr><td id="name">Maximum Batch Size</td><td>put-db-record-max-batch-size</td><td id="default-value">0</td><td id="allowable-values"></td><td id="description">Specifies maximum batch size for INSERT and UPDATE statements. This parameter has no effect for other statements specified in 'Statement Type'. Zero means the batch size is not limited.<br/><strong>Supports Expression Language: true (will be evaluated using flow file attributes and variable registry)</strong><br/><br/><strong>This Property is only considered if </strong><strong>the [Statement Type] Property is set to one of the following values: [Use statement.type Attribute], [Use Record Path], [INSERT], [UPDATE]</strong></td></tr></table><h3>Relationships: </h3><table id="relationships"><tr><th>Name</th><th>Description</th></tr><tr><td>retry</td><td>A FlowFile is routed to this relationship if the database cannot be updated but attempting the operation again may succeed</td></tr><tr><td>success</td><td>Successfully created FlowFile from SQL query result set.</td></tr><tr><td>failure</td><td>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</td></tr></table><h3>Reads Attributes: </h3><table id="reads-attributes"><tr><th>Name</th><th>Description</th></tr><tr><td>statement.type</td><td>If 'Use statement.type Attribute' is selected for the Statement Type property, the value of this attribute will be used to determine the type of statement (INSERT, UPDATE, DELETE, SQL, etc.) to generate and execute.</td></tr></table><h3>Writes Attributes: </h3><table id="writes-attributes"><tr><th>Name</th><th>Description</th></tr><tr><td>putdatabaserecord.error</td><td>If an error occurs during processing, the flow file will be routed to failure or retry, and this attribute will be populated with the cause of the error.</td></tr></table><h3>State management: </h3>This component does not store state.<h3>Restricted: </h3>This component is not restricted.<h3>Input requirement: </h3>This component requires an incoming relationship.<h3>System Resource Considerations:</h3>None specified.</body></html>