You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Tim Robertson <ti...@gmail.com> on 2010/03/16 11:02:30 UTC
Expanding comma separated values in a column
Hi all,
I have a table of 2 columns of strings, with example row as:
Col1 Col2
123 23,34,45,67... up to around 1 million
I'd like to expand the comma separated values to a new taller KVP table:
Col1 Col2
123 23
123 34
123 45
123 67
123 .... potentially 1,000,000 rows generated
Can someone please point me in the right direction?
Thanks
Tim
Re: Expanding comma separated values in a column
Posted by Tim Robertson <ti...@gmail.com>.
Thanks. Indeed there is not, so that is good news for me.
On Tue, Mar 16, 2010 at 9:18 PM, Paul Yang <py...@facebook.com> wrote:
> I forgot to mention, if there is no WHERE clause in the query, then the
> query will work fine.
>
>
>
> *From:* Tim Robertson [mailto:timrobertson100@gmail.com]
> *Sent:* Tuesday, March 16, 2010 12:20 PM
>
> *To:* hive-user@hadoop.apache.org
> *Subject:* Re: Expanding comma separated values in a column
>
>
>
> Ok, thanks Paul.
>
>
>
> Well, it looked like it worked anyway, but I will rerun... I got the
> expected number of records on the output, but have not yet loaded back into
> Mysql or put the the web app on it to see how the data looks.
>
>
>
> I'm really very impressed with Hive. Looking forward to hooking that up to
> HBase and showing what we can now achieve over our struggling Mysql
> environment.
>
>
>
> Cheers,
>
> Tim
>
>
>
>
>
>
>
> On Tue, Mar 16, 2010 at 8:07 PM, Paul Yang <py...@facebook.com> wrote:
>
> Hey Tim,
>
>
>
> Just FYI, there is a bug with lateral views and the predicate push down
> that will be patched very soon. Until then, you might need to use:
>
>
>
> set hive.optimize.ppd = false;
>
>
>
> Cheers,
>
> Paul
>
>
>
>
>
> *From:* Tim Robertson [mailto:timrobertson100@gmail.com]
> *Sent:* Tuesday, March 16, 2010 6:30 AM
> *To:* hive-user@hadoop.apache.org
> *Subject:* Re: Expanding comma separated values in a column
>
>
>
> Perfect. Thanks
>
>
>
> On Tue, Mar 16, 2010 at 11:36 AM, Sonal Goyal <so...@gmail.com>
> wrote:
>
> Hi Tim,
>
> You can use the explode UDTF. More here:
>
> http://wiki.apache.org/hadoop/Hive/LanguageManual/LateralView
>
> HTH
> Thanks and Regards,
> Sonal
>
>
>
> On Tue, Mar 16, 2010 at 3:32 PM, Tim Robertson <ti...@gmail.com>
> wrote:
>
> Hi all,
>
>
>
> I have a table of 2 columns of strings, with example row as:
>
>
>
> Col1 Col2
>
> 123 23,34,45,67... up to around 1 million
>
>
>
> I'd like to expand the comma separated values to a new taller KVP table:
>
>
>
> Col1 Col2
>
> 123 23
>
> 123 34
>
> 123 45
>
> 123 67
>
> 123 .... potentially 1,000,000 rows generated
>
>
>
> Can someone please point me in the right direction?
>
>
>
> Thanks
>
> Tim
>
>
>
>
>
>
>
>
>
>
>
RE: Expanding comma separated values in a column
Posted by Paul Yang <py...@facebook.com>.
I forgot to mention, if there is no WHERE clause in the query, then the query will work fine.
From: Tim Robertson [mailto:timrobertson100@gmail.com]
Sent: Tuesday, March 16, 2010 12:20 PM
To: hive-user@hadoop.apache.org
Subject: Re: Expanding comma separated values in a column
Ok, thanks Paul.
Well, it looked like it worked anyway, but I will rerun... I got the expected number of records on the output, but have not yet loaded back into Mysql or put the the web app on it to see how the data looks.
I'm really very impressed with Hive. Looking forward to hooking that up to HBase and showing what we can now achieve over our struggling Mysql environment.
Cheers,
Tim
On Tue, Mar 16, 2010 at 8:07 PM, Paul Yang <py...@facebook.com>> wrote:
Hey Tim,
Just FYI, there is a bug with lateral views and the predicate push down that will be patched very soon. Until then, you might need to use:
set hive.optimize.ppd = false;
Cheers,
Paul
From: Tim Robertson [mailto:timrobertson100@gmail.com<ma...@gmail.com>]
Sent: Tuesday, March 16, 2010 6:30 AM
To: hive-user@hadoop.apache.org<ma...@hadoop.apache.org>
Subject: Re: Expanding comma separated values in a column
Perfect. Thanks
On Tue, Mar 16, 2010 at 11:36 AM, Sonal Goyal <so...@gmail.com>> wrote:
Hi Tim,
You can use the explode UDTF. More here:
http://wiki.apache.org/hadoop/Hive/LanguageManual/LateralView
HTH
Thanks and Regards,
Sonal
On Tue, Mar 16, 2010 at 3:32 PM, Tim Robertson <ti...@gmail.com>> wrote:
Hi all,
I have a table of 2 columns of strings, with example row as:
Col1 Col2
123 23,34,45,67... up to around 1 million
I'd like to expand the comma separated values to a new taller KVP table:
Col1 Col2
123 23
123 34
123 45
123 67
123 .... potentially 1,000,000 rows generated
Can someone please point me in the right direction?
Thanks
Tim
Re: Expanding comma separated values in a column
Posted by Tim Robertson <ti...@gmail.com>.
Ok, thanks Paul.
Well, it looked like it worked anyway, but I will rerun... I got the
expected number of records on the output, but have not yet loaded back into
Mysql or put the the web app on it to see how the data looks.
I'm really very impressed with Hive. Looking forward to hooking that up to
HBase and showing what we can now achieve over our struggling Mysql
environment.
Cheers,
Tim
On Tue, Mar 16, 2010 at 8:07 PM, Paul Yang <py...@facebook.com> wrote:
> Hey Tim,
>
>
>
> Just FYI, there is a bug with lateral views and the predicate push down
> that will be patched very soon. Until then, you might need to use:
>
>
>
> set hive.optimize.ppd = false;
>
>
>
> Cheers,
>
> Paul
>
>
>
>
>
> *From:* Tim Robertson [mailto:timrobertson100@gmail.com]
> *Sent:* Tuesday, March 16, 2010 6:30 AM
> *To:* hive-user@hadoop.apache.org
> *Subject:* Re: Expanding comma separated values in a column
>
>
>
> Perfect. Thanks
>
>
>
> On Tue, Mar 16, 2010 at 11:36 AM, Sonal Goyal <so...@gmail.com>
> wrote:
>
> Hi Tim,
>
> You can use the explode UDTF. More here:
>
> http://wiki.apache.org/hadoop/Hive/LanguageManual/LateralView
>
> HTH
> Thanks and Regards,
> Sonal
>
>
>
> On Tue, Mar 16, 2010 at 3:32 PM, Tim Robertson <ti...@gmail.com>
> wrote:
>
> Hi all,
>
>
>
> I have a table of 2 columns of strings, with example row as:
>
>
>
> Col1 Col2
>
> 123 23,34,45,67... up to around 1 million
>
>
>
> I'd like to expand the comma separated values to a new taller KVP table:
>
>
>
> Col1 Col2
>
> 123 23
>
> 123 34
>
> 123 45
>
> 123 67
>
> 123 .... potentially 1,000,000 rows generated
>
>
>
> Can someone please point me in the right direction?
>
>
>
> Thanks
>
> Tim
>
>
>
>
>
>
>
>
>
RE: Expanding comma separated values in a column
Posted by Paul Yang <py...@facebook.com>.
Hey Tim,
Just FYI, there is a bug with lateral views and the predicate push down that will be patched very soon. Until then, you might need to use:
set hive.optimize.ppd = false;
Cheers,
Paul
From: Tim Robertson [mailto:timrobertson100@gmail.com]
Sent: Tuesday, March 16, 2010 6:30 AM
To: hive-user@hadoop.apache.org
Subject: Re: Expanding comma separated values in a column
Perfect. Thanks
On Tue, Mar 16, 2010 at 11:36 AM, Sonal Goyal <so...@gmail.com>> wrote:
Hi Tim,
You can use the explode UDTF. More here:
http://wiki.apache.org/hadoop/Hive/LanguageManual/LateralView
HTH
Thanks and Regards,
Sonal
On Tue, Mar 16, 2010 at 3:32 PM, Tim Robertson <ti...@gmail.com>> wrote:
Hi all,
I have a table of 2 columns of strings, with example row as:
Col1 Col2
123 23,34,45,67... up to around 1 million
I'd like to expand the comma separated values to a new taller KVP table:
Col1 Col2
123 23
123 34
123 45
123 67
123 .... potentially 1,000,000 rows generated
Can someone please point me in the right direction?
Thanks
Tim
Re: Expanding comma separated values in a column
Posted by Tim Robertson <ti...@gmail.com>.
Perfect. Thanks
On Tue, Mar 16, 2010 at 11:36 AM, Sonal Goyal <so...@gmail.com> wrote:
> Hi Tim,
>
> You can use the explode UDTF. More here:
>
> http://wiki.apache.org/hadoop/Hive/LanguageManual/LateralView
>
> HTH
> Thanks and Regards,
> Sonal
>
>
>
> On Tue, Mar 16, 2010 at 3:32 PM, Tim Robertson <ti...@gmail.com>wrote:
>
>> Hi all,
>>
>> I have a table of 2 columns of strings, with example row as:
>>
>> Col1 Col2
>> 123 23,34,45,67... up to around 1 million
>>
>> I'd like to expand the comma separated values to a new taller KVP table:
>>
>> Col1 Col2
>> 123 23
>> 123 34
>> 123 45
>> 123 67
>> 123 .... potentially 1,000,000 rows generated
>>
>> Can someone please point me in the right direction?
>>
>> Thanks
>> Tim
>>
>>
>>
>
Re: Expanding comma separated values in a column
Posted by Sonal Goyal <so...@gmail.com>.
Hi Tim,
You can use the explode UDTF. More here:
http://wiki.apache.org/hadoop/Hive/LanguageManual/LateralView
HTH
Thanks and Regards,
Sonal
On Tue, Mar 16, 2010 at 3:32 PM, Tim Robertson <ti...@gmail.com>wrote:
> Hi all,
>
> I have a table of 2 columns of strings, with example row as:
>
> Col1 Col2
> 123 23,34,45,67... up to around 1 million
>
> I'd like to expand the comma separated values to a new taller KVP table:
>
> Col1 Col2
> 123 23
> 123 34
> 123 45
> 123 67
> 123 .... potentially 1,000,000 rows generated
>
> Can someone please point me in the right direction?
>
> Thanks
> Tim
>
>
>