hive SELECT Statement Select Specific Rows

Example

This query will return all columns from the table sales where the values in the column amount is greater than 10 and the data in the region column in "US".

SELECT * FROM sales WHERE amount > 10 AND region = "US"

You can use regular expressions to select the columns you want to obtain. The following statement will get the data from column name and all the columns starting with the prefix address.

SELECT name, address.* FROM Employees

You can also use the keyword LIKE (combined with the character '%') to match strings that begin with or end with a particular substring. The following query will return all the rows where the column city begins with "New"

SELECT name, city FROM Employees WHERE city LIKE 'New%'

You can use the keyword RLIKE to use Java regular expressions. The following query will return rows which column name contains the words "smith" or "son".

SELECT name, address FROM Employee WHERE name RLIKE '.*(smith|son).*'

You can apply functions to the returned data. The following sentence will return all name in upper case.

SELECT upper(name) FROM Employees

You can use different mathematical functions , collection functions, type conversion functions, date functions, conditional functions or string functions.

In order to limit the number of rows given in result, you can use the LIMIT keyword. The following statement will return only ten rows.

SELECT * FROM Employees LIMIT 10