hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ning Zhang <nzh...@facebook.com>
Subject Re: Multiple insert statement and levels of aggregation
Date Fri, 15 Oct 2010 18:17:23 GMT
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
> 


Mime
View raw message