By | March 2, 2016

Hive Database Example

I hope everyone is aware about the hive basic things, like why it is useful and how it works, in this post we will discuss about Hive Database example and concepts, I have divided this post into parts so please find other part links at the end of this post.

Hive Database

We called it catalog of table, normally in production we use databases to manage and organized tables into logical structures, in Hive if we are not specifying any database name before table name then default database will be used, default is a hive’s default database

Now let’s see the syntax how to create database in hive


By using IF NOT EXISTS we can ignore these warnings also there is alternative word for DATABASE like SCHEMA

How to see existing databases into Hive


This command will display all the preexisting databases from hive.

Let’s say we have long list of databases into hive and we only interested in particular database so in that case we can use regular expression and example for this is below


So above command will display the databases which start with the letter d

Additionally, hive table creates a separate directory for each database so in this case if we create any table by using any database then for the tables subdirectories will be created in that database directory, the default hive database directory is created by referring hive.metastore.warehouse.dir, it’s available in hive-site.xml file and the default location is /user/hive/warehouse

How to create database by passing user given path

CREATE DATABASE toodey LOCATION ‘/user/path/’

How to add comments while creating hive database

CREATE DATABASE toodey COMMENT ‘Toodey is my name’

How to see the structure of particular database or describe it


This command will also show the additional details about the database like its path

How to use the database in Hive, the syntax looks similar to normal sql only

USE toodey;

How to delete Hive database or drop it


Here we need to use CASCADE keyword at the end of the command because hive won’t allow to delete database directly so in this case if we ignore CASCADE keyword then we manually need to delete all tables from that respective database before deleting actual database.

How could we alter any properties into existing table, in hive alter command is also available, let’s say we need to add some DBPROPERTIES to existing database for this command would be

ALTER DATABASE toodey SET DBPROPERTIES (‘date’ = ’03-03-2016’);

In the next part we will discuss about HIVE TABLES, so subscribe for next update and enjoy my site

Leave a Reply

Your email address will not be published. Required fields are marked *