You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Mathieu Despriee <md...@octo.com> on 2013/01/10 09:39:31 UTC
Stack function in Hive : how to specify multiple aliases?
Hi folks,
I want to use the stack function, described here :
https://cwiki.apache.org/Hive/languagemanual-udf.html#LanguageManualUDF-BuiltinTableGeneratingFunctions%2528UDTF%2529
Hive asks me to provide the multiple aliases for the resulting columns
("The number of aliases in the AS clause does not match the number of
colums output by the UDTF, expected 3 aliases but got 1").
What's the syntax to provide multiple aliases ?
Thanks,
Mathieu
unsubscribe
Posted by Sachin Khurana <Sa...@aexp.com>.
unsubscribe
American Express made the following annotations on Thu Jan 10 2013 06:03:03
******************************************************************************
"This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you."
American Express a ajout� le commentaire suivant le Thu Jan 10 2013 06:03:03
Ce courrier et toute pi�ce jointe qu'il contient sont r�serv�s au seul destinataire indiqu� et peuvent renfermer des renseignements confidentiels et privil�gi�s. Si vous n'�tes pas le destinataire pr�vu, toute divulgation, duplication, utilisation ou distribution du courrier ou de toute pi�ce jointe est interdite. Si vous avez re�u cette communication par erreur, veuillez nous en aviser par courrier et d�truire imm�diatement le courrier et les pi�ces jointes. Merci.
******************************************************************************
-------------------------------------------------------------------------------
Re: Stack function in Hive : how to specify multiple aliases?
Posted by Nitin Pawar <ni...@gmail.com>.
I dont think you can do this directly in a query. (I can be wrong)
I just asked took suggestion from a hive guru and he suggested to try
lateral view
https://cwiki.apache.org/Hive/languagemanual-lateralview.html
On Thu, Jan 10, 2013 at 1:42 AM, Mathieu Despriee <md...@octo.com>wrote:
> Not working either :
>
> SELECT stack(2,AppWeb, ByteWeb, PacketWeb, AppP2P, ByteP2P, PacketP2P) AS
> App,Byte,Packet FROM testApp2;
> > FAILED: SemanticException 1:76 Only a single expression in the SELECT
> clause is supported with UDTF's. Error encountered near token 'Byte'
>
> I tried to quote the aliases or to use array-style with no luck.
>
> Is there any description of hive grammar somewhere ?
> I only found this doc :
> https://cwiki.apache.org/Hive/languagemanual-select.html, but "select_expr"
> is not described ....
>
>
>
>
>
> 2013/1/10 Nitin Pawar <ni...@gmail.com>
>
>> I never ran into this kind of problem but can you try select as A,B,C
>>
>>
>> On Thu, Jan 10, 2013 at 12:58 AM, Mathieu Despriee <md...@octo.com>wrote:
>>
>>> SELECT stack(2,AppWeb, ByteWeb, PacketWeb, AppP2P, ByteP2P, PacketP2P)
>>> AS A FROM testApp2;
>>>
>>>
>>> 2013/1/10 Nitin Pawar <ni...@gmail.com>
>>>
>>>> can you provide your query ?
>>>>
>>>>
>>>> On Thu, Jan 10, 2013 at 12:39 AM, Mathieu Despriee <md...@octo.com>wrote:
>>>>
>>>>> Hi folks,
>>>>>
>>>>> I want to use the stack function, described here :
>>>>> https://cwiki.apache.org/Hive/languagemanual-udf.html#LanguageManualUDF-BuiltinTableGeneratingFunctions%2528UDTF%2529
>>>>>
>>>>> Hive asks me to provide the multiple aliases for the resulting columns
>>>>> ("The number of aliases in the AS clause does not match the number of
>>>>> colums output by the UDTF, expected 3 aliases but got 1").
>>>>>
>>>>> What's the syntax to provide multiple aliases ?
>>>>>
>>>>> Thanks,
>>>>> Mathieu
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Nitin Pawar
>>>>
>>>
>>>
>>
>>
>> --
>> Nitin Pawar
>>
>
>
--
Nitin Pawar
Re: Stack function in Hive : how to specify multiple aliases?
Posted by Mathieu Despriee <md...@octo.com>.
Yep, that worked.
As suggested by Nitin, LATERAL VIEW is a helpful too, and here's the syntax
:
SELECT Id, Name, App, Byte, Packet FROM testApp2
LATERAL VIEW stack(2,AppWeb, ByteWeb, PacketWeb, AppP2P, ByteP2P,
PacketP2P) T AS App,Byte,Packet ;
Thanks for your help guys,
Mathieu
2013/1/10 Dean Wampler <de...@thinkbiganalytics.com>
> Try "as (alias1, alias2, ...)"
>
>
> On Thu, Jan 10, 2013 at 3:42 AM, Mathieu Despriee <md...@octo.com>wrote:
>
>> Not working either :
>>
>> SELECT stack(2,AppWeb, ByteWeb, PacketWeb, AppP2P, ByteP2P, PacketP2P) AS
>> App,Byte,Packet FROM testApp2;
>> > FAILED: SemanticException 1:76 Only a single expression in the SELECT
>> clause is supported with UDTF's. Error encountered near token 'Byte'
>>
>> I tried to quote the aliases or to use array-style with no luck.
>>
>> Is there any description of hive grammar somewhere ?
>> I only found this doc :
>> https://cwiki.apache.org/Hive/languagemanual-select.html, but "
>> select_expr" is not described ....
>>
>>
>>
>>
>>
>> 2013/1/10 Nitin Pawar <ni...@gmail.com>
>>
>>> I never ran into this kind of problem but can you try select as A,B,C
>>>
>>>
>>> On Thu, Jan 10, 2013 at 12:58 AM, Mathieu Despriee <md...@octo.com>wrote:
>>>
>>>> SELECT stack(2,AppWeb, ByteWeb, PacketWeb, AppP2P, ByteP2P, PacketP2P)
>>>> AS A FROM testApp2;
>>>>
>>>>
>>>> 2013/1/10 Nitin Pawar <ni...@gmail.com>
>>>>
>>>>> can you provide your query ?
>>>>>
>>>>>
>>>>> On Thu, Jan 10, 2013 at 12:39 AM, Mathieu Despriee <mdespriee@octo.com
>>>>> > wrote:
>>>>>
>>>>>> Hi folks,
>>>>>>
>>>>>> I want to use the stack function, described here :
>>>>>> https://cwiki.apache.org/Hive/languagemanual-udf.html#LanguageManualUDF-BuiltinTableGeneratingFunctions%2528UDTF%2529
>>>>>>
>>>>>> Hive asks me to provide the multiple aliases for the resulting
>>>>>> columns ("The number of aliases in the AS clause does not match the number
>>>>>> of colums output by the UDTF, expected 3 aliases but got 1").
>>>>>>
>>>>>> What's the syntax to provide multiple aliases ?
>>>>>>
>>>>>> Thanks,
>>>>>> Mathieu
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Nitin Pawar
>>>>>
>>>>
>>>>
>>>
>>>
>>> --
>>> Nitin Pawar
>>>
>>
>>
>
>
> --
> *Dean Wampler, Ph.D.*
> thinkbiganalytics.com
> +1-312-339-1330
>
>
Re: Stack function in Hive : how to specify multiple aliases?
Posted by Dean Wampler <de...@thinkbiganalytics.com>.
Try "as (alias1, alias2, ...)"
On Thu, Jan 10, 2013 at 3:42 AM, Mathieu Despriee <md...@octo.com>wrote:
> Not working either :
>
> SELECT stack(2,AppWeb, ByteWeb, PacketWeb, AppP2P, ByteP2P, PacketP2P) AS
> App,Byte,Packet FROM testApp2;
> > FAILED: SemanticException 1:76 Only a single expression in the SELECT
> clause is supported with UDTF's. Error encountered near token 'Byte'
>
> I tried to quote the aliases or to use array-style with no luck.
>
> Is there any description of hive grammar somewhere ?
> I only found this doc :
> https://cwiki.apache.org/Hive/languagemanual-select.html, but "select_expr"
> is not described ....
>
>
>
>
>
> 2013/1/10 Nitin Pawar <ni...@gmail.com>
>
>> I never ran into this kind of problem but can you try select as A,B,C
>>
>>
>> On Thu, Jan 10, 2013 at 12:58 AM, Mathieu Despriee <md...@octo.com>wrote:
>>
>>> SELECT stack(2,AppWeb, ByteWeb, PacketWeb, AppP2P, ByteP2P, PacketP2P)
>>> AS A FROM testApp2;
>>>
>>>
>>> 2013/1/10 Nitin Pawar <ni...@gmail.com>
>>>
>>>> can you provide your query ?
>>>>
>>>>
>>>> On Thu, Jan 10, 2013 at 12:39 AM, Mathieu Despriee <md...@octo.com>wrote:
>>>>
>>>>> Hi folks,
>>>>>
>>>>> I want to use the stack function, described here :
>>>>> https://cwiki.apache.org/Hive/languagemanual-udf.html#LanguageManualUDF-BuiltinTableGeneratingFunctions%2528UDTF%2529
>>>>>
>>>>> Hive asks me to provide the multiple aliases for the resulting columns
>>>>> ("The number of aliases in the AS clause does not match the number of
>>>>> colums output by the UDTF, expected 3 aliases but got 1").
>>>>>
>>>>> What's the syntax to provide multiple aliases ?
>>>>>
>>>>> Thanks,
>>>>> Mathieu
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Nitin Pawar
>>>>
>>>
>>>
>>
>>
>> --
>> Nitin Pawar
>>
>
>
--
*Dean Wampler, Ph.D.*
thinkbiganalytics.com
+1-312-339-1330
Re: Stack function in Hive : how to specify multiple aliases?
Posted by Mathieu Despriee <md...@octo.com>.
Not working either :
SELECT stack(2,AppWeb, ByteWeb, PacketWeb, AppP2P, ByteP2P, PacketP2P) AS
App,Byte,Packet FROM testApp2;
> FAILED: SemanticException 1:76 Only a single expression in the SELECT
clause is supported with UDTF's. Error encountered near token 'Byte'
I tried to quote the aliases or to use array-style with no luck.
Is there any description of hive grammar somewhere ?
I only found this doc :
https://cwiki.apache.org/Hive/languagemanual-select.html, but "select_expr"
is not described ....
2013/1/10 Nitin Pawar <ni...@gmail.com>
> I never ran into this kind of problem but can you try select as A,B,C
>
>
> On Thu, Jan 10, 2013 at 12:58 AM, Mathieu Despriee <md...@octo.com>wrote:
>
>> SELECT stack(2,AppWeb, ByteWeb, PacketWeb, AppP2P, ByteP2P, PacketP2P) AS
>> A FROM testApp2;
>>
>>
>> 2013/1/10 Nitin Pawar <ni...@gmail.com>
>>
>>> can you provide your query ?
>>>
>>>
>>> On Thu, Jan 10, 2013 at 12:39 AM, Mathieu Despriee <md...@octo.com>wrote:
>>>
>>>> Hi folks,
>>>>
>>>> I want to use the stack function, described here :
>>>> https://cwiki.apache.org/Hive/languagemanual-udf.html#LanguageManualUDF-BuiltinTableGeneratingFunctions%2528UDTF%2529
>>>>
>>>> Hive asks me to provide the multiple aliases for the resulting columns
>>>> ("The number of aliases in the AS clause does not match the number of
>>>> colums output by the UDTF, expected 3 aliases but got 1").
>>>>
>>>> What's the syntax to provide multiple aliases ?
>>>>
>>>> Thanks,
>>>> Mathieu
>>>>
>>>
>>>
>>>
>>> --
>>> Nitin Pawar
>>>
>>
>>
>
>
> --
> Nitin Pawar
>
Re: Stack function in Hive : how to specify multiple aliases?
Posted by Nitin Pawar <ni...@gmail.com>.
I never ran into this kind of problem but can you try select as A,B,C
On Thu, Jan 10, 2013 at 12:58 AM, Mathieu Despriee <md...@octo.com>wrote:
> SELECT stack(2,AppWeb, ByteWeb, PacketWeb, AppP2P, ByteP2P, PacketP2P) AS
> A FROM testApp2;
>
>
> 2013/1/10 Nitin Pawar <ni...@gmail.com>
>
>> can you provide your query ?
>>
>>
>> On Thu, Jan 10, 2013 at 12:39 AM, Mathieu Despriee <md...@octo.com>wrote:
>>
>>> Hi folks,
>>>
>>> I want to use the stack function, described here :
>>> https://cwiki.apache.org/Hive/languagemanual-udf.html#LanguageManualUDF-BuiltinTableGeneratingFunctions%2528UDTF%2529
>>>
>>> Hive asks me to provide the multiple aliases for the resulting columns
>>> ("The number of aliases in the AS clause does not match the number of
>>> colums output by the UDTF, expected 3 aliases but got 1").
>>>
>>> What's the syntax to provide multiple aliases ?
>>>
>>> Thanks,
>>> Mathieu
>>>
>>
>>
>>
>> --
>> Nitin Pawar
>>
>
>
--
Nitin Pawar
Re: Stack function in Hive : how to specify multiple aliases?
Posted by Mathieu Despriee <md...@octo.com>.
SELECT stack(2,AppWeb, ByteWeb, PacketWeb, AppP2P, ByteP2P, PacketP2P) AS A
FROM testApp2;
2013/1/10 Nitin Pawar <ni...@gmail.com>
> can you provide your query ?
>
>
> On Thu, Jan 10, 2013 at 12:39 AM, Mathieu Despriee <md...@octo.com>wrote:
>
>> Hi folks,
>>
>> I want to use the stack function, described here :
>> https://cwiki.apache.org/Hive/languagemanual-udf.html#LanguageManualUDF-BuiltinTableGeneratingFunctions%2528UDTF%2529
>>
>> Hive asks me to provide the multiple aliases for the resulting columns
>> ("The number of aliases in the AS clause does not match the number of
>> colums output by the UDTF, expected 3 aliases but got 1").
>>
>> What's the syntax to provide multiple aliases ?
>>
>> Thanks,
>> Mathieu
>>
>
>
>
> --
> Nitin Pawar
>
Re: Stack function in Hive : how to specify multiple aliases?
Posted by Nitin Pawar <ni...@gmail.com>.
can you provide your query ?
On Thu, Jan 10, 2013 at 12:39 AM, Mathieu Despriee <md...@octo.com>wrote:
> Hi folks,
>
> I want to use the stack function, described here :
> https://cwiki.apache.org/Hive/languagemanual-udf.html#LanguageManualUDF-BuiltinTableGeneratingFunctions%2528UDTF%2529
>
> Hive asks me to provide the multiple aliases for the resulting columns
> ("The number of aliases in the AS clause does not match the number of
> colums output by the UDTF, expected 3 aliases but got 1").
>
> What's the syntax to provide multiple aliases ?
>
> Thanks,
> Mathieu
>
--
Nitin Pawar