hadoop-common-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Apache Wiki <wikidi...@apache.org>
Subject [Hadoop Wiki] Update of "Hive/LanguageManual/LanguageManual/Union" by RaghothamMurthy
Date Wed, 21 Jan 2009 23:05:55 GMT
Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Hadoop Wiki" for change notification.

The following page has been changed by RaghothamMurthy:
http://wiki.apache.org/hadoop/Hive/LanguageManual/LanguageManual/Union

New page:
Syntax:
{{{
select_statement UNION ALL select_statement UNION ALL select_statement ...
}}}

UNION is used to combine the result from multiple SELECT statements into a single result set.
We currently only support UNION ALL (bag union) i.e. duplicates are not eliminated. The number
and names of columns returned by each select_statement has to be the same. Otherwise, a schema
error is thrown.

If some additional processing has to be done on the result of the UNION, the entire statement
expression can be embedded in a FROM clause like below:
{{{
SELECT *
FROM (
  select_statement
  UNION ALL
  select_statement
) unionResult
}}}


For example, if we suppose there are two different tables that track which user has published
a video and which user has published a comment, the following query joins the results of a
union all with the user table to create a single annotated stream for all the video publishing
and comment publishing events:
{{{
    SELECT u.id, actions.date
    FROM (
        SELECT av.uid AS uid 
        FROM action_video av 
        WHERE av.date = '2008-06-03' 
        UNION ALL 
        SELECT ac.uid AS uid 
        FROM action_comment ac 
        WHERE ac.date = '2008-06-03' 
     ) actions JOIN users u ON (u.id = actions.uid) 
}}}

Mime
View raw message