Blog

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

Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.