...
- 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
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;
...
A SELECT statement can take regex-based column specification in Hive releases prior to 0.13.0, or in 0.13.0 and later releases if the configuration property hive.support.quoted.identifiers
is set to none
.
- We use Java regex syntax. Try http://www.fileformat.info/tool/regex.htm for testing purposes.
- The following query selects all columns except ds and hr.
...