Search code examples
apache-nifi

Apache NiFi: UpdateRecord processor retunrs java.lang.NumberFormatException error

Asking a question, there is a problem while sending e-commerce information to BigQuery in a csv file. In csv, the value of the ORDER_DATE column should go into the yyyy-MM-dd HH:mm:ss format in the DATETIME type column in the BigQuery, tried to find some references on Google.

Environment

  • Apache NiFi 1.14.0 on Docker

Attempt1

Replacement Value Strategy was selected to Literal Value, and/ORDER_DATE was added as a new field. The value is as follows:

${field.value:toDate('yyyyMMddhhmmss','GMT'):format('yyyy-MM-dd hh:mm:ss', 'GMT')}

Symptom is below: enter image description here

Attempt2

Found a new reference, that the NiFi expression was wrong, so I did another way:

${field.value:equals('0'):ifElse('', ${field.value:replace(${field.value},${field.value:toDate('yyyyMMddHHmmss'):format('yyyy-MM-dd HH:mm:ss'):toString()})})}

Likewise symptom is below: enter image description here

Someone asked me to open the log, checked and it's as follows:

java.lang.NumberFormatException: For input string: "2022-01-08 15:22:17"

Any ideas? Thank you :)

Solution

  • Solved the problem using UpdateRecord without adding to upstream ValidateCSV.

    In controller services, CSVReader and CSVRecordSetWriter were modified as follows:

    • Changed property Schema Access Strategy of processor CSVReader to User String Fields From Header
    • Changed all properties to the default of processor CSVRecordSetWriter


    Here are the test results:

    • In GenerateFlowFile, the following text to change to format yyyyMMddHmmss was made into csv enter image description here

    • NiFi expressions to be modified in UpdateRecord enter image description here

    • CSVRecordSetWriter left the default setting and set CSVReader as follows enter image description here

    • After completing the above setting, the value may be converted without an error enter image description here



    Searched to see the file in the error log and found the cause. It seemed to be inferred as type string, but it was type float, resulting in the above error.

    Thank you for your comments. @VikramsinhShinde @Sdairs