Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

ConvertJSONToSQL vs eg. mysql decimal(5,4)

Highlighted

ConvertJSONToSQL vs eg. mysql decimal(5,4)

Explorer

HI

I am working with NIFI. there is an scene that export an mysql table to another mysql table, The tables are the same table structure. source table has a field 'a' that is decimal(5,4), target table has a field 'b' that is decimal(5,4) too. The issue was triggered. eg. the value 0.1494 from source, the result was cut off to 0.1490. The root cause seems the below code.

///From ConvertJSONToSQL, the ColumnSize() is precision length ,but the value length is 6, so the subString method change the value to 0.149 .. 
                final Integer colSize = desc.getColumnSize();                
                final JsonNode fieldNode = rootNode.get(fieldName);               
                 if (!fieldNode.isNull()) {                   
                        String fieldValue = fieldNode.asText();                   
                        if (colSize != null && fieldValue.length() > colSize) {                   
                        fieldValue = fieldValue.substring(0, colSize);         
                  }            
                attributes.put("sql.args." + fieldCount + ".value", fieldValue);                }

Is this a bug?

How to resolve it?

5 REPLIES 5

Re: ConvertJSONToSQL vs eg. mysql decimal(5,4)

I believe you are running into NIFI-1613. Unfortunately I'm not aware of any workaround at this time.

Re: ConvertJSONToSQL vs eg. mysql decimal(5,4)

Hi @Paul Yang

If you have the option to use the latest Apache NiFi, it maybe possible as it provides 'decimal' Avro logical support since NiFi 1.3.0.

By using logical decimal type and PutDatabaseRecord processor, you can preserve decimal precision defined at the source MySQL table.

I created a simple example, currently ExecuteSQL and QueryDatabaseTable can generate Avro logical types.

16236-mysqldecimal.png

Template is available here in Gist.

https://gist.github.com/ijokarumawak/0bd4e0572e776313e8d5056d7d04f444

If you're receiving data in JSON format somewhere other than the source MySQL table, then I'd recommend using ConvertRecord processor to convert JSON into Avro with specifying desired Avro schema with decimal field before passing those into PutDatabaseRecord.

Hope this helps,

Koji

Re: ConvertJSONToSQL vs eg. mysql decimal(5,4)

Explorer

Thanks @kkawamura , currently, I change the decimal(5,4) to decimal(7,4) to workaround the bug.

It is so big bug.

Re: ConvertJSONToSQL vs eg. mysql decimal(5,4)

Explorer

HI, What is the reason the nifi team did not fix the bug even until the latest nifi version? for ConvertJsonToSql, is this a critical bug? or some other reason?

Re: ConvertJSONToSQL vs eg. mysql decimal(5,4)

Hi @Paul Yang

I am not aware of any specific reason to not fix a bug. Unfortunately the original effort to fix the issue has been left inactive until now. I've picked it up again and proposed a fix against the latest Apache NiFi codebase. Hopefully it can be merged soon.

https://github.com/apache/nifi/pull/1976

Thanks again for reporting this issue!

Don't have an account?
Coming from Hortonworks? Activate your account here