You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@nifi.apache.org by Dnyaneshwar Pawar <dn...@persistent.com> on 2019/04/09 10:42:16 UTC
Apache NiFi not converting/recognizing decimal type in
ConvertAvroToJSON Processor while executing SQL query using ExecuteSQL
processor
Hi All,
We have EMPLOYEE table (definition is as shown below) created in derby database, which have SALARY as DECIMAL datatype. We are facing issue that its not recognizing decimal datatype and not converting it as expected (i.e. it converting SALARY field in String format instead of decimal).
TABLE : EMPLOYEE
COLUMN DEFINITION :
[cid:image003.jpg@01D4EEE1.EF7C4CA0]
We have created below sample NiFi flow to execute SQL query with Above table (ExecuteSQL Processor properties are also shown in below screenshot);
[cid:image004.png@01D4EEDE.9B388530]
As we are getting JSON response as string for Decimal data field, we modified property 'Use Avro Logical Types' to true to get Decimal values as expected, but we are getting below JSON response which contains SALARY field value in binary format which is not recognizable, is there a way where we can get decimal values as expected instead of getting in string or in binary format using same processors (like ExecuteSQL). Please suggest if we can do any property modification in above NiFi flow so that we can get proper value for Decimal data field as well.
JSON Response when property : 'Use Avro Logical Types' modified to true ;
[
{
"EMP_ID": 1,
"EMP_NAME": "ABC",
"SALARY": {
"bytes": " æö"
},
"DEPT": "IT"
},
{
"EMP_ID": 2,
"EMP_NAME": "XYZ",
"SALARY": {
"bytes": "\u001e\u0084®"
},
"DEPT": "HR"
},
{
"EMP_ID": 3,
"EMP_NAME": "TTT",
"SALARY": {
"bytes": "-Æ\\"
},
"DEPT": "FINANCE"
}
]
Regards,
Dnyaneshwar Pawar
DISCLAIMER
==========
This e-mail may contain privileged and confidential information which is the property of Persistent Systems Ltd. It is intended only for the use of the individual or entity to which it is addressed. If you are not the intended recipient, you are not authorized to read, retain, copy, print, distribute or use this message. If you have received this communication in error, please notify the sender and delete all copies of this message. Persistent Systems Ltd. does not accept any liability for virus infected mails.
Re: Apache NiFi not converting/recognizing decimal type in
ConvertAvroToJSON Processor while executing SQL query using ExecuteSQL processor
Posted by Matt Burgess <ma...@apache.org>.
Since your flow is ExecuteSQL -> ConvertAvroToJSON, you could instead just
use ExecuteSQLRecord with a JsonRecordSetWriter, that will do the same as
ConvertRecord without the extra step.
Regards,
Matt
On Tue, Apr 9, 2019 at 10:09 AM Bryan Bende <bb...@gmail.com> wrote:
> Hello,
>
> The way to represent decimals in Avro is to use a logical type of bytes
> [1], so I believe this is working as expected.
>
> I would be curious if you get any different result by removing
> ConvertAvroToJson and instead using ConvertRecord with Avro reader and Json
> writer.
>
> -Bryan
>
> [1] https://avro.apache.org/docs/1.8.2/spec.html#Decimal
>
>
> On Tue, Apr 9, 2019 at 6:59 AM Dnyaneshwar Pawar <
> dnyaneshwar_pawar@persistent.com> wrote:
>
>> Hi All,
>>
>>
>>
>> We have EMPLOYEE table (definition is as shown below) created in derby
>> database, which have SALARY as DECIMAL datatype. We are facing issue that
>> its not recognizing decimal datatype and not converting it as expected
>> (i.e. it converting SALARY field in String format instead of decimal).
>>
>>
>>
>> *TABLE* : EMPLOYEE
>>
>> *COLUMN DEFINITION* :
>>
>>
>>
>>
>>
>> We have created below sample NiFi flow to execute SQL query with Above
>> table (ExecuteSQL Processor properties are also shown in below screenshot);
>>
>>
>>
>>
>>
>> As we are getting JSON response as string for Decimal data field, we
>> modified property ‘Use Avro Logical Types’ to * true* to get Decimal
>> values as expected, but we are getting below JSON response which contains
>> SALARY field value in binary format which is not recognizable, is there a
>> way where we can get decimal values as expected instead of getting in
>> string or in binary format using same processors (like ExecuteSQL). Please
>> suggest if we can do any property modification in above NiFi flow so that
>> we can get proper value for Decimal data field as well.
>>
>>
>>
>> *JSON Response when property : ‘Use Avro Logical Types’ modified to true
>> ;*
>>
>>
>>
>> [
>>
>> {
>>
>> "EMP_ID": 1,
>>
>> "EMP_NAME": "ABC",
>>
>> "SALARY": {
>>
>> "bytes": " æö"
>>
>> },
>>
>> "DEPT": "IT"
>>
>> },
>>
>> {
>>
>> "EMP_ID": 2,
>>
>> "EMP_NAME": "XYZ",
>>
>> "SALARY": {
>>
>> "bytes": "\u001e\u0084®"
>>
>> },
>>
>> "DEPT": "HR"
>>
>> },
>>
>> {
>>
>> "EMP_ID": 3,
>>
>> "EMP_NAME": "TTT",
>>
>> "SALARY": {
>>
>> "bytes": "-Æ\\"
>>
>> },
>>
>> "DEPT": "FINANCE"
>>
>> }
>>
>> ]
>>
>>
>>
>>
>>
>>
>>
>> Regards,
>>
>> Dnyaneshwar Pawar
>>
>>
>>
>>
>> DISCLAIMER
>> ==========
>> This e-mail may contain privileged and confidential information which is
>> the property of Persistent Systems Ltd. It is intended only for the use of
>> the individual or entity to which it is addressed. If you are not the
>> intended recipient, you are not authorized to read, retain, copy, print,
>> distribute or use this message. If you have received this communication in
>> error, please notify the sender and delete all copies of this message.
>> Persistent Systems Ltd. does not accept any liability for virus infected
>> mails.
>>
>
Re: Apache NiFi not converting/recognizing decimal type in
ConvertAvroToJSON Processor while executing SQL query using ExecuteSQL processor
Posted by Bryan Bende <bb...@gmail.com>.
Hello,
The way to represent decimals in Avro is to use a logical type of bytes
[1], so I believe this is working as expected.
I would be curious if you get any different result by removing
ConvertAvroToJson and instead using ConvertRecord with Avro reader and Json
writer.
-Bryan
[1] https://avro.apache.org/docs/1.8.2/spec.html#Decimal
On Tue, Apr 9, 2019 at 6:59 AM Dnyaneshwar Pawar <
dnyaneshwar_pawar@persistent.com> wrote:
> Hi All,
>
>
>
> We have EMPLOYEE table (definition is as shown below) created in derby
> database, which have SALARY as DECIMAL datatype. We are facing issue that
> its not recognizing decimal datatype and not converting it as expected
> (i.e. it converting SALARY field in String format instead of decimal).
>
>
>
> *TABLE* : EMPLOYEE
>
> *COLUMN DEFINITION* :
>
>
>
>
>
> We have created below sample NiFi flow to execute SQL query with Above
> table (ExecuteSQL Processor properties are also shown in below screenshot);
>
>
>
>
>
> As we are getting JSON response as string for Decimal data field, we
> modified property ‘Use Avro Logical Types’ to * true* to get Decimal
> values as expected, but we are getting below JSON response which contains
> SALARY field value in binary format which is not recognizable, is there a
> way where we can get decimal values as expected instead of getting in
> string or in binary format using same processors (like ExecuteSQL). Please
> suggest if we can do any property modification in above NiFi flow so that
> we can get proper value for Decimal data field as well.
>
>
>
> *JSON Response when property : ‘Use Avro Logical Types’ modified to true
> ;*
>
>
>
> [
>
> {
>
> "EMP_ID": 1,
>
> "EMP_NAME": "ABC",
>
> "SALARY": {
>
> "bytes": " æö"
>
> },
>
> "DEPT": "IT"
>
> },
>
> {
>
> "EMP_ID": 2,
>
> "EMP_NAME": "XYZ",
>
> "SALARY": {
>
> "bytes": "\u001e\u0084®"
>
> },
>
> "DEPT": "HR"
>
> },
>
> {
>
> "EMP_ID": 3,
>
> "EMP_NAME": "TTT",
>
> "SALARY": {
>
> "bytes": "-Æ\\"
>
> },
>
> "DEPT": "FINANCE"
>
> }
>
> ]
>
>
>
>
>
>
>
> Regards,
>
> Dnyaneshwar Pawar
>
>
>
>
> DISCLAIMER
> ==========
> This e-mail may contain privileged and confidential information which is
> the property of Persistent Systems Ltd. It is intended only for the use of
> the individual or entity to which it is addressed. If you are not the
> intended recipient, you are not authorized to read, retain, copy, print,
> distribute or use this message. If you have received this communication in
> error, please notify the sender and delete all copies of this message.
> Persistent Systems Ltd. does not accept any liability for virus infected
> mails.
>