Support Questions
Find answers, ask questions, and share your expertise

Adding columns to sql in nifi

Solved Go to solution
Highlighted

Adding columns to sql in nifi

Explorer

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Adding columns to sql in nifi

Super Guru

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.

View solution in original post

4 REPLIES 4
Highlighted

Re: Adding columns to sql in nifi

Expert Contributor

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

Highlighted

Re: Adding columns to sql in nifi

Explorer

Thanks! I think this should solve my problem!

Highlighted

Re: Adding columns to sql in nifi

Super Guru

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.

View solution in original post

Highlighted

Re: Adding columns to sql in nifi

Explorer

Thanks a lot!