SelectSQL processor for numeric table columns results in zero AVRO values

[Vitaly Krivoy <Vi...@jhancock.com>]

I am using a SelectSQL processor to pull data from DB2 in AVRO format with complete table selection query of the type: "select * from schema_name.table_name".
Initially I set "Use AVRO Logical Types" SelectSQL property to true. VARCHAR text fields were extracted fine, but dates were extracted as a numeric values such as 4798 as opposed to DB2's 1975-02-02. Additionally, key columns were extracted as zeros. This is an example of data in a key column: 201050013678989702. It would be extracted as 0. DbVisualizer shows that these columns have TYPE_NAME BIGINT, DATA_TYPE -5, COLUMN_SIZE 19, BUFFER_LENGTH 8.  After I changed "Use AVRO Logical Types" property to false, the dates are now extracted correctly, but numeric key columns are still extracted as 0. I tried to play with  SelectSQL processor "Default Decimal Precision" and "Default Decimal Scale" to no avail. How do I get this right? Thank you.

STATEMENT OF CONFIDENTIALITY The information contained in this email message and any attachments may be confidential and legally privileged and is intended for the use of the addressee(s) only. If you are not an intended recipient, please: (1) notify me immediately by replying to this message; (2) do not use, disseminate, distribute or reproduce any part of the message or any attachment; and (3) destroy all copies of this message and any attachments.

RE: SelectSQL processor for numeric table columns results in zero AVRO values

[Vitaly Krivoy <Vi...@jhancock.com>]

I'll answer my own question for the benefit of others who may bump up against a similar problem. There is an internal nifi class JdbsCommon, that ExecuteSQL (and other processors) use to convert from SQL types to AVRO types. The conversion is performed in the convertToAvroStream() method of this class. The expected precision of BIGINT is 0-19.  ExecuteSQL processor allows to customize this value by setting Default Decimal Precision property. There is a check inside  convertToAvroStream() which would convert a BIGING value to String if precision is outside of 0-19. So you would think that if you set Default Decimal Precision to 20, the conversion would happen. Unfortunately, there is a bug in the line that of code that check of default precision. Instead of comparing against the user specified value, it compares against the system default which is always 19 for BIGINT, thus insuring that the value will not be converted to a String. So the sequence of bits stays as they were and somewhere down the line in some Stream Writer class is interpreted as 0.  With this bug it seems that it is impossible to execute "Select * from table", but instead one would have to select columns individually and surround BIGINT columns with an ugly cast call, which in case of DB2 would look something like that:
select  CAST (CAST(PARTY_INFO_ID AS CHAR(50)) AS VARCHAR(50)) from IBMMDM.CDL_PARTY_INFO
PARTY_INFO_ID is the BIGINT column
And, for completeness, the buggy code:
else if (value instanceof Number || value instanceof Boolean) {
                        if (javaSqlType == BIGINT) {
                            int precision = meta.getPrecision(i);
                            if (precision < 0 || precision > MAX_DIGITS_IN_BIGINT) {
                                rec.put(i - 1, value.toString());
                            } else {
                                rec.put(i - 1, value);
                            }
                        } else {
                            rec.put(i - 1, value);
                        }

Meta.getPrecision(i) will always return 19, which = MAX_DIGITS_IN_BIGINT
User specified precision, which is passed into the method, but ignored, should have been used in this line.

Cheers!

From: Vitaly Krivoy [mailto:Vitaly_Krivoy@jhancock.com]
Sent: Wednesday, March 28, 2018 2:08 PM
To: users@nifi.apache.org
Subject: SelectSQL processor for numeric table columns results in zero AVRO values

I am using a SelectSQL processor to pull data from DB2 in AVRO format with complete table selection query of the type: "select * from schema_name.table_name".
Initially I set "Use AVRO Logical Types" SelectSQL property to true. VARCHAR text fields were extracted fine, but dates were extracted as a numeric values such as 4798 as opposed to DB2's 1975-02-02. Additionally, key columns were extracted as zeros. This is an example of data in a key column: 201050013678989702. It would be extracted as 0. DbVisualizer shows that these columns have TYPE_NAME BIGINT, DATA_TYPE -5, COLUMN_SIZE 19, BUFFER_LENGTH 8.  After I changed "Use AVRO Logical Types" property to false, the dates are now extracted correctly, but numeric key columns are still extracted as 0. I tried to play with  SelectSQL processor "Default Decimal Precision" and "Default Decimal Scale" to no avail. How do I get this right? Thank you.

STATEMENT OF CONFIDENTIALITY The information contained in this email message and any attachments may be confidential and legally privileged and is intended for the use of the addressee(s) only. If you are not an intended recipient, please: (1) notify me immediately by replying to this message; (2) do not use, disseminate, distribute or reproduce any part of the message or any attachment; and (3) destroy all copies of this message and any attachments.

STATEMENT OF CONFIDENTIALITY The information contained in this email message and any attachments may be confidential and legally privileged and is intended for the use of the addressee(s) only. If you are not an intended recipient, please: (1) notify me immediately by replying to this message; (2) do not use, disseminate, distribute or reproduce any part of the message or any attachment; and (3) destroy all copies of this message and any attachments.