Usage of ALTER in Hadoop Hive

Alter is a DDL command which helps in modifying the structure of the database objects. We can change the user provided properties of the database, structure of the tables and name of the objects.

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'= 'UserName', 'Created’= ’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";