Skip to main content
Brunno
Brunno
Silver ⭐️⭐️
July 31, 2024
Solved

how to convert string to integer or numeric using apache nifi processor UpdateRecord

how are you? I need assistance with converting a string to an integer or numeric value using Apache NiFi’s UpdateRecord processor. I’ve tried using toNumber and toInt, but neither worked. Can you provide guidance on how to achieve this conversion?

Brunno_0-1722457722414.png

tHANK

 

 

SAMSAL
SAMSALAnswer
Platinum ⭐️⭐️⭐️
August 7, 2024

Hi,
It seems the data is being converted into none datetime format . when I try a simple input with the date you provided and then do an Update Record using the toDate function I get an an epoch time which is a long integer value, Im not sure why this is happening but if you are using records reader like avro or json and you use the infer schema setting the output can be unpredictable. I would suggest to not do an update record on the date values and just try to insert them as they come since they look to be a valid datetime format string.

View in thread

6 replies

DianaTorres
Community Manager
DianaTorres
Community Manager
July 31, 2024

@Brunno Welcome to the Cloudera Community!

To help you get the best possible solution, I have tagged our NiFi experts @MattWho @SAMSAL @joseomjr  who may be able to assist you further.

Please keep us updated on your post, and we hope you find a satisfactory solution to your query.

Translate
Regards, Diana Torres, Senior Community Moderator
    Brunno
    BrunnoAuthor
    Silver ⭐️⭐️
    August 5, 2024

    So, my problem now is that I can't insert it into my database using the PutDatabaseRecord processor. It is reporting this error: "13:42:05 BRT ERROR PutDatabaseRecord[id=fe4d0a37-0190-1000-7115-2eb52a682eef] Failed to put Records to database for FlowFile[filename=788b7747-61ed-4eb8-bbdc-7a26b0521352]. Routing to failure: java.sql.BatchUpdateException: The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value. ". My data comes as a string (in this format: "QTDEMBALAGEM" : "3", "VALIDITY DATE" : "2023-01-01 00:00:00.0", "DTAFABRICACAO" : "2020-09-23 00:00:00.0").

    Before inserting, my data goes through the UpdateRecord processor, where the obligation is to convert it to datetime. I use these commands to do this conversion: (toDate(/DTAFABRICACAO, 'yyyy-MM-dd HH:mm:ss.S')) and (toDate(/DTAVALIDADE, 'yyyy-MM-dd HH:mm:ss. S')).

    Unfortunately I don't know how to avoid this error and be able to insert it into the database.

    Help me NiFi experts @MattWho @SAMSAL @joseomjr

    Brunno_1-1722877127774.pngBrunno_2-1722877141163.png

    Brunno_3-1722877189828.pngBrunno_4-1722877202911.png

     




     

     

    Translate
    DianaTorres
    Community Manager
    DianaTorres
    Community Manager
    August 6, 2024

    @bbahamondes @haridjh Hi! Do you have any insights here? Thanks!

     

    Translate
    Regards, Diana Torres, Senior Community Moderator
      SAMSAL
      SAMSALAnswer
      Platinum ⭐️⭐️⭐️
      August 7, 2024

      Hi,
      It seems the data is being converted into none datetime format . when I try a simple input with the date you provided and then do an Update Record using the toDate function I get an an epoch time which is a long integer value, Im not sure why this is happening but if you are using records reader like avro or json and you use the infer schema setting the output can be unpredictable. I would suggest to not do an update record on the date values and just try to insert them as they come since they look to be a valid datetime format string.

      Translate
        haridjh
        Gold ⭐️
        haridjh
        Gold ⭐️
        August 8, 2024

        @Brunno 
        In ExecuteSQL , Query result will be converted to Avro format. Therefore , use the Convert Avro* or related processors to have the avro data converted to correct formatted file and then update to the target DB  

        https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-standard-nar/2.0.0/org.apache.nifi.processors.standard.ExecuteSQL/index.html

        Translate
          Brunno
          BrunnoAuthor
          Silver ⭐️⭐️
          August 9, 2024

          I did as suggested by colleague @SAMSAL. I saved column by column, until I discovered that I had corrupted data. I had a data where the year was like "1111". As I had little experience, it was difficult to spot possible errors. Thank you everyone, especially my colleague @Former Member@DianaTorres

          Translate
          Terms & ConditionsAccessibility statement