hadoop-mapreduce-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "sreebalineni ." <sreebalin...@gmail.com>
Subject Re: Hive showing SemanticException [Error 10002]: Line 3:21 Invalid column reference 'mbdate
Date Thu, 29 Oct 2015 03:14:46 GMT
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" <kjayapal17@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
>

Mime
View raw message