...
- 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;
...
- GROUP BY
- SORT/ORDER/CLUSTER/DISTRIBUTE BY
- JOIN
- UNION
- TABLESAMPLE
- Subqueries
- Virtual Columns
- Operators and UDFs
- LATERAL VIEW
- Windowing, OVER, and Analytics
- Common Table Expressions