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