org.apache.nifi.processors.standard

Class PutDatabaseRecord

java.lang.Object

org.apache.nifi.components.AbstractConfigurableComponent

org.apache.nifi.processor.AbstractSessionFactoryProcessor

org.apache.nifi.processor.AbstractProcessor

org.apache.nifi.processors.standard.PutDatabaseRecord

All Implemented Interfaces:

ConfigurableComponent, Processor

@EventDriven
 @InputRequirement(value=INPUT_REQUIRED)
 @Tags(value={"sql","record","jdbc","put","database","update","insert","delete"})
 @CapabilityDescription(value="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).")
 @ReadsAttribute(attribute="statement.type",
                description="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.")
 @WritesAttribute(attribute="putdatabaserecord.error",
                 description="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.")
public class PutDatabaseRecord
extends AbstractProcessor

Nested Class Summary

Nested Classes

Modifier and Type	Class and Description
(package private) static class	PutDatabaseRecord.DMLSettings
(package private) static class	PutDatabaseRecord.PreparedSqlAndColumns
private static class	PutDatabaseRecord.RecordPathStatementType
(package private) static class	PutDatabaseRecord.SchemaKey
(package private) static class	PutDatabaseRecord.SqlAndIncludedColumns A holder class for a SQL prepared statement and a BitSet indicating which columns are being updated (to determine which values from the record to set on the statement) A value of null for getIncludedColumns indicates that all columns/fields should be included.

Field Summary

Fields

Modifier and Type	Field and Description
(package private) static PropertyDescriptor	ALLOW_MULTIPLE_STATEMENTS
(package private) static PropertyDescriptor	CATALOG_NAME
(package private) static PropertyDescriptor	DATA_RECORD_PATH
private DatabaseAdapter	databaseAdapter
private RecordPath	dataRecordPath
(package private) static PropertyDescriptor	DB_TYPE
protected static Map<String,DatabaseAdapter>	dbAdapters
(package private) static PropertyDescriptor	DBCP_SERVICE
static String	DELETE_TYPE
(package private) static AllowableValue	FAIL_UNMATCHED_COLUMN
(package private) static AllowableValue	FAIL_UNMATCHED_FIELD
(package private) static PropertyDescriptor	FIELD_CONTAINING_SQL
(package private) static AllowableValue	IGNORE_UNMATCHED_COLUMN
(package private) static AllowableValue	IGNORE_UNMATCHED_FIELD
static String	INSERT_IGNORE_TYPE
static String	INSERT_TYPE
(package private) static PropertyDescriptor	MAX_BATCH_SIZE
protected static List<PropertyDescriptor>	propDescriptors
(package private) static String	PUT_DATABASE_RECORD_ERROR
(package private) static PropertyDescriptor	QUERY_TIMEOUT
(package private) static PropertyDescriptor	QUOTE_IDENTIFIERS
(package private) static PropertyDescriptor	QUOTE_TABLE_IDENTIFIER
(package private) static PropertyDescriptor	RECORD_READER_FACTORY
private Function<Record,String>	recordPathOperationType
(package private) static Relationship	REL_FAILURE
(package private) static Relationship	REL_RETRY
static Relationship	REL_SUCCESS
protected static Set<Relationship>	relationships
(package private) static PropertyDescriptor	SCHEMA_NAME
private com.github.benmanes.caffeine.cache.Cache<PutDatabaseRecord.SchemaKey,TableSchema>	schemaCache
static String	SQL_TYPE
(package private) static PropertyDescriptor	STATEMENT_TYPE
(package private) static String	STATEMENT_TYPE_ATTRIBUTE
(package private) static PropertyDescriptor	STATEMENT_TYPE_RECORD_PATH
(package private) static PropertyDescriptor	TABLE_NAME
(package private) static PropertyDescriptor	TABLE_SCHEMA_CACHE_SIZE
(package private) static PropertyDescriptor	TRANSLATE_FIELD_NAMES
(package private) static PropertyDescriptor	UNMATCHED_COLUMN_BEHAVIOR
(package private) static PropertyDescriptor	UNMATCHED_FIELD_BEHAVIOR
(package private) static PropertyDescriptor	UPDATE_KEYS
static String	UPDATE_TYPE
static String	UPSERT_TYPE
static String	USE_ATTR_TYPE
static String	USE_RECORD_PATH
(package private) static AllowableValue	WARNING_UNMATCHED_COLUMN

Constructor Summary

Constructors

Constructor and Description
PutDatabaseRecord()

Method Summary

