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

[#1]

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, 

[#2 · Accepted solution]

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-Apa...

[#3]

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

[#4]

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

[#5]

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-Apa...

Cheers

[#6]

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

[#7]

What exactly the error is?

[#8]

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.

[#9]

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).

[#10]

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 !