Hadoop---Hive的基本操作

创建数据库(使用SCHEMA方式)
CREATE SCHEMA userdb;

创建数据库
CREATE DATABASE userdb;

创建数据库（存在则不创建，不存在则创建）
CREATE DATABASE IF NOT EXISTS userdb;

列出数据库列表
SHOW DATABASES;

删除数据库
DROP DATABASE IF EXISTS userdb;

删除数据库(全部删除相应的表在删除数据库之前)
DROP DATABASE IF EXISTS userdb CASCADE;

删除数据库(使用SCHEMA方式)
DROP SCHEMA userdb;

创建表
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;

修改表名
ALTER TABLE employee RENAME TO emp;

修改列名和列数据类型
ALTER TABLE emp CHANGE name ename String;

增加列（列名为dept，类型为String）
ALTER TABLE emp ADD COLUMNS(
dept STRING COMMENT 'Department name');

删除列
ALTER TABLE employee DROP COLUMN destination;

使用empid代替eid列，name代替ename列
ALTER TABLE emp REPLACE COLUMNS(
eid INT empid Int,
ename STRING name String);

删除表
DROP TABLE IF EXISTS emp;

查看所有表
SHOW TABELS;

smple.txt
1201    Gopal    45000    Technical    manager
1202    Manisha    45000    Proof    reader
1203    Masthanvali    40000    Technical    writer
1204    Kiran    40000    Hr    Admin
1205    Kranthi    30000    Op    Admin

插入数据(将文本插入表中)
LOAD DATA LOCAL INPATH '/home/hadoop/smple.txt'
OVERWRITE INTO TABLE employee;

插入数据
insert into employee values(102,'ss','ddd','dd');

删除数据

查询一条数据
select * from employee where eid=1204;

查询所有数据
select * from employee;

创建视图（为工资超过30000的创建一个视图）
CREATE VIEW emp_30000 AS
SELECT * FROM employee 
WHERE salary>30000;

查看视图
select * from emp_30000;

删除视图
DROP VIEW emp_30000;

创建索引（对salary列创建一个索引）
CREATE INDEX index_salary ON TABLE employee(salary) 
AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
with deferred rebuild
IN TABLE index_salary_employee;

删除索引(删除index_salary索引)
DROP INDEX index_salary ON employee;

升序排列
SELECT eid, name FROM employee ORDER BY name ASC;

降序排列
SELECT eid, name FROM employee ORDER BY name DESC;

分组
SELECT name, count(*) FROM employee GROUP BY name;

连接查询

JOIN（内连接）
SELECT c.id, c.name, c.age, o.amount
FROM CUSTOMERS c JOIN ORDERS o
ON (c.id = o.CUSTOMERS); 

LEFT OUTER JOIN（左外连接）
SELECT c.id, c.name, c.age, o.amount
FROM CUSTOMERS c 
LEFT OUTER JOIN ORDERS o
ON (c.id = o.CUSTOMERS); 

RIGHT OUTER JOIN（右外连接）
SELECT c.id, c.name, c.age, o.amount
FROM CUSTOMERS c 
RIGHT OUTER JOIN ORDERS o
ON (c.id = o.CUSTOMERS); 

FULL OUTER JOIN（全连接）
SELECT c.id, c.name, c.age, o.amount
FROM CUSTOMERS c 
FULL OUTER JOIN ORDERS o
ON (c.id = o.CUSTOMERS);