You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@hive.apache.org by Alex Boisvert <al...@bizo.com> on 2010/10/15 19:59:48 UTC

Multiple insert statement and levels of aggregation

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

Re: Multiple insert statement and levels of aggregation

Posted by Alex Boisvert <al...@bizo.com>.

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 <nz...@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
> >
>
>

Re: Multiple insert statement and levels of aggregation

Posted by Ning Zhang <nz...@facebook.com>.

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
>