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/Select" by CarlSteinbach
Date Tue, 02 Mar 2010 01:45:31 GMT
Dear Wiki user,

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

The "Hive/LanguageManual/Select" page has been changed by CarlSteinbach.
The comment on this change is: Add section TOC.
http://wiki.apache.org/hadoop/Hive/LanguageManual/Select?action=diff&rev1=9&rev2=10

--------------------------------------------------

+ <<TableOfContents>>
+ 
  == Select Syntax ==
  {{{
  SELECT [ALL | DISTINCT] select_expr, select_expr, ...
@@ -18, +20 @@

  SELECT * FROM t1
  }}}
  
+ === WHERE Clause ===
-  * Where clause - The where condition is a [[Hive/LanguageManual/Types|boolean]] [[Hive/LanguageManual/Expressions|expression]].
For example, the following query returns only those sales records which have an amount greater
than 10 from the US region. Hive does not support IN, EXISTS or subqueries in the WHERE clause.
+ The where condition is a [[Hive/LanguageManual/Types|boolean]] [[Hive/LanguageManual/Expressions|expression]].
For example, the following query returns only those sales records which have an amount greater
than 10 from the US region. Hive does not support IN, EXISTS or subqueries in the WHERE clause.
  {{{
  SELECT * FROM sales WHERE amount > 10 AND region = "US"
  }}}
  
+ === ALL and DISTINCT Clauses ===
-  * The ALL and DISTINCT options specify whether duplicate rows should be returned. If none
of these options are given, the default is ALL (all matching rows are returned). DISTINCT
specifies removal of duplicate rows from the result set.
+ The ALL and DISTINCT options specify whether duplicate rows should be returned. If none
of these options are given, the default is ALL (all matching rows are returned). DISTINCT
specifies removal of duplicate rows from the result set.
  {{{
  hive> SELECT col1, col2 FROM t1
      1 3
@@ -39, +43 @@

      2
  }}}
  
+ === Partition Based Queries ===
-  * Partition based queries. In general, a SELECT query scans the entire table (other than
for [[Hive/LanguageManual/Sampling|sampling]]). If a table created using the [[Hive/LanguageManual/DDL|PARTITIONED
BY]] clause, a query can do '''input pruning''' and scan only a fraction of the table relevant
to the query. Hive currently does input pruning only if the partition predicates are specified
in the WHERE clause closest to the table_reference in the FROM clause. For example, if table
page_views is partitioned on column date, the following query retrieves rows for just one
day 2008-03-31.
+ In general, a SELECT query scans the entire table (other than for [[Hive/LanguageManual/Sampling|sampling]]).
If a table created using the [[Hive/LanguageManual/DDL|PARTITIONED BY]] clause, a query can
do '''input pruning''' and scan only a fraction of the table relevant to the query. Hive currently
does input pruning only if the partition predicates are specified in the WHERE clause closest
to the table_reference in the FROM clause. For example, if table page_views is partitioned
on column date, the following query retrieves rows for just one day 2008-03-31.
  {{{
      SELECT page_views.* 
      FROM page_views 
@@ -50, +55 @@

  
   * See also [[Hive/LanguageManual/SortBy|Sort By / Cluster By / Distribute By / Order By]]
  
+ === HAVING Clause ===
-  * Having - Hive currently does not support HAVING clause. A similar effect can be gotten
by using a subquery. For example, 
+ Hive currently does not support HAVING clause. A similar effect can be achieved by using
a subquery. For example, 
  {{{
  SELECT col1 FROM t1 GROUP BY col1 HAVING SUM(col2) > 10
  }}}
- can be rewritten as
+ can also be expressed as
  {{{
  SELECT col1 FROM (SELECT col1, SUM(col2) AS col2sum FROM t1 GROUP BY col1) t2 WHERE t2.col2sum
> 10
  }}}
  
+ === LIMIT Clause ===
-  * Limit indicates the number of rows to be returned. The rows returned are chosen at random.
The following query returns 5 rows from t1 at random.
+ Limit indicates the number of rows to be returned. The rows returned are chosen at random.
The following query returns 5 rows from t1 at random.
  {{{
  SELECT * FROM t1 LIMIT 5
  }}}
@@ -70, +77 @@

  SELECT * FROM sales SORT BY amount DESC LIMIT 5
  }}}
  
+ === REGEX Column Specification ===
-  * A SELECT statement can take regex-based column specification.
+ A SELECT statement can take regex-based column specification.
   * We use java regex syntax. Try http://www.fileformat.info/tool/regex.htm for testing purposes.
   * The following query select all columns except ds and hr.
  {{{

Mime
View raw message