You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@nifi.apache.org by "Tiago Luís Sebastião (DSI)" <ti...@cgd.pt> on 2023/04/19 10:56:15 UTC
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
RE: NiFi 1.19.1 - ExecuteSQL - Decimal Precision above 6 returns "OE-7"
Posted by "Tiago Luís Sebastião (DSI)" <ti...@cgd.pt>.
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:isha.lamboo@virtualsciences.nl]
Sent: 19 de abril de 2023 15:10
To: users@nifi.apache.org
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) <ti...@cgd.pt>>
Verzonden: woensdag 19 april 2023 12:56
Aan: users@nifi.apache.org<ma...@nifi.apache.org>
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
RE: NiFi 1.19.1 - ExecuteSQL - Decimal Precision above 6 returns "OE-7"
Posted by Isha Lamboo <is...@virtualsciences.nl>.
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) <ti...@cgd.pt>
Verzonden: woensdag 19 april 2023 12:56
Aan: users@nifi.apache.org
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