I forgot to mention, if there is no WHERE clause in the query, then the query will work fine.
From: Tim Robertson [mailto:[email protected]] Sent: Tuesday, March 16, 2010 12:20 PM To: [email protected] 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 <[email protected]<mailto:[email protected]>> 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:[email protected]<mailto:[email protected]>] Sent: Tuesday, March 16, 2010 6:30 AM To: [email protected]<mailto:[email protected]> Subject: Re: Expanding comma separated values in a column Perfect. Thanks On Tue, Mar 16, 2010 at 11:36 AM, Sonal Goyal <[email protected]<mailto:[email protected]>> 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 <[email protected]<mailto:[email protected]>> 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