Modifier and Type	Method and Description
private void	checkValuesForRequiredColumns(RecordSchema recordSchema, TableSchema tableSchema, PutDatabaseRecord.DMLSettings settings)
protected Collection<ValidationResult>	customValidate(ValidationContext validationContext)
private void	executeDML(ProcessContext context, ProcessSession session, FlowFile flowFile, Connection con, RecordReader recordReader, String explicitStatementType, PutDatabaseRecord.DMLSettings settings)
private void	executeSQL(ProcessContext context, FlowFile flowFile, Connection connection, RecordReader recordReader)
(package private) PutDatabaseRecord.SqlAndIncludedColumns	generateDelete(RecordSchema recordSchema, String tableName, TableSchema tableSchema, PutDatabaseRecord.DMLSettings settings)
(package private) PutDatabaseRecord.SqlAndIncludedColumns	generateInsert(RecordSchema recordSchema, String tableName, TableSchema tableSchema, PutDatabaseRecord.DMLSettings settings)
(package private) PutDatabaseRecord.SqlAndIncludedColumns	generateInsertIgnore(RecordSchema recordSchema, String tableName, String updateKeys, TableSchema tableSchema, PutDatabaseRecord.DMLSettings settings)
(package private) String	generateTableName(PutDatabaseRecord.DMLSettings settings, String catalog, String schemaName, String tableName, TableSchema tableSchema)
(package private) PutDatabaseRecord.SqlAndIncludedColumns	generateUpdate(RecordSchema recordSchema, String tableName, String updateKeys, TableSchema tableSchema, PutDatabaseRecord.DMLSettings settings)
(package private) PutDatabaseRecord.SqlAndIncludedColumns	generateUpsert(RecordSchema recordSchema, String tableName, String updateKeys, TableSchema tableSchema, PutDatabaseRecord.DMLSettings settings)
private List<Record>	getDataRecords(Record outerRecord)
private String	getJdbcUrl(Connection connection)
private Set<String>	getNormalizedColumnNames(RecordSchema schema, boolean translateFieldNames)
Set<Relationship>	getRelationships()
private String	getStatementType(ProcessContext context, FlowFile flowFile)
protected PropertyDescriptor	getSupportedDynamicPropertyDescriptor(String propertyDescriptorName)
protected List<PropertyDescriptor>	getSupportedPropertyDescriptors()
private Set<String>	getUpdateKeyColumnNames(String tableName, String updateKeys, TableSchema tableSchema)
private Set<String>	normalizeKeyColumnNamesAndCheckForValues(RecordSchema recordSchema, String updateKeys, PutDatabaseRecord.DMLSettings settings, Set<String> updateKeyColumnNames, String quoteString)
void	onScheduled(ProcessContext context)
void	onTrigger(ProcessContext context, ProcessSession session)
private void	putToDatabase(ProcessContext context, ProcessSession session, FlowFile flowFile, Connection connection)
private void	setParameter(PreparedStatement ps, int index, Object value, int fieldSqlType, int sqlType)
private String	validateStatementType(String statementType, FlowFile flowFile)

Methods inherited from class org.apache.nifi.processor.AbstractProcessor

onTrigger

Methods inherited from class org.apache.nifi.processor.AbstractSessionFactoryProcessor

getControllerServiceLookup, getIdentifier, getLogger, getNodeTypeProvider, init, initialize, isConfigurationRestored, isScheduled, toString, updateConfiguredRestoredTrue, updateScheduledFalse, updateScheduledTrue

Methods inherited from class org.apache.nifi.components.AbstractConfigurableComponent

equals, getPropertyDescriptor, getPropertyDescriptors, hashCode, onPropertyModified, validate

Methods inherited from class java.lang.Object

clone, finalize, getClass, notify, notifyAll, wait, wait, wait

Methods inherited from interface org.apache.nifi.processor.Processor

isStateful

Methods inherited from interface org.apache.nifi.components.ConfigurableComponent

getPropertyDescriptor, getPropertyDescriptors, onPropertyModified, validate

Field Detail

UPDATE_TYPE

public static final String UPDATE_TYPE

See Also:

Constant Field Values

INSERT_TYPE

public static final String INSERT_TYPE

See Also:

Constant Field Values

DELETE_TYPE

public static final String DELETE_TYPE

See Also:

Constant Field Values

UPSERT_TYPE

public static final String UPSERT_TYPE

See Also:

Constant Field Values

INSERT_IGNORE_TYPE

public static final String INSERT_IGNORE_TYPE

See Also:

Constant Field Values

SQL_TYPE

public static final String SQL_TYPE

See Also:

Constant Field Values

USE_ATTR_TYPE

public static final String USE_ATTR_TYPE

See Also:

Constant Field Values

USE_RECORD_PATH

public static final String USE_RECORD_PATH

See Also:

Constant Field Values

STATEMENT_TYPE_ATTRIBUTE

static final String STATEMENT_TYPE_ATTRIBUTE

See Also:

Constant Field Values

PUT_DATABASE_RECORD_ERROR

