...
Code Block |
---|
[WITH CommonTableExpression (, CommonTableExpression)*] (Note: Only available starting with Hive 0.13.0) SELECT [ALL | DISTINCT] select_expr, select_expr, ... FROM table_reference [WHERE where_condition] [GROUP BY col_list] [ORDER BY col_list] [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list] ] [LIMIT number[offset,] rows] |
- 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.- Table names and column names are case insensitive.
- In Hive 0.12 and earlier, only alphanumeric and underscore characters are allowed in table and column names.
- In Hive 0.13 and later, column names can contain any Unicode character (see HIVE-6013). Any column name that is specified within backticks (
`
) is treated literally. Within a backtick string, use double backticks (``
) to represent a backtick character. - To revert to pre-0.13.0 behavior and restrict column names to alphanumeric and underscore characters, set the configuration property
hive.support.quoted.identifiers
tonone
. In this configuration, backticked names are interpreted as regular expressions. For details, see Supporting Quoted Identifiers in Column Names (attached to HIVE-6013). Also see REGEX Column Specification below.
Simple query. For example, the following query retrieves all columns and all rows from table t1.
Code Block language sql SELECT * FROM t1
Info title Note As of Hive 0.13.0, FROM is optional (for example,
SELECT 1+1
).To get the current database (as of Hive 0.13.0), use the current_database() function:
Code Block language sql SELECT current_database()
To specify a database, either qualify the table names with database names ("
db_name.table_name
" starting in Hive 0.7) or issue the USE statement before the query statement (starting in Hive 0.6)."
db_name.table_name
" allows a query to access tables in different databases.USE sets the database for all subsequent HiveQL statements. Reissue it with the keyword "
default
" to reset to the default database.Code Block language sql USE database_name; SELECT query_specifications; USE default;
...
Code Block |
---|
SELECT col1 FROM (SELECT col1, SUM(col2) AS col2sum FROM t1 GROUP BY col1) t2 WHERE t2.col2sum > 10 |
LIMIT Clause
The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement.
LIMIT takes one or two numeric arguments, which must both be non-negative integer constants.
The first argument specifies the offset of the first row to return (as of Hive 2.0.0) and the second specifies the maximum number of rows to return.
A single argument stands for the maximum number of rows and the offset defaults to 0.
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.
Code Block SET mapred.reduce.tasks = 1 SELECT * FROM sales SORT BY amount DESC LIMIT 5
REGEX Column Specification
...