Knime Hive connector

Hi team ,

I facing issues at knime hive connector node , It is showing error where as all the setting is correct and in place..Kindly look on the error log below:

2017-09-13 16:08:12,780 : DEBUG : AWT-EventQueue-0 : HiveDriverDetector : Hive Connector : 2:1 : Using open source Hive driver: org.apache.hive.jdbc.HiveDriver
2017-09-13 16:08:12,780 : DEBUG : AWT-EventQueue-0 : DatabaseDriverLoader : Hive Connector : 2:1 : Database driver retrieved from user defined drivers: org.apache.hive.jdbc.HiveDriverDriver info: Driver class name: org.apache.hive.jdbc.HiveDriver major version: 2 minor version: 1 jdbc compliant: false
2017-09-13 16:08:12,781 : DEBUG : AWT-EventQueue-0 : PriorityDriverFactory : Hive Connector : 2:1 : DB driver: org.apache.hive.jdbc.HiveDriver found in driver factory: org.knime.core.node.port.database.RegisteredDriversConnectionFactory. Driver info: Driver class name: org.knime.core.node.port.database.DatabaseWrappedDriver major version: 2 minor version: 1 jdbc compliant: false
2017-09-13 16:08:21,650 : DEBUG : AWT-EventQueue-0 : HiveConnectorNodeModel : Hive Connector : 2:1 : Using jdbc url: jdbc:hive2://wukclkwbapp03:10001/default;transportMode=http;httpPath=cliservice
2017-09-13 16:08:21,652 : DEBUG : AWT-EventQueue-0 : HiveDriverDetector : Hive Connector : 2:1 : Using open source Hive driver: org.apache.hive.jdbc.HiveDriver
2017-09-13 16:08:21,665 : DEBUG : main : Hive Connector : Hive Connector : 2:1 : reset
2017-09-13 16:08:21,666 : DEBUG : main : Hive Connector : Hive Connector : 2:1 : clean output ports.
2017-09-13 16:08:21,666 : DEBUG : main : NodeContainer : Hive Connector : 2:1 : Hive Connector 2:1 has new state: IDLE
2017-09-13 16:08:21,670 : DEBUG : main : HiveDriverDetector : Hive Connector : 2:1 : Using open source Hive driver: org.apache.hive.jdbc.HiveDriver
2017-09-13 16:08:21,671 : DEBUG : main : HiveConnectorNodeModel : Hive Connector : 2:1 : Using jdbc url: jdbc:hive2://wukclkwbapp03:10001/default;transportMode=http;httpPath=cliservice
2017-09-13 16:08:21,674 : DEBUG : main : Hive Connector : Hive Connector : 2:1 : Configure succeeded. (Hive Connector)
2017-09-13 16:08:21,674 : DEBUG : main : NodeContainer : Hive Connector : 2:1 : Hive Connector 2:1 has new state: CONFIGURED
2017-09-13 16:08:22,552 : DEBUG : AWT-EventQueue-0 : HiveConnectorNodeModel : Hive Connector : 2:1 : Using jdbc url: jdbc:hive2://wukclkwbapp03:10001/default;transportMode=http;httpPath=cliservice
2017-09-13 16:08:22,556 : DEBUG : AWT-EventQueue-0 : HiveDriverDetector : Hive Connector : 2:1 : Using open source Hive driver: org.apache.hive.jdbc.HiveDriver
2017-09-13 16:08:22,569 : DEBUG : main : Hive Connector : Hive Connector : 2:1 : reset
2017-09-13 16:08:22,570 : DEBUG : main : Hive Connector : Hive Connector : 2:1 : clean output ports.
2017-09-13 16:08:22,570 : DEBUG : main : NodeContainer : Hive Connector : 2:1 : Hive Connector 2:1 has new state: IDLE
2017-09-13 16:08:22,576 : DEBUG : main : HiveDriverDetector : Hive Connector : 2:1 : Using open source Hive driver: org.apache.hive.jdbc.HiveDriver
2017-09-13 16:08:22,577 : DEBUG : main : HiveConnectorNodeModel : Hive Connector : 2:1 : Using jdbc url: jdbc:hive2://wukclkwbapp03:10001/default;transportMode=http;httpPath=cliservice
2017-09-13 16:08:22,583 : DEBUG : main : Hive Connector : Hive Connector : 2:1 : Configure succeeded. (Hive Connector)
2017-09-13 16:08:22,583 : DEBUG : main : NodeContainer : Hive Connector : 2:1 : Hive Connector 2:1 has new state: CONFIGURED
2017-09-13 16:08:25,306 : DEBUG : main : ExecuteAction :  :  : Creating execution job for 1 node(s)...
2017-09-13 16:08:25,307 : DEBUG : main : NodeContainer :  :  : Hive Connector 2:1 has new state: CONFIGURED_MARKEDFOREXEC
2017-09-13 16:08:25,308 : DEBUG : main : NodeContainer :  :  : Hive Connector 2:1 has new state: CONFIGURED_QUEUED
2017-09-13 16:08:25,309 : DEBUG : main : NodeContainer :  :  : KNIME_AND_HIVE_FRIENDSHIP 2 has new state: EXECUTING
2017-09-13 16:08:25,314 : DEBUG : KNIME-Worker-6 : WorkflowManager : Hive Connector : 2:1 : Hive Connector 2:1 doBeforePreExecution
2017-09-13 16:08:25,315 : DEBUG : KNIME-Worker-6 : NodeContainer : Hive Connector : 2:1 : Hive Connector 2:1 has new state: PREEXECUTE
2017-09-13 16:08:25,315 : DEBUG : KNIME-Worker-6 : WorkflowManager : Hive Connector : 2:1 : Hive Connector 2:1 doBeforeExecution
2017-09-13 16:08:25,316 : DEBUG : KNIME-Worker-6 : NodeContainer : Hive Connector : 2:1 : Hive Connector 2:1 has new state: EXECUTING
2017-09-13 16:08:25,316 : DEBUG : KNIME-Worker-6 : WorkflowFileStoreHandlerRepository : Hive Connector : 2:1 : Adding handler e597e7ca-d72d-438f-a17a-364cf6004f4f (Hive Connector 2:1: <no directory>) - 1 in total
2017-09-13 16:08:25,316 : DEBUG : KNIME-Worker-6 : LocalNodeExecutionJob : Hive Connector : 2:1 : Hive Connector 2:1 Start execute
2017-09-13 16:08:25,317 : DEBUG : KNIME-Worker-6 : HiveConnectorNodeModel : Hive Connector : 2:1 : Using jdbc url: jdbc:hive2://wukclkwbapp03:10001/default;transportMode=http;httpPath=cliservice
2017-09-13 16:08:25,320 : DEBUG : KNIME-WFM-Parent-Notifier : NodeContainer :  :  : ROOT  has new state: EXECUTING
2017-09-13 16:08:25,330 : DEBUG : KNIME-Worker-6 : NodeContext : Hive Connector : 2:1 : Workflow user found: mohit.sharma
2017-09-13 16:08:25,331 : DEBUG : KNIME-Worker-6 : DatabaseDriverLoader : Hive Connector : 2:1 : Database driver retrieved from user defined drivers: org.apache.hive.jdbc.HiveDriverDriver info: Driver class name: org.apache.hive.jdbc.HiveDriver major version: 2 minor version: 1 jdbc compliant: false
2017-09-13 16:08:25,331 : DEBUG : KNIME-Worker-6 : PriorityDriverFactory : Hive Connector : 2:1 : DB driver: org.apache.hive.jdbc.HiveDriver found in driver factory: org.knime.core.node.port.database.RegisteredDriversConnectionFactory. Driver info: Driver class name: org.knime.core.node.port.database.DatabaseWrappedDriver major version: 2 minor version: 1 jdbc compliant: false
2017-09-13 16:08:25,344 : DEBUG : pool-4-thread-4 : RegisteredDriversConnectionFactory : Hive Connector : 2:1 : Opening database connection to "jdbc:hive2://wukclkwbapp03:10001/default;transportMode=http;httpPath=cliservice"...
2017-09-13 16:08:26,683 : DEBUG : KNIME-Worker-6 : Hive Connector : Hive Connector : 2:1 : reset
2017-09-13 16:08:26,684 : ERROR : KNIME-Worker-6 : Hive Connector : Hive Connector : 2:1 : Execute failed: Could not create connection to database: org.apache.commons.logging.impl.LogFactoryImpl
2017-09-13 16:08:26,685 : DEBUG : KNIME-Worker-6 : Hive Connector : Hive Connector : 2:1 : Execute failed: Could not create connection to database: org.apache.commons.logging.impl.LogFactoryImpl
java.sql.SQLException: Could not create connection to database: org.apache.commons.logging.impl.LogFactoryImpl
    at com.knime.bigdata.hive.node.connector.HiveConnectorNodeModel.execute(HiveConnectorNodeModel.java:140)
    at org.knime.core.node.NodeModel.executeModel(NodeModel.java:567)
    at org.knime.core.node.Node.invokeFullyNodeModelExecute(Node.java:1128)
    at org.knime.core.node.Node.execute(Node.java:915)
    at org.knime.core.node.workflow.NativeNodeContainer.performExecuteNode(NativeNodeContainer.java:561)
    at org.knime.core.node.exec.LocalNodeExecutionJob.mainExecute(LocalNodeExecutionJob.java:95)
    at org.knime.core.node.workflow.NodeExecutionJob.internalRun(NodeExecutionJob.java:179)
    at org.knime.core.node.workflow.NodeExecutionJob.run(NodeExecutionJob.java:110)
    at org.knime.core.util.ThreadUtils$RunnableWithContextImpl.runWithContext(ThreadUtils.java:328)
    at org.knime.core.util.ThreadUtils$RunnableWithContext.run(ThreadUtils.java:204)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at org.knime.core.util.ThreadPool$MyFuture.run(ThreadPool.java:123)
    at org.knime.core.util.ThreadPool$Worker.run(ThreadPool.java:246)