static final String PUT_DATABASE_RECORD_ERROR

See Also:

Constant Field Values

IGNORE_UNMATCHED_FIELD

static final AllowableValue IGNORE_UNMATCHED_FIELD

FAIL_UNMATCHED_FIELD

static final AllowableValue FAIL_UNMATCHED_FIELD

IGNORE_UNMATCHED_COLUMN

static final AllowableValue IGNORE_UNMATCHED_COLUMN

WARNING_UNMATCHED_COLUMN

static final AllowableValue WARNING_UNMATCHED_COLUMN

FAIL_UNMATCHED_COLUMN

static final AllowableValue FAIL_UNMATCHED_COLUMN

REL_SUCCESS

public static final Relationship REL_SUCCESS

REL_RETRY

static final Relationship REL_RETRY

REL_FAILURE

static final Relationship REL_FAILURE

relationships

protected static Set<Relationship> relationships

RECORD_READER_FACTORY

static final PropertyDescriptor RECORD_READER_FACTORY

STATEMENT_TYPE

static final PropertyDescriptor STATEMENT_TYPE

STATEMENT_TYPE_RECORD_PATH

static final PropertyDescriptor STATEMENT_TYPE_RECORD_PATH

DATA_RECORD_PATH

static final PropertyDescriptor DATA_RECORD_PATH

DBCP_SERVICE

static final PropertyDescriptor DBCP_SERVICE

CATALOG_NAME

static final PropertyDescriptor CATALOG_NAME

SCHEMA_NAME

static final PropertyDescriptor SCHEMA_NAME

TABLE_NAME

static final PropertyDescriptor TABLE_NAME

TRANSLATE_FIELD_NAMES

static final PropertyDescriptor TRANSLATE_FIELD_NAMES

UNMATCHED_FIELD_BEHAVIOR

static final PropertyDescriptor UNMATCHED_FIELD_BEHAVIOR

UNMATCHED_COLUMN_BEHAVIOR

static final PropertyDescriptor UNMATCHED_COLUMN_BEHAVIOR

UPDATE_KEYS

static final PropertyDescriptor UPDATE_KEYS

FIELD_CONTAINING_SQL

static final PropertyDescriptor FIELD_CONTAINING_SQL

ALLOW_MULTIPLE_STATEMENTS

static final PropertyDescriptor ALLOW_MULTIPLE_STATEMENTS

QUOTE_IDENTIFIERS

static final PropertyDescriptor QUOTE_IDENTIFIERS

QUOTE_TABLE_IDENTIFIER

static final PropertyDescriptor QUOTE_TABLE_IDENTIFIER

QUERY_TIMEOUT

static final PropertyDescriptor QUERY_TIMEOUT

TABLE_SCHEMA_CACHE_SIZE

static final PropertyDescriptor TABLE_SCHEMA_CACHE_SIZE

MAX_BATCH_SIZE

static final PropertyDescriptor MAX_BATCH_SIZE

DB_TYPE

static final PropertyDescriptor DB_TYPE

dbAdapters

protected static final Map<String,DatabaseAdapter> dbAdapters

propDescriptors

protected static List<PropertyDescriptor> propDescriptors

schemaCache

private com.github.benmanes.caffeine.cache.Cache<PutDatabaseRecord.SchemaKey,TableSchema> schemaCache

databaseAdapter

private DatabaseAdapter databaseAdapter

recordPathOperationType

private volatile Function<Record,String> recordPathOperationType

dataRecordPath

private volatile RecordPath dataRecordPath

Constructor Detail

PutDatabaseRecord

public PutDatabaseRecord()

Method Detail

getRelationships

public Set<Relationship> getRelationships()

Specified by:

getRelationships in interface Processor

Overrides:

getRelationships in class AbstractSessionFactoryProcessor

getSupportedPropertyDescriptors

protected List<PropertyDescriptor> getSupportedPropertyDescriptors()

Overrides:

getSupportedPropertyDescriptors in class AbstractConfigurableComponent

getSupportedDynamicPropertyDescriptor

protected PropertyDescriptor getSupportedDynamicPropertyDescriptor(String propertyDescriptorName)

Overrides:

getSupportedDynamicPropertyDescriptor in class AbstractConfigurableComponent

customValidate

protected Collection<ValidationResult> customValidate(ValidationContext validationContext)

Overrides:

customValidate in class AbstractConfigurableComponent

onScheduled

@OnScheduled
public void onScheduled(ProcessContext context)

onTrigger

public void onTrigger(ProcessContext context,
                      ProcessSession session)
               throws ProcessException

Specified by:

onTrigger in class AbstractProcessor

Throws:

ProcessException

executeSQL

