Handy hive cheatsheet

Hive_cheatsheet.md

CREATE DATABASE [IF NOT EXISTS] userdb;
CREATE SCHEMA userdb;

DROP DATABASE IF EXISTS userdb;
DROP DATABASE IF EXISTS userdb CASCADE;

CREATE TABLE IF NOT EXISTS employee ( eid int, name String,
salary String, destination String)
COMMENT ‘Employee details’
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘\t’
LINES TERMINATED BY ‘\n’
STORED AS TEXTFILE;

LOAD DATA LOCAL INPATH '/home/user/sample.txt'
OVERWRITE INTO TABLE employee;

ALTER TABLE employee RENAME TO emp;
ALTER TABLE employee CHANGE salary salary Double;
ALTER TABLE employee CHANGE name ename String;
ALTER TABLE employee ADD COLUMNS ( 
dept STRING COMMENT 'Department name');
ALTER TABLE employee REPLACE COLUMNS ( 
eid INT empid Int, 
ename STRING name String);

DROP TABLE IF EXISTS employee;

ALTER TABLE employee
> ADD PARTITION (year=’2013’)
> location '/2012/part2012';
ALTER TABLE employee DROP [IF EXISTS]
   > PARTITION (year=’1203’);
   
SELECT * FROM employee WHERE Salary>=40000;
SELECT * FROM employee WHERE Salary>40000 && Dept=TP;

SELECT ceil(2.6) from temp;

CREATE VIEW emp_30000 AS
SELECT * FROM employee
WHERE salary>30000;

CREATE INDEX inedx_salary ON TABLE employee(salary)
AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler';

SELECT Id, Name, Dept FROM employee ORDER BY DEPT;

SELECT c.ID, c.NAME, c.AGE, o.AMOUNT 
FROM CUSTOMERS c JOIN ORDERS o 
ON (c.ID = o.CUSTOMER_ID);

Ref: https://www.tutorialspoint.com/hive