3 Replies Latest reply on Dec 18, 2015 2:38 AM by nt RSS
    nt Newbie

    How to setup to connect apache-hive-server with JDBC?

    Hi.

     

    I want to connect from thingworx to the apache-hive-server with JDBC. but, i'm run into probrem.

    connection-pool settings occured something wrong? Network is not wrong.(Other java native client is correct moving.)

     

    my settings

    2015y11m30d_171015724.jpg

    error message.

    2015y11m30d_171045989.jpg

     

    ■ApplicationLog:

    2015-11-30 16:11:18.328+0900 [L: ERROR] [O: c.t.w.BaseService] [I: ] [U: Administrator] [S: ] [T: http-nio-80-exec-7] [context: com.thingworx.webservices.context.HttpExecutionContext@11123240][message: Unable to Invoke Service SelectData on hiveConnection : Cannot get a connection, pool error Unable to validate object]

     

    ・ "Cannot get a connection, pool error Unable to validate object"

    -> "connectionValidationString" is wrong?

    i tried to enter "select now()" and "select 1" and "select * from dual" ,

    but still give same error.

     

    ■Added Libraries for Hive JDBC

    slf4j-log4j12-1.5.11.jar

    slf4j-api-1.5.11.jar

    log4j-1.2.16.jar

    libthrift-0.9.2.jar

    httpcore-4.2.5.jar

    httpclient-4.2.5.jar

    hive-service-1.1.1.jar

    hive-jdbc-1.1.1.jar

    hive-common-1.1.1.jar

    hive-cli-1.1.1.jar

    hadoop-common-2.7.1.jar

    guava-18.0.jar

    commons-logging-1.1.3.jar

     

     

    ThingWorx version is 5.4

     

    Please let me know how connect apache-hive-server.

     

    Thanks.

      • Re: How to setup to connect apache-hive-server with JDBC?
        maneeshr Explorer

        Nakam t Could you please send your "catalina.properties" file of tomcat?

          • Re: How to setup to connect apache-hive-server with JDBC?
            nt Newbie

            Hi Rao.

             

            thank you for your reply.

             

            This is all "catalina.properties" file of tomcat.

            --catalina.properties (exclude comments)----------------------------------------------

             

            package.access=sun.,org.apache.catalina.,org.apache.coyote.,org.apache.jasper.,\

            org.apache.naming.resources.,org.apache.tomcat.

             

            package.definition=sun.,java.,org.apache.catalina.,org.apache.coyote.,\

            org.apache.jasper.,org.apache.naming.,org.apache.tomcat.

             

            common.loader=${catalina.base}/lib,${catalina.base}/lib/*.jar,${catalina.home}/lib,${catalina.home}/lib/*.jar

             

            server.loader=

             

            shared.loader=

             

            tomcat.util.scan.DefaultJarScanner.jarsToSkip=\

            bootstrap.jar,commons-daemon.jar,tomcat-juli.jar,\

            annotations-api.jar,el-api.jar,jsp-api.jar,servlet-api.jar,websocket-api.jar,\

            catalina.jar,catalina-ant.jar,catalina-ha.jar,catalina-tribes.jar,\

            jasper.jar,jasper-el.jar,ecj-*.jar,\

            tomcat-api.jar,tomcat-util.jar,tomcat-coyote.jar,tomcat-dbcp.jar,\

            tomcat-jni.jar,tomcat-spdy.jar,\

            tomcat-i18n-en.jar,tomcat-i18n-es.jar,tomcat-i18n-fr.jar,tomcat-i18n-ja.jar,\

            tomcat-juli-adapters.jar,catalina-jmx-remote.jar,catalina-ws.jar,\

            tomcat-jdbc.jar,\

            tools.jar,\

            commons-beanutils*.jar,commons-codec*.jar,commons-collections*.jar,\

            commons-dbcp*.jar,commons-digester*.jar,commons-fileupload*.jar,\

            commons-httpclient*.jar,commons-io*.jar,commons-lang*.jar,commons-logging*.jar,\

            commons-math*.jar,commons-pool*.jar,\

            jstl.jar,\

            geronimo-spec-jaxrpc*.jar,wsdl4j*.jar,\

            ant.jar,ant-junit*.jar,aspectj*.jar,jmx.jar,h2*.jar,hibernate*.jar,httpclient*.jar,\

            jmx-tools.jar,jta*.jar,log4j.jar,log4j-1*.jar,mail*.jar,slf4j*.jar,\

            xercesImpl.jar,xmlParserAPIs.jar,xml-apis.jar,\

            junit.jar,junit-*.jar,hamcrest*.jar,org.hamcrest*.jar,ant-launcher.jar,\

            cobertura-*.jar,asm-*.jar,dom4j-*.jar,icu4j-*.jar,jaxen-*.jar,jdom-*.jar,\

            jetty-*.jar,oro-*.jar,servlet-api-*.jar,tagsoup-*.jar,xmlParserAPIs-*.jar,\

            xom-*.jar

             

            org.apache.catalina.startup.ContextConfig.jarsToSkip=

             

            org.apache.catalina.startup.TldConfig.jarsToSkip=tomcat7-websocket.jar

             

            tomcat.util.buf.StringCache.byte.enabled=true

             

            -----------------------------------------------------------------------------

             

            Regards.

             

            Nakam

              • Re: How to setup to connect apache-hive-server with JDBC?
                nt Newbie

                ThingWorx is to use method - "PreparedStatement.setQueryTimeout", but that is not supported on hive-jdbc.

                 

                this is more information log.

                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                PoolableConnectionFactory - Failed to validate a poolable connection

                java.sql.SQLException: Method not supported

                  at org.apache.hive.jdbc.HiveStatement.setQueryTimeout(HiveStatement.java:739)

                  at org.apache.commons.dbcp2.PoolableConnection.validate(PoolableConnection.java:234)

                  at org.apache.commons.dbcp2.PoolableConnectionFactory.validateConnection(PoolableConnectionFactory.java:303)

                  at org.apache.commons.dbcp2.PoolableConnectionFactory.validateObject(PoolableConnectionFactory.java:288)

                  at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:488)

                  at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:361)

                  at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:102)

                  at com.thingworx.things.database.QueryHandler.internalExecute(QueryHandler.java:51)

                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                 

                org.apache.hive.jdbc.HiveStatement.setQueryTimeout

                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                public void setQueryTimeout(int seconds) throws SQLException {

                  throw new SQLException("Method not supported");

                }

                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                 

                Thank you for your support!