hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John Omernik <j...@omernik.com>
Subject Union in Multi Insert
Date Tue, 12 Feb 2013 03:34:27 GMT
I am trying to do a union, group by, and multi insert all at once. I know
this convoluted but I what I am trying to do is avoid having to scan
through the original table more than once... if I can get all my data from
two columns that I want to pull together, in one round of mappers, I win...
 Basically, col2 and col3 are they type of data, one is src, one is dst, I
want a single record for every uniq value of both col2 and col3.

Any thoughts?



from (
    select
       col1, col2, col3, col4
    from table
) a
INSERT overwrite TABLE table_summary
select col1, unioned_col, count(1), count(distinct col4) from
(select col1, col2 as unioned_col, col4
UNION ALL
select col1, col3 as unioned_col, col4
) b
group by col1, unioned_col

Mime
View raw message