LanguageManual Select
Skip to end of metadata
Go to start of metadata

Select Syntax

  • 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 supports a number of operators and UDFs in the WHERE clause:

As of Hive 0.13 some types of subqueries are supported in the WHERE clause.

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.

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

If a table page_views is joined with another table dim_users, you can specify a range of partitions in the ON clause as follows:

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:

can also be expressed as

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.

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

REGEX Column Specification

A SELECT statement can take regex-based column specification.

More Select Syntax

See the following documents for additional syntax and features of SELECT statements:

GROUP BY

SORT BY, ORDER BY, CLUSTER BY, DISTRIBUTE BY

JOIN

UNION ALL

TABLESAMPLE

Subqueries

Virtual Columns

Operators and UDFs

LATERAL VIEW

Windowing, OVER, and Analytics

Common Table Expressions

Labels
  • No labels