Working with databases in Apache Hive

In this article We will learn how to work on databases in Apache Hive. We will learn how to create, drop, change and use database in Apache Hive.

1) Check existing databases.

We check existing databases in Hive using show databases command. Apache Hive comes with a database called default.

Command :

show databases;

2) Creating a new database;

We can create a new database in Apache Hive using create command.

Command syntax:

create database [if not exist] {database-name};

3) Switching databases

By default , Queries will be run on default databases. If we want to run a query on different database, We have to change the current database.

We use use command to change the current database in Apache Hive.

Command :

use test;

The picture below changes current database to test database and creates a new table called dummy in test database.

4) Drop database

We can drop databases using drop command. We need to drop all tables in the database before droping the database. Otherwise we get database is not empty, one or more tables exist  error.

Command :

drop database test;

The picture below shows droping databse called test after deleting it's table dummy.

5) Using database name in a hive query

If we do not specify any database name in hive query, Hive runs it on current database.

We have to prefix database name to table name if we want to run the hive query on a particular database.

The query below runs on test database.

Select count(*) from test.dummy;