...
| Code Block |
|---|
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 view, a join construct or a subquery.
- Simple query. For example, the following query retrieves all columns and all rows from table 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.
...
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 partition pruning and scan only a fraction of the table relevant to the partitions specified by the query. Hive currently does partition pruning if the partition predicates are specified in the WHERE clause or the ON clause in a JOIN. For example, if table page_views is partitioned on column date, the following query retrieves rows for just days between 2008-03-01 and 2008-03-31.
| Code Block |
|---|
SELECT page_views.*
FROM page_views
WHERE page_views.date >= '2008-03-01' AND page_views.date <= '2008-03-31'
|
...
| Code Block |
|---|
SELECT page_views.*
FROM page_views JOIN dim_users
ON (page_views.user_id = dim_users.id AND 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 Clause
Hive added support for the HAVING clause in version 0.7.0. In older versions of Hive it is possible to achieve the same effect by using a subquery, e.g:
...
- 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.
...