@SideEffectFree @SupportsBatching @SeeAlso(value=PutSQL.class) @InputRequirement(value=INPUT_REQUIRED) @Tags(value={"json","sql","database","rdbms","insert","update","delete","relational","flat"}) @CapabilityDescription(value="Converts a JSON-formatted FlowFile into an UPDATE, INSERT, or DELETE SQL statement. The incoming FlowFile is expected to be \"flat\" JSON message, meaning that it consists of a single JSON element and each field maps to a simple type. If a field maps to a JSON object, that JSON object will be interpreted as Text. If the input is an array of JSON elements, each element in the array is output as a separate FlowFile to the \'sql\' relationship. Upon successful conversion, the original FlowFile is routed to the \'original\' relationship and the SQL is routed to the \'sql\' relationship.") @WritesAttribute(attribute="mime.type",description="Sets mime.type of FlowFile that is routed to \'sql\' to \'text/plain\'.") @WritesAttribute(attribute="<sql>.table",description="Sets the <sql>.table attribute of FlowFile that is routed to \'sql\' to the name of the table that is updated by the SQL statement. The prefix for this attribute (\'sql\', e.g.) is determined by the SQL Parameter Attribute Prefix property.") @WritesAttribute(attribute="<sql>.catalog",description="If the Catalog name is set for this database, specifies the name of the catalog that the SQL statement will update. If no catalog is used, this attribute will not be added. The prefix for this attribute (\'sql\', e.g.) is determined by the SQL Parameter Attribute Prefix property.") @WritesAttribute(attribute="fragment.identifier",description="All FlowFiles routed to the \'sql\' relationship for the same incoming FlowFile (multiple will be output for the same incoming FlowFile if the incoming FlowFile is a JSON Array) will have the same value for the fragment.identifier attribute. This can then be used to correlate the results.") @WritesAttribute(attribute="fragment.count",description="The number of SQL FlowFiles that were produced for same incoming FlowFile. This can be used in conjunction with the fragment.identifier attribute in order to know how many FlowFiles belonged to the same incoming FlowFile.") @WritesAttribute(attribute="fragment.index",description="The position of this FlowFile in the list of outgoing FlowFiles that were all derived from the same incoming FlowFile. This can be used in conjunction with the fragment.identifier and fragment.count attributes to know which FlowFiles originated from the same incoming FlowFile and in what order the SQL FlowFiles were produced") @WritesAttribute(attribute="<sql>.args.N.type",description="The output SQL statements are parametrized in order to avoid SQL Injection Attacks. The types of the Parameters to use are stored in attributes named <sql>.args.1.type, <sql>.args.2.type, <sql>.args.3.type, and so on. The type is a number representing a JDBC Type constant. Generally, this is useful only for software to read and interpret but is added so that a processor such as PutSQL can understand how to interpret the values. The prefix for this attribute (\'sql\', e.g.) is determined by the SQL Parameter Attribute Prefix property.") @WritesAttribute(attribute="<sql>.args.N.value",description="The output SQL statements are parametrized in order to avoid SQL Injection Attacks. The values of the Parameters to use are stored in the attributes named sql.args.1.value, sql.args.2.value, sql.args.3.value, and so on. Each of these attributes has a corresponding <sql>.args.N.type attribute that indicates how the value should be interpreted when inserting it into the database.The prefix for this attribute (\'sql\', e.g.) is determined by the SQL Parameter Attribute Prefix property.") public class ConvertJSONToSQL extends AbstractProcessor
Modifier and Type | Class and Description |
---|---|
private static class |
ConvertJSONToSQL.ColumnDescription |
private static class |
ConvertJSONToSQL.SchemaKey |
private static class |
ConvertJSONToSQL.TableSchema |
Constructor and Description |
---|
ConvertJSONToSQL() |
Modifier and Type | Method and Description |
---|---|
protected static String |
createSqlStringValue(org.codehaus.jackson.JsonNode fieldNode,
Integer colSize,
int sqlType)
Try to create correct SQL String representation of value.
|
private String |
generateDelete(org.codehaus.jackson.JsonNode rootNode,
Map<String,String> attributes,
String tableName,
ConvertJSONToSQL.TableSchema schema,
boolean translateFieldNames,
boolean ignoreUnmappedFields,
boolean failUnmappedColumns,
boolean warningUnmappedColumns,
boolean escapeColumnNames,
boolean quoteTableName,
String attributePrefix) |
private String |
generateInsert(org.codehaus.jackson.JsonNode rootNode,
Map<String,String> attributes,
String tableName,
ConvertJSONToSQL.TableSchema schema,
boolean translateFieldNames,
boolean ignoreUnmappedFields,
boolean failUnmappedColumns,
boolean warningUnmappedColumns,
boolean escapeColumnNames,
boolean quoteTableName,
String attributePrefix) |
private String |
generateUpdate(org.codehaus.jackson.JsonNode rootNode,
Map<String,String> attributes,
String tableName,
String updateKeys,
ConvertJSONToSQL.TableSchema schema,
boolean translateFieldNames,
boolean ignoreUnmappedFields,
boolean failUnmappedColumns,
boolean warningUnmappedColumns,
boolean escapeColumnNames,
boolean quoteTableName,
String attributePrefix) |
private Set<String> |
getNormalizedColumnNames(org.codehaus.jackson.JsonNode node,
boolean translateFieldNames) |
Set<Relationship> |
getRelationships() |
protected List<PropertyDescriptor> |
getSupportedPropertyDescriptors() |
private static String |
normalizeColumnName(String colName,
boolean translateColumnNames) |
void |
onScheduled(ProcessContext context) |
void |
onTrigger(ProcessContext context,
ProcessSession session) |
onTrigger
getControllerServiceLookup, getIdentifier, getLogger, getNodeTypeProvider, init, initialize, isConfigurationRestored, isScheduled, toString, updateConfiguredRestoredTrue, updateScheduledFalse, updateScheduledTrue
customValidate, equals, getPropertyDescriptor, getPropertyDescriptors, getSupportedDynamicPropertyDescriptor, hashCode, onPropertyModified, validate
clone, finalize, getClass, notify, notifyAll, wait, wait, wait
getPropertyDescriptor, getPropertyDescriptors, onPropertyModified, validate
private static final String UPDATE_TYPE
private static final String INSERT_TYPE
private static final String DELETE_TYPE
static final AllowableValue IGNORE_UNMATCHED_FIELD
static final AllowableValue FAIL_UNMATCHED_FIELD
static final AllowableValue IGNORE_UNMATCHED_COLUMN
static final AllowableValue WARNING_UNMATCHED_COLUMN
static final AllowableValue FAIL_UNMATCHED_COLUMN
static final PropertyDescriptor CONNECTION_POOL
static final PropertyDescriptor STATEMENT_TYPE
static final PropertyDescriptor TABLE_NAME
static final PropertyDescriptor CATALOG_NAME
static final PropertyDescriptor SCHEMA_NAME
static final PropertyDescriptor TRANSLATE_FIELD_NAMES
static final PropertyDescriptor UNMATCHED_FIELD_BEHAVIOR
static final PropertyDescriptor UNMATCHED_COLUMN_BEHAVIOR
static final PropertyDescriptor UPDATE_KEY
static final PropertyDescriptor QUOTED_IDENTIFIERS
static final PropertyDescriptor QUOTED_TABLE_IDENTIFIER
static final PropertyDescriptor SQL_PARAM_ATTR_PREFIX
static final PropertyDescriptor TABLE_SCHEMA_CACHE_SIZE
static final Relationship REL_ORIGINAL
static final Relationship REL_SQL
static final Relationship REL_FAILURE
private com.github.benmanes.caffeine.cache.Cache<ConvertJSONToSQL.SchemaKey,ConvertJSONToSQL.TableSchema> schemaCache
protected List<PropertyDescriptor> getSupportedPropertyDescriptors()
getSupportedPropertyDescriptors
in class AbstractConfigurableComponent
public Set<Relationship> getRelationships()
getRelationships
in interface Processor
getRelationships
in class AbstractSessionFactoryProcessor
@OnScheduled public void onScheduled(ProcessContext context)
public void onTrigger(ProcessContext context, ProcessSession session) throws ProcessException
onTrigger
in class AbstractProcessor
ProcessException
private Set<String> getNormalizedColumnNames(org.codehaus.jackson.JsonNode node, boolean translateFieldNames)
private String generateInsert(org.codehaus.jackson.JsonNode rootNode, Map<String,String> attributes, String tableName, ConvertJSONToSQL.TableSchema schema, boolean translateFieldNames, boolean ignoreUnmappedFields, boolean failUnmappedColumns, boolean warningUnmappedColumns, boolean escapeColumnNames, boolean quoteTableName, String attributePrefix)
protected static String createSqlStringValue(org.codehaus.jackson.JsonNode fieldNode, Integer colSize, int sqlType)
private String generateUpdate(org.codehaus.jackson.JsonNode rootNode, Map<String,String> attributes, String tableName, String updateKeys, ConvertJSONToSQL.TableSchema schema, boolean translateFieldNames, boolean ignoreUnmappedFields, boolean failUnmappedColumns, boolean warningUnmappedColumns, boolean escapeColumnNames, boolean quoteTableName, String attributePrefix)
private String generateDelete(org.codehaus.jackson.JsonNode rootNode, Map<String,String> attributes, String tableName, ConvertJSONToSQL.TableSchema schema, boolean translateFieldNames, boolean ignoreUnmappedFields, boolean failUnmappedColumns, boolean warningUnmappedColumns, boolean escapeColumnNames, boolean quoteTableName, String attributePrefix)
Copyright © 2020 Apache NiFi Project. All rights reserved.