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

JOIN
(
SELECT DISTINCT
d.supplier,d.amount
FROM
trade.rawtrans d
) b
ON ( a.supplier = b.supplier ) ;
The INSERT Statement
You can use the INSERT statement to add rows to your table. However, it's a good idea to check the structure of your
table before you consider inserting data. To check the trade.suppliertot table, for example, I would use the DESCRIBE
command:
hive> DESCRIBE trade.suppliertot ;
OK
payyear int
paymonth int
supplier string
totamount double
Time taken: 0.317 seconds
This shows that my table has two integer columns named “payyear” and “paymonth,” followed by a string column
named “supplier” and a double (real) column named “totamount.” So, the table has four columns. I would then check
to see what university suppliers exist in the data by using the following SELECT statement:
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
So, I have used a SELECT statement to select all ( * ) columns from the table called “suppliertot” in the database
named “trade.” I added a WHERE clause that searches for suppliers whose name starts with the word “UNIVERSITY”.
This shows me that there are two such rows in the table. Therefore, there are two ways to change the data in this table:
the first is to load data from the HDFS file system, and the second is to insert rows from a SELECT statement.
If I should decide to use the second approach, the following statement inserts rows into the trade.suppliertot
table from the SELECT statement on rows 2 and 3 below. Notice that the WHERE clause is the same as that above, so it
is the two UNIVERSITY rows that will be affected. Notice also that a combination of table rows and hard-coded values
have been selected. The payyear and paymonth values have been selected from the table, while the hard-coded values
'UNIVERSITY OF SEMTECH' and 700.0 have been set in column positions 3 and 4 of the SELECT statement:
INSERT INTO TABLE trade.suppliertot
SELECT payyear,paymonth,'UNIVERSITY OF SEMTECH',700.0 FROM
trade.suppliertot WHERE supplier LIKE 'UNIVERSITY%' ;