Hive, mail # user - Stack function in Hive : how to specify multiple aliases?

Stack function in Hive : how to specify multiple aliases?

Mathieu Despriee 2013-01-10, 08:39

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

Re: Stack function in Hive : how to specify multiple aliases?

Nitin Pawar 2013-01-10, 08:43

can you provide your query ?
On Thu, Jan 10, 2013 at 12:39 AM, Mathieu Despriee <[EMAIL PROTECTED]>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?

Mathieu Despriee 2013-01-10, 08:58

SELECT stack(2,AppWeb, ByteWeb, PacketWeb, AppP2P, ByteP2P, PacketP2P) AS A
FROM testApp2;
2013/1/10 Nitin Pawar <[EMAIL PROTECTED]>

> can you provide your query ?
>
>
> On Thu, Jan 10, 2013 at 12:39 AM, Mathieu Despriee <[EMAIL PROTECTED]>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?

Nitin Pawar 2013-01-10, 09:08

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 <[EMAIL PROTECTED]>wrote:

> SELECT stack(2,AppWeb, ByteWeb, PacketWeb, AppP2P, ByteP2P, PacketP2P) AS
> A FROM testApp2;
>
>
> 2013/1/10 Nitin Pawar <[EMAIL PROTECTED]>
>
>> can you provide your query ?
>>
>>
>> On Thu, Jan 10, 2013 at 12:39 AM, Mathieu Despriee <[EMAIL PROTECTED]>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?

Mathieu Despriee 2013-01-10, 09:42

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 <[EMAIL PROTECTED]>

> 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 <[EMAIL PROTECTED]>wrote:
>
>> SELECT stack(2,AppWeb, ByteWeb, PacketWeb, AppP2P, ByteP2P, PacketP2P) AS
>> A FROM testApp2;
>>
>>
>> 2013/1/10 Nitin Pawar <[EMAIL PROTECTED]>
>>
>>> can you provide your query ?
>>>
>>>
>>> On Thu, Jan 10, 2013 at 12:39 AM, Mathieu Despriee <[EMAIL PROTECTED]>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?

Nitin Pawar 2013-01-10, 10:14

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 <[EMAIL PROTECTED]>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 <[EMAIL PROTECTED]>
>
>> 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 <[EMAIL PROTECTED]>wrote:
>>
>>> SELECT stack(2,AppWeb, ByteWeb, PacketWeb, AppP2P, ByteP2P, PacketP2P)
>>> AS A FROM testApp2;
>>>
>>>
>>> 2013/1/10 Nitin Pawar <[EMAIL PROTECTED]>
>>>
>>>> can you provide your query ?
>>>>
>>>>
>>>> On Thu, Jan 10, 2013 at 12:39 AM, Mathieu Despriee <[EMAIL PROTECTED]>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?

Dean Wampler 2013-01-10, 14:13

Try "as (alias1, alias2, ...)"

On Thu, Jan 10, 2013 at 3:42 AM, Mathieu Despriee <[EMAIL PROTECTED]>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 <[EMAIL PROTECTED]>
>
>> 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 <[EMAIL PROTECTED]>wrote:
>>
>>> SELECT stack(2,AppWeb, ByteWeb, PacketWeb, AppP2P, ByteP2P, PacketP2P)
>>> AS A FROM testApp2;
>>>
>>>
>>> 2013/1/10 Nitin Pawar <[EMAIL PROTECTED]>
>>>
>>>> can you provide your query ?
>>>>
>>>>
>>>> On Thu, Jan 10, 2013 at 12:39 AM, Mathieu Despriee <[EMAIL PROTECTED]>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?

Mathieu Despriee 2013-01-11, 15:00

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 <[EMAIL PROTECTED]>

> Try "as (alias1, alias2, ...)"
>
>
> On Thu, Jan 10, 2013 at 3:42 AM, Mathieu Despriee <[EMAIL PROTECTED]>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 <[EMAIL PROTECTED]>
>>
>>> 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 <[EMAIL PROTECTED]>wrote:
>>>
>>>> SELECT stack(2,AppWeb, ByteWeb, PacketWeb, AppP2P, ByteP2P, PacketP2P)
>>>> AS A FROM testApp2;
>>>>
>>>>
>>>> 2013/1/10 Nitin Pawar <[EMAIL PROTECTED]>
>>>>
>>>>> can you provide your query ?
>>>>>
>>>>>
>>>>> On Thu, Jan 10, 2013 at 12:39 AM, Mathieu Despriee <[EMAIL PROTECTED]
>>>>> > 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
>
>