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/Joins" by AshishThusoo
Date Wed, 21 Jan 2009 23:04:04 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 AshishThusoo:
http://wiki.apache.org/hadoop/Hive/LanguageManual/LanguageManual/Joins

------------------------------------------------------------------------------
+ == Join Syntax ==
+ Hive supports the following syntax for joining tables:
+ 
  {{{
  join_table:
      table_reference JOIN table_factor [join_condition]
@@ -21, +24 @@

      expression = expression
  }}}
  
+ Only equality joins and outer joins are supported in Hive. Hive does not support join conditions
that are not equality
+ conditions as it is very difficult to express such conditions as a map/reduce job. Also,
more than two tables can be
+ joined in Hive.
+ 
+ Some salient points to consider when writing join queries are as follows:
+ 
+  * Only equality joins are allowed e.g.
+ {{{ 
+   SELECT a.* FROM a JOIN b ON (a.id = b.id) 
+ }}}
+   is a valid join, however
+ {{{
+   SELECT a.* FROM a JOIN b ON (a.id <> b.id)
+ }}}
+   is NOT allowed
+  * More than 2 tables can be joined in the same query e.g.
+ {{{
+   SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
+ }}}
+   is a valid join
+  * Hive converts joins over multiple tables into a single map/reduce job if for every table
the same column is used in the join clauses e.g.
+ {{{
+   SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)
+ }}}
+   is converted into a single map/reduce job as only key1 column for b is involved in the
join. On the other hand
+ {{{
+   SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
+ }}}
+   is converted into two map/reduce jobs because key1 column from b is used in the first
join condition and key2 column from b is used in the second one. The first map/reduce job
joins a with b and the results are then joined with c in the second map/reduce job.
+  * In every map/reduce stage of the join, the last table in the sequence is streamed through
the reducers where as the others are buffered. Therefore, it helps to reduce the memory needed
in the reducer for buffering the rows for a particular value of the join key by organizing
the tables such that the largest tables appear last in the sequence. e.g. in
+ {{{
+   SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)
+ }}}
+   all the three tables are joined in a single map/reduce job and the values for a particular
value of the key for tables a and b are buffered in the memory in the reducers. Then for each
row retrieved from c, the join is computed with the buffered rows. Similarly for
+ {{{
+   SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
+ }}}
+   there are two map/reduce jobs involved in computing the join. The first of these joins
a with b and buffers the values of a while streaming the values of b in the reducers. The
second of one of these jobs buffers the results of the first join while streaming the values
of c through the reducers.
+ 

Mime
View raw message