From FlowFile insert into MySQL, how do I return auto increment id value as flowfile attribute in Apache Nifi ?

[#1]

Dear all, 

I have a query regarding the insertion of a flowfile into a MySQL Database. Specifically, I would like to know how to retrieve the auto-incremented ID from the database and store it as a flowfile attribute.

Consider the following example flowfile (representing one record, with many more records in reality):

My goal is to insert this data into 'table1,' which has an auto-incremented ID field (let's assume that after insertion, the ID will be 3001). Could you please guide me on how to retrieve this auto-incremented ID and assign it to a flowfile attribute?

[#2]

Hi @jarviszzzz ,

If you use the PutSQL process there is property called "Obtain Generated Keys" which is described as follows:

"If true, any key that is automatically generated by the database will be added to the FlowFile that generated it using the sql.generate.key attribute. This may result in slightly slower performance and is not supported by all databases."

So basically you dont have to do anything extra besides setting this property to true. The new id should be written back as flowfile attribute which will be called "sql.generate.key".

The PutSQL is very flexible, you can convert the json to SQL using the ConvertJsonToSQL processor and then use the PutSQL without specifying anything in the SQL Statement property, keep in mind that if you choose the ConvertJsonToSQL approach the fields names should match the target table column names as well as the data types should be compatible.  If the fields names dont match and\or you need some flexibility on how to insert the values, you can specify the sql insert statement in the Sql Statement property and use expression language to reference the different json field values but you need first to extract them into flowfile attributes using processor like EvaluateJsonPath and set the destination to flowfile attributes.

If that helps please accept solution.

Thanks

[#3]

Hi @SAMSAL,

Thanks for your reply!

However, I obtained this sql statement as output of the flowfile.

Please check below attachment (from data provenance)

 Here is my setup for Putsql:

Am I done something wrong?

[#4]

what seems to be the issue? please provide more details . If you are getting any error messages please share

[#5]

Hi @SAMSAL ,

Please refer to the attachment:

For the "Queryrecord" Processor, I tried to select updated ID from flowfile.

How can I do that to obtain those new ids?

[#6]

Can you provide screenshot of the ConvertAvroToJson processor? What is the output you are getting out of this processor?

[#7]

Hi @SAMSAL 

Input from data provenance:

Output:

[#8 · Accepted solution]

OK, I see what is happening here. The QueryRecord is not needed and its giving you the error because its expecting Json format but its getting an insert sql statement that is created from the ConvertJsonToSQL processor. Does the record get inserted after the PutSQL gets executed on the insert statement? If so then the new id should be written as flowfile attribute "sql.generate.key" and its not going to be part of the flowfile content. The QueryRecord is not needed here and its not used for this purpose. Each flowfile will have a content which is the actual data that it represents and attributes representing the metadata , when you list the flowfile click on the (i) icon in the first column and select the Attributes tab and it should be there with the new ID value. I got confused because the ConvertJsontoSQL is showing an error status in your screenshot

[#9]

Thanks Samsal