Failed to run hive query from java application

[#1]

Hi All,

 Currently we are building a Java application to connect hive database to perform data transfer. We are encountering issue while issuing the below query through java application

"select count(*) from tablename where partition_key='xxxxx'".

The above table is partitioned by partition_key column. below is the complete error log from Java

### Error querying database. Cause: java.sql.SQLException: [Cloudera][HiveJDBCDriver](500051) ERROR processing query/statement. Error Code: [Cloudera][JSQLEngine](12010) The table "dbname"."tablename" could not be found., SQL state: HY000, Query: select count(*) from dbname.tablename where partition_key='201912310002' .
### The error may exist in com/hds/datatransfer/mapper/hive/HiveRepository.java (best guess)
### The error may involve com.hds.datatransfer.mapper.hive.HiveRepository.test1
### The error occurred while executing a query
### SQL: select count(*) from dbname.tablename where partition_key='201912310002' 
### Cause: java.sql.SQLException: [Cloudera][HiveJDBCDriver](500051) ERROR processing query/statement. Error Code: [Cloudera][JSQLEngine](12010) The table "dbname"."tablename " could not be found., SQL state: HY000, Query: select count(*) from dbname.tablename where partition_key='201912310002' .

This table is stored as ORC file format

Note: same query if I run it in any editor, it is working fine.

And also we observed that if Java application perform count operation on non partition table, it is working. 

We are unsure about this strange behavior, can you please help me on this.

Thanks! 

[#2 · Accepted solution]

Hi Zara,

You need to check a couple of things - which may give more clues on the why.

1. Are you using the latest Hive JDBC driver? (is it HDP or CDH/CDP?) 

Check out https://www.cloudera.com/downloads/connectors/hive/jdbc/2-6-11.html

2. Do you get the same error if you use backticks for the database name and/or for the table name?

select count(*) from `dbname`.`tablename` where partition_key='xxxxx';

3. Did you look at the HiveServer2 logs? What do they show you? What was the real query submitted to the HS2 and what was the failure there? What does it look like for a non-partitioned table?

4. The "UseNativeQuery" option of the JDBC driver sometimes can solve such interesting issues - as it is a flag whether the Hive query is already translated to a Hive native format. Check the docs.

https://docs.cloudera.com/documentation/other/connectors/hive-jdbc/2-6-11/Cloudera-JDBC-Driver-for-A...

5. Also you can turn on debug level logs at the JDBC driver side, see LogLevel and LogPath JDBC connection string properties.