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.

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

Solved Go to solution

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

New Contributor

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.

41592-screen-shot-2017-10-26-at-50325-pm.png

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

Please Advise..!

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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

New Contributor

@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.

6 REPLIES 6

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

Super Guru
@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:-

41608-executesql.png

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

New Contributor

@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.

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

Super Guru
@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.

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

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.

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

New Contributor

@Matt Burgess Thanks for the explanation and suggestions.

Highlighted

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

New Contributor

@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.

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