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

Running the same SELECT statement to check the university supplier rows in the table now yields four rows. The
year and month were selected from the existing rows, while the supplier and total values were hard-coded:
SELECT * FROM trade.suppliertot WHERE supplier LIKE 'UNIVERSITY%' ;
2013 2 UNIVERSITY OF EAST LONDON 550.0
2013 2 UNIVERSITY OF THE ARTS LONDON 550.0
2013 2 UNIVERSITY OF SEMTECH 700.0
2013 2 UNIVERSITY OF SEMTECH 700.0
I could also use an OVERWRITE clause in an INSERT statement that will cause existing rows to be overwritten.
Running the INSERT statement again, using the clause INSERT OVERWRITE , causes all four university rows to be
changed:
INSERT OVERWRITE TABLE trade.suppliertot
SELECT payyear,paymonth,'UNIVERSITY OF SEMTECH',950.0 FROM
trade.suppliertot WHERE supplier LIKE 'UNIVERSITY%' ;
SELECT * FROM trade.suppliertot WHERE supplier LIKE 'UNIVERSITY%' ;
2013 2 UNIVERSITY OF SEMTECH 950.0
2013 2 UNIVERSITY OF SEMTECH 950.0
2013 2 UNIVERSITY OF SEMTECH 950.0
2013 2 UNIVERSITY OF SEMTECH 950.0
As this resulting data now shows, all of the rows have the same values. They have all been overwritten by the
INSERT statement.
Organization of Table Data
Simply retrieving data is but one aspect of analytics; organizing the data is another. The following SELECT statement
will produce a list of suppliers and the number of transactions associated with them in the table trade.rawtrans:
SELECT supplier, COUNT(*) FROM trade.rawtrans GROUP BY supplier ;
Although the count of transactions is grouped ( GROUP BY ) by each supplier so the name and total count for each
supplier is displayed, the statement does not provide any control over the order in which the data is presented. This is
where the ORDER BY clause can be useful. With it, you can order your data in several ways, such as by supplier name,
or present the count values in ascending or descending order. For example, this command will display the supplier
transaction count list in reverse alphabetical order:
SELECT supplier, COUNT(*) FROM trade.rawtrans GROUP BY supplier ORDER BY supplier DESC ;
The DESC clause means “descending”; that is, the supplier names starting with Z will be at the top while those
starting with A will be at the bottom of the list.