question Re: SQL Query Failed with Cloudera Hive JDBC driver but works fine in Hue in Archives of Support Questions (Read Only)

SQL Query Failed with Cloudera Hive JDBC driver but works fine in Hue

spurusothaman — Tue, 21 Apr 2026 13:45:00 GMT

We have a Hive SQL that runs fine in Hue but when we run that same query via Hive JDBC driver it fails with the below error message,

java.sql.SQLException: [Cloudera][HiveJDBCDriver](500051) ERROR processing query/statement. Error Code: 10009, SQL state: TStatus(statusCode:ERROR_STATUS, infoMessages:[*org.apache.hive.service.cli.HiveSQLException:Error while compiling statement: FAILED: SemanticException [Error 10009]: Line 1:5602 Invalid table alias 'F':17:16, org.apache.hive.service.cli.operation.Operation:toSQLException:Operation.java:400, org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:187, org.apache.hive.service.cli.operation.SQLOperation:runInternal:SQLOperation.java:271, org.apache.hive.service.cli.operation.Operation:run:Operation.java:337, org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementInternal:HiveSessionImpl.java:439, org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementAsync:HiveSessionImpl.java:416, org.apache.hive.service.cli.CLIService:executeStatementAsync:CLIService.java:282, org.apache.hive.service.cli.thrift.ThriftCLIService:ExecuteStatement:ThriftCLIService.java:501, org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1313, org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1298, org.apache.thrift.ProcessFunction:process:ProcessFunction.java:39, org.apache.thrift.TBaseProcessor:process:TBaseProcessor.java:39, org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge$Server$TUGIAssumingProcessor:process:HadoopThriftAuthBridge.java:705, org.apache.thrift.server.TThreadPoolServer$WorkerProcess:run:TThreadPoolServer.java:286, java.util.concurrent.ThreadPoolExecutor:runWorker:ThreadPoolExecutor.java:1142, java.util.concurrent.ThreadPoolExecutor$Worker:run:ThreadPoolExecutor.java:617, java.lang.Thread:run:Thread.java:745, *org.apache.hadoop.hive.ql.parse.SemanticException:Line 1:5602 Invalid table alias 'F':36:20,

Re: SQL Query Failed with Cloudera Hive JDBC driver but works fine in Hue

EricL — Mon, 01 Jan 2018 02:20:08 GMT

I don't see what exact query was sent to HS2 to run that caused the issue. But from the error message, it looks like that there is some issue on the query string itself. This could happen when there is bugs in the HIve JDBC driver that you are using and does not tramsform query correctly.

Couple of things you can check:

1. what version of JDBC driver are you using? If not latest, maybe try the latest version:

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

2. Check if UseNativeQuery is set or not, Default is 0, meaning it will transform query inside JDBC before sending to HS2. You can try to set it to 1 to disable transformation. Details can be found here:

http://www.cloudera.com/documentation/other/connectors/hive-jdbc/latest/Cloudera-JDBC-Driver-for-Apache-Hive-Install-Guide.pdf

Re: SQL Query Failed with Cloudera Hive JDBC driver but works fine in Hue

spurusothaman — Wed, 03 Jan 2018 16:49:30 GMT

worked with enabling the use of Native query option. Thanks for your help.

Re: SQL Query Failed with Cloudera Hive JDBC driver but works fine in Hue

Neha1 — Fri, 11 May 2018 10:01:01 GMT

Hi,

Can you please share what tool you used for Hive JDBC connection and how you enabled the 'useNativeQuery' option.

I am using soap Ui open source and not able to find out where to set this options as enabled.

Driver: com.cloudera.hive.jdbc4.HS2Driver

Connection String :

jdbc:hive2://<cloudera serverserver>/<database>;AuthMech=1;principal=<>;KrbRealm=<>;KrbHostFQDN=<>;KrbServiceName=hive;KrbAuthType=2

Re: SQL Query Failed with Cloudera Hive JDBC driver but works fine in Hue

EricL — Fri, 11 May 2018 11:50:32 GMT

Hi,

You just need to add UseNativeQuery into the connection string, so yours will look like below:

jdbc:hive2://<cloudera serverserver>/<database>;AuthMech=1;principal=<>;KrbRealm=<>;KrbHostFQDN=<>;KrbServiceName=hive;KrbAuthType=2;UseNativeQuery=1

Manual can be found here:
http://www.cloudera.com/documentation/other/connectors/hive-jdbc/latest/Cloudera-JDBC-Driver-for-Apache-Hive-Install-Guide.pdf

Cheers

Re: SQL Query Failed with Cloudera Hive JDBC driver but works fine in Hue

Neha1 — Fri, 11 May 2018 12:02:18 GMT

Hi,

Thanks for the reply . I have treidthe same. But unfortunately it did not work.

I am getting the same error.

😞

For reference query is :

select cr.name, from_unixtime(unix_timestamp(cr.transaction_timestamp,'yyyyMMddHHmmss'),'yyyy-MM-dd HH:mm'), s.store_name
from customer cr , d_store s ,
where cr.d_customer_key = :customerKey
and cr.d_store_key = s.d_store_key

Re: SQL Query Failed with Cloudera Hive JDBC driver but works fine in Hue

EricL — Sat, 12 May 2018 07:01:11 GMT

What exactly the error is?

Re: SQL Query Failed with Cloudera Hive JDBC driver but works fine in Hue

EricL — Sat, 12 May 2018 22:43:26 GMT

The easy way to see if UseNativeQuery is in play here is to look at either JDBC driver log or Impala daemon log to see the query submitted is different from the query you actually ran. When UseNativeQuery=0, JDBC driver will transform query and the format submitted will be very different from your original query.

I suggest you have a look to confirm. If you can share the exact error, that can help me to see what else it might be happening if UseNativeQuery is not in play.

Re: SQL Query Failed with Cloudera Hive JDBC driver but works fine in Hue

Neha1 — Thu, 31 May 2018 09:10:08 GMT

Hi,

I am sorry, I couldnt respond as I got stuck with some work. I am not able to understand the problems , with different queries.

Today I tried the query, it gives me error (see error log at the end of the message. )

select substring(date,1,4)year,substring(date,5,2)month, sum(value) value, sum(discount) discount from customer where
customer_id = :custId
and date >= date_format(add_months(add_months(date_add(last_day(add_months(current_date, -1)),1),-3),-12),'yyyyMMdd')
and date < date_format(add_months(date_add(last_day(add_months(current_date, -1)),1),-3),'yyyyMMdd')
GROUP BY substring(date,1,4),substring(date,5,2)

In this query I am using ':custId' to fetch the customer id from property (coming from test data file in soap ui). Please see attached image.

But when i run this query with hard coded value of customer id like below: , it works fine.

select substring(date,1,4)year,substring(date,5,2)month, sum(value) value, sum(discount) discount from customer where
customer_id = '1234'
and date >= date_format(add_months(add_months(date_add(last_day(add_months(current_date, -1)),1),-3),-12),'yyyyMMdd')
and date < date_format(add_months(date_add(last_day(add_months(current_date, -1)),1),-3),'yyyyMMdd')
GROUP BY substring(date,1,4),substring(date,5,2)

ERROR LOG:

2018-05-31 10:56:52 - Error getting response; java.sql.SQLException: [Simba][HiveJDBCDriver](500051) ERROR processing query/statement. Error Code: 40000, SQL state: TStatus(statusCode:ERROR_STATUS, infoMessages:[*org.apache.hive.service.cli.HiveSQLException:Error while compiling statement: FAILED: ParseException line 2:17 cannot recognize input near '?' 'and' 'transaction_date_key' in expression specification:28:27, org.apache.hive.service.cli.operation.Operation:toSQLException:Operation.java:400, org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:188, org.apache.hive.service.cli.operation.SQLOperation:runInternal:SQLOperation.java:267, org.apache.hive.service.cli.operation.Operation:run:Operation.java:337, org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementInternal:HiveSessionImpl.java:439, org.apache.hive.service.cli.session.HiveSessionImpl:executeStatement:HiveSessionImpl.java:405, sun.reflect.GeneratedMethodAccessor57:invoke::-1, sun.reflect.DelegatingMethodAccessorImpl:invoke:DelegatingMethodAccessorImpl.java:43, java.lang.reflect.Method:invoke:Method.java:498, org.apache.hive.service.cli.session.HiveSessionProxy:invoke:HiveSessionProxy.java:78, org.apache.hive.service.cli.session.HiveSessionProxy:access$000:HiveSessionProxy.java:36, org.apache.hive.service.cli.session.HiveSessionProxy$1:run:HiveSessionProxy.java:63, java.security.AccessController:doPrivileged:AccessController.java:-2, javax.security.auth.Subject:doAs:Subject.java:422, org.apache.hadoop.security.UserGroupInformation:doAs:UserGroupInformation.java:1920, org.apache.hive.service.cli.session.HiveSessionProxy:invoke:HiveSessionProxy.java:59, com.sun.proxy.$Proxy25:executeStatement::-1, org.apache.hive.service.cli.CLIService:executeStatement:CLIService.java:257, org.apache.hive.service.cli.thrift.ThriftCLIService:ExecuteStatement:ThriftCLIService.java:501, org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1313, org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1298, org.apache.thrift.ProcessFunction:process:ProcessFunction.java:39, org.apache.thrift.TBaseProcessor:process:TBaseProcessor.java:39, org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge$Server$TUGIAssumingProcessor:process:HadoopThriftAuthBridge.java:746, org.apache.thrift.server.TThreadPoolServer$WorkerProcess:run:TThreadPoolServer.java:286, java.util.concurrent.ThreadPoolExecutor:runWorker:ThreadPoolExecutor.java:1149, java.util.concurrent.ThreadPoolExecutor$Worker:run:ThreadPoolExecutor.java:624, java.lang.Thread:run:Thread.java:748, *org.apache.hadoop.hive.ql.parse.ParseException:line 2:17 cannot recognize input near '?' 'and' 'transaction_date_key' in expression specification:32:5, org.apache.hadoop.hive.ql.parse.ParseDriver:parse:ParseDriver.java:204, org.apache.hadoop.hive.ql.parse.ParseDriver:parse:ParseDriver.java:166, org.apache.hadoop.hive.ql.Driver:compile:Driver.java:466, org.apache.hadoop.hive.ql.Driver:compileInternal:Driver.java:1279, org.apache.hadoop.hive.ql.Driver:compileAndRespond:Driver.java:1266, org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:186], sqlState:42000, errorCode:40000, errorMessage:Error while compiling statement: FAILED: ParseException line 2:17 cannot recognize input near '?' 'and' 'transaction_date_key' in expression specification), Query: select substring(transaction_date_key,1,4)year,substring(transaction_date_key,5,2)month, sum(transaction_value) transaction_value, sum(total_discount) total_discount from f_customer_transaction where
d_customer_key = ?
and transaction_date_key >= date_format(add_months(add_months(date_add(last_day(add_months(current_date, -1)),1),-3),-12),'yyyyMMdd')
and transaction_date_key < date_format(add_months(date_add(last_day(add_months(current_date, -1)),1),-3),'yyyyMMdd')
GROUP BY substring(transaction_date_key,1,4),substring(transaction_date_key,5,2).

Re: SQL Query Failed with Cloudera Hive JDBC driver but works fine in Hue

prossblad — Fri, 13 Sep 2019 09:36:49 GMT

For me UseNativeQuery=0 works well with the Simba jdbc Spark driver.

So there is a misunderstood in the Simba Spark documentation. In fact it's written that 0 is the default value, but in reality the default value is 1 and not 0 !

Re: SQL Query Failed with Cloudera Hive JDBC driver but works fine in Hue

EricL — Sat, 14 Sep 2019 03:43:30 GMT

@prossblad ,

In Cloudera's Hive JDBC driver the default value for UseNativeQuery is 0, please refer to below doc:
https://www.cloudera.com/documentation/other/connectors/hive-jdbc/latest/Cloudera-JDBC-Driver-for-Apache-Hive-Install-Guide.pdf

Simba jdbc Spark driver also uses 0 as default value for UseNativeQuery, see below:
https://www.simba.com/products/Spark/doc/JDBC_InstallGuide/content/jdbc/hi/options/usenativequery.htm

Cheers
Eric

Re: SQL Query Failed with Cloudera Hive JDBC driver but works fine in Hue

Travail — Tue, 12 Jul 2022 11:49:58 GMT

Just to confirm, are you saying that when you explicitly set Simba JDBC Spark Driver UseNativeQuery=0, everything worked, but when it wasn't explicitly set (left to default) there were problems ?