Support Questions
Find answers, ask questions, and share your expertise
Check out our newest addition to the community, the Cloudera Innovation Accelerator group hub.

Moving data from Oracle into IBM DB2 via NiFi


Hello, so I am stuck on this NiFi flow, everything will flow correctly until I get to the PutSQL, when the flowfiles are queued between the CONVERTJSONTOSQL AND PUTSQL, 'view' content looks like:

"INSERT INTO [new_table_name] (Column_name1,Column_name2,Column_name3,Column_name4,Column_name5,Column_name6,Column_name7,Column_name8,Column_name9,Column_name10,Column_name11,Column_name12,Column_name13,Column_name14 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

The ??????? are where I believe my error is and it's probably due to my SQL Statement in the 2nd picture below, any advice or help would be greatly appreciated.




Super Guru

The SQL Statement property in PutSQL is for when you want to retain the content of the incoming flow file but execute some SQL statement as a result of receiving the flow file. This makes it more event-driven, and you can use attributes from the flow file in the SQL statement, but at the end of evaluation, it has to be a literal statement, not a prepared statement with ?s or []s. ConvertJSONToSQL generates a prepared statement for you, along with attributes that PutSQL looks for (if SQL Statement is not set) to populate the statement with.

Having said that, you can replace everything in your flow above after QueryDatabaseTable with PutDatabaseRecord, that way you don't have to do any splits or conversions. PutDatabaseRecord effectively does each of those steps in one execution: it generates a prepared statement then iterates over each record in the flow file, adding the values to a batch, then executes the batch. You should find it is less error-prone, easier to configure, and reduces the number of flow files and provenance events in the system.


Thank you very much Mr. Burgess!