question Re: ReplaceText doesn't work properly in Support Questions

ReplaceText doesn't work properly

stefan_constant — Thu, 29 Mar 2018 18:31:34 GMT

replacetext.jpgDear all,

I want to create a CSV file on a monthly bases by executing a SQL script. For this I am using ExecuteSQL processor and ConvertAvroToJSON, SplitJSON, EvaluateJsonPath, ReplaceText and PutFile.

My problem is with ReplaceText processor. Provide a list with empty values even if the JSON values are there and are valid. Please see the attached files. I tried every recommended solution provided here on community but didn't help me.

Kind regards,
Stefan

Re: ReplaceText doesn't work properly

Shu_ashu — Sun, 18 Aug 2019 05:57:59 GMT

@Stefan Constantin

Could you please change the below properties in Replace text processor and run again

Make sure you are using same attribute names(case sensitive) in replace text processor those are matching with evaluatejson path processor.

(or)

More optimal way of doing this use case is if you are using NiFi 1.2+ then use ConvertRecord processor with Avro Reader as record reader and CSV Set writer as Record setwriter this processor even accepts array of json messages and does conversion to CSV.

Flow:-

Executesql
ConvertRecord
PutFile

This Link describes how to use Convert Record processor.

Let us know if you are having any issues..!!

Re: ReplaceText doesn't work properly

stefan_constant — Thu, 29 Mar 2018 23:11:30 GMT

Hello @Shu,

Unfortunately, both flows didn't work. First one, with ReplaceText having the properties you mentioned still outputted empty values even though the attributes are the same.
For the second one with ConvertRecord, I receive this error: Failed to process StandardFlowFileRecord[...] will route to failure: java.lang.ArrayIndexOutOfBoundsException: -40 java.lang.ArrayIndexOutOfBoundsException: -40

I even try to convert the Avro to JSON and then to pass to ConvertRecord but still the same error...

Any help will be welcomed ! Thank you !

Kind regards,
Stefan

Re: ReplaceText doesn't work properly

RahulSoni — Fri, 30 Mar 2018 09:55:03 GMT

@Stefan Constantin

If you are using NiFi 1.2+, I would highly recommend NOT using EvaluateJSONPath. Now talking about the alternate approach, which is using ConvertRecord where you are facing arrayIndexOutOfBoundException, the problem probably is with your schema. Use default null values for your columns in your schema for ConvertRecord Processor like.

{
"type":"record",
"name":"nifi_logs",
"fields":[
	{"name":"column1","type":["null","string"]},
	{"name":"column2","type":["null","string"]},
	{"name":"column3","type":["null","string"]},
	{"name":"column4","type":["null","string"]}
	]
}

Try this and let know if you still face any problems.

Cheers!

Re: ReplaceText doesn't work properly

stefan_constant — Fri, 30 Mar 2018 15:10:23 GMT

Hello @Rahul Soni,

Since beggining I tried with null like default values but still I receive that error. Also I validated the JSON so is OK, here is my JSON:

{ "type": "record", "name": "DnpReport", "fields" : [ {"name": "inv_idn", "type": ["null", "string"]}, {"name": "inv_number", "type": ["null", "string"]}, {"name": "usr_mdf", "type": ["null", "string"]}, {"name": "invIssDat", "type": ["null", "string"]}, {"name": "invCliCod", "type": ["null", "string"]}, {"name": "invCliNam", "type": ["null", "string"]}, {"name": "invCli_RegCountry", "type": ["null", "string"]}, {"name": "invoiceClass", "type": ["null", "string"]}, {"name": "corCliCod", "type": ["null", "string"]}, {"name": "ofoChiNmb", "type": ["null", "string"]}, {"name": "bsnCtgDsc", "type": ["null", "string"]}, {"name": "bsnCtgCod", "type": ["null", "string"]}, {"name": "bsnCtgDspCod", "type": ["null", "string"]}, {"name": "invTyp", "type": ["null", "string"]}, {"name": "invStu", "type": ["null", "string"]}, {"name": "invOrgCod", "type": ["null", "string"]}, {"name": "cryCod", "type": ["null", "string"]}, {"name": "amnWthVat", "type": ["null", "string"]}, {"name": "amnWthVatInEur", "type": ["null", "string"]}, {"name": "mediaFeesInEur", "type": ["null", "string"]}, {"name": "kcFeesInEur", "type": ["null", "string"]} ] }

Kind regards,
Stefan

Re: ReplaceText doesn't work properly

stefan_constant — Fri, 30 Mar 2018 17:18:06 GMT

Hello @Shu, @Rahul Soni,

Do you have any other ideas ? Thank you !

Kind regards,
Stefan

Re: ReplaceText doesn't work properly

RahulSoni — Fri, 30 Mar 2018 17:33:21 GMT

No problem.if your existing approach is not working. Please.share.some.info regarding your convert record processor etc and same dataset it is passing for and is failing for

