question Re: Parse and modify data befor calling PutDatabaseRecord in Support Questions

Parse and modify data befor calling PutDatabaseRecord

quiz_oxis — Fri, 18 Aug 2017 14:13:41 GMT

Hi,

I've just started with nifi and am very new to its workings. I'm using version 1.3.0

I have a flow where i read some data from a microsoft sql db table using ExecuteSQL and then have PutDatabaseRecrod processor insert it into a mysql database. It works straight out of the box.

Before the PutDatabaseRecord i need to parse (regexp) value of a column, and create new fields.

I would appreciate if someone can hint out as to what processor to use here.

Thanks & regards,

Re: Parse and modify data befor calling PutDatabaseRecord

quentin_toulou — Fri, 18 Aug 2017 16:10:20 GMT

A common flow to modify your data is : ConvertAvroToJson -> splitJson -> EvaluateJsonPath -> UpdateAttribute

So you will have one flowfile by row and with the EvaluateJsonPath you convert your fields as attributes of this flowfile. After that you can use UpdateAttribute to modify an attribute or add new one. You can use Nifi Expression Language.

Re: Parse and modify data befor calling PutDatabaseRecord

quiz_oxis — Tue, 22 Aug 2017 17:30:24 GMT

Thanks Quentin, it works but i couldn't figure out the UpdateAttribute part. Using EvaluateJsonPath creates the attribute (

ZComponent) with a value

Sis_Grp.Dom="LOCAPPLE",Name="CrashGuard"

I now need to parse this value into seperate attributes. how can i achieved this with updateattribute

Sis_Grp.Dom=LOCAPPLE and Name=CrashGuard

Re: Parse and modify data befor calling PutDatabaseRecord

quentin_toulou — Tue, 22 Aug 2017 20:24:25 GMT

With the UpdateAttribute, you can add your own property. (With the '+' button in the 'Properties' tab)

Add a property with the name : Sis_Grp.Dom and the value : ${ZComponent:substringAfter('"'):substringBefore('"')}

(the argument for the substringBefore is double quote between simple quotes.)

and a second with the name : Name and the value : ${ZComponent:substringAfter('Name="'):substringBefore('"')}

You can find all the Nifi Expression language here

Re: Parse and modify data befor calling PutDatabaseRecord

quiz_oxis — Tue, 22 Aug 2017 21:22:55 GMT

That works, i now have two new attributes. how can i add them to my flowfile content?

Re: Parse and modify data befor calling PutDatabaseRecord

quentin_toulou — Tue, 22 Aug 2017 23:46:15 GMT

You can use the ReplaceText processor. Use ${attribute_name} to write an attribute in your text.