Created 08-27-2018 05:56 AM
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
Created 08-27-2018 11:08 AM
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.
Created 09-05-2018 06:29 AM
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?
Created 09-05-2018 10:01 PM
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.
Created 09-11-2018 02:34 AM
Got it now, does this work the same way as the PutDatabaseRecord as well?
Created 09-11-2018 03:58 AM
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.
Created 09-20-2018 01:13 AM
Ok, I will try that Shu. Hope it will load this time.
Created on 09-11-2018 09:54 AM - edited 08-17-2019 06:18 PM
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: