- 11 Jun, 2019
- 0 Comments
- 1 Min Read
Hive Basic Important command
Start Hive Metastore
hive --service metastore
Create Database
hive> CREATE DATABASE IF NOT EXISTS emp;
SHOW DATABASES
hive> SHOW DATABASES; OK default emp Time taken: 0.059 seconds, Fetched: 2 row(s)
Use Database
hive>USE emp;
Describe Database
hive>DESCRIBE DATABASE emp; hive>DESCRIBE SCHEMA emp;
Drop Database
hive>DROP DATABASE emp; hive>DROP DATABASE emp CASCADE;
Hive DDL Table Commands
CREATE TABLE IF NOT EXISTS emp.employee ( id int, name string, age int, gender string ) COMMENT 'Employee Table' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
Show Tables
SHOW TABLES; // This shows all tables from the current database SHOW TABLES in emp; // This shows all tables in the emp database
Describe Table
hive> DESCRIBE FORMATTED employee; hive> DESCRIBE EXTENDED employee;
Truncate Table
hive>TRUNCATE TABLE emp;
Alter Table
hive>ALTER TABLE employee RENAME TO employee2;
Drop Table
hive>DROP TABLE employee2; hive>DROP TABLE emp.employee2; hive>DROP TABLE IF EXISTS employee2 PURGE;
Internal Tables
CREATE TABLE IF NOT EXISTS emp.employee ( id int, name string, age int, gender string ) COMMENT 'Employee Table' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
External Table
CREATE EXTERNAL TABLE emp.employee_external ( id int, name string, age int, gender string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/user/hive/data/employee_external';
Hive – Load Data Into Table
In hive with DML statements, we can add data to the Hive table in 2 different ways.
- Using INSERT Command
- Load Data Statement
INSERT INTO TABLE emp.employee VALUES ('Dikshant',1,'95'),('Akshat', 2 , '96'),('Dhruv',3,'90');
2. Load Data Statement
LOAD DATA LOCAL INPATH '/home/dikshant/Documents/data.csv' INTO TABLE student;
Insert overwrite Directory
INSERT OVERWRITE DIRECTORY '/output/data_delimited' SELECT * FROM data_schema.data_table