Skip to main content
AbhishekSingh
AbhishekSingh
Gold ⭐️
July 8, 2022
Solved

How to convert String Date "Wed Oct 13 15:58:58 IST 2021" to DATETIME datatype like "2021-10-13 15:58:58" in JsonPathReader to save in MySql DB

I am getting String Date "Wed Oct 13 15:58:58 IST 2021" in changeCaptureMySql processor flowfile content out claim which is getting read from mysql-bin.log file as source DB data. Destination DB column has datatype DATETIME. I am using the JsonPathReader and AvroSchemaRegistry to read the each column data to save in db but it is giving data incorrect date-time value error. 

Q1. Is there any away I can convert String date time value to DATETIME to save it without error?

Q2. How to define the DATETIME data type in AvroSchemaRegistry ?. 

I tried below data type in AvroSchemaRegistry but it won't work.

Please find the attached screenshot for reference.

{ "name": "TIMESTAMP" , "type": { "type":"long", "logicalType":"timestamp-millis"} }, { "name": "DATE" , "type": { "type":"int", "logicalType":"date"} }

incorrect_datetimeValueError.pngScreenshot from 2022-07-08 20-15-03.png 

SAMSAL
SAMSALAnswer
Platinum ⭐️⭐️⭐️
July 11, 2022

You cant use Expression Language when setting values in the JsonPathReader processor. You need to get the value first as is using the json path  and then use an updateAttribute processor on it using the expression above. In updateAttribute you can use the same variable name or different one.

View in thread

5 replies

SAMSAL
SAMSAL
Platinum ⭐️⭐️⭐️
July 8, 2022

Hi,

I ran into this issue before when dealing with ISO timeformat and it would not allow me to write to the DB. In this case you have to use an updateAttribute to convert this format to the format that DB can accept (yyyy-MM-dd HH:mm:ss), this worked for me:

${literal('Wed Oct 13 15:58:58 IST 2021'):replaceAll('(IST)\s',''):toDate('EEE MMM dd HH:mm:ss yyyy'):format('yyyy-MM-dd HH:mm:ss')}

Hope it would help.

Translate
AbhishekSingh
AbhishekSinghAuthor
Gold ⭐️
July 11, 2022

I tried above conversion but I am getting below error.

AvroSchema: { "name": "enrolled_at", "type": { "type":"int", "logicalType":"date"} },

ErrorLog:

org.apache.nifi.serialization.MalformedRecordException: Successfully parsed a JSON object from input but failed to convert into a Record object with the given schema
at org.apache.nifi.json.AbstractJsonRowRecordReader.nextRecord(AbstractJsonRowRecordReader.java:162)
at org.apache.nifi.serialization.RecordReader.nextRecord(RecordReader.java:50)
at jdk.internal.reflect.GeneratedMethodAccessor653.invoke(Unknown Source)

Caused by: com.jayway.jsonpath.InvalidPathException: Function with name: {literal does not exist. 

Translate
SAMSAL
SAMSAL
Platinum ⭐️⭐️⭐️
July 11, 2022

Hi,

Can you post the Expression Language you used? I'm seeing in the error message that its complaining about the "literal" function doesnt exist. Did you use it with the variable name that contains the original date value? If so then remove the literal function and just keep the variable since literal works with constants. So the expression should be something like this:

 

${date_variable:replaceAll('(IST)\s',''):toDate('EEE MMM dd HH:mm:ss yyyy'):format('yyyy-MM-dd HH:mm:ss')}

Translate
SAMSAL
SAMSALAnswer
Platinum ⭐️⭐️⭐️
July 11, 2022

You cant use Expression Language when setting values in the JsonPathReader processor. You need to get the value first as is using the json path  and then use an updateAttribute processor on it using the expression above. In updateAttribute you can use the same variable name or different one.

Translate
Terms & ConditionsAccessibility statement