Creating and Managing Databases in Hive

Learn how to create and manage databases in Hive to organize your tables effectively. This guide covers creating new databases, listing existing databases, and best practices for organizing your Hive data warehouse.

Creating Databases in Hive

Understanding Hive Databases

In Hive, a database acts as a container or namespace for storing tables. You can organize your tables into multiple databases to improve management and prevent naming conflicts. Hive provides a default database named default.

Checking Existing Databases

Before creating a new database, it's a good idea to check what databases already exist:

hive> show databases;

This command will display a list of existing databases, including the default database.

Creating a New Database

To create a new database, use the following command. Replace demo with your desired database name:

hive> CREATE DATABASE demo;

After executing this command, a new database named demo will be created. Verify its creation by running show databases; again.

Handling Duplicate Database Names

Database names must be unique. Attempting to create a database with an existing name will result in an error. To prevent this error, use the IF NOT EXISTS clause:

hive> CREATE DATABASE IF NOT EXISTS demo;

This command will only create the database if it doesn't already exist.

Adding Properties to a Database

You can add properties to a database using key-value pairs:

hive> CREATE DATABASE demo
> WITH DBPROPERTIES ('creator' = 'Gaurav Chawla', 'date' = '2019-06-03');

Retrieving Database Information

To view a database's properties and other information:

hive> DESCRIBE DATABASE EXTENDED demo;