How to use ReplaceText processor in Nifi to replace values with attributes/variables based on CSV headers/fields to produce INSERT statement to use in PutDatabaseRecord?

[#1]

I have a CSV flowfile below as an example and I want to pass each record to a variable/attribute to form a INSERT sql statement using ReplaceText processor then PutDatabase to write in the table. My flow is, Getfile > ExtractText > ReplaceText > PutDatabaseRecord

Or is it better to use CSVReader instead then PutDatabaseRecord? which one is faster?

CSV:

id,fname,lname

1,John,Doe

2,Jane,Doe

[#2]

Second flow i.e. using PutDatabaseRecord processor will be faster as these record oriented processors designed to work with chunk of records instead of one record at a time.

PutDatabase record processors reads the incoming flowfile data based on your Record Reader controller service and then prepares and executes the sql statements as a single batch.

Refer to this link for configuring/usage of PutDatabaseRecord processor and also explains how we are doing the same exact flow in Old NiFi versions vs New NiFi versions.

1.Replace Text processor is used to change/add the contents of flowfile and by using this processor we cannot change the attribute values of the flowfile.

2.Update Attribute processor: as this processor name describes, if we want to change/add the value of the attribute then we are going to use Update Attribute processor and by using this processor we cannot change the content of the flowfile.

-

If the Answer helped to resolve your issue, Click on Accept button below to accept the answer, That would be great help to Community users to find solution quickly for these kind of issues.

[#3]

Hi Shu,

thank you for your response. How can map the columns in my input file as the attribute?

For example my insert statement is "Insert into employee values(?,?,?)" how can I replace it (question marks) with the columns in my input file to that statement?

[#4]

@Andrew Bailon

We need to extract the values, add them to flow file attributes and in replace text processor we need to prepare insert statement mapping to values

insert into employee values (${value1},${value2}..)

Refer to this and this links to get familiar how to replace question marks and usage of PutSQL processor.

[#5]

Got it now, does this work the same way as the PutDatabaseRecord as well?

[#6]

@Andrew Bailon

By using PutDatabaseRecord processor we don't need to use any of(replace text,putsql,convertjsontosql) processors at all, as PutDatabaseRecord processor will read the incoming data based on the Record Reader controller service and creates the `insert/update statements` based on the strategy that we have selected and executes them in the target database.

[#7]

@Shu

Ok, I will try that Shu. Hope it will load this time.

[#8]

Hi Shu,

I'm getting an error for PutDatabaseRecord though i think my settings are correct. Not sure where it failed.

Here's my flow:

UpdateAttribute:

CSV Reader:

Avro Schema Registry

Tables:

@Shu