question Re: ValidateRecord Date Field in Support Questions

ValidateRecord Date Field

SAMSAL — Fri, 23 Feb 2024 15:09:50 GMT

Hi Everyone,

I'm trying to use ValidateRecord processor where I have a json input with date field as follows:

{ "name": "sam", "age": "20", "dob": "01/01/2004", "id": "001" }

In the validaterecord Record Writer Im using JsonRecordSetWriter with the following configuration:

Where I'm using the following avro schema in the Schema Text property:

{ "name": "person_rec", "type": "record", "namespace": "nifi.com", "fields": [ { "name": "name", "type": "string" }, { "name": "age", "type": "int" }, { "name": "dob", "type": "int", "logicalType": "date" }, { "name": "id", "type": "string" } ] }

Also I'm setting the Date Format to : MM/dd/yyyy

I though based on the description that when I set the Date Format it will be able to parse the date when reading\writing date fields. I thought by date field it means using logicalType "date" which means the type must be "int" according to avro schema. My expectation is that my validate record will be able to validate against date field as string with the specified format and write it as such however I keep getting error:

Successfully parsed a JSON object from input but failed to convert into a Record object with the given schema
- Caused by: java.lang.NumberFormatException: For input string: "01/01/2004"

Not sure if I miss understanding or missing something here but how this is suppose to work if its expecting string? How the specified Date Format knows what the date field if its not defined as such in the avro schema?

Can someone help please ?
Thanks

Re: ValidateRecord Date Field

cotopaul — Mon, 26 Feb 2024 07:49:41 GMT

@SAMSAL I just tried what you have describe using an GenerateFlowFile (where I set your JSON text) and a ValidateRecord and I can't really reproduce your error. (NiFi 1.19.1)
I used an JSON Reader, with default settings - Infer Schema and no Date Format set.

Next, in terms of writer, as you stated, I used an JsonRecordSetWriter, where I defined your mentioned schema and tried with and without the date format.

Each time, my FlowFile when into the valid queue, without any errors.

And the file from the valid queue looks like:

Maybe there is something extra in your Flow which is causing this error, or maybe your NiFi version is causing this behavior?

In terms of properties, the following have been configured in my ValidateRecord Processor:

Re: ValidateRecord Date Field

SAMSAL — Tue, 27 Feb 2024 13:15:46 GMT

Hi @cotopaul ,

Thanks for your help. Actually you are right. Im not sure what I was thinking when I posted this or under what circumstances I was getting this error but I retried what I posted and it work so I apologize if I wasted your and the community time. However, what if I want to store the schema in the ValidateRecord processor itself as I would think that would be the proper way to use. Notice how in that processor there is no such field for setting the Date\Time format , and if I provide my schema to it by setting (Use Schema Text Property) I dont get an error but my flowfile goes to the "invalid" relationship complaining that the dob filed doesnt have the valid type!

In this case I did not use any schema def in the record reader\write but I kept the data format set to MM/dd/yyyy.

Is there a way to fix this or am I using the processor the wrong way?

Thanks again for your help.