Caused by: java.sql.SQLException: org.apache.commons.logging.LogConfigurationException: java.lang.ClassNotFoundException: org.apache.commons.logging.impl.LogFactoryImpl (Caused by java.lang.ClassNotFoundException: org.apache.commons.logging.impl.LogFactoryImpl)
    at org.knime.core.node.port.database.connection.CachedConnectionFactory.getConnection(CachedConnectionFactory.java:240)
    at org.knime.core.node.port.database.DatabaseConnectionSettings.createConnection(DatabaseConnectionSettings.java:439)
    at com.knime.bigdata.hive.node.connector.HiveConnectorNodeModel.execute(HiveConnectorNodeModel.java:132)
    ... 13 more
Caused by: org.apache.commons.logging.LogConfigurationException: java.lang.ClassNotFoundException: org.apache.commons.logging.impl.LogFactoryImpl (Caused by java.lang.ClassNotFoundException: org.apache.commons.logging.impl.LogFactoryImpl)
    at org.apache.commons.logging.LogFactory.createFactory(LogFactory.java:1158)
    at org.apache.commons.logging.LogFactory$2.run(LogFactory.java:960)
    at java.security.AccessController.doPrivileged(Native Method)
    at org.apache.commons.logging.LogFactory.newFactory(LogFactory.java:957)
    at org.apache.commons.logging.LogFactory.getFactory(LogFactory.java:624)
    at org.apache.commons.logging.LogFactory.getLog(LogFactory.java:655)
    at org.apache.http.conn.ssl.DefaultHostnameVerifier.<init>(DefaultHostnameVerifier.java:69)
    at org.apache.http.impl.client.HttpClientBuilder.build(HttpClientBuilder.java:901)
    at org.apache.hive.jdbc.HiveConnection.getHttpClient(HiveConnection.java:402)
    at org.apache.hive.jdbc.HiveConnection.createHttpTransport(HiveConnection.java:250)
    at org.apache.hive.jdbc.HiveConnection.openTransport(HiveConnection.java:193)
    at org.apache.hive.jdbc.HiveConnection.<init>(HiveConnection.java:157)
    at org.apache.hive.jdbc.HiveDriver.connect(HiveDriver.java:107)
    at org.knime.core.node.port.database.DatabaseWrappedDriver.connect(DatabaseWrappedDriver.java:81)
    at org.knime.core.node.port.database.connection.CachedConnectionFactory.createConnection(CachedConnectionFactory.java:320)
    at com.knime.bigdata.commons.security.kerberos.KerberosConnectionFactory.createConnection(KerberosConnectionFactory.java:67)
    at org.knime.core.node.port.database.connection.CachedConnectionFactory.createConnection(CachedConnectionFactory.java:301)
    at org.knime.core.node.port.database.connection.CachedConnectionFactory$1.call(CachedConnectionFactory.java:225)
    at org.knime.core.node.port.database.connection.CachedConnectionFactory$1.call(CachedConnectionFactory.java:1)
    at org.knime.core.util.ThreadUtils$CallableWithContextImpl.callWithContext(ThreadUtils.java:344)
    at org.knime.core.util.ThreadUtils$CallableWithContext.call(ThreadUtils.java:244)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.ClassNotFoundException: org.apache.commons.logging.impl.LogFactoryImpl
    at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
    at org.apache.commons.logging.LogFactory.createFactory(LogFactory.java:1020)
    ... 24 more
