Hive Alter Database

The ALTER DATABASE command in Hive is used to change the metadata of an existing database. Metadata means the information about the database, not the actual data inside the tables. One important thing to remember is that ALTER DATABASE does not change the data in the tables.

By using ALTER DATABASE, you can:

• Assign ownership to the right person or team.
• Add notes or descriptions about the database’s purpose.
• Keep your Hive environment clean, organized, and easy to manage.

For example, you can change the database's owner, set or update properties like who created it, when it was created, or any other custom note you want to add.

Why do we need ALTER DATABASE in Hive?

In big companies, it's essential to keep track of who is responsible for which database, when it was created, and what it is used for. This is where ALTER DATABASE becomes very handy.

For example, you may want to transfer the ownership of a database to a new team member who is now responsible for it. Or you may want to add a property that says, "This database is for the Sales Team only".

Syntax -

Hive provides an easy-to-remember way to use the ALTER DATABASE command. Here are the common things you can do with it:

Set Database Properties:

ALTER DATABASE database_name 
	SET DBPROPERTIES ('property_name' = 'property_value');

Change Database Owner to a User:

ALTER DATABASE database_name 
	SET OWNER USER user_name;

Change Database Owner to a Role:

ALTER DATABASE database_name 
	SET OWNER ROLE role_name;

In terms of behavior, DATABASE and SCHEMA are interchangeable. The SCHEMA option in ALTER was introduced in Hive 0.14.0.

Examples -

Scenario1: Adding Database Properties. Let’s assume you have a database called employee_db and you want to mention who created it.

ALTER DATABASE employee_db 
	SET DBPROPERTIES ('created_by' = 'Pawan Kumar');

This command will not touch any tables or data. It just updates the property saying Pawan Kumar created this database.

Scenario2: Adding Multiple Properties. We may want to mention the purpose of the database and the team responsible for it.

ALTER DATABASE employee_db SET DBPROPERTIES (
  'created_by' = 'Admin',
  'purpose' = 'Employee Management',
  'department' = 'Human Resources'
);

Now, whenever someone checks this database, they will easily know what it’s for and who is in charge.

Scenario3: Changing the Database Owner. Sometimes, the ownership of a database needs to change. This happens when someone leaves the project or when a new team takes over.

ALTER DATABASE employee_db 
	SET OWNER USER hr_manager;

Or, if the ownership should belong to a team role instead of an individual user:

ALTER DATABASE employee_db 
	SET OWNER ROLE hr_team;

Changing the owner helps manage permissions better, especially when working with large teams.

How to check database properties after ALTER?

After altering the database, you probably want to see if your changes were successful. You can do this with the DESCRIBE DATABASE EXTENDED command.

DESCRIBE DATABASE EXTENDED employee_db;

This will show you the database name, its description, where it’s located, who owns it, and all the DBPROPERTIES you added.

---