I can look in further

Re: ReplaceText doesn't work properly

stefan_constant — Fri, 30 Mar 2018 19:12:53 GMT

Json input:  [ {
  "INV_IDN" : "247048764",
  "INV_NUMBER" : "181120060",
  "USR_MDF" : "15/03/2018 08:34:00 by LDL",
  "INVISSDAT" : "15/03/2018",
  "INVCLICOD" : "FUNDQ",
  "INVCLINAM" : "FUNDQUEST",
  "INVCLI_REGCOUNTRY" : "FR",
  "INVOICECLASS" : "CUS - assujetti in EU outside Lx",
  "CORCLICOD" : "BNPAMFR",
  "OFOCHINMB" : "20173748",
  "BSNCTGDSC" : "Fund Data Management",
  "BSNCTGCOD" : "LIS",
  "BSNCTGDSPCOD" : "FDM",
  "INVTYP" : "Credit Note",
  "INVSTU" : "Validated",
  "INVORGCOD" : "LU",
  "CRYCOD" : "EUR",
  "AMNWTHVAT" : "-7,543.23",
  "AMNWTHVATINEUR" : "-7,543.23",
  "MEDIAFEESINEUR" : "0",
  "KCFEESINEUR" : "-7543.23"
} ]

Json schema: {
  "type": "record",
  "name": "DnpReport",
  "fields" : [
    {"name": "inv_idn", "type": ["null", "string"]},
    {"name": "inv_number", "type": ["null", "string"]},
    {"name": "usr_mdf", "type": ["null", "string"]},
    {"name": "invIssDat", "type": ["null", "string"]},
    {"name": "invCliCod", "type": ["null", "string"]},
    {"name": "invCliNam", "type": ["null", "string"]},
    {"name": "invCli_RegCountry", "type": ["null", "string"]},
    {"name": "invoiceClass", "type": ["null", "string"]},
    {"name": "corCliCod", "type": ["null", "string"]},
    {"name": "ofoChiNmb", "type": ["null", "string"]},
    {"name": "bsnCtgDsc", "type": ["null", "string"]},
    {"name": "bsnCtgCod", "type": ["null", "string"]},
    {"name": "bsnCtgDspCod", "type": ["null", "string"]},
	{"name": "invTyp", "type": ["null", "string"]},
	{"name": "invStu", "type": ["null", "string"]},
	{"name": "invOrgCod", "type": ["null", "string"]},
	{"name": "cryCod", "type": ["null", "string"]},
	{"name": "amnWthVat", "type": ["null", "string"]},
	{"name": "amnWthVatInEur", "type": ["null", "string"]},
	{"name": "mediaFeesInEur", "type": ["null", "string"]},
	{"name": "kcFeesInEur", "type": ["null", "string"]}
	]
}

Re: ReplaceText doesn't work properly

Shu_ashu — Fri, 30 Mar 2018 20:06:24 GMT

@Stefan Constantin

The issue is with your json schema because schema key names are not matching with json message key names as json schema having lower case,camel case in name value but json message having upper case names (inv_idn,INV_IDN)

Change the schema in Avro Schema registry to match with incoming json names(i.e upper case) everything will work as expected

Json Schema:-

{
  "type": "record",
  "name": "DnpReport",
  "fields" : [
    {"name": "INV_IDN", "type": ["null", "string"]},
    {"name": "INV_NUMBER", "type": ["null", "string"]},
    {"name": "USR_MDF", "type": ["null", "string"]},
    {"name": "INVISSDAT", "type": ["null", "string"]},
    {"name": "INVCLICOD", "type": ["null", "string"]},
    {"name": "INVCLINAM", "type": ["null", "string"]},
    {"name": "INVCLI_REGCOUNTRY", "type": ["null", "string"]},
    {"name": "INVOICECLASS", "type": ["null", "string"]},
    {"name": "CORCLICOD", "type": ["null", "string"]},
    {"name": "OFOCHINMB", "type": ["null", "string"]},
    {"name": "BSNCTGDSC", "type": ["null", "string"]},
    {"name": "BSNCTGCOD", "type": ["null", "string"]},
    {"name": "BSNCTGDSPCOD", "type": ["null", "string"]},
{"name": "INVTYP", "type": ["null", "string"]},
{"name": "INVSTU", "type": ["null", "string"]},
{"name": "INVORGCOD", "type": ["null", "string"]},
{"name": "CRYCOD", "type": ["null", "string"]},
{"name": "AMNWTHVAT", "type": ["null", "string"]},
{"name": "AMNWTHVATINEUR", "type": ["null", "string"]},
{"name": "MEDIAFEESINEUR", "type": ["null", "string"]},
{"name": "KCFEESINEUR", "type": ["null", "string"]}
]
}

Output:-

