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?
进一步分析表明，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-2625.在即将发布的 NiFi 版本中(由于 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 数组的索引，例如上面的7"，实际上是 JSON 数组中的索引 6)，则可以使用 EvaluateJsonPath 在 ConvertJSONToSQL 之前，以确保您保留正确的原始值(假设您调用属性 sql.args.7.original).然后在 ConvertJSONToSQL 之后你可以使用 UpdateAttribute 用正确的原始值替换不正确/截断的值，也许通过将属性 sql.args.7.value 设置为 ${sql.args.7.original}.
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屋！
