question Re: how to convert string to integer or numeric using apache nifi processor UpdateRecord in Support Questions

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

Brunno — Wed, 31 Jul 2024 20:30:54 GMT

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

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

DianaTorres — Wed, 31 Jul 2024 22:54:32 GMT

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

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

Brunno — Mon, 05 Aug 2024 17:00:58 GMT

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

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

DianaTorres — Tue, 06 Aug 2024 21:31:36 GMT

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

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

SAMSAL — Wed, 07 Aug 2024 11:36:46 GMT

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.

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

haridjh — Thu, 08 Aug 2024 10:42:55 GMT

@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

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

Brunno — Fri, 09 Aug 2024 19:35:28 GMT

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 @diana DianaTorres