Analytics with Hadoop - Big Data Made Easy: A Working Guide to the Complete Hadoop Toolset - page 272

The SELECT Statement
The SQL SELECT statement starts with the keyword SELECT, which is followed by a comma-separated list of columns.
The source of the data is identified in the FROM clause, which is followed by the name of the table from which it is
selecting data.
In continuing the second example begun in the previous section, I use the the table with the name trade.
rawtrans. The SELECT statement selects three columns: “paydate,” “supplier,” and “amount.” The “paydate” column,
however, is transformed via the DateConv UDF. The payyear and paymonth values are derived from the “paydate”
column via the year and month functions in Hive. The “amount” column is sum totaled with the aggregating
function SUM .
SELECT
year( DateConv (paydate) ) as payyear,
month(DateConv (paydate) ) as paymonth,
supplier,
SUM(amount) as totamount
FROM
trade.rawtrans
GROUP BY
year( DateConv (paydate) ) ,
month(DateConv (paydate) ) ,
supplier ;
Because the aggregating function SUM is used, a GROUP BY clause is needed to specify which columns are to be
sum totaled. The general rule is that all columns that are not aggregated (i.e. payyear, paymonth, and supplier) must
be in the GROUP BY clause. This example groups data by year, month, and supplier.
The WHERE Clause
When you are running a SELECT statement, you might want to filter the data returned, either because there is too
much data or because you would like to filter on one of the columns. By combining the WHERE clause, column names,
simple operators, and logical expressions like AND and OR , you can build complex filters.
Consider the following example, which adds a filter via the WHERE keyword to the SELECT statement from the
earlier example:
SELECT
year( DateConv (paydate) ) as payyear,
month(DateConv (paydate) ) as paymonth,
supplier,
SUM(amount) as totamount
FROM
trade.rawtrans
WHERE
supplier NOT LIKE 'UK Trade%' AND
supplier NOT LIKE 'Corporate%'
GROUP BY
year( DateConv (paydate) ) ,
month(DateConv (paydate) ) ,
supplier ;