2017-09-13 16:08:26,746 : DEBUG : KNIME-Worker-6 : WorkflowManager : Hive Connector : 2:1 : Hive Connector 2:1 doBeforePostExecution
2017-09-13 16:08:26,746 : DEBUG : KNIME-Worker-6 : NodeContainer : Hive Connector : 2:1 : Hive Connector 2:1 has new state: POSTEXECUTE
2017-09-13 16:08:26,747 : DEBUG : KNIME-Worker-6 : WorkflowManager : Hive Connector : 2:1 : Hive Connector 2:1 doAfterExecute - failure
2017-09-13 16:08:26,748 : DEBUG : KNIME-Worker-6 : Hive Connector : Hive Connector : 2:1 : reset
2017-09-13 16:08:26,748 : DEBUG : KNIME-Worker-6 : Hive Connector : Hive Connector : 2:1 : clean output ports.
2017-09-13 16:08:26,748 : DEBUG : KNIME-Worker-6 : WorkflowFileStoreHandlerRepository : Hive Connector : 2:1 : Removing handler e597e7ca-d72d-438f-a17a-364cf6004f4f (Hive Connector 2:1: <no directory>) - 0 remaining
2017-09-13 16:08:26,749 : DEBUG : KNIME-Worker-6 : NodeContainer : Hive Connector : 2:1 : Hive Connector 2:1 has new state: IDLE
2017-09-13 16:08:26,749 : DEBUG : KNIME-Worker-6 : HiveDriverDetector : Hive Connector : 2:1 : Using open source Hive driver: org.apache.hive.jdbc.HiveDriver
2017-09-13 16:08:26,750 : DEBUG : KNIME-Worker-6 : HiveConnectorNodeModel : Hive Connector : 2:1 : Using jdbc url: jdbc:hive2://wukclkwbapp03:10001/default;transportMode=http;httpPath=cliservice
2017-09-13 16:08:26,758 : DEBUG : KNIME-Worker-6 : Hive Connector : Hive Connector : 2:1 : Configure succeeded. (Hive Connector)
2017-09-13 16:08:26,759 : DEBUG : KNIME-Worker-6 : NodeContainer : Hive Connector : 2:1 : Hive Connector 2:1 has new state: CONFIGURED
2017-09-13 16:08:26,759 : DEBUG : KNIME-Worker-6 : NodeContainer : Hive Connector : 2:1 : KNIME_AND_HIVE_FRIENDSHIP 2 has new state: IDLE
2017-09-13 16:08:26,760 : DEBUG : KNIME-WFM-Parent-Notifier : NodeContainer :  :  : ROOT  has new state: IDLE
2017-09-13 16:08:26,780 : DEBUG : KNIME-Node-Usage-Writer : NodeTimer$GlobalNodeStats :  :  : Successfully wrote node usage stats to file: C:\Users\mohit.sharma\.metadata\knime\nodeusage_3.0.json
 

 

Hi Mohit,

it looks like you have added the open-source Hive 2.1 JDBC driver to KNIME (via File > Preferences > KNIME > Databases). Unfortunately the driver jar you supplied is missing some classes, such as org.apache.commons.logging.impl.LogFactoryImpl (hence the ClassNotFoundException).

Adding the open-source Hive drivers in this manner is rather tricky and does not work properly in many cases. I would recommend to use the proprietary (Simba-based) Hive JDBC driver supplied by your Hadoop vendor.

For Hortonworks, this is:

https://s3.amazonaws.com/public-repo-1.hortonworks.com/HDP/hive-jdbc4/1.0.40.1052/Simba_HiveJDBC41_1.0.40.1052.zip

 

For Cloudera this is only available after a login:

https://www.cloudera.com/downloads/connectors/hive/jdbc/2-5-19.html

 

You need to unzip these drivers into an empty directory. The Cloudera driver then gives you another bunch of zip files, for different JDBC versions (unzip the JDBC 4.1 zip). In the end you should have a directory with several jar files in it. 

After that, switch to  KNIME and  open File > Preferences > KNIME > Databases. Here, remove all other Hive drivers you have added so farn.  Then add the directory with the driver jars via the "Add Directory" button. Confirm and close the dialog.

In your workflow, open the Hive Connector dialog again. It should now indicate that it is using the new proprietary Hive JDBC driver. Please note that these drivers probably require different JDBC parameters for your setup. The drivers include a PDF that describe the available parameters.

Best,

Björn

Hi Mohit,

the nightly KNIME store build now contains an updated Hive JDBC driver that will work with transportMode=http and Kerberos authentication:

https://www.knime.com/form/nightly-build

(you may have to add the KNIME store nightly build update site, the URL is on the nightly build update site)

 

The updated JDBC driver will be officially released beginning of December as part of KNIME AP 3.5.

 

Best,

Björn