NiFi / Postgres Database Problems Inserting Records

[#1]

Hello,

Is there anyone out there that can help me with the following two problems?  Thanks in advance.

Problem #1:  I have a very simple CSV input file (it has 6 mock satellite names).  I am using the ExtractText processor to capture the satellite name into a user-defined property that becomes an attribute [Property: satellite_name, Value: (.{1,200})]

ExtractText makes 3 copies of the same attribute; I only want one.  I can see this when I look at the processor's data provenance - it makes: satellite_name, satellite_name.0, satellite_name.1, fills these with the first of the 6 mock satellite names, then picks up the other five and puts them in satellite_name.2, satellite_name.3, etc.

Problem #2:   I have tried a lot of different settings that I found in Google searches, but when I run the processor group, it does not stop inserting the flowfile data into the database, and it only inserts the first satellite name.

The "matchted" flow of my NiFi processors is: GetFile -> ExtractText -> PutSQL -> LogAttribute.  (The "unmatched" flow is GetFile -> ExtractText -> (different) LogAttribute.)

I do not have any problems using NiFi to access the Postgres database.  NiFi is running on Windows, Postgres is running on a Linux VM (on the same PC).  In the PutSQL processor, this is my insert statement:  

INSERT INTO satellite.satname (satellite_name) VALUES('${satellite_name}');

That's the vital information about my problems; I can provide screenshots if needed if you think you can help and would like more information.  I sincerely appreciate any time/effort anyone puts forward to helping me.  I am new to NiFi and have come a long way getting it to talk to a database running on a VM.

Thanks again,

[#2]

@johndcal   I am excited that you are learning NiFi.  It is my favorite tool to solve any Use Case I have.   

Problem 1:   Your extractText logic is matching all values, not one, and is put them into attributes as an array (0,1,2,3,4,etc).   It's okay to use this in this manner, just use the values you need an ignore the rest. There are things you can do to single out each value better but I would recommend that you use a CSVRecordReader Controller Service to parse the CSV.      This allows much more control over the values as well as the schema.

Problem 2:  Can you send your flow screenshot?  You said it doesn't stop inserting the FlowFiles... is the first processor in your flow always on?  What I mean by this, if that first processsor's run schedule is 0 sec, it will always run, and continuously generate FlowFiles.     When I am creating a flow for first time, I set the first proc to some controllable run schedule.  For example 30 seconds.   I push play, then immediately stop it, then step the Flow Files through each downstream Processor, one at a time, testing at each queue that the FlowFiles attributes are as expected.   Once I know I have a full operating flow, then I address how to trigger the flow to start, or the appropriate timing to always run.

If this answer resolves your issue or allows you to move forward, please choose to ACCEPT this solution and close this topic. If you have further dialogue on this topic please comment here or feel free to private message me. If you have new questions related to your Use Case please create separate topic and feel free to tag me in your post.  

Thanks,

[#3]

Steven,

Thanks much for your quick reply.  You were correct; I had my GetFile scheduled to zero, so I reset it to 30 seconds.  I do have a CSVReader configured, and made one change so that the first line would not be read as column headers (there are two settings for that, I had the first one correct, but not the second.)

My current text data file looks like this:

sat-01

sat-02

...

sat-06

After a quick test, only the first value went into the database.  Looking at the Provenance Event for my ExtractText processor, here are the attributes it captured:

satellite_name

sat-01

No value set

satellite_name.0

sat-01

No value set

satellite_name.1

sat-01

No value set

satellite_name.2

sat-02

No value set

satellite_name.3

sat-03

No value set

satellite_name.4

sat-04

No value set

satellite_name.5

sat-05

No value set

satellite_name.6

sat-06

No value set

I wish there was one attribute (satellite_name) and each name was picked up as a separate record, but not in an array.  The array is ok, I just don't know how to deal with it using SQL in NiFi expression language.

The ExtractText Output Claim content is perfect; again, only first name is going to the database.

Thanks again for your help.

[#4]

Use query record processor, have CSVReader and JSONWriter output.

do

SELECT satellite_name

FROM FLOWFILE

Next processor can grab an attribute