Hive and HiveQL: SQL on Hadoop Explained

Data Types

The following is the type of data that Hive offers

• Numeric : TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL
• String : STRING, VARCHAR, CHAR
• Date and time types : TIMESTAMP, DATE
• Boolean type : BOOLEAN(TRUE/FALSE)
• Binary types : BINARY

Operators

The following is the operators that Hive offers

• Relational Operator : =, !=, , >=, IS (NOT) NULL, (R)LIKE, REGEXP
• Arithmetic Operator : +, -, *, /, %, &, |, ^, ~
• Logical Operator : AND, &&, OR, ||, NOT, !

Functions

• The following is the functions that Hive offers
• Aggregation Function : count( ), sum( ), avg( ), min( ), max( )
• Mathematical Function : round( ), floor( ), ceil( ), rand( )
• String Function : concat( ), substr( ), upper( ), lower( ), (l/r)trim( )
• Date/Time Function : to_date( ), year( ), month( ), day( )

Query

The following is the example of queries on HiveQL. In this example, it represents the query of SELECT…WHERE , SELECT…ORDER BY, SELECT…GROUP BY, JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.

SELECT * FROM employee WHERE salary>30000; hive> SELECT Id, Name, Dept FROM employee ORDER BY DEPT; hive> SELECT Dept,count(*) FROM employee GROUP BY DEPT; hive> SELECT c.ID, c.NAME, c.AGE, o.AMOUNT > FROM CUSTOMERS c JOIN ORDERS o > ON (c.ID = o.CUSTOMER_ID); hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE > FROM CUSTOMERS c > LEFT OUTER JOIN ORDERS o > ON (c.ID = o.CUSTOMER_ID); hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE > FROM CUSTOMERS c > RIGHT OUTER JOIN ORDERS o > ON (c.ID = o.CUSTOMER_ID); hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE > FROM CUSTOMERS c > FULL OUTER JOIN ORDERS o > ON (c.ID = o.CUSTOMER_ID);

Partitioning

Hive consists of table partitions. It is the way to divide a table based on the value of column such as date, city and department. The partition helps to get query faster. For more efficient of query, a table or partition is sub-divided to buckets and bucketing works based on hash function value on a part of table column. For example, let’s suppose that you need a detail information for every-employee who is joining the company on 2012 from a dataset that has Tab1 table that is including id, name, dept, yoj(tear of joining). If you save employees information that has yoj separately on a different file, the time of query would be decreased. The following is the example of the way to divide the data and file.

The example file includes an employee data table.

/tab1/employeedata/file1 id, name, dept, yoj 1, gopal, TP, 2012 2, kiran, HR, 2012 3, kaleel,SC, 2013 4, Prasanth, SC, 2013

It divides two files using year column.

/tab1/employeedata/2012/file2 1, gopal, TP, 2012 2, kiran, HR, 2012 /tab1/employeedata/2013/file3 3, kaleel,SC, 2013 4, Prasanth, SC, 2013 

hive> ALTER TABLE employee > ADD PARTITION (year=’2013’) > location '/2012/part2012'; hive> ALTER TABLE employee PARTITION (year=’1203’) > RENAME TO PARTITION (Yoj=’1203’); hive> ALTER TABLE employee DROP [IF EXISTS] > PARTITION (year=’1203’);