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.
