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

[#1]

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?

tHANK

[#2]

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

[#3]

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

[#4]

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

[#5 · Accepted solution]

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.

[#6]

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

[#7]

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 @dianaDianaTorres