You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by yogesh dhari <yo...@live.com> on 2012/09/27 21:29:12 UTC
ERROR: Hive subquery showing
Hi all,
I have a table called ABC, like
name grp
A 1
B 2
C 4
D 8
I want the output like the name having greatest grp i.e D;
I wrote a query:
select name from ( select MAX(grp) from ABC ) gy ;
but it gives error
FAILED: Error in semantic analysis: Line 1:7 Invalid table alias or column reference 'name': (possible column names are: _col0)
Please help and suggest why it is so, and what would be the query;
Thanks & regards
Yogesh Kumar
RE: ERROR: Hive subquery showing
Posted by yogesh dhari <yo...@live.com>.
thanks Chen,
I want output like ( the name and grp having highest grp)
D 8
for the table.
name grp
A 1
B 2
C 4
D 8
Query :
select name from ( select MAX(grp) as name from ABC ) gy ;
showing output: 8
which can be obtained by simple : select MAX(grp) from ABC ( I think here outer query is not performing)
Please Suggest
Regards
Yogesh Kumar
Date: Thu, 27 Sep 2012 15:33:11 -0400
Subject: Re: ERROR: Hive subquery showing
From: chen.song.82@gmail.com
To: user@hive.apache.org
Can you try this?
select name from ( select MAX(grp) as name from ABC ) gy ;
On Thu, Sep 27, 2012 at 3:29 PM, yogesh dhari <yo...@live.com> wrote:
Hi all,
I have a table called ABC, like
name 牋 grp
A牋牋牋牋牋牋 1
B牋牋牋牋牋牋 2
C牋牋牋牋牋牋 4
D牋牋牋牋牋牋 8
I want the output like the name having greatest grp i.e D;
I wrote a query:
select name from ( select MAX(grp) from ABC ) gy ;
but it gives error
FAILED: Error in semantic analysis: Line 1:7 Invalid table alias or column reference 'name': (possible column names are: _col0)
Please help and suggest why it is so, and what would be the query;
Thanks & regards
Yogesh Kumar
--
Chen Song
Re: ERROR: Hive subquery showing
Posted by Chen Song <ch...@gmail.com>.
Sorry that I misunderstood the question. I think Phil's query will do the
trick.
On Thu, Sep 27, 2012 at 4:46 PM, Philip Tromans
<ph...@gmail.com>wrote:
> How about:
> select name from ABC order by grp desc limit 1?
>
> Phil.
> On Sep 27, 2012 9:02 PM, "yogesh dhari" <yo...@live.com> wrote:
>
>> Hi Bejoy,
>>
>> I tried this one also but here it throws horrible error:
>>
>> i.e:
>>
>> hive: select name from ABD where grp=MAX(grp);
>>
>> FAILED: Hive Internal Error: java.lang.NullPointerException(null)
>> java.lang.NullPointerException
>> at
>> org.apache.hadoop.hive.ql.plan.ExprNodeGenericFuncDesc.newInstance(ExprNodeGenericFuncDesc.java:214)
>> at
>> org.apache.hadoop.hive.ql.parse.TypeCheckProcFactory$DefaultExprProcessor.getXpathOrFuncExprNodeDesc(TypeCheckProcFactory.java:684)
>> at
>> org.apache.hadoop.hive.ql.parse.TypeCheckProcFactory$DefaultExprProcessor.process(TypeCheckProcFactory.java:805)
>> at
>> org.apache.hadoop.hive.ql.lib.DefaultRuleDispatcher.dispatch(DefaultRuleDispatcher.java:89)
>> at
>> org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.dispatch(DefaultGraphWalker.java:88)
>> at
>> org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.walk(DefaultGraphWalker.java:125)
>> at
>> org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.startWalking(DefaultGraphWalker.java:102)
>> at
>> org.apache.hadoop.hive.ql.parse.TypeCheckProcFactory.genExprNode(TypeCheckProcFactory.java:161)
>> at
>> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genExprNodeDesc(SemanticAnalyzer.java:7506)
>> at
>> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genExprNodeDesc(SemanticAnalyzer.java:7464)
>> at
>> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genFilterPlan(SemanticAnalyzer.java:1513)
>> at
>> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genFilterPlan(SemanticAnalyzer.java:1494)
>> at
>> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genBodyPlan(SemanticAnalyzer.java:5886)
>> at
>> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:6524)
>> at
>> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:7282)
>> at
>> org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:243)
>> at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:430)
>> at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:337)
>> at org.apache.hadoop.hive.ql.Driver.run(Driver.java:889)
>> at
>> org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:255)
>> at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:212)
>> at
>> org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:403)
>> at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:671)
>> at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:554)
>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>> at
>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
>> at
>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>> at java.lang.reflect.Method.invoke(Method.java:601)
>> at org.apache.hadoop.util.RunJar.main(RunJar.java:156)
>>
>> Regards
>> Yogesh Kumar
>>
>> ------------------------------
>> Subject: Re: ERROR: Hive subquery showing
>> To: user@hive.apache.org
>> From: bejoy_ks@yahoo.com
>> Date: Thu, 27 Sep 2012 19:48:25 +0000
>>
>> Hi yogesh
>>
>> What about a query like this
>> select name from ABC WHERE grp=MAX(grp);
>>
>> Regards
>> Bejoy KS
>>
>> Sent from handheld, please excuse typos.
>> ------------------------------
>> *From: * Chen Song <ch...@gmail.com>
>> *Date: *Thu, 27 Sep 2012 15:33:11 -0400
>> *To: *<us...@hive.apache.org>
>> *ReplyTo: * user@hive.apache.org
>> *Subject: *Re: ERROR: Hive subquery showing
>>
>> Can you try this?
>> *
>> *
>> *select name from ( select MAX(grp) as name from ABC ) gy ;*
>>
>> On Thu, Sep 27, 2012 at 3:29 PM, yogesh dhari <yo...@live.com>wrote:
>>
>> Hi all,
>>
>> I have a table called ABC, like
>>
>> name grp
>> A 1
>> B 2
>> C 4
>> D 8
>>
>> I want the output like the name having greatest grp i.e D;
>>
>> I wrote a query:
>> *
>> select name from ( select MAX(grp) from ABC ) gy ;*
>>
>> but it gives error
>>
>> FAILED: Error in semantic analysis: Line 1:7 Invalid table alias or
>> column reference 'name': (possible column names are: _col0)
>>
>> Please help and suggest why it is so, and what would be the query;
>>
>>
>> Thanks & regards
>> Yogesh Kumar
>>
>>
>>
>>
>>
>>
>>
>>
>> --
>> Chen Song
>>
>>
>>
--
Chen Song
RE: ERROR: Hive subquery showing
Posted by Philip Tromans <ph...@gmail.com>.
How about:
select name from ABC order by grp desc limit 1?
Phil.
On Sep 27, 2012 9:02 PM, "yogesh dhari" <yo...@live.com> wrote:
> Hi Bejoy,
>
> I tried this one also but here it throws horrible error:
>
> i.e:
>
> hive: select name from ABD where grp=MAX(grp);
>
> FAILED: Hive Internal Error: java.lang.NullPointerException(null)
> java.lang.NullPointerException
> at
> org.apache.hadoop.hive.ql.plan.ExprNodeGenericFuncDesc.newInstance(ExprNodeGenericFuncDesc.java:214)
> at
> org.apache.hadoop.hive.ql.parse.TypeCheckProcFactory$DefaultExprProcessor.getXpathOrFuncExprNodeDesc(TypeCheckProcFactory.java:684)
> at
> org.apache.hadoop.hive.ql.parse.TypeCheckProcFactory$DefaultExprProcessor.process(TypeCheckProcFactory.java:805)
> at
> org.apache.hadoop.hive.ql.lib.DefaultRuleDispatcher.dispatch(DefaultRuleDispatcher.java:89)
> at
> org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.dispatch(DefaultGraphWalker.java:88)
> at
> org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.walk(DefaultGraphWalker.java:125)
> at
> org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.startWalking(DefaultGraphWalker.java:102)
> at
> org.apache.hadoop.hive.ql.parse.TypeCheckProcFactory.genExprNode(TypeCheckProcFactory.java:161)
> at
> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genExprNodeDesc(SemanticAnalyzer.java:7506)
> at
> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genExprNodeDesc(SemanticAnalyzer.java:7464)
> at
> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genFilterPlan(SemanticAnalyzer.java:1513)
> at
> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genFilterPlan(SemanticAnalyzer.java:1494)
> at
> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genBodyPlan(SemanticAnalyzer.java:5886)
> at
> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:6524)
> at
> org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:7282)
> at
> org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:243)
> at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:430)
> at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:337)
> at org.apache.hadoop.hive.ql.Driver.run(Driver.java:889)
> at
> org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:255)
> at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:212)
> at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:403)
> at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:671)
> at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:554)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:601)
> at org.apache.hadoop.util.RunJar.main(RunJar.java:156)
>
> Regards
> Yogesh Kumar
>
> ------------------------------
> Subject: Re: ERROR: Hive subquery showing
> To: user@hive.apache.org
> From: bejoy_ks@yahoo.com
> Date: Thu, 27 Sep 2012 19:48:25 +0000
>
> Hi yogesh
>
> What about a query like this
> select name from ABC WHERE grp=MAX(grp);
>
> Regards
> Bejoy KS
>
> Sent from handheld, please excuse typos.
> ------------------------------
> *From: * Chen Song <ch...@gmail.com>
> *Date: *Thu, 27 Sep 2012 15:33:11 -0400
> *To: *<us...@hive.apache.org>
> *ReplyTo: * user@hive.apache.org
> *Subject: *Re: ERROR: Hive subquery showing
>
> Can you try this?
> *
> *
> *select name from ( select MAX(grp) as name from ABC ) gy ;*
>
> On Thu, Sep 27, 2012 at 3:29 PM, yogesh dhari <yo...@live.com>wrote:
>
> Hi all,
>
> I have a table called ABC, like
>
> name grp
> A 1
> B 2
> C 4
> D 8
>
> I want the output like the name having greatest grp i.e D;
>
> I wrote a query:
> *
> select name from ( select MAX(grp) from ABC ) gy ;*
>
> but it gives error
>
> FAILED: Error in semantic analysis: Line 1:7 Invalid table alias or column
> reference 'name': (possible column names are: _col0)
>
> Please help and suggest why it is so, and what would be the query;
>
>
> Thanks & regards
> Yogesh Kumar
>
>
>
>
>
>
>
>
> --
> Chen Song
>
>
>
RE: ERROR: Hive subquery showing
Posted by yogesh dhari <yo...@live.com>.
Hi Bejoy,
I tried this one also but here it throws horrible error:
i.e:
hive: select name from ABD where grp=MAX(grp);
FAILED: Hive Internal Error: java.lang.NullPointerException(null)
java.lang.NullPointerException
at org.apache.hadoop.hive.ql.plan.ExprNodeGenericFuncDesc.newInstance(ExprNodeGenericFuncDesc.java:214)
at org.apache.hadoop.hive.ql.parse.TypeCheckProcFactory$DefaultExprProcessor.getXpathOrFuncExprNodeDesc(TypeCheckProcFactory.java:684)
at org.apache.hadoop.hive.ql.parse.TypeCheckProcFactory$DefaultExprProcessor.process(TypeCheckProcFactory.java:805)
at org.apache.hadoop.hive.ql.lib.DefaultRuleDispatcher.dispatch(DefaultRuleDispatcher.java:89)
at org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.dispatch(DefaultGraphWalker.java:88)
at org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.walk(DefaultGraphWalker.java:125)
at org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.startWalking(DefaultGraphWalker.java:102)
at org.apache.hadoop.hive.ql.parse.TypeCheckProcFactory.genExprNode(TypeCheckProcFactory.java:161)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genExprNodeDesc(SemanticAnalyzer.java:7506)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genExprNodeDesc(SemanticAnalyzer.java:7464)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genFilterPlan(SemanticAnalyzer.java:1513)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genFilterPlan(SemanticAnalyzer.java:1494)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genBodyPlan(SemanticAnalyzer.java:5886)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:6524)
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:7282)
at org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:243)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:430)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:337)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:889)
at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:255)
at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:212)
at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:403)
at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:671)
at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:554)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at org.apache.hadoop.util.RunJar.main(RunJar.java:156)
Regards
Yogesh Kumar
Subject: Re: ERROR: Hive subquery showing
To: user@hive.apache.org
From: bejoy_ks@yahoo.com
Date: Thu, 27 Sep 2012 19:48:25 +0000
Hi yogesh
What about a query like this
select name from ABC WHERE grp=MAX(grp);
Regards
Bejoy KS
Sent from handheld, please excuse typos.From: Chen Song <ch...@gmail.com>
Date: Thu, 27 Sep 2012 15:33:11 -0400To: <us...@hive.apache.org>ReplyTo: user@hive.apache.org
Subject: Re: ERROR: Hive subquery showing
Can you try this?
select name from ( select MAX(grp) as name from ABC ) gy ;
On Thu, Sep 27, 2012 at 3:29 PM, yogesh dhari <yo...@live.com> wrote:
Hi all,
I have a table called ABC, like
name grp
A 1
B 2
C 4
D 8
I want the output like the name having greatest grp i.e D;
I wrote a query:
select name from ( select MAX(grp) from ABC ) gy ;
but it gives error
FAILED: Error in semantic analysis: Line 1:7 Invalid table alias or column reference 'name': (possible column names are: _col0)
Please help and suggest why it is so, and what would be the query;
Thanks & regards
Yogesh Kumar
--
Chen Song
Re: ERROR: Hive subquery showing
Posted by Bejoy KS <be...@yahoo.com>.
Hi yogesh
What about a query like this
select name from ABC WHERE grp=MAX(grp);
Regards
Bejoy KS
Sent from handheld, please excuse typos.
-----Original Message-----
From: Chen Song <ch...@gmail.com>
Date: Thu, 27 Sep 2012 15:33:11
To: <us...@hive.apache.org>
Reply-To: user@hive.apache.org
Subject: Re: ERROR: Hive subquery showing
Can you try this?
*
*
*select name from ( select MAX(grp) as name from ABC ) gy ;*
On Thu, Sep 27, 2012 at 3:29 PM, yogesh dhari <yo...@live.com> wrote:
> Hi all,
>
> I have a table called ABC, like
>
> name grp
> A 1
> B 2
> C 4
> D 8
>
> I want the output like the name having greatest grp i.e D;
>
> I wrote a query:
> *
> select name from ( select MAX(grp) from ABC ) gy ;*
>
> but it gives error
>
> FAILED: Error in semantic analysis: Line 1:7 Invalid table alias or column
> reference 'name': (possible column names are: _col0)
>
> Please help and suggest why it is so, and what would be the query;
>
>
> Thanks & regards
> Yogesh Kumar
>
>
>
>
>
>
--
Chen Song
Re: ERROR: Hive subquery showing
Posted by Chen Song <ch...@gmail.com>.
Can you try this?
*
*
*select name from ( select MAX(grp) as name from ABC ) gy ;*
On Thu, Sep 27, 2012 at 3:29 PM, yogesh dhari <yo...@live.com> wrote:
> Hi all,
>
> I have a table called ABC, like
>
> name grp
> A 1
> B 2
> C 4
> D 8
>
> I want the output like the name having greatest grp i.e D;
>
> I wrote a query:
> *
> select name from ( select MAX(grp) from ABC ) gy ;*
>
> but it gives error
>
> FAILED: Error in semantic analysis: Line 1:7 Invalid table alias or column
> reference 'name': (possible column names are: _col0)
>
> Please help and suggest why it is so, and what would be the query;
>
>
> Thanks & regards
> Yogesh Kumar
>
>
>
>
>
>
--
Chen Song