PutSql - date format error

Question

I'm trying to read data from 'Teradata' and populate the same to 'Oracle' database. Please find below my flow,

'ExecuteSQL' -> 'SplitAvro' -> 'ConvertAvroToJSON' -> 'ConvertJSONToSQL' -> 'PutSQL'

Error message: Cannot update database for StandardFlowFileRecord due to org.apache.nifi.processor.exception.ProcessException: The value of the sql.args.7.value is '2999-12', which cannot be converted to a timestamp; routing to failure: org.apache.nifi.processor.exception.ProcessException: The value of the sql.args.7.value is '2999-12', which cannot be converted to a timestamp

It seems i need to format the Date field before i populate the same in to Oracle. Could you please explain me which processor should contain this date formatting logic?

Furthur analysis shows that the particular value was truncated by the 'ConvertJSONToSQL' processor. input value to 'ConvertJSONToSQL' processor - "2002-02-04" Processed value - "2002-02"

Answer 1

It looks like you may be running into NIFI-2625. In an upcoming release of NiFi (due to NIFI-3430), you may be able to work around this by specifying the date format in an attribute, however that might not fix the truncated values you are seeing.

If you know the name of the field (or index into a JSON array, such as "7" above which is really index 6 in a JSON array) that contains the timestamp value, you could use EvaluateJsonPath ahead of ConvertJSONToSQL, to ensure you retain the correct original value (let's say you call the attribute sql.args.7.original). Then after ConvertJSONToSQL you could use UpdateAttribute to replace the incorrect/truncated value with the correct original one, perhaps by setting the attribute sql.args.7.value to ${sql.args.7.original}.