Hive - Average

[pinkstondevin]

Hello all,

I was testing aggregate functions with Hive/Hadoop yesterday and came acrosse an interesting.

Average returns a method not supported error.  I can do a sum/count to return the average, but it is not as efficient.  If i send the query "SELECT avg(col) FROM someTable" hadoop starts a map reduce and completes without errors, but TEIID throws an error.

Any idea?

Thanks!

[rareddy]

What is the error?

[pinkstondevin]

Ramesh,

Sorry for the delay. 

When i use the query: "SELECT AVG(yearID) FROM hiveDB" this is the error:

07:36:56,863 WARN  [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue1) sEYRFVYPQamf Connector worker process failed for atomic-request=sEYRFVYPQamf.0.1.0: org.teiid.translator.TranslatorException: Unexpected exception while translating results: Method not supported

        at org.teiid.translator.jdbc.JDBCQueryExecution.next(JDBCQueryExecution.java:108)

        at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.handleBatch(ConnectorWorkItem.java:316) [teiid-engine-8.3.0.Final.jar:8.3.0.Final]

        at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.more(ConnectorWorkItem.java:141) [teiid-engine-8.3.0.Final.jar:8.3.0.Final]

        at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:467) [teiid-engine-8.3.0.Final.jar:8.3.0.Final]

        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:178) [teiid-engine-8.3.0.Final.jar:8.3.0.Final]

        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:175) [teiid-engine-8.3.0.Final.jar:8.3.0.Final]

        at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303) [rt.jar:1.6.0_43]

        at java.util.concurrent.FutureTask.run(FutureTask.java:138) [rt.jar:1.6.0_43]

        at org.teiid.dqp.internal.process.DQPCore$FutureWork.run(DQPCore.java:120) [teiid-engine-8.3.0.Final.jar:8.3.0.Final]

        at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:264) [teiid-engine-8.3.0.Final.jar:8.3.0.Final]

        at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:123) [teiid-engine-8.3.0.Final.jar:8.3.0.Final]

        at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:298) [teiid-engine-8.3.0.Final.jar:8.3.0.Final]

        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895) [rt.jar:1.6.0_43]

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918) [rt.jar:1.6.0_43]

        at java.lang.Thread.run(Thread.java:662) [rt.jar:1.6.0_43]

Caused by: java.sql.SQLException: Method not supported

        at org.apache.hadoop.hive.jdbc.HiveBaseResultSet.getBigDecimal(HiveBaseResultSet.java:105)

        at org.jboss.jca.adapters.jdbc.WrappedResultSet.getBigDecimal(WrappedResultSet.java:424)

        at org.teiid.translator.jdbc.JDBCExecutionFactory.retrieveValue(JDBCExecutionFactory.java:888)

        at org.teiid.translator.jdbc.JDBCQueryExecution.next(JDBCQueryExecution.java:101)

        ... 14 more

07:36:56,881 WARN  [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue2) sEYRFVYPQamf TEIID30020 Processing exception 'TEIID30504 hive-connector: Unexpected exception while translating results: Method not supported' for request sEYRFVYPQamf.0.  Exception type org.teiid.core.TeiidProcessingException thrown from org.apache.hadoop.hive.jdbc.HiveBaseResultSet.getBigDecimal(HiveBaseResultSet.java:105). Enable more detailed logging to see the entire stacktrace.

Thanks

[rareddy]

Devin,

From this https://cwiki.apache.org/Hive/languagemanual-udf.html  the AVG function is supported for pushdown. I am guessing may be average on the data type on BigDecimal is not supported by Hive driver. Can you check if you have this issue on any other numeric columns?

Ramesh..

[shawkins]

From looking at their functions and old code I would say that they did not expect to return BigDecimal (just double) and generally BigDecimal was likely an unsupported type for them.  However the trunk version of the Hive driver does support getBigDecimal - http://svn.apache.org/repos/asf/hive/trunk/jdbc/src/java/org/apache/hadoop/hive/jdbc/HiveBaseResultSet.java

What driver version are you using and is there a later one that could be used?

Steve