RE: Best way of Unpivoting of hiva table data. Any Analytic function for unpivoting
You can do something like this:
select id,key,value from my_table lateral view explode
(map('fname',first_name,'lname',last_name)) t;
Given the following table:
id, first_name,last_name
__
1,Dudu,Markovitz
2,Andrew,Sears
The result will look like:
Id,key,value
__
1,fname,Dudu
1,lname,Markovitz
2,fname, Andrew
2,lname, Sears
Dudu
From: mahender bigdata [mailto:mahender.bigd...@outlook.com]
Sent: Wednesday, April 06, 2016 12:59 AM
To: user@hive.apache.org
Subject: Re: Best way of Unpivoting of hiva table data. Any Analytic function
for unpivoting
Thanks Dudu.. So I make changes to use Union All.
So i hope there is no bultin- udf for doing this functionality
On 4/5/2016 2:08 PM, Markovitz, Dudu wrote:
Hi
Please make sure you are use "union all" and not "union".
"union all" just spools one query result after the other.
"union" eliminates duplicated rows, therefore works much harder.
Dudu
From: mahender bigdata [mailto:mahender.bigd...@outlook.com]
Sent: Tuesday, April 05, 2016 11:50 PM
To: user@hive.apache.org<mailto:user@hive.apache.org>
Subject: Re: Best way of Unpivoting of hiva table data. Any Analytic function
for unpivoting
Hi Adrew,
Sorry for delay in response. currently I'm using Select with Union :-) . I
would like to know is there any Built-in Hive UDF available for unpivoting .
On 3/30/2016 2:23 PM, Andrew Sears wrote:
>From mytable
Select id, 'mycol' as name, col1 as value
Union
Select id, 'mycol2' as name, col2 as value
Something like this might work for you?
Cheers,
Andrew
On Mon, Mar 28, 2016 at 7:53 PM, Ryan Harris
<ryan.har...@zionsbancorp.com<mailto:ryan.har...@zionsbancorp.com>> wrote:
collect_list(col) will give you an array with all of the data from that column
However, the scalability of this approach will have limits.
-Original Message-
From: mahender bigdata [mailto:mahender.bigd...@outlook.com]
Sent: Monday, March 28, 2016 5:47 PM
To: user@hive.apache.org<mailto:user@hive.apache.org>
Subject: Best way of Unpivoting of hiva table data. Any Analytic function for
unpivoting
Hi,
Has any one implemented Unpivoting of Hive external table data. We would
like Convert Columns into Multiple Rows. We have external table, which
holds almost 2 GB of Data. is there best and quicker way of Converting
columns into Row. Any Analytic functions available in Hive to do Unpivoting.
==
THIS ELECTRONIC MESSAGE, INCLUDING ANY ACCOMPANYING DOCUMENTS, IS CONFIDENTIAL
and may contain information that is privileged and exempt from disclosure under
applicable law. If you are neither the intended recipient nor responsible for
delivering the message to the intended recipient, please note that any
dissemination, distribution, copying or the taking of any action in reliance
upon the message is strictly prohibited. If you have received this
communication in error, please notify the sender immediately. Thank you.
Re: Best way of Unpivoting of hiva table data. Any Analytic function for unpivoting
Thanks Dudu.. So I make changes to use Union All.
So i hope there is no bultin- udf for doing this functionality
On 4/5/2016 2:08 PM, Markovitz, Dudu wrote:
Hi
Please make sure you are use “union all” and not “union”.
“union all” just spools one query result after the other.
“union” eliminates duplicated rows, therefore works much harder.
Dudu
*From:*mahender bigdata [mailto:mahender.bigd...@outlook.com]
*Sent:* Tuesday, April 05, 2016 11:50 PM
*To:* user@hive.apache.org
*Subject:* Re: Best way of Unpivoting of hiva table data. Any Analytic
function for unpivoting
Hi Adrew,
Sorry for delay in response. currently I'm using Select with Union :-)
. I would like to know is there any Built-in Hive UDF available for
unpivoting .
On 3/30/2016 2:23 PM, Andrew Sears wrote:
From mytable
Select id, 'mycol' as name, col1 as value
Union
Select id, 'mycol2' as name, col2 as value
Something like this might work for you?
Cheers,
Andrew
On Mon, Mar 28, 2016 at 7:53 PM, Ryan Harris
<ryan.har...@zionsbancorp.com
<mailto:ryan.har...@zionsbancorp.com>> wrote:
collect_list(col) will give you an array with all of the data
from that column
However, the scalability of this approach will have limits.
-Original Message-
From: mahender bigdata [mailto:mahender.bigd...@outlook.com]
Sent: Monday, March 28, 2016 5:47 PM
To: user@hive.apache.org <mailto:user@hive.apache.org>
Subject: Best way of Unpivoting of hiva table data. Any
Analytic function for unpivoting
Hi,
Has any one implemented Unpivoting of Hive external table
data. We would
like Convert Columns into Multiple Rows. We have external
table, which
holds almost 2 GB of Data. is there best and quicker way of
Converting
columns into Row. Any Analytic functions available in Hive to
do Unpivoting.
==
THIS ELECTRONIC MESSAGE, INCLUDING ANY ACCOMPANYING DOCUMENTS,
IS CONFIDENTIAL and may contain information that is privileged
and exempt from disclosure under applicable law. If you are
neither the intended recipient nor responsible for delivering
the message to the intended recipient, please note that any
dissemination, distribution, copying or the taking of any
action in reliance upon the message is strictly prohibited. If
you have received this communication in error, please notify
the sender immediately. Thank you.
RE: Best way of Unpivoting of hiva table data. Any Analytic function for unpivoting
Hi Please make sure you are use "union all" and not "union". "union all" just spools one query result after the other. "union" eliminates duplicated rows, therefore works much harder. Dudu From: mahender bigdata [mailto:mahender.bigd...@outlook.com] Sent: Tuesday, April 05, 2016 11:50 PM To: user@hive.apache.org Subject: Re: Best way of Unpivoting of hiva table data. Any Analytic function for unpivoting Hi Adrew, Sorry for delay in response. currently I'm using Select with Union :-) . I would like to know is there any Built-in Hive UDF available for unpivoting . On 3/30/2016 2:23 PM, Andrew Sears wrote: >From mytable Select id, 'mycol' as name, col1 as value Union Select id, 'mycol2' as name, col2 as value Something like this might work for you? Cheers, Andrew On Mon, Mar 28, 2016 at 7:53 PM, Ryan Harris <ryan.har...@zionsbancorp.com<mailto:ryan.har...@zionsbancorp.com>> wrote: collect_list(col) will give you an array with all of the data from that column However, the scalability of this approach will have limits. -Original Message- From: mahender bigdata [mailto:mahender.bigd...@outlook.com] Sent: Monday, March 28, 2016 5:47 PM To: user@hive.apache.org<mailto:user@hive.apache.org> Subject: Best way of Unpivoting of hiva table data. Any Analytic function for unpivoting Hi, Has any one implemented Unpivoting of Hive external table data. We would like Convert Columns into Multiple Rows. We have external table, which holds almost 2 GB of Data. is there best and quicker way of Converting columns into Row. Any Analytic functions available in Hive to do Unpivoting. == THIS ELECTRONIC MESSAGE, INCLUDING ANY ACCOMPANYING DOCUMENTS, IS CONFIDENTIAL and may contain information that is privileged and exempt from disclosure under applicable law. If you are neither the intended recipient nor responsible for delivering the message to the intended recipient, please note that any dissemination, distribution, copying or the taking of any action in reliance upon the message is strictly prohibited. If you have received this communication in error, please notify the sender immediately. Thank you.
Re: Best way of Unpivoting of hiva table data. Any Analytic function for unpivoting
Hi Adrew,
Sorry for delay in response. currently I'm using Select with Union :-) .
I would like to know is there any Built-in Hive UDF available for
unpivoting .
On 3/30/2016 2:23 PM, Andrew Sears wrote:
From mytable
Select id, 'mycol' as name, col1 as value
Union
Select id, 'mycol2' as name, col2 as value
Something like this might work for you?
Cheers,
Andrew
On Mon, Mar 28, 2016 at 7:53 PM, Ryan Harris
<ryan.har...@zionsbancorp.com <mailto:ryan.har...@zionsbancorp.com>>
wrote:
collect_list(col) will give you an array with all of the data from
that column
However, the scalability of this approach will have limits.
-Original Message-
From: mahender bigdata [mailto:mahender.bigd...@outlook.com]
Sent: Monday, March 28, 2016 5:47 PM
To: user@hive.apache.org
Subject: Best way of Unpivoting of hiva table data. Any Analytic
function for unpivoting
Hi,
Has any one implemented Unpivoting of Hive external table data. We
would
like Convert Columns into Multiple Rows. We have external table,
which
holds almost 2 GB of Data. is there best and quicker way of
Converting
columns into Row. Any Analytic functions available in Hive to do
Unpivoting.
==
THIS ELECTRONIC MESSAGE, INCLUDING ANY ACCOMPANYING DOCUMENTS, IS
CONFIDENTIAL and may contain information that is privileged and
exempt from disclosure under applicable law. If you are neither
the intended recipient nor responsible for delivering the message
to the intended recipient, please note that any dissemination,
distribution, copying or the taking of any action in reliance upon
the message is strictly prohibited. If you have received this
communication in error, please notify the sender immediately.
Thank you.
Re: Best way of Unpivoting of hiva table data. Any Analytic function for unpivoting
From mytable Select id, 'mycol' as name, col1 as value Union Select id, 'mycol2' as name, col2 as value Something like this might work for you? Cheers, Andrew On Mon, Mar 28, 2016 at 7:53 PM, Ryan Harris < ryan.har...@zionsbancorp.com [ryan.har...@zionsbancorp.com] > wrote: collect_list(col) will give you an array with all of the data from that column However, the scalability of this approach will have limits. -Original Message- From: mahender bigdata [mailto:mahender.bigd...@outlook.com] Sent: Monday, March 28, 2016 5:47 PM To: user@hive.apache.org Subject: Best way of Unpivoting of hiva table data. Any Analytic function for unpivoting Hi, Has any one implemented Unpivoting of Hive external table data. We would like Convert Columns into Multiple Rows. We have external table, which holds almost 2 GB of Data. is there best and quicker way of Converting columns into Row. Any Analytic functions available in Hive to do Unpivoting. == THIS ELECTRONIC MESSAGE, INCLUDING ANY ACCOMPANYING DOCUMENTS, IS CONFIDENTIAL and may contain information that is privileged and exempt from disclosure under applicable law. If you are neither the intended recipient nor responsible for delivering the message to the intended recipient, please note that any dissemination, distribution, copying or the taking of any action in reliance upon the message is strictly prohibited. If you have received this communication in error, please notify the sender immediately. Thank you.
RE: Best way of Unpivoting of hiva table data. Any Analytic function for unpivoting
collect_list(col) will give you an array with all of the data from that column However, the scalability of this approach will have limits. -Original Message- From: mahender bigdata [mailto:mahender.bigd...@outlook.com] Sent: Monday, March 28, 2016 5:47 PM To: user@hive.apache.org Subject: Best way of Unpivoting of hiva table data. Any Analytic function for unpivoting Hi, Has any one implemented Unpivoting of Hive external table data. We would like Convert Columns into Multiple Rows. We have external table, which holds almost 2 GB of Data. is there best and quicker way of Converting columns into Row. Any Analytic functions available in Hive to do Unpivoting. == THIS ELECTRONIC MESSAGE, INCLUDING ANY ACCOMPANYING DOCUMENTS, IS CONFIDENTIAL and may contain information that is privileged and exempt from disclosure under applicable law. If you are neither the intended recipient nor responsible for delivering the message to the intended recipient, please note that any dissemination, distribution, copying or the taking of any action in reliance upon the message is strictly prohibited. If you have received this communication in error, please notify the sender immediately. Thank you.
Best way of Unpivoting of hiva table data. Any Analytic function for unpivoting
Hi, Has any one implemented Unpivoting of Hive external table data. We would like Convert Columns into Multiple Rows. We have external table, which holds almost 2 GB of Data. is there best and quicker way of Converting columns into Row. Any Analytic functions available in Hive to do Unpivoting.
