Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

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

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
Highlighted

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

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