COMPLETE DDL Commands in Hive

This article is to cover all the DDL commands in Hive.

 

CREATE

DATABASE LEVEL:

To create the database with properties

CREATE DATABASE TestDB

WITH dbProperties(‘Creator: ‘= ‘Username’, ‘Created-Date:’ = ‘01-01-2020’);

 Or we can just create the database without any properties.

CREATE DATABASE TestDB;

 

TABLE LEVEL:

Creating a table in HIve with different type of data types.

CREATE TABLE Emp(EmpID INT, Ename string, ESal float, DeptNo INT)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ‘,’;

Or we can simply create table without delimitation specification. 

CREATE TABLE Sample(Line string);

Creating an external table

CREATE EXTERNAL TABLE TestTable

(test_id INT, col2 INT, col3 STRING)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ‘,’

LOCATION ‘/user/hive/warehouse/datadirectory’;

 
ALTER

Database Level:

Database Properties:

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);

This helps in modifying the database properties which user has provided at the time of database creation.

Example:

ALTER DATABASE TestDB SET dbProperties(‘Edited-by ‘= ‘UserName’, ‘Created-On’=’2020-08-01’);

Database Access:

ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;

This command helps in modifying the access level to a different user or a role.

Example:

ALTER DATABASE TestDB SET OWNER USER cloudera;

 

Database Location:

ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path;

Though it is documented that the database location can be changed, however it is not allowing to do so in Hive 1.1.0 in CDH 5.10.0

 

Database Name:

ALTER DATABASE test_db RENAME TO test_db_new;

Though this is also documented but renaming a database is not allowed in Hive 1.1.0 in CDH 5.10.0

Note: As per the link here, there is no functionality to rename the existing database however there is a workaround to rename the database if you have necessary privileges on Hive metastore without changing the database location.

 

TABLE LEVEL:

Renaming A Table:

ALTER TABLE [old_db_name.]old_table_name RENAME TO [new_db_name.]new_table_name;

Example: ALTER TABLE tbOutput RENAME TO tbResults

 

Adding Columns of an Existing Table:

ALTER TABLE name ADD COLUMNS (col_spec[, col_spec ...]);

Example: ALTER TABLE test ADD COLUMNS (Col2 string, Col3 int)

 

Change Column Type

ALTER TABLE name CHANGE column_name new_name new_type;

Example: ALTER TABLE test CHANGE col2 col2 int;

 

Change Column Type and Name

ALTER TABLE name CHANGE column_name new_name new_type;

Example: ALTER TABLE test CHANGE col2 col4 string;

 

Replace Columns in the Table:

ALTER TABLE name REPLACE COLUMNS (col_spec[, col_spec ...]);

The above command used to remove all existing columns and adds the new set of columns. REPLACE is used when you want to have an altogether different columns to your table.

Example: ALTER TABLE test REPLACE COLUMNS (id int, col2 int, col3 string)

 

Change Table’s Location:

ALTER TABLE [DATABASE] SET LOCATION [NewLocation]

Example: ALTER TABLE dbtest SET LOCATION "hdfs:/DB/dbTest";

 

DROP

DATABASE LEVEL

DROP DATABASE TestDB;

By default, the mode is RESTRICT which blocks the deletion of database if it holds tables. Hence use ‘Cascade’ to drop the database even it has tables.

DROP DATABASE TestDB CASCADE;

To drop the database if exists and even it contains tables

DROP DATABASE IF EXISTS TestDB CASCADE;

To avoid dropping the database which has tables.

DROP DATABASE IF EXISTS TestDB RESTRICT;

 

TABLE LEVEL

DROP TABLE tbTest;

 

RENAME

Refer to ‘Alter’ commands.

 

TRUNCATE

Truncate table <tablename>

Example: TRUNCATE TABLE tbTest;

 

LIST OUT THE DATABASES OR TABLES

SHOW DATABASES;

DESC TestDB;

 

DESCRIBE DATABASE EXTENDED TestDB;

Or you can use

DESC DATABASE EXTENDED TestDB;

SHOW TABLES;

Hope you find this article helpful.