Non-simple SQL query not working in Database Reader accessing Hive from Knime Server

Hi there, 

I am trying to access Hive from Knime using the Big Data Connectors. 

For this I use a simple workflow with just a Hive Connector and a Database Reader (see attached zip file).

For simple queries like "select * from <table>" this works on both Knime Desktop and Knime Server. However, making the query a bit more involved (e.g. "select count(*) from <table>") this stops working on Knime Server (it still works on Knime Desktop). The resulting error on Knime Server is given by: 

Database Reader 6:325 - Execute failed: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask

When changing the execution engine to Spark in the Cloudera cluster, I get the following error:

Database Reader 9:325 - Execute failed: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.spark.SparkTask

Following advice from different places on the internet I have tried the following to no avail:

  • Set the setting hive.auto.convert.join to false in the Cloudera cluster (via Cloudera manager)
  • Tried different users in the hive connector (my personal useraccount, hive, hdfs)

I cannot find any relevant log messages in the Knime Server logs or the Hive logs.

I rule out a network problem between Knime Server and the Cloudera cluster, since the simple query works fine.

Any thoughts?

Thanks, Andreas

Hi Andreas,

a few questions:

1) What is the full error message? (you can find it in the KNIME Executor log on the KNIME Server machine).

2) What JDBC driver are you using? (the embedded driver vs proprietary Cloudera driver)

3) Does the query work from within beeline?

If you can, it will speed things up to provide the KNIME Executor log that contains all messages from the execution of your workflow.

Best,

Björn

Hi Björn,

thanks for the quick reply.

Regarding 1: I sent you the KNIME Executor log via email (I hope I guessed the email address right). In the logs you will find successful runs as well as unsuccessful runs (see below).

Also I got an interesting new finding. When editing and uploading the workflow from KNIME Desktop 3.3.1 on Windows, the workflow executes fine on KNIME Server 4.5.0. Editing and uploading the same workflow from KNIME Desktop 3.5.1 on Mac OS, I get the above error. Might there be a compatibility problem?

Please let me know if the answers to 2) and 3) are still relevant so I can investigate further.

BR Andreas

Hi Björn,

a colleague of mine had contact with someone from your support team and they were able to solve the problem. I will try to get the details of the solution so it can be posted here publicly for the case someone else runs into the same problem.

Thanks for your support!

BR Andreas