private void executeSQL(ProcessContext context,
                        FlowFile flowFile,
                        Connection connection,
                        RecordReader recordReader)
                 throws IllegalArgumentException,
                        MalformedRecordException,
                        IOException,
                        SQLException

Throws:

IllegalArgumentException

MalformedRecordException

IOException

SQLException

executeDML

private void executeDML(ProcessContext context,
                        ProcessSession session,
                        FlowFile flowFile,
                        Connection con,
                        RecordReader recordReader,
                        String explicitStatementType,
                        PutDatabaseRecord.DMLSettings settings)
                 throws IllegalArgumentException,
                        MalformedRecordException,
                        IOException,
                        SQLException

Throws:

IllegalArgumentException

MalformedRecordException

IOException

SQLException

setParameter

private void setParameter(PreparedStatement ps,
                          int index,
                          Object value,
                          int fieldSqlType,
                          int sqlType)
                   throws IOException

Throws:

IOException

getDataRecords

private List<Record> getDataRecords(Record outerRecord)

getJdbcUrl

private String getJdbcUrl(Connection connection)

getStatementType

private String getStatementType(ProcessContext context,
                                FlowFile flowFile)

validateStatementType

private String validateStatementType(String statementType,
                                     FlowFile flowFile)

putToDatabase

private void putToDatabase(ProcessContext context,
                           ProcessSession session,
                           FlowFile flowFile,
                           Connection connection)
                    throws Exception

Throws:

Exception

generateTableName

String generateTableName(PutDatabaseRecord.DMLSettings settings,
                         String catalog,
                         String schemaName,
                         String tableName,
                         TableSchema tableSchema)

getNormalizedColumnNames

private Set<String> getNormalizedColumnNames(RecordSchema schema,
                                             boolean translateFieldNames)

generateInsert

PutDatabaseRecord.SqlAndIncludedColumns generateInsert(RecordSchema recordSchema,
                                                       String tableName,
                                                       TableSchema tableSchema,
                                                       PutDatabaseRecord.DMLSettings settings)
                                                throws IllegalArgumentException,
                                                       SQLException

Throws:

IllegalArgumentException

SQLException

generateUpsert

PutDatabaseRecord.SqlAndIncludedColumns generateUpsert(RecordSchema recordSchema,
                                                       String tableName,
                                                       String updateKeys,
                                                       TableSchema tableSchema,
                                                       PutDatabaseRecord.DMLSettings settings)
                                                throws IllegalArgumentException,
                                                       SQLException,
                                                       MalformedRecordException

Throws:

IllegalArgumentException

SQLException

MalformedRecordException

generateInsertIgnore

PutDatabaseRecord.SqlAndIncludedColumns generateInsertIgnore(RecordSchema recordSchema,
                                                             String tableName,
                                                             String updateKeys,
                                                             TableSchema tableSchema,
                                                             PutDatabaseRecord.DMLSettings settings)
                                                      throws IllegalArgumentException,
                                                             SQLException,
                                                             MalformedRecordException

Throws:

IllegalArgumentException

SQLException

MalformedRecordException

generateUpdate

PutDatabaseRecord.SqlAndIncludedColumns generateUpdate(RecordSchema recordSchema,
                                                       String tableName,
                                                       String updateKeys,
                                                       TableSchema tableSchema,
                                                       PutDatabaseRecord.DMLSettings settings)
                                                throws IllegalArgumentException,
                                                       MalformedRecordException,
                                                       SQLException

Throws:

IllegalArgumentException

MalformedRecordException

SQLException

generateDelete

PutDatabaseRecord.SqlAndIncludedColumns generateDelete(RecordSchema recordSchema,
                                                       String tableName,
                                                       TableSchema tableSchema,
                                                       PutDatabaseRecord.DMLSettings settings)
                                                throws IllegalArgumentException,
                                                       MalformedRecordException,
                                                       SQLDataException

Throws:

IllegalArgumentException

MalformedRecordException

SQLDataException

checkValuesForRequiredColumns

private void checkValuesForRequiredColumns(RecordSchema recordSchema,
                                           TableSchema tableSchema,
                                           PutDatabaseRecord.DMLSettings settings)

getUpdateKeyColumnNames

private Set<String> getUpdateKeyColumnNames(String tableName,
                                            String updateKeys,
                                            TableSchema tableSchema)
                                     throws SQLIntegrityConstraintViolationException

Throws:

SQLIntegrityConstraintViolationException

normalizeKeyColumnNamesAndCheckForValues

private Set<String> normalizeKeyColumnNamesAndCheckForValues(RecordSchema recordSchema,
                                                             String updateKeys,
                                                             PutDatabaseRecord.DMLSettings settings,
                                                             Set<String> updateKeyColumnNames,
                                                             String quoteString)
                                                      throws MalformedRecordException

Throws:

MalformedRecordException