Nifi ReplaceText Processor inserting zero length strings

Question

The Apache Nifi ReplaceText processor isn't behaving the way I expect. I can't figure out why the expression evaluation is inserting zero length strings where the data should be going.

The ReplaceText processor's configuration is:

Replacement strategy is: Always Replace.

Evaluation Mode is: Entire text.

The processor chain is: QueryDatabaseTable->SplitAvro->UpdateAttribute->ReplaceText->PutSQL

The replacement value in the ReplaceText processor is:

INSERT INTO public.journal (payee, amount, billed_date, paid_date) 
VALUES ('${payee}', ${amount}, '${billed_date}', '${paid_date}');

It should become….

INSERT INTO public.journal (payee, amount, billed_date, paid_date) 
VALUES ('Dead End LLC', 2000.000, ‘2018-02-01’, ‘2018-02-01’);

Instead I get:

INSERT INTO public.journal (payee, amount, billed_date, paid_date) 
VALUES (‘’, , ‘’, ‘’);

Which is especially frustrating when I look at the output of the preceding UpdateAttribute processor step and see…

[ {
  "payee" : "Dead End LLC",
  "amount" : "2000.00",
  "billed_date" : "2018-02-01",
  "paid_date" : "2018-02-02"
} ]

This breaks my brain since the expression processing appears to be working just fine but not pulling in the right data (which my naive implementation assumes will be there.)

Previous reading that got me to where I am now:

Database Extract

Database Insert

Bryan Bende Bryan Bende · Accepted Answer · 2018-07-02T20:50:04

The reason you are getting empty string is because the expressions for '${payee}', ${amount}, '${billed_date}', '${paid_date} are evaluating to no value, and that is because you probably do not have flow file attributes with those names.

You cannot go directly from a value in Avro in the content of a flow file into NiFi's expression language, you would need to first extract the values from content into flow file attributes.

Something like this would probably work...

QueryDatabaseTable-> SplitAvro-> ConvertAvroToJson -> EvaluteJsonPath -> UpdateAttribute -> ReplaceText -> PutSQL

In EvluateJsonPath is where you would extract the values from the json into flow file attributes.

Nifi ReplaceText Processor inserting zero length strings

1 Answers