Hi Isha, That’s precisely what we are doing to overcome the issue. It’s not the database since the JDBC driver outside NIFI works fine.
Would be cool if NIFI didn’t assume any suitable precision or allow a configuration for that… We have 500+ daily queries that go through that processor, we still don’t know the real impact because we only detected two days ago, but yeah we started to force a type cast to string as a workaround. Thank you. Tiago Sebastião From: Isha Lamboo [mailto:[email protected]] Sent: 19 de abril de 2023 15:10 To: [email protected] Subject: RE: NiFi 1.19.1 - ExecuteSQL - Decimal Precision above 6 returns "OE-7" *** ATENÇÃO: esta mensagem de e-mail tem origem externa! A cibersegurança é uma responsabilidade partilhada. Não aceda a links nem anexos de mensagens suspeitas ou inesperadas. CSIRT CGD *** ________________________________ Hi Tiago, The content is not messed up but converted to scientific notation. This is valid in json and various other data formats. Apparently either the database or NiFi considers decimal(x,7) the point at which scientific notation is more suitable. Since you’re going to CSV format, you can convert the values to string with the desired format right in the SQL query. Regards, Isha Van: Tiago Luís Sebastião (DSI) <[email protected]<mailto:[email protected]>> Verzonden: woensdag 19 april 2023 12:56 Aan: [email protected]<mailto:[email protected]> Onderwerp: NiFi 1.19.1 - ExecuteSQL - Decimal Precision above 6 returns "OE-7" Hi, I’m using a standalone deployment (Nifi 1.19.1) that runs directly on the server. I’ve noticed something tricky for the first time… When executing a query with Nifi processor ExecuteSQL the maximum decimal precision that it returns is 6, anything above that returns “0E-7”. Query: “ select 0.0000000::decimal(38,7) as Test1 -- NOT OK -> Returns 0E-7 ,0.0000000::decimal(38,6) as Test2 – OK -> Returns 0,000000 ,0.0000000::decimal(12,7) as Test3 -- Needed -> NOT OK -> Returns 0E-7 ,0.0000000::decimal(12,6) as Test4 -- OK -> Returns 0,000000 “ ExecuteSQL . List Queue . View content: " Obj___avro.schemaö_{"type":"record","name":"NiFi_ExecuteSQL_Record","namespace":"any.data","fields":[{"name":"TEST1","type":["null","string"]},{"name":"TEST2","type":["null","string"]},{"name":"TEST3","type":["null","string"]},{"name":"TEST4","type":["null","string"]}]}_avro.codec_nulléõ žSø®²›_yá‚_„_@__0E-7__0.000000__0E-7__0.000000éõ žSø®²›_yá‚_„ " After ExecuteSQL I use a ConvertRecord with AvroReader and CSVRecordSetWriter but since the content is already messed up it converts to: “0E-7; 0.000000;0E-7;0.000000” Any idea if this is a known issue or how to solve it? ----- ExecuteSQL Specs Database Connection Pooling Service: DBCPConnectionPool-Netezza -> IBM Cloud Pak for Data System (PostgreSQL) setSQL: select 0.0000000::decimal(38,7) as Test1, 0.0000000::decimal(38,6) as Test2, 0.0000000::decimal(12,7) as Test3, 0.0000000::decimal(12,6) as Test4 Normalize Table/Column Names: false Use Avro Logical Types: false Compression Format: NONE Default Decimal Precision: 10 Default Decimal Scale: 0 ----- Thanks in advance. Tiago Sebastião
