Apache Nifi ExecuteSQL processor error: org.apache.avro.unresolvedUnionException: not in union ["null","float"]

[#1]

Using the ExecuteSQL processor (nifi 1.3) to fetch table from Teradata Database. The processor gives error (org.apache.avro.unresolvedUnionException: not in union ["null","float"]) when the table in the Teradata database has the 'Float' datatype.

The process is good when fetching the same table (float datatype) from Postgres database.

Please Advise..!

Thanks.

[#2]

@Gurinderbeer Singh

To resolve this error find out the column/columns which are causing issues

1. Cast those column/columns

2. Use the whole cast select query in ExecuteSQL processor.

instead of

select * from <table-name>

Use the Cast Select statement

select col1,col2,cast(col3 as decimal((n,m))) from <table-name> //give values for n(is the total number of digits (precision)) and m(is the number of digits to the right of the decimal point (scale).

Ex:- decimal(10,2) //total number of digit is 10, 2 digits to right of decimal point.

(or)

select col1,col2,cast(col3 as varchar(n)) from <table-name> //if you are not sure with decimal values then cast as varchar specify value for n(varying-length column of length n).

Ex:- varchar(30),varchar(20)...

Executesql Configs:-

[#3]

@Shu Thanks for the suggestions. The current process is using this Cast to varchar(), but I was wondering if we can get the data as float in AVRO.

[#4]

@Gurinderbeer Singh

As we are casting the datatype of the column to varchar() or decimal() after execute sql processor converts as string type.

But if you know the data of the column and once you import the data into Directory then you can create hive table column data type as float, double, decimal, string data type you need.

[#5]

According to this, it looks like Teradata doesn't support the DOUBLE data type, and instead supports FLOAT as the JDBC type yet returns a Double object when getObject is called. When the Double is inserted into an Avro record whose field type is "float", you get the above error.

This IMO is an issue with Teradata being out of spec with JDBC. Although NiFi could demote a Double into a float field, that is not prudent as there can be data/precision loss or other issues of which the user would never be made aware.

A workaround might be to use a DECIMAL column instead of DOUBLE, or to cast the column(s) as @Shu suggested.

[#6]

@Matt Burgess Thanks for the explanation and suggestions.

[#7 · Accepted solution]

@Matt Burgess

JDBC spec does state that REAL maps to (single-precision) float; FLOAT maps to double precision, thus the mapping to in avro should be to double not float, reference table B-1 page 190:

http://download.oracle.com/otn-pub/jcp/jdbc-4_3-mrel3-eval-spec/jdbc4.3-fr-spec.pdf

So the following code fix resolved the issue.

https://github.com/apache/nifi/blob/master/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-proce...

original

case FLOAT:
case REAL:                  
builder.name(columnName).type().unionOf().nullBuilder().endNull().and().floatType().endUnion().noDefault();                    
break;
              
case DOUBLE:                   
builder.name(columnName).type().unionOf().nullBuilder().endNull().and().doubleType().endUnion().noDefault();                    
break;

Modified

case REAL:                  
builder.name(columnName).type().unionOf().nullBuilder().endNull().and().floatType().endUnion().noDefault();                    
break;

case FLOAT:        
case DOUBLE:                   
builder.name(columnName).type().unionOf().nullBuilder().endNull().and().doubleType().endUnion().noDefault();                    
break;

Thanks to @Ron Mahoney for finding this issue.