Hive Basic Important command

Start Hive Metastore

1	hive --service metastore

Create Database

1	hive> CREATE DATABASE IF NOT EXISTS emp;

SHOW DATABASES

1 2 3 4 5

hive> SHOW DATABASES; OK default emp Time taken: 0.059 seconds, Fetched: 2 row(s)

Use Database

1	hive>USE emp;

Describe Database

1 2	hive>DESCRIBE DATABASE emp; hive>DESCRIBE SCHEMA emp;

Drop Database

1 2	hive>DROP DATABASE emp; hive>DROP DATABASE emp CASCADE;

Hive DDL Table Commands

1 2 3

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

1 2	SHOW TABLES; // This shows all tables from the current database SHOW TABLES in emp; // This shows all tables in the emp database

Describe Table

1 2	hive> DESCRIBE FORMATTED employee; hive> DESCRIBE EXTENDED employee;

Truncate Table

1	hive>TRUNCATE TABLE emp;

Alter Table

1	hive>ALTER TABLE employee RENAME TO employee2;

Drop Table

1 2 3

hive>DROP TABLE employee2; hive>DROP TABLE emp.employee2; hive>DROP TABLE IF EXISTS employee2 PURGE;

Internal Tables

1 2 3 4 5 6 7 8

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

1 2 3 4 5 6 7 8

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

1	INSERT INTO TABLE emp.employee VALUES ('Dikshant',1,'95'),('Akshat', 2 , '96'),('Dhruv',3,'90');

2. Load Data Statement

1	LOAD DATA LOCAL INPATH '/home/dikshant/Documents/data.csv' INTO TABLE student;

Insert overwrite Directory

1 2 3

INSERT OVERWRITE DIRECTORY '/output/data_delimited' SELECT * FROM data_schema.data_table