[Hive-languagemanual] Select Base Use

• Select Syntax
  • WHERE Clause
  • All and DISTINCT clauses
  • Partition Based Queries
  • Having Clause
  • LIMIT Clause
  • REGEX Column Specification
  • More Select Syntax
    • 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

Select Syntax

[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] [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_referenceIndicates the input to the query. It can be a regular table, a view, a join construct or a subquery.
• Table names and column names is case insensitive.
  • in Hive 0.12 and earlier, only alphanumeric and underscore characters is 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, which 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 Configur ation property  hive.support.quoted.identifiers  to  none . In this configuration, backticked names is 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.

  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 "to reset to the default default database.

  USE database_name;SELECTquery_specifications;USE default;

WHERE Clause

The WHERE condition is a Boolean expression. For example, the following query returns only those sales records which has an amount greater than from the US region. Hive supports a number ofoperators and UDFs in the WHERE clause:

SELECT* FROM sales WHERE amount > 10 ANDregion = "US"

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

All and DISTINCT clauses

The all and DISTINCT options Specify whether duplicate rows should be returned. If None of the these options is given, the default was all (all matching rows is returned). DISTINCT specifies removal of duplicate rows from the result set. Note, Hive supports SELECT DISTINCT * since 0.15 (HIVE-9194).

hive> SELECT col1, col2 FROM t1    13    13    14    25hive> SELECT DISTINCT col1, col2 FROM t1    13    14    25hive> SELECT DISTINCT col1 FROM t1    1    2

Partition Based Queries partition query

In general, a SELECT query scans the entire (all) table (other than for sampling (sampling)). If a table created using the partitioned by clause, a query can does 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 is specified in the WHERE clause or the TO clause in a JOIN. For example, if Table page_views are partitioned on column date, the following query retrieves rows for just days between 2 008-03-01 and 2008-03-31.

SELECT page_views.*FROM page_viewsWHERE page_views.date >= ‘2008-03-01‘AND page_views.date <= ‘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 fo Llows:

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 have clause in version 0.7.0. In older versions of Hive it's possible to achieve the same effect by using a subquery, e.g:

SELECT col1 FROM t1 GROUP BY col1 HAVING SUM(col2) > 10

Can also be expressed as

SELECT col1 FROM (SELECT col1, SUM(col2) AS col2sum FROM t1 GROUP BY col1) t2 WHERE t2.col2sum > 10

LIMIT Clause

Limit indicates the number of rows to be returned. The rows returned is chosen at random. The following query returns 5 rows from T1 at random.

SELECT * FROM t1 LIMIT 5

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

  SET mapred.reduce.tasks = 1SELECT * FROM sales SORT BY amount DESC LIMIT 5

REGEX column specification Regular expression columns

A SELECT statement can take regex-based column specification in Hive releases prior to 0.13.0, or in 0.13.0 and later Rele ASEs If the configuration property was hive.support.quoted.identifiers 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.

SELECT `(ds|hr)?+.+` FROM sales

More Select Syntax

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

GROUP Bysort by, ORDER by, CLUSTER by, distribute Byjoin

Join optimization

Outer Join Behavior

UNION alltablesamplesubqueriesvirtual columnsoperators and Udfslateral viewwindowing, over, and Analytics Common Table Expressions

[Hive-languagemanual] Select Base Use