You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hadoop.apache.org by Kumar Jayapal <kj...@gmail.com> on 2015/10/27 22:57:27 UTC
hive showing SemanticException [Error 10002]: Line 3:21 Invalid
column reference 'mbdate'
Hello,
Can some please help. When I execute hive query with as case statement I
get this error " Error while compiling statement: FAILED: SemanticException
[Error 10002]: Line 3:21 Invalid column reference 'mbdate'
Here is the query :
select a.mbcmpy, a.mbwhse, a.mbdept, a.mbitem,
(CASE WHEN to_date(a.mbdate) = d.today_ly THEN (a.mbdsun) END) as TODAY_LY
FROM items a
JOIN ivsdays d
ON a.mbdate = d.cldatei
Join ivsref r
ON r.company = a.mbcmpy
AND r.warehouse = a.mbwhse
AND r.itemnumber = a.mbitem
WHERE
a.mbcmpy = 1
AND a.mbdept = 20
group by
a.mbcmpy, a.mbwhse, a.mbdept, a.mbitem, Today_ly
ORDER by
1,2,3,4,5
Same query work in Impala. I had checked mbdate column is present in the
table.
Here is the hue log :
[27/Oct/2015 14:53:21 -0700] dbms ERROR Bad status for request
TExecuteStatementReq(confOverlay={},
sessionHandle=TSessionHandle(sessionId=THandleIdentifier(secret='L1:\x9c3KB\x94\xaf\x8c\xfa\x8d\x98\x97\xe1Q',
guid='+o\x00\xe8\xc5\x12C\xab\xbb\xb5KV\xe0\xf5\x93\xc9')), runAsync=True,
statement='select a.mbcmpy, a.mbwhse, a.mbdept, a.mbitem, \n\n(CASE WHEN
to_date(a.mbdate) = d.today_ly THEN (a.mbdsun) END) as TODAY_LY\n\nFROM
tlog.item_detail a\nJOIN Adv_analytics.ivsdays d\nON a.mbdate =
d.cldatei\nJoin adv_analytics.ivsref r\nON r.company = a.mbcmpy\nAND
r.warehouse = a.mbwhse \nAND r.itemnumber = a.mbitem\n\n\nWHERE\na.mbcmpy =
1\nAND a.mbdept = 20\n\n\ngroup by \n a.mbcmpy, a.mbwhse, a.mbdept,
a.mbitem, Today_ly\n\nORDER by\n1,2,3,4,5'):
TExecuteStatementResp(status=TStatus(errorCode=10002, errorMessage="Error
while compiling statement: FAILED: SemanticException [Error 10002]: Line
3:21 Invalid column reference 'mbdate'", sqlState='42000',
infoMessages=["*org.apache.hive.service.cli.HiveSQLException:Error while
compiling statement: FAILED: SemanticException [Error 10002]: Line 3:21
Invalid column reference 'mbdate':17:16",
'org.apache.hive.service.cli.operation.Operation:toSQLException:Operation.java:315',
'org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:102',
'org.apache.hive.service.cli.operation.SQLOperation:runInternal:SQLOperation.java:171',
'org.apache.hive.service.cli.operation.Operation:run:Operation.java:257',
'org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementInternal:HiveSessionImpl.java:398',
'org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementAsync:HiveSessionImpl.java:385',
'org.apache.hive.service.cli.CLIService:executeStatementAsync:CLIService.java:258',
'org.apache.hive.service.cli.thrift.ThriftCLIService:ExecuteStatement:ThriftCLIService.java:490',
'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.hive.service.auth.TSetIpAddressProcessor:process:TSetIpAddressProcessor.java:56',
'org.apache.thrift.server.TThreadPoolServer$WorkerProcess:run:TThreadPoolServer.java:285',
'java.util.concurrent.ThreadPoolExecutor:runWorker:ThreadPoolExecutor.java:1145',
'java.util.concurrent.ThreadPoolExecutor$Worker:run:ThreadPoolExecutor.java:615',
'java.lang.Thread:run:Thread.java:745',
"*org.apache.hadoop.hive.ql.parse.SemanticException:Line 3:21 Invalid
column reference 'mbdate':32:16",
'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genAllExprNodeDesc:SemanticAnalyzer.java:10299',
'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genExprNodeDesc:SemanticAnalyzer.java:10247',
'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genSelectPlan:SemanticAnalyzer.java:3720',
'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genSelectPlan:SemanticAnalyzer.java:3499',
'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genPostGroupByBodyPlan:SemanticAnalyzer.java:8761',
'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genBodyPlan:SemanticAnalyzer.java:8716',
'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genPlan:SemanticAnalyzer.java:9573',
'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genPlan:SemanticAnalyzer.java:9466',
'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genOPTree:SemanticAnalyzer.java:9902',
'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:analyzeInternal:SemanticAnalyzer.java:9913',
'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:analyzeInternal:SemanticAnalyzer.java:9830',
'org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer:analyze:BaseSemanticAnalyzer.java:222',
'org.apache.hadoop.hive.ql.Driver:compile:Driver.java:422',
'org.apache.hadoop.hive.ql.Driver:compile:Driver.java:306',
'org.apache.hadoop.hive.ql.Driver:compileInternal:Driver.java:1111',
'org.apache.hadoop.hive.ql.Driver:compileAndRespond:Driver.java:1105',
'org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:100'],
statusCode=3), operationHandle=None)
Traceback (most recent call last):
File
"/opt/cloudera/parcels/CDH-5.4.5-1.cdh5.4.5.p1037.741/lib/hue/apps/beeswax/src/beeswax/server/dbms.py",
line 450, in execute_and_watch
handle = self.client.query(query, query_history.statement_number)
File
"/opt/cloudera/parcels/CDH-5.4.5-1.cdh5.4.5.p1037.741/lib/hue/apps/beeswax/src/beeswax/server/hive_server2_lib.py",
line 866, in query
return self._client.execute_async_query(query, statement)
File
"/opt/cloudera/parcels/CDH-5.4.5-1.cdh5.4.5.p1037.741/lib/hue/apps/beeswax/src/beeswax/server/hive_server2_lib.py",
line 669, in execute_async_query
return self.execute_async_statement(statement=query_statement,
confOverlay=configuration)
File
"/opt/cloudera/parcels/CDH-5.4.5-1.cdh5.4.5.p1037.741/lib/hue/apps/beeswax/src/beeswax/server/hive_server2_lib.py",
line 687, in execute_async_statement
res = self.call(self._client.ExecuteStatement, req)
File
"/opt/cloudera/parcels/CDH-5.4.5-1.cdh5.4.5.p1037.741/lib/hue/apps/beeswax/src/beeswax/server/hive_server2_lib.py",
line 593, in call
raise QueryServerException(Exception('Bad status for request %s:\n%s' %
(req, res)), message=message)
QueryServerException: Bad status for request
TExecuteStatementReq(confOverlay={},
sessionHandle=TSessionHandle(sessionId=THandleIdentifier(secret='L1:\x9c3KB\x94\xaf\x8c\xfa\x8d\x98\x97\xe1Q',
guid='+o\x00\xe8\xc5\x12C\xab\xbb\xb5KV\xe0\xf5\x93\xc9')), runAsync=True,
statement='select a.mbcmpy, a.mbwhse, a.mbdept, a.mbitem, \n\n(CASE WHEN
to_date(a.mbdate) = d.today_ly THEN (a.mbdsun) END) as TODAY_LY\n\nFROM
tlog.item_detail a\nJOIN Adv_analytics.ivsdays d\nON a.mbdate =
d.cldatei\nJoin adv_analytics.ivsref r\nON r.company = a.mbcmpy\nAND
r.warehouse = a.mbwhse \nAND r.itemnumber = a.mbitem\n\n\nWHERE\na.mbcmpy =
1\nAND a.mbdept = 20\n\n\ngroup by \n a.mbcmpy, a.mbwhse, a.mbdept,
a.mbitem, Today_ly\n\nORDER by\n1,2,3,4,5'):
TExecuteStatementResp(status=TStatus(errorCode=10002, errorMessage="Error
while compiling statement: FAILED: SemanticException [Error 10002]: Line
3:21 Invalid column reference 'mbdate'", sqlState='42000',
infoMessages=["*org.apache.hive.service.cli.HiveSQLException:Error while
compiling statement: FAILED: SemanticException [Error 10002]: Line 3:21
Invalid column reference 'mbdate':17:16",
'org.apache.hive.service.cli.operation.Operation:toSQLException:Operation.java:315',
'org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:102',
'org.apache.hive.service.cli.operation.SQLOperation:runInternal:SQLOperation.java:171',
'org.apache.hive.service.cli.operation.Operation:run:Operation.java:257',
'org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementInternal:HiveSessionImpl.java:398',
'org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementAsync:HiveSessionImpl.java:385',
'org.apache.hive.service.cli.CLIService:executeStatementAsync:CLIService.java:258',
'org.apache.hive.service.cli.thrift.ThriftCLIService:ExecuteStatement:ThriftCLIService.java:490',
'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.hive.service.auth.TSetIpAddressProcessor:process:TSetIpAddressProcessor.java:56',
'org.apache.thrift.server.TThreadPoolServer$WorkerProcess:run:TThreadPoolServer.java:285',
'java.util.concurrent.ThreadPoolExecutor:runWorker:ThreadPoolExecutor.java:1145',
'java.util.concurrent.ThreadPoolExecutor$Worker:run:ThreadPoolExecutor.java:615',
'java.lang.Thread:run:Thread.java:745',
"*org.apache.hadoop.hive.ql.parse.SemanticException:Line 3:21 Invalid
column reference 'mbdate':32:16",
'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genAllExprNodeDesc:SemanticAnalyzer.java:10299',
'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genExprNodeDesc:SemanticAnalyzer.java:10247',
'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genSelectPlan:SemanticAnalyzer.java:3720',
'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genSelectPlan:SemanticAnalyzer.java:3499',
'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genPostGroupByBodyPlan:SemanticAnalyzer.java:8761',
'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genBodyPlan:SemanticAnalyzer.java:8716',
'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genPlan:SemanticAnalyzer.java:9573',
'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genPlan:SemanticAnalyzer.java:9466',
'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genOPTree:SemanticAnalyzer.java:9902',
'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:analyzeInternal:SemanticAnalyzer.java:9913',
'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:analyzeInternal:SemanticAnalyzer.java:9830',
'org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer:analyze:BaseSemanticAnalyzer.java:222',
'org.apache.hadoop.hive.ql.Driver:compile:Driver.java:422',
'org.apache.hadoop.hive.ql.Driver:compile:Driver.java:306',
'org.apache.hadoop.hive.ql.Driver:compileInternal:Driver.java:1111',
'org.apache.hadoop.hive.ql.Driver:compileAndRespond:Driver.java:1105',
'org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:100'],
statusCode=3), operationHandle=None)
Thanks
Jay