question Re: Adding columns to sql in nifi in Archives of Support Questions (Read Only)

Adding columns to sql in nifi

vscherbakov — Tue, 19 Jun 2018 23:31:52 GMT

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?

Re: Adding columns to sql in nifi

anarasimham — Wed, 20 Jun 2018 05:56:01 GMT

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

Re: Adding columns to sql in nifi

vscherbakov — Wed, 20 Jun 2018 17:35:40 GMT

Thanks! I think this should solve my problem!

Re: Adding columns to sql in nifi

mburgess — Wed, 20 Jun 2018 21:53:15 GMT

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.

Re: Adding columns to sql in nifi

vscherbakov — Thu, 21 Jun 2018 15:52:47 GMT

Thanks a lot!