Cool, I hadn't come across lateral views yet. I'll see if I can use that. thanks!! alex
On Fri, Oct 15, 2010 at 11:17 AM, Ning Zhang <nzh...@facebook.com> wrote: > In the multi-insert statement, you cannot put another FROM clause. What you > can do is to put both UDTF in the FROM clause: > > FROM foo lateral view someUDTF(foo.a) as t1_a lateral view > anotherUDTF(foo.a) as T2_a > INSERT ... > SELECT a,b,c,count(1), t1_a > .. > SELECT a,b,c,count(1), t2_a > .. > > > On Oct 15, 2010, at 10:59 AM, Alex Boisvert wrote: > > > Hi, > > > > I'd like to write a multiple-insert select statement where I need to call > different UDTFs and perform several levels of aggregation based on the > result of the initial table, e.g., > > > > FROM (SELECT * from TABLE foo) foo > > INSERT OVERWRITE TABLE bar > > SELECT a, b, c, count(1) FROM (SELECT someUDTF(foo.a)) > > GROUP BY a, b > > INSERT OVERWRITE TABLE baz > > SELECT d, e, f, count(1) FROM (SELECT anotherDifferentUDTF(foo.a)) > > GROUP BY d, e; > > > > However, Hive complains that the SELECTs table bar and baz cannot specify > a different FROM clause. > > > > Is there a way to achieve this without creating intermediate tables? > > > > alex > > > >
