Nifi executesql connecting to hana fails on creating avro schema

Question

Reviewing code the error gets thrown on undefined datatype that needs to be converted for avro schema. however the only column I am selecting is NVARCHAR(5000) type which is there in the code.

2017-04-21 01:33:51,446 WARN [Timer-Driven Process Thread-1] o.a.n.c.t.ContinuallyRunProcessorTask java.lang.IllegalArgumentException: createSchema: Unknown SQL type 2011 cannot be converted to Avro type at org.apache.nifi.processors.standard.util.JdbcCommon.createSchema(JdbcCommon.java:349) ~[na:na] at org.apache.nifi.processors.standard.util.JdbcCommon.convertToAvroStream(JdbcCommon.java:92) ~[na:na] at org.apache.nifi.processors.standard.util.JdbcCommon.convertToAvroStream(JdbcCommon.java:87) ~[na:na] at org.apache.nifi.processors.standard.util.JdbcCommon.convertToAvroStream(JdbcCommon.java:77) ~[na:na] at org.apache.nifi.processors.standard.ExecuteSQL$2.process(ExecuteSQL.java:205) ~[na:na] at org.apache.nifi.controller.repository.StandardProcessSession.write(StandardProcessSession.java:2329) ~[nifi-framework-core-1.1.2.jar:1.1.2] at org.apache.nifi.processors.standard.ExecuteSQL.onTrigger(ExecuteSQL.java:199) ~[na:na] at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27) ~[nifi-api-1.1.2.jar:1.1.2] at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1099) ~[nifi-framework-core-1.1.2.jar:1.1.2] at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:136) [nifi-framework-core-1.1.2.jar:1.1.2] at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47) [nifi-framework-core-1.1.2.jar:1.1.2] at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:132) [nifi-framework-core-1.1.2.jar:1.1.2] at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [na:1.8.0_102] at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) [na:1.8.0_102] at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) [na:1.8.0_102] at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) [na:1.8.0_102] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_102] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_102] at java.lang.Thread.run(Thread.java:745) [na:1.8.0_102]

Bryan Bende Bryan Bende · Accepted Answer · 2017-04-21T12:46:10

In the JDBC API from JDK8, NCLOB is 2011 and NVARCHAR is -9:

public static final int NCLOB = 2011;
public static final int NVARCHAR = -9;

It looks like the driver you are using is returning 2011 for the column even though you believe the column is NVARCHAR. I'm not totally sure but that seems like incorrect behavior from the hana driver.

It could probably be handled on the NiFi side of things by adding NCLOB to this case statement in JdbcCommon:

 case CHAR:
 case LONGNVARCHAR:
 case LONGVARCHAR:
 case NCHAR:
 case NVARCHAR:
 case VARCHAR:
 case CLOB:

Nifi executesql connecting to hana fails on creating avro schema

1 Answers