INV_IDN,INV_NUMBER,USR_MDF,INVISSDAT,INVCLICOD,INVCLINAM,INVCLI_REGCOUNTRY,INVOICECLASS,CORCLICOD,OFOCHINMB,BSNCTGDSC,BSNCTGCOD,BSNCTGDSPCOD,INVTYP,INVSTU,INVORGCOD,CRYCOD,AMNWTHVAT,AMNWTHVATINEUR,MEDIAFEESINEUR,KCFEESINEUR
247048764,181120060,15/03/2018 08:34:00 by LDL,15/03/2018,FUNDQ,FUNDQUEST,FR,CUS - assujetti in EU outside Lx,BNPAMFR,20173748,Fund Data Management,LIS,FDM,Credit Note,Validated,LU,EUR,"-7,543.23","-7,543.23",0,-7543.23

Reference template:-

181962-csv.xml

Re: ReplaceText doesn't work properly

Shu_ashu — Sun, 18 Aug 2019 05:57:51 GMT

@Stefan Constantin

If you want header with lower and camel case names then in csvsetwriter controller change the below property to false.
Include Header Line

false

now we are not going to get the header line included in the output flowfile, then use Replace text processor to add our own header to the file

Replace text configs:-

Search Value

(?s)(^.*$)

Replacement Value

inv_idn,inv_number,usr_mdf,invIssDat,invCliCod,invCliNam,invCli_RegCountry,invoiceClass,corCliCod,ofoChiNmb,bsnCtgDsc,bsnCtgCod,bsnCtgDspCod,invTyp,invStu,invOrgCod,cryCod,amnWthVat,amnWthVatInEur,mediaFeesInEur,kcFeesInEur

Character Set

UTF-8

Maximum Buffer Size

1 MB //needs to change the value according to the file size that we are getting after convertrecord processor

Replacement Strategy

Prepend //we are prepend the the content with the above header line

Evaluation Mode

Entire text

Input content:-

247048764,181120060,15/03/2018 08:34:00 by LDL,15/03/2018,FUNDQ,FUNDQUEST,FR,CUS - assujetti in EU outside Lx,BNPAMFR,20173748,Fund Data Management,LIS,FDM,Credit Note,Validated,LU,EUR,"-7,543.23","-7,543.23",0,-7543.23

Output Content:-

inv_idn,inv_number,usr_mdf,invIssDat,invCliCod,invCliNam,invCli_RegCountry,invoiceClass,corCliCod,ofoChiNmb,bsnCtgDsc,bsnCtgCod,bsnCtgDspCod,invTyp,invStu,invOrgCod,cryCod,amnWthVat,amnWthVatInEur,mediaFeesInEur,kcFeesInEur
247048764,181120060,15/03/2018 08:34:00 by LDL,15/03/2018,FUNDQ,FUNDQUEST,FR,CUS - assujetti in EU outside Lx,BNPAMFR,20173748,Fund Data Management,LIS,FDM,Credit Note,Validated,LU,EUR,"-7,543.23","-7,543.23",0,-7543.23

Re: ReplaceText doesn't work properly

stefan_constant — Fri, 30 Mar 2018 21:44:46 GMT

Hello @Shu,

I didn't added here but of course I tried also with upper letters and still the same issue with ConvertRecord.
With the EvaluateJsonPath, same issue again. I tried many other things but nothing worked. I was thinking that it will be really easy to output a file from a simple select query from a table but it seems that is not like that..

Re: ReplaceText doesn't work properly

Shu_ashu — Sun, 18 Aug 2019 05:57:43 GMT

@Stefan Constantin

As you are doing ExecuteSql to pull data the format of output flowfile is in Avro and having embedded schema with the content so we can use Embedded AvroSchema as the Schema Access Strategy in ConvertRecord processor by using embedded schema we are skipping defining Schema Registry in both Reader and Writer controller services.

Convert Record processor Configs:-

Record Reader

AvroReader

Avro Reader Configs:-

Record Writer

CSVRecordSetWriter

Csv set writer Configs:-

With this configs we are reading the Avro data and converting to Csv, if you want header then keep includer header line property to True.

Flow xml for your reference avro-to-csv.xml

Re: ReplaceText doesn't work properly

Shu_ashu — Fri, 30 Mar 2018 21:52:42 GMT

@Stefan Constantin

I tried with your sample record and i'm able to get the csv data as i have mentioned in the comment above and also i have attached my xml you can save and upload to your NiFi instance and compare both find where are you missing some configs.

Could you share your Convert Record processors Record Reader, Record Writer,Avro Schema Registry Config screenshots.

Re: ReplaceText doesn't work properly

stefan_constant — Tue, 03 Apr 2018 15:52:58 GMT

Hello everyone !
I tried with Embedded schema, like Shu mentioned, and now everything is working perfectly. I feel so bad that it was so easy and simple, just few processors...anyway, thank you for your time and help, I really appreciate it !

Kind regards,
Stefan