PutSql-日期格式错误 [英] PutSql - date format error

问题描述

我正在尝试从"Teradata"中读取数据，并将其填充到"Oracle"数据库中.请在我的流程下方找到

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'

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

错误消息: 无法更新StandardFlowFileRecord的数据库 由于org.apache.nifi.processor.exception.ProcessException:sql.args.7.value的值为'2999-12'，无法转换为时间戳；路由失败:org.apache.nifi.processor.exception.ProcessException:sql.args.7.value的值为"2999-12"，无法转换为时间戳

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

似乎我需要先格式化日期"字段，然后再将其填充到Oracle中.您能否解释一下哪个处理器应该包含此日期格式逻辑?

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分析表明，特定值已被'ConvertJSONToSQL'处理器截断. 输入值到"ConvertJSONToSQL"处理器-"2002-02-04" 加工值-"2002-02"

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"

推荐答案

您似乎正在遇到 NIFI-3430 )，您可以可以通过在属性中指定日期格式来解决此问题，但是这可能无法解决您看到的截断值的问题.

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.

如果您知道包含时间戳记值的字段名称(或指向JSON数组的索引，例如JSON数组中的索引6，例如上面的"7")，则可以使用

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

这篇关于PutSql-日期格式错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！