4 Replies Latest reply on May 2, 2013 8:32 AM by Steven Hawkins

    Hive - Average

    devin pinkston Newbie

      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!

        • 1. Re: Hive - Average
          Ramesh Reddy Master

          What is the error?

          • 2. Re: Hive - Average
            devin pinkston Newbie

            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

            • 3. Re: Hive - Average
              Ramesh Reddy Master

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

              • 4. Re: Hive - Average
                Steven Hawkins Master

                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