Key-Value Map-Reduce SEO

Select Syntax

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[   CLUSTER BY col_list
  | [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]

• A SELECT statement can be part of a union query or a subquery of another query.
• table_reference indicates the input to the query. It can be a regular table, a join construct or a subquery.
• Simple query. For example, the following query retrieves all columns and all rows from table t1.

SELECT * FROM t1

• Where clause – The where condition is a boolean 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"

• 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
    1 3
    1 4
    2 5
hive> SELECT DISTINCT col1, col2 FROM t1
    1 3
    1 4
    2 5
hive> SELECT DISTINCT col1 FROM t1
    1
    2

• Partition based queries. In general, a SELECT query scans the entire table (other than for sampling). If a table created using the 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
    WHERE page_views.date >= '2008-03-01' AND page_views.date <= '2008-03-31'

• See also Group By
• See also Sort By / Cluster By / Distribute By / Order By
• Having – Hive currently does not support HAVING clause. A similar effect can be gotten by using a subquery. For example,

SELECT col1 FROM t1 GROUP BY col1 HAVING SUM(col2) > 10

can be rewritten as

SELECT col1 FROM (SELECT col1, SUM(col2) AS col2sum FROM t1 GROUP BY col1) t2 WHERE t2.col2sum > 10

• 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

• Top k queries. The following query returns the top 5 sales records wrt amount.

SET mapred.reduce.tasks = 1
SELECT * FROM sales SORT BY amount DESC LIMIT 5

• 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.

SELECT `(ds|hr)?+.+` FROM sales

This entry was posted on Sunday, October 25th, 2009 at 9:05 am and is filed under hadoop map-reduce, hive & pig. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.