Adding columns to sql in nifi

[#1]

I figured out how to add data from the server to the database *applause*.
But the question arose: can I add an extra column with file name/id in the data that I load into the database to understand the data from which file I'm using. Mbe nifi got some processor toadd persistent data to each line before we take them into database?

[#2]

Yes, if what you are asking is to add an extra piece of data to a NiFi FlowFile then you can do that.

What I am not sure of is the format of the data in your FlowFile - is it JSON, CSV, something else? If it is a human-readable format, you can use the ReplaceText processor to add more data into your FlowFile content. You'll need to modify your destination table schema and add another column to it assuming you're using Hive to read the data. The ReplaceText processor accepts statements in NiFi expression language so you'll want to read up on that to find out how to best find your string location and then insert text into it.

https://nifi.apache.org/docs/nifi-docs/html/expression-language-guide.html

[#3]

Thanks! I think this should solve my problem!

[#4 · Accepted solution]

You can use UpdateRecord to add a field to your records, then PutDatabaseRecord to put it into your database. Using the record-aware processors allows you better control over the content (rather than ReplaceText which can be brittle and only supports things like CSV and JSON, not Avro), and you don't need to use the Split/Merge pattern; instead these processors operate on many records in a single flow file, making things more efficient.

If the table has already been created and you are trying to insert new rows with an additional column, then (as of NiFi 1.5.0) you might be able to use PutSQL before PutDatabaseRecord to execute a statement like ALTER TABLE ADD COLUMN IF NOT EXISTS or something like that, but it is probably better to issue that statement once on your table externally so you don't need to do that for each flow file coming through NiFi.

[#5]

Thanks a lot!