Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Query fails complaining aliased column not found in HIVE. Works fine in SPARK SQL

avatar
New Contributor

My query:

SELECT
AJ.ASSETID,
AJ.AJTOTIME,
AJ.AJTOLOCID,
LOCTO.LOCNAME AS TOLOCATION,
AJ.AJFROMLOCID,
LOCFROM.LOCNAME AS FROMLOCATION,
AJ.ASMSTATEID
FROM
NPD_AJ AJ
LEFT OUTER JOIN NPD_LOCATION LOCTO ON (AJ.AJTOLOCID = LOCTO.LOCID)
LEFT OUTER JOIN NPD_LOCATION LOCFROM ON (AJ.AJFROMLOCID = LOCFROM.LOCID)
WHERE
AJ.AJFROMLOCID <> AJ.AJTOLOCID
AND LOCTO.LOCTYPE = 'SVC'
AND AJ.AJSTATUS <> 'DISCARD'
ORDER BY
AJ.ASSETID,
AJ.AJTOTIME

 

Error I get:

java.sql.SQLException: [Amazon][HiveJDBCDriver](500051) ERROR processing query/statement. Error Code: 10002, SQL state: TStatus(statusCode:ERROR_STATUS, infoMessages:[*org.apache.hive.service.cli.HiveSQLException:Error while compiling statement: FAILED: SemanticException [Error 10002]: Line 1:210 Invalid column reference 'TOLOCATION':28:27, org.apache.hive.service.cli.operation.Operation:toSQLException:Operation.java:380, org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:206, org.apache.hive.service.cli.operation.SQLOperation:runInternal:SQLOperation.java:290, org.apache.hive.service.cli.operation.Operation:run:Operation.java:320, org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementInternal:HiveSessionImpl.java:530, org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementAsync:HiveSessionImpl.java:517, sun.reflect.GeneratedMethodAccessor7: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:1698, org.apache.hive.service.cli.session.HiveSessionProxy:invoke:HiveSessionProxy.java:59, com.sun.proxy.$Proxy35:executeStatementAsync::-1, org.apache.hive.service.cli.CLIService:executeStatementAsync:CLIService.java:310, org.apache.hive.service.cli.thrift.ThriftCLIService:ExecuteStatement:ThriftCLIService.java:530, org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1437, org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1422, org.apache.thrift.ProcessFunction:process:ProcessFunction.java:39, org.apache.thrift.TBaseProcessor:process:TBaseProcessor.java:39, org.apache.hive.service.auth.TSetIpAddressProcessor:process:TSetIpAddressProcessor.java:56, 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.SemanticException:Line 1:210 Invalid column reference 'TOLOCATION':43:16, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genAllExprNodeDesc:SemanticAnalyzer.java:11620, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genExprNodeDesc:SemanticAnalyzer.java:11568, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genSelectPlan:SemanticAnalyzer.java:4394, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genSelectPlan:SemanticAnalyzer.java:4167, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genPostGroupByBodyPlan:SemanticAnalyzer.java:9705, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genBodyPlan:SemanticAnalyzer.java:9644, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genPlan:SemanticAnalyzer.java:10549, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genPlan:SemanticAnalyzer.java:10427, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genOPTree:SemanticAnalyzer.java:11125, org.apache.hadoop.hive.ql.parse.CalcitePlanner:genOPTree:CalcitePlanner.java:481, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:analyzeInternal:SemanticAnalyzer.java:11138, org.apache.hadoop.hive.ql.parse.CalcitePlanner:analyzeInternal:CalcitePlanner.java:286, org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer:analyze:BaseSemanticAnalyzer.java:258, org.apache.hadoop.hive.ql.Driver:compile:Driver.java:512, org.apache.hadoop.hive.ql.Driver:compileInternal:Driver.java:1317, org.apache.hadoop.hive.ql.Driver:compileAndRespond:Driver.java:1295, org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:204], sqlState:42000, errorCode:10002, errorMessage:Error while compiling statement: FAILED: SemanticException [Error 10002]: Line 1:210 Invalid column reference 'TOLOCATION'), Query: SELECT `ORDERBYLIST_5a6ac026`.`_SYNTHJOIN_dedbbdd1___AJassetid_`, `ORDERBYLIST_5a6ac026`.`_SYNTHJOIN_dedbbdd1___AJajtotime_`, `ORDERBYLIST_5a6ac026`.`_SYNTHJOIN_dedbbdd1___AJajtolocid_`, `ORDERBYLIST_5a6ac026`.`TOLOCATION`, `ORDERBYLIST_5a6ac026`.`_SYNTHJOIN_dedbbdd1___AJajfromlocid_`, `ORDERBYLIST_5a6ac026`.`FROMLOCATION`, `ORDERBYLIST_5a6ac026`.`_SYNTHJOIN_dedbbdd1___AJasmstateid_` FROM ( SELECT `SYNTHJOIN_dedbbdd1`.`AJassetid` `_SYNTHJOIN_dedbbdd1___AJassetid_` , `SYNTHJOIN_dedbbdd1`.`AJajtotime` `_SYNTHJOIN_dedbbdd1___AJajtotime_` , `SYNTHJOIN_dedbbdd1`.`AJajtolocid` `_SYNTHJOIN_dedbbdd1___AJajtolocid_` , `SYNTHJOIN_dedbbdd1`.`LOCTOlocname` `_SYNTHJOIN_dedbbdd1___LOCTOlocname_` , `SYNTHJOIN_dedbbdd1`.`AJajfromlocid` `_SYNTHJOIN_dedbbdd1___AJajfromlocid_` , `LOCFROM`.`locname` `FROMLOCATION`, `SYNTHJOIN_dedbbdd1`.`AJasmstateid` `_SYNTHJOIN_dedbbdd1___AJasmstateid_` FROM ( SELECT `LOCTO`.`locid` AS `LOCTOlocid` , `LOCTO`.`locname` AS `LOCTOlocname` , `LOCTO`.`loctype` AS `LOCTOloctype` , `AJ`.`assetid` AS `AJassetid` , `AJ`.`asmstateid` AS `AJasmstateid` , `AJ`.`ajfromlocid` AS `AJajfromlocid` , `AJ`.`ajfromloclat` AS `AJajfromloclat` , `AJ`.`ajfromloclong` AS `AJajfromloclong` , `AJ`.`ajscore` AS `AJajscore` , `AJ`.`ajsourcetype` AS `AJajsourcetype` , `AJ`.`ajstatus` AS `AJajstatus` , `AJ`.`ajtolocid` AS `AJajtolocid` , `AJ`.`ajtoloclat` AS `AJajtoloclat` , `AJ`.`ajtoloclong` AS `AJajtoloclong` , `AJ`.`ajtotime` AS `AJajtotime` , `AJ`.`fromtime` AS `AJfromtime` FROM `default`.`npd_aj` `AJ` LEFT JOIN `default`.`npd_location` `LOCTO` ON ( `AJ`.`ajtolocid`=`LOCTO`.`locid` )) `SYNTHJOIN_dedbbdd1` LEFT JOIN `default`.`npd_location` `LOCFROM` ON ( `SYNTHJOIN_dedbbdd1`.`AJajfromlocid`=`LOCFROM`.`locid` ) WHERE ( `SYNTHJOIN_dedbbdd1`.`AJajstatus`!='DISCARD' AND ( `SYNTHJOIN_dedbbdd1`.`AJajfromlocid`!=`SYNTHJOIN_dedbbdd1`.`AJajtolocid` AND `SYNTHJOIN_dedbbdd1`.`LOCTOloctype`='SVC' ) ) ORDER BY `_SYNTHJOIN_dedbbdd1___AJassetid_` ASC, `_SYNTHJOIN_dedbbdd1___AJajtotime_` ASC ) `ORDERBYLIST_5a6ac026`. at com.amazon.hive.hivecommon.api.HS2Client.executeStatementInternal(Unknown Source) at com.amazon.hive.hivecommon.api.HS2Client.executeStatement(Unknown Source) at com.amazon.hive.hivecommon.dataengine.HiveJDBCNativeQueryExecutor.executeQuery(Unknown Source) at com.amazon.hive.hivecommon.dataengine.HiveJDBCDSIExtQueryExecutor.execute(Unknown Source) at com.amazon.hive.jdbc.common.SStatement.executeNoParams(Unknown Source) at com.amazon.hive.jdbc.common.SStatement.execute(Unknown Source) at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291) at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291) at org.apache.zeppelin.jdbc.JDBCInterpreter.executeSql(JDBCInterpreter.java:718) at org.apache.zeppelin.jdbc.JDBCInterpreter.interpret(JDBCInterpreter.java:802) at org.apache.zeppelin.interpreter.LazyOpenInterpreter.interpret(LazyOpenInterpreter.java:101) at org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$InterpretJob.jobRun(RemoteInterpreterServer.java:602) at org.apache.zeppelin.scheduler.Job.run(Job.java:185) at org.apache.zeppelin.scheduler.ParallelScheduler$JobRunner.run(ParallelScheduler.java:162) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) Caused by: com.amazon.hive.support.exceptions.GeneralException: [Amazon][HiveJDBCDriver](500051) ERROR processing query/statement. Error Code: 10002, SQL state: TStatus(statusCode:ERROR_STATUS, infoMessages:[*org.apache.hive.service.cli.HiveSQLException:Error while compiling statement: FAILED: SemanticException [Error 10002]: Line 1:210 Invalid column reference 'TOLOCATION':28:27, org.apache.hive.service.cli.operation.Operation:toSQLException:Operation.java:380, org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:206, org.apache.hive.service.cli.operation.SQLOperation:runInternal:SQLOperation.java:290, org.apache.hive.service.cli.operation.Operation:run:Operation.java:320, org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementInternal:HiveSessionImpl.java:530, org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementAsync:HiveSessionImpl.java:517, sun.reflect.GeneratedMethodAccessor7: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:1698, org.apache.hive.service.cli.session.HiveSessionProxy:invoke:HiveSessionProxy.java:59, com.sun.proxy.$Proxy35:executeStatementAsync::-1, org.apache.hive.service.cli.CLIService:executeStatementAsync:CLIService.java:310, org.apache.hive.service.cli.thrift.ThriftCLIService:ExecuteStatement:ThriftCLIService.java:530, org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1437, org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1422, org.apache.thrift.ProcessFunction:process:ProcessFunction.java:39, org.apache.thrift.TBaseProcessor:process:TBaseProcessor.java:39, org.apache.hive.service.auth.TSetIpAddressProcessor:process:TSetIpAddressProcessor.java:56, 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.SemanticException:Line 1:210 Invalid column reference 'TOLOCATION':43:16, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genAllExprNodeDesc:SemanticAnalyzer.java:11620, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genExprNodeDesc:SemanticAnalyzer.java:11568, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genSelectPlan:SemanticAnalyzer.java:4394, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genSelectPlan:SemanticAnalyzer.java:4167, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genPostGroupByBodyPlan:SemanticAnalyzer.java:9705, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genBodyPlan:SemanticAnalyzer.java:9644, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genPlan:SemanticAnalyzer.java:10549, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genPlan:SemanticAnalyzer.java:10427, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genOPTree:SemanticAnalyzer.java:11125, org.apache.hadoop.hive.ql.parse.CalcitePlanner:genOPTree:CalcitePlanner.java:481, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:analyzeInternal:SemanticAnalyzer.java:11138, org.apache.hadoop.hive.ql.parse.CalcitePlanner:analyzeInternal:CalcitePlanner.java:286, org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer:analyze:BaseSemanticAnalyzer.java:258, org.apache.hadoop.hive.ql.Driver:compile:Driver.java:512, org.apache.hadoop.hive.ql.Driver:compileInternal:Driver.java:1317, org.apache.hadoop.hive.ql.Driver:compileAndRespond:Driver.java:1295, org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:204], sqlState:42000, errorCode:10002, errorMessage:Error while compiling statement: FAILED: SemanticException [Error 10002]: Line 1:210 Invalid column reference 'TOLOCATION'), Query: SELECT `ORDERBYLIST_5a6ac026`.`_SYNTHJOIN_dedbbdd1___AJassetid_`, `ORDERBYLIST_5a6ac026`.`_SYNTHJOIN_dedbbdd1___AJajtotime_`, `ORDERBYLIST_5a6ac026`.`_SYNTHJOIN_dedbbdd1___AJajtolocid_`, `ORDERBYLIST_5a6ac026`.`TOLOCATION`, `ORDERBYLIST_5a6ac026`.`_SYNTHJOIN_dedbbdd1___AJajfromlocid_`, `ORDERBYLIST_5a6ac026`.`FROMLOCATION`, `ORDERBYLIST_5a6ac026`.`_SYNTHJOIN_dedbbdd1___AJasmstateid_` FROM ( SELECT `SYNTHJOIN_dedbbdd1`.`AJassetid` `_SYNTHJOIN_dedbbdd1___AJassetid_` , `SYNTHJOIN_dedbbdd1`.`AJajtotime` `_SYNTHJOIN_dedbbdd1___AJajtotime_` , `SYNTHJOIN_dedbbdd1`.`AJajtolocid` `_SYNTHJOIN_dedbbdd1___AJajtolocid_` , `SYNTHJOIN_dedbbdd1`.`LOCTOlocname` `_SYNTHJOIN_dedbbdd1___LOCTOlocname_` , `SYNTHJOIN_dedbbdd1`.`AJajfromlocid` `_SYNTHJOIN_dedbbdd1___AJajfromlocid_` , `LOCFROM`.`locname` `FROMLOCATION`, `SYNTHJOIN_dedbbdd1`.`AJasmstateid` `_SYNTHJOIN_dedbbdd1___AJasmstateid_` FROM ( SELECT `LOCTO`.`locid` AS `LOCTOlocid` , `LOCTO`.`locname` AS `LOCTOlocname` , `LOCTO`.`loctype` AS `LOCTOloctype` , `AJ`.`assetid` AS `AJassetid` , `AJ`.`asmstateid` AS `AJasmstateid` , `AJ`.`ajfromlocid` AS `AJajfromlocid` , `AJ`.`ajfromloclat` AS `AJajfromloclat` , `AJ`.`ajfromloclong` AS `AJajfromloclong` , `AJ`.`ajscore` AS `AJajscore` , `AJ`.`ajsourcetype` AS `AJajsourcetype` , `AJ`.`ajstatus` AS `AJajstatus` , `AJ`.`ajtolocid` AS `AJajtolocid` , `AJ`.`ajtoloclat` AS `AJajtoloclat` , `AJ`.`ajtoloclong` AS `AJajtoloclong` , `AJ`.`ajtotime` AS `AJajtotime` , `AJ`.`fromtime` AS `AJfromtime` FROM `default`.`npd_aj` `AJ` LEFT JOIN `default`.`npd_location` `LOCTO` ON ( `AJ`.`ajtolocid`=`LOCTO`.`locid` )) `SYNTHJOIN_dedbbdd1` LEFT JOIN `default`.`npd_location` `LOCFROM` ON ( `SYNTHJOIN_dedbbdd1`.`AJajfromlocid`=`LOCFROM`.`locid` ) WHERE ( `SYNTHJOIN_dedbbdd1`.`AJajstatus`!='DISCARD' AND ( `SYNTHJOIN_dedbbdd1`.`AJajfromlocid`!=`SYNTHJOIN_dedbbdd1`.`AJajtolocid` AND `SYNTHJOIN_dedbbdd1`.`LOCTOloctype`='SVC' ) ) ORDER BY `_SYNTHJOIN_dedbbdd1___AJassetid_` ASC, `_SYNTHJOIN_dedbbdd1___AJajtotime_` ASC ) `ORDERBYLIST_5a6ac026`. ... 20 more

1 REPLY 1

avatar
Super Guru
It looks like that you are using Hive JDBC driver, and also seems like the driver has transformed the query which become invalid.

Two options here:

1. check which version of JDBC driver you using, try use the latest one to see if it helps

2. disable the query transformation in JDBC using UseNativeQuery and set it to 1, please refer to user manual below:

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