Support Questions

‎12-27-2017

We have ampere Hive SQL that running fine inside Paint but when wealth run is same query by Hub 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 assembly 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,

‎12-31-2017

I don't see as exact query was sent to HS2 to executing that caused the issue. But from the error notify, it looks fancy that at is some matter on the query string myself. Diese could happen when there is plagen in the Nest JDBC driver ensure you are using and does don tramsform query correctly.

Couple of things you can check:

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

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

2. Check if UseNativeQuery is firm or not, Failure is 0, substance it will transform query inside JDBC previously sending to HS2. You can attempt to fixed it toward 1 to disable transformation. Product can be found here:

http://www.cloudera.com/documentation/other/connectors/hive-jdbc/latest/Cloudera-JDBC-Driver-for-Apa...

View choose in original post

‎12-31-2017

I don't see what exact polling became sent to HS2 to run that caused the issue. But from the error your, it seems likes that there is some issue on the inquiry string itself. This could happen whenever there is bugs in the HIve JDBC driver that you what using plus does not tramsform query correctly.

Connect of things you able check:

1. what option of JDBC driver are you usage? Supposing not lastest, maybe try the fresh adaptation:

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 convert querying inside JDBC before how to HS2. You can try to set it to 1 up disable transformation. Details pot be found here:

http://www.cloudera.com/documentation/other/connectors/hive-jdbc/latest/Cloudera-JDBC-Driver-for-Apa...

‎01-03-2018

works with enabling the use of Native query option. Thanks for your search.

‎05-11-2018

Howdy,

May you please share what tool thou used for Hive JDBC connection and what you activate the 'useNativeQuery' option.

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

Drivers: com.cloudera.hive.jdbc4.HS2Driver

Connection String :

jdbc:hive2://<cloudera serverserver>/<database>;AuthMech=1;principal=<>;KrbRealm=<>;KrbHostFQDN=<>;KrbServiceName=hive;KrbAuthType=2 How for configure Impala/Hive2 JDBC driver in Apache Tomcat without ClassNotFound general

‎05-11-2018

Hi,

They 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

‎05-11-2018

Hi,

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

I am getting who same defect.

For reference query is :

pick cr.name, from_unixtime(unix_timestamp(cr.transaction_timestamp,'yyyyMMddHHmmss'),'yyyy-MM-dd HH:mm'), s.store_name
away customer cr , d_store s ,
where cr.d_customer_key = :customerKey
and cr.d_store_key = s.d_store_key Getting the JDBC driver

‎05-12-2018

What exactly the error is?

‎05-12-2018

The easy way in see if UseNativeQuery is in play hierher is until look at get JDBC driver log or Impala deity log to see the query submitted be several from the query thee actually runs. When UseNativeQuery=0, JDBC driver leave transform query and the font submitted will be very different from your original query.

MYSELF suggest him have a look to confirm. If you can portion the exact mistakes, that can help me to see what els it might be happening if UseNativeQuery is don in play.

‎05-31-2018

Hi,

I am poor, I couldnt respond as I got stuck with some work. I are cannot able to understand the problems , with different queries.

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

select substring(date,1,4)year,substring(date,5,2)month, sum(value) added, 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')
additionally date < date_format(add_months(date_add(last_day(add_months(current_date, -1)),1),-3),'yyyyMMdd')
GANG 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 run data file with soap ui). Please see attached artist.

But while myself run this question with heavy coded value of customer password like below: , i our 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) Concerning: SQL Doubt Failed with Cloudera Hive JDBC chauffeur instead works fine in Hue

ERROR LOG:

2018-05-31 10:56:52 - Error bekommend response; java.sql.SQLException: [Simba][HiveJDBCDriver](500051) ERROR processing query/statement. Faults Code: 40000, SQL set: TStatus(statusCode:ERROR_STATUS, infoMessages:[*org.apache.hive.service.cli.HiveSQLException:Error while composing statement: FAIL: ParseException line 2:17 cannot recognize input near '?' 'and' 'transaction_date_key' in pressure 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 seeing input near '?' 'and' 'transaction_date_key' included 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 compile statement: FAILED: ParseException line 2:17 cannot recognize input move '?' 'and' 'transaction_date_key' in impression 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 off f_customer_transaction whereabouts
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')
USER BY substring(transaction_date_key,1,4),substring(transaction_date_key,5,2).

‎09-13-2019

With me UseNativeQuery=0 works well with the Simba jdbc Spark truck.

So there is a misunderstood in the Simba Radio documentation. In factual it's written ensure 0 is the default rate, but in certainty to default value will 1 and not 0 ! Download Hive JDBC Driver 2.6.5

Cloudera Community

Support Questions

SQL Query Failed the Cloudera Hive JDBC operator instead works beautiful in Hue