HiveQL
Select-Where
Syntax
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[HAVING having_condition]
[CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list]]
[LIMIT number];
Example
hive> SELECT * FROM employee WHERE salary>30000;
JDBC Program
HiveQLWhere
Select-Order By
Syntax
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[HAVING having_condition]
[ORDER BY col_list]]
[LIMIT number];
Example
hive> SELECT Id, Name, Dept FROM employee ORDER BY DEPT;
JDBC Program
HiveQLOrderBy
Select-Group By
Syntax
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[HAVING having_condition]
[ORDER BY col_list]]
[LIMIT number];
Example
hive> SELECT Dept,count(*) FROM employee GROUP BY DEPT;
JDBC Program
HiveQLGroupBy
Select-Joins
Syntax
join_table:
table_reference JOIN table_factor [join_condition]
| table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference
join_condition
| table_reference LEFT SEMI JOIN table_reference join_condition
| table_reference CROSS JOIN table_reference [join_condition]
Example
orders
hive> CREATE TABLE IF NOT EXISTS orders ( oid int, odate DATE, customer_id int, amount Double)
COMMENT 'Employee orders'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;
102 2009-10-08 1203 3000
100 2009-10-08 1203 1500
101 2009-11-20 1202 1560
103 2008-05-20 1204 2060
hive> LOAD DATA LOCAL INPATH '/home/hadoop/hive/orders.txt'
OVERWRITE INTO TABLE orders;
JOIN
hive> SELECT c.ID, c.NAME, c.salary, o.AMOUNT
FROM employee c JOIN ORDERS o
ON (c.ID = o.CUSTOMER_ID);
LEFT OUTER JOIN
hive> SELECT c.ID, c.NAME, o.AMOUNT, o.odate
FROM employee c
LEFT OUTER JOIN ORDERS o
ON (c.ID = o.CUSTOMER_ID);
RIGHT OUTER JOIN
hive> SELECT c.ID, c.NAME, o.AMOUNT, o.odate FROM employee c RIGHT OUTER JOIN ORDERS o ON (c.ID = o.CUSTOMER_ID);
FULL OUTER JOIN
hive> SELECT c.ID, c.NAME, o.AMOUNT, o.odate
FROM employee c
FULL OUTER JOIN ORDERS o
ON (c.ID = o.CUSTOMER_ID);