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.