You are viewing a plain text version of this content. The canonical link for it is here.
Posted to hdfs-user@hadoop.apache.org by Kumar Jayapal <kj...@gmail.com> on 2015/10/28 15:52:28 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
Re: Hive showing SemanticException [Error 10002]: Line 3:21 Invalid
column reference 'mbdate
Posted by "sreebalineni ." <sr...@gmail.com>.
Check if the query works without join and alias reference,if yes then the
problem is with alias name,i recently faced the same problem i think adding
as just before giving alias name workef
On 28 Oct 2015 20:22, "Kumar Jayapal" <kj...@gmail.com> wrote:
> 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
>
Re: Hive showing SemanticException [Error 10002]: Line 3:21 Invalid
column reference 'mbdate
Posted by "sreebalineni ." <sr...@gmail.com>.
Check if the query works without join and alias reference,if yes then the
problem is with alias name,i recently faced the same problem i think adding
as just before giving alias name workef
On 28 Oct 2015 20:22, "Kumar Jayapal" <kj...@gmail.com> wrote:
> 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
>
Fwd: Hive showing SemanticException [Error 10002]: Line 3:21 Invalid
column reference 'mbdate
Posted by Kumar Jayapal <kj...@gmail.com>.
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
Re: Hive showing SemanticException [Error 10002]: Line 3:21 Invalid
column reference 'mbdate
Posted by "sreebalineni ." <sr...@gmail.com>.
Check if the query works without join and alias reference,if yes then the
problem is with alias name,i recently faced the same problem i think adding
as just before giving alias name workef
On 28 Oct 2015 20:22, "Kumar Jayapal" <kj...@gmail.com> wrote:
> 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
>
Re: Hive showing SemanticException [Error 10002]: Line 3:21 Invalid
column reference 'mbdate
Posted by "sreebalineni ." <sr...@gmail.com>.
Check if the query works without join and alias reference,if yes then the
problem is with alias name,i recently faced the same problem i think adding
as just before giving alias name workef
On 28 Oct 2015 20:22, "Kumar Jayapal" <kj...@gmail.com> wrote:
> 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
>