Hadoop Hive Create, Drop, Alter, Use Database Commands and Examples

  • Post author:
  • Post last modified:February 27, 2018
  • Post category:BigData
  • Reading time:3 mins read

Hadoop Hive is database framework on the top of Hadoop distributed file systems (HDFS) developed by Facebook to analyze structured data. It supports almost all commands that regular database supports. Hadoop hive create, drop, alter, use database commands are database DDL commands. This article explains these commands with an examples.

Hadoop Hive Create, Drop, Alter, Use Database Commands

Hive contains a default database named default.

Read:

Hadoop Hive SHOW DATABASES commds

This command displays all the databases available in Hive.

Below is the example of using show database command:

hive> show databases;
OK
default
test_db

Hadoop Hive Create Database Command

Hadoop Hive create database is a statement used to create a databases. You can compare Hive database as a namespace in HBase. It is a collection of various tables.

The syntax for Hive create database statement is as follows:

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

The uses of SCHEMA and DATABASE are interchangeable in Hive – they mean the same thing

Below is the example of create database in Hive:

hive> create database test_db;
OK
Time taken: 0.272 seconds

Hadoop Hive USE Database Command

USE command sets the current database for all subsequent HiveQL query statements.

Below is the example of USE database command in Hive:

hive> use test_db;
OK
Time taken: 0.225 seconds

hive> use default;
OK
Time taken: 0.222 seconds

To revert to the default database, use the keyword name “default” instead of a database name.

Hadoop Hive Alter Database Command

Hadoop Hive alter database is a statement used to change the properties of a databases in Hive. You can add or remove the database comments, properties etc by using alter database statements.

The syntax for Hive alter database statement is as follows:

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;

Below is the example of alter database in Hive:

hive> ALTER DATABASE test_db SET DBPROPERTIES ('Date' = '2017-10-12');
OK
Time taken: 0.473 seconds

Hive Drop Database Command

Hadoop Hive drop database is a statement used to drop the databases in Hive.

The syntax for Hive drop database statement is as follows:

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

The default behaviour is RESTRICT, where DROP DATABASE will fail if the database is not empty. To drop the tables in the database as well, use DROP DATABASE … with CASCADE option.

Below is the example of drop database in Hive:

hive> drop database test_db;
OK
Time taken: 1.474 seconds

Hadoop Hive Rename Database Command

As of current version Hadoop Hive does not supports rename of existing database.

Tags: