5)Hive Data Definition Language(DDL)- Database

Hive Data Definition Language(DDL):

Create/Drop/Alter/Use Database: 

We will see the following below one by one

1. Create Database
2. Drop Database
3. Alter Database
4. use Database

1)Create Database
As we knew, from the basic, with the help of database names, users can have same tables name in different databases,

Syntax:

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
  [COMMENT database_comment]
  [LOCATION hdfs_path]
  [WITH DBPROPERTIES (property_name=property_value, ...)];

Usage of arguments:

• IF NOT EXISTS – optional but recommended to use, so that, if a database with same name already exists, will not try to create it again and will not show any error message.
•  COMMENT – also optional. It can be used for short description to database
•  LOCATION – also optional. By default all the hive databases will be created under default warehouse directory (set by the property hive.metastore.warehouse.dir) as /user/hive/warehouse/database_name.db . But if we want to specify our own location then this option can be specified.
• DBPROPERTIES – Optional but used to specify any properties of database in the form of (key, value).

Example:

hive (default)> CREATE DATABASE IF NOT EXISTS Mano_db COMMENT "contains_tech_data" LOCATION '/MANO/hive_warehouse_' WITH DBPROPERTIES('NAME'='Mano','Email'='manosoftware1@gmail.com');

2)Drop Database:

Syntax:

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

Usage of arguments:

 

• RESTRICT - The default behavior is RESTRICT, where DROP DATABASE will fail if the database is not empty.
• CASCADE -  To drop the tables in the database as well.

Using RESTRICT(default behavior):

hive (mano_db)> drop database if exists mano_db RESTRICT;

Using CASCADE:
hive (mano_db)> drop database if exists mano_db CASCADE;

3)Alter Database:
With alter, below operations are allowed.

• Assign any new (key, value) pairs into DBPROPERTIES
• Set owner user or role to the Database
• Set the location of database on HDFS

Syntax:

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);

ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;

ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path;

Examples:

1)Alter DBPROPERTIES:hive (mano_db)> ALTER DATABASE MANO_DB SET DBPROPERTIES

('Email'='manomistry@gmail.com'); 

2)Alter OWNER [USER|ROLE]

Before Alter:

After Alter:
hive (mano_db)> ALTER DATABASE MANO_DB set OWNER USER Govind;

3)Alter LOCATION:

Note:

• Not allowed to alter the directory location or database name.
• Not allowed to delete or unset the db properties which have been defined before.

4)Use Database:

Syntax:

USE database_name;
USE DEFAULT;

Previous page                                                                                                              Next page