- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Float this Question for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
SQL Query Failed with Cloudera Hive JDBC driver but works fine in Hue
- Labels:
-
Apache Hive
-
Cloudera Hue
Created on ‎12-27-2017 11:32 AM - edited ‎09-16-2022 05:40 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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,
Created ‎12-31-2017 06:20 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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...
Created ‎12-31-2017 06:20 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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...
Created on ‎01-03-2018 08:49 AM - edited ‎01-03-2018 08:49 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
worked with enabling the use of Native query option. Thanks for your help.
Created ‎05-11-2018 03:01 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Created ‎05-11-2018 04:50 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Created ‎05-11-2018 05:02 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Created ‎05-12-2018 12:01 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Created ‎05-12-2018 03:43 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Created on ‎05-31-2018 02:06 AM - edited ‎05-31-2018 02:10 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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).
Created ‎09-13-2019 02:36 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 !
