Skip to main content
SAMSAL
SAMSAL
Platinum ⭐️⭐️⭐️
February 23, 2024
Question

ValidateRecord Date Field

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:

SAMSAL_0-1708699962585.png

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

2 replies

cotopaul
cotopaul
Platinum ⭐️
February 26, 2024

@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.

cotopaul_0-1708933316827.png
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. 

cotopaul_1-1708933397906.png 
Each time, my FlowFile when into the valid queue, without any errors.

cotopaul_2-1708933466915.png
And the file from the valid queue looks like:

 

cotopaul_4-1708933571646.png

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:

cotopaul_5-1708933742235.png

Translate
    SAMSAL
    SAMSALAuthor
    Platinum ⭐️⭐️⭐️
    February 27, 2024

    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!

     

    SAMSAL_0-1709039589497.png

     

    SAMSAL_1-1709039626510.png

    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.

     

    Translate
      Terms & ConditionsAccessibility statement