unable to run hive query from soapui jdbc test step
Hi,
I am trying to run the below 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 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(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 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)
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).