How to create a database at specific location in Hive?

Create database in Hive

The database is an organised collection of tables.Hive has the default database with the name as “default“. If we do not specify the database name while creating the table, it will be created in the default database. The default location is /apps/hive/warehouse/<table_name>.

If we want to create custom database, we need to run the create database command as below. It will create the database in the following location /apps/hive/warehouse/<database_name>.db

Example

Let’s create a database in Hive and find the HDFS location of that database.

As we mentioned, we have created the database as seller_offers in Hive. In order to find the location of that database, we need to run the describe database command as below.

In the output, we can see the location column that has HDFS directory name. Here test_hdp_cluster is a Hadoop cluster name. Followed by cluster name, it is showing the database location as /apps/hive/warehouse/seller_offers.db

Create database at specific location in Hive

Create database command can creates a database at specific location as well. For that, we need to give the location property in the create database command. Please follow the syntax as mentioned below

Example

Lets create a database sells_report in the following HDFS location /apps/seller/datamart/sells_report.

The database sells_report is created in the given directory. On this database, we can create all the sells report related tables. While creating the tables, we need to mention this location in the create table statement. Sot that the data will be stored under the sells_report database location.

As we shown in the create table statement, we have used <database_name>.<table_name> to create the table on specific database. Next we have mentioned the location as database location with table name.

  • Database directory is /apps/seller/datamart/sells_report
  • Table directory is /apps/seller/datamart/sells_report/micro_seller_report

The create table statement is created another directory micro_seller_report under the database location. If we insert the records into this table, the data will be stored in a table directory. Similarly we can create other tables under the database location.