Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Moving data from Oracle into IBM DB2 via NiFi

Highlighted

Moving data from Oracle into IBM DB2 via NiFi

New Contributor

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.


107842-1555071430353.png



107767-1555071370544.png

2 REPLIES 2

Re: Moving data from Oracle into IBM DB2 via NiFi

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.

Re: Moving data from Oracle into IBM DB2 via NiFi

New Contributor

Thank you very much Mr. Burgess!