Summary -

In this topic, we described about the Alter Database with detailed example.

Default database cannot be altered as it creates by system as a default. A user defined database can be altered according to the user requirements. Altering database can be done on any user defined database at any point of time, but the database should be successfully created. ALTER command is used to alter the database and syntax for altering would be like below.

Syntax -

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES
 (property_name=property_value, ...);  
ALTER (DATABASE|SCHEMA) database_name SET OWNER 
[USER|ROLE] user_or_role;   

Mostly ALTER can be used to alter database or schema properties and owner change. The database and schema are interchangeable. DATABASE or SCHEMA is same thing in behavior. SCHEMA in ALTER added in Hive 0.14.0.

Below example describes about to change the user role to admin in database.

Hive> ALTER DATABASE std_db SET OWNER user1 ROLE admin

Below example describes about to change the user role to admin in schema.

Hive> ALTER SCHEMA newsch SET OWNER user1 ROLE admin