Database Reader Execute failed: Method not supported when connecting to Hive

KNIME Analytics Platform
1

Hello!

I have succeeded in connecting to Hive from KNIME Database Reader node by copying all files from Hive lib folder into KNIME_HOME/jre/lib/ext and registering a new db driver in KNIME (org.apache.hadoop.hive.jdbc.HiveDriver from hive-jdbc-0.9.0.jar). I can also get metadata (tables and fields) in Database Reader dialog from "jdbc:hive://localhost:10000/default".

1) However, the execution of the node fails for a query as simple as 'select * from test' with the reported error "ERROR Database Reader Execute failed: Method not supported". 

I have noticed a lot of unimplemented methods in hive source, which throwi exception with that message. Nevertheless, I can succesfully query Hive from a simple JDBC Java client as suggested in (https://cwiki.apache.org/Hive/hiveclient.html) and there is this encouraging Hive related post (http://tech.knime.org/node/20858)

2) I also get errors (warnings) for nonstandard sql, Hive specific queries like 'show tables': WARN Database Reader java.sql.SQLException: Query returned non-zero code: 11, cause: FAILED: Parse Error: line 1:15 cannot recognize input near 'show' 'tables' ')' in subquery source

Thanks for any suggestions.

Here is the listing of my KNIME_HOME/jre/lib/ext folder:

 

 

  /opt/knime_2.7.1/jre/lib/ext:
 
  drwxrwxr-x  2 hadoop root     4096 Feb 11 14:24 .
  drwxrwxr-x 15 hadoop root     4096 Feb 11 14:24 ..
  -rw-rw-r--  1 hadoop hadoop  105209 Feb  7 16:44 antlr-runtime-3.0.1.jar
  -rw-rw-r--  1 hadoop hadoop   41123 Feb  7 16:44 commons-cli-1.2.jar
  -rw-rw-r--  1 hadoop hadoop   46725 Feb  7 16:44 commons-codec-1.3.jar
  -rw-rw-r--  1 hadoop hadoop  575389 Feb  7 16:44 commons-collections-3.2.1.jar
  -rw-rw-r--  1 hadoop hadoop  160519 Feb  7 16:44 commons-dbcp-1.4.jar
  -rw-rw-r--  1 hadoop hadoop  261809 Feb  7 16:44 commons-lang-2.4.jar
  -rw-rw-r--  1 hadoop hadoop   38015 Feb  7 16:44 commons-logging-1.0.4.jar
  -rw-rw-r--  1 hadoop hadoop   26202 Feb  7 16:44 commons-logging-api-1.0.4.jar
  -rw-rw-r--  1 hadoop hadoop   96221 Feb  7 16:44 commons-pool-1.5.4.jar
  -rw-rw-r--  1 hadoop hadoop   16880 Feb  7 16:44 datanucleus-connectionpool-2.0.3.jar
  -rw-rw-r--  1 hadoop hadoop 2002412 Feb  7 16:44 datanucleus-core-2.0.3.jar
  -rw-rw-r--  1 hadoop hadoop  188159 Feb  7 16:44 datanucleus-enhancer-2.0.3.jar
  -rw-rw-r--  1 hadoop hadoop 1225113 Feb  7 16:44 datanucleus-rdbms-2.0.3.jar
  -rw-rw-r--  1 hadoop hadoop 2446767 Feb  7 16:44 derby-10.4.2.0.jar
  -rw-rw-r--  1 hadoop root     8934 Nov 30 11:33 dnsns.jar
  -rw-rw-r--  1 hadoop hadoop 1143921 Feb  7 16:44 guava-r09.jar
  -rw-rw-r--  1 hadoop hadoop 4030916 Feb  8 09:53 hadoop-core-1.1.1.jar
  -rw-rw-r--  1 hadoop hadoop 3105047 Feb  7 16:44 hbase-0.92.0.jar
  -rw-rw-r--  1 hadoop hadoop 1483963 Feb  7 16:44 hbase-0.92.0-tests.jar
  -rw-rw-r--  1 hadoop hadoop    3914 Feb  7 16:44 hive-builtins-0.9.0.jar
  -rw-rw-r--  1 hadoop hadoop   30040 Feb  7 16:44 hive-cli-0.9.0.jar
  -rw-rw-r--  1 hadoop hadoop   58875 Feb  7 16:44 hive-common-0.9.0.jar
  -rw-rw-r--  1 hadoop hadoop  112516 Feb  7 16:44 hive-contrib-0.9.0.jar
  -rw-rw-r--  1 hadoop hadoop  112516 Feb  7 16:44 hive_contrib.jar
  -rw-rw-r--  1 hadoop hadoop 3530006 Feb  7 16:44 hive-exec-0.9.0.jar
  -rw-rw-r--  1 hadoop hadoop   54747 Feb  7 16:44 hive-hbase-handler-0.9.0.jar
  -rw-rw-r--  1 hadoop hadoop   23608 Feb  7 16:44 hive-hwi-0.9.0.jar
  -rw-rw-r--  1 hadoop hadoop   66037 Feb 11 14:20 hive-jdbc-0.10.0.jar
  -rw-rw-r--  1 hadoop hadoop   59133 Feb  7 16:44 hive-jdbc-0.9.0.jar
  -rw-rw-r--  1 hadoop hadoop 1719375 Feb  7 16:44 hive-metastore-0.9.0.jar
  -rw-rw-r--  1 hadoop hadoop   14157 Feb  7 16:44 hive-pdk-0.9.0.jar
  -rw-rw-r--  1 hadoop hadoop  519798 Feb  7 16:44 hive-serde-0.9.0.jar
  -rw-rw-r--  1 hadoop hadoop  174538 Feb  7 16:44 hive-service-0.9.0.jar
  -rw-rw-r--  1 hadoop hadoop  113333 Feb  7 16:44 hive-shims-0.9.0.jar
  -rw-rw-r--  1 hadoop hadoop  227500 Feb  7 16:44 jackson-core-asl-1.8.8.jar
  -rw-rw-r--  1 hadoop hadoop   17884 Feb  7 16:44 jackson-jaxrs-1.8.8.jar
  -rw-rw-r--  1 hadoop hadoop  668564 Feb  7 16:44 jackson-mapper-asl-1.8.8.jar
  -rw-rw-r--  1 hadoop hadoop   32353 Feb  7 16:44 jackson-xc-1.8.8.jar
  -rw-rw-r--  1 hadoop hadoop   16993 Feb  7 16:44 JavaEWAH-0.3.2.jar
  -rw-rw-r--  1 hadoop hadoop  198552 Feb  7 16:44 jdo2-api-2.3-ec.jar
  -rw-rw-r--  1 hadoop hadoop   87325 Feb  7 16:44 jline-0.9.94.jar
  -rw-rw-r--  1 hadoop hadoop   45944 Feb  7 16:44 json-20090211.jar
  -rw-rw-r--  1 hadoop hadoop  175982 Feb  7 16:44 libfb303-0.7.0.jar
  -rw-rw-r--  1 hadoop hadoop  175982 Feb  7 16:44 libfb303.jar
  -rw-rw-r--  1 hadoop hadoop  301547 Feb  7 16:44 libthrift-0.7.0.jar
  -rw-rw-r--  1 hadoop hadoop  301547 Feb  7 16:44 libthrift.jar
  -rw-rw-r--  1 hadoop root  1023488 Nov 30 11:33 localedata.jar
  -rw-rw-r--  1 hadoop hadoop  481535 Feb  7 16:44 log4j-1.2.16.jar
  -rw-rw-r--  1 hadoop root      581 Nov 30 11:33 meta-index
  -rw-rw-r--  1 hadoop hadoop   25496 Feb  7 16:44 slf4j-api-1.6.1.jar
  -rw-rw-r--  1 hadoop hadoop    9753 Feb  7 16:44 slf4j-log4j12-1.6.1.jar
  -rw-rw-r--  1 hadoop hadoop  229928 Feb  7 16:44 stringtemplate-3.1-b1.jar
  -rw-rw-r--  1 hadoop root    15817 Nov 30 11:33 sunec.jar
  -rw-rw-r--  1 hadoop root   200013 Nov 30 11:33 sunjce_provider.jar
  -rw-rw-r--  1 hadoop root   241235 Nov 30 11:33 sunpkcs11.jar
  -rw-rw-r--  1 hadoop root    68653 Nov 30 11:33 zipfs.jar
  -rw-rw-r--  1 hadoop hadoop  767592 Feb  7 16:44 zookeeper-3.4.3.jar
 

 

2

Thanks for reporting the problem which came up with 2.7! We will put into our tracking system to have this fixed for the next release. Cheers.

3

We had a problem in the database integration supporting HIVE since KNIME 2.3.3 which will be fixed with KNIME 2.7.2 coming out soon. Thanks for reporting this issue.

4

Now I want to integrate knme with my java project, and I use a java bar code sdk, are there suitable peoducts for it?