Hive – Create Database Examples

Using CREATE DATABASE statement you can create a new Database in Hive, like any other RDBMS Databases, the Hive database is a namespace to store the tables.

In this article, I will explain how to create a database, its syntax, and usage with examples in Hive shell, Java, and Scala languages.

Hive by default, contains a default database, you can get this using SHOW DATABASES; statement.

Note: When running SQL statements on the Hive or Beeline shell terminal, the statement should end with a semicolon.

Hive Create Database Syntax

Hive’s CREATE DATABASE statement is used to create a new database within the Hive environment. This statement allows users to define a database name and optional properties such as location, comment, and other settings.

Using the optional clause IF NOT EXISTS ensures that the database is only created if it does not already exist and use WITH DBPROPERTIES clause to specify additional properties for the database.

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

Note: Creating a database with an already existing name in a database returns an error hence it is recommended to use IF NOT EXISTS

Create a Database from the Hive Beeline shell

Use the below command to create a database.

jdbc:hive2://> CREATE DATABASE emp;
OK
Time taken: 0.929 seconds
jdbc:hive2://>

Make sure the database you are creating doesn’t exist on Hive warehouse, if it exists, it throws Database emp already exists error.

jdbc:hive2://> CREATE DATABASE emp;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Database emp already exists

If you wanted to check if the database already exists before creating, use IF NOT EXISTS clause.

jdbc:hive2://> CREATE DATABASE IF NOT EXISTS emp;
OK
Time taken: 0.063 seconds
jdbc:hive2://>

Hive by default creates a directory with a database name .db extension (for example emp.db) under its warehouse directory. By default warehouse directory located is /user/hive/warehouse on hdfs.

prabha@prabha:~$ hdfs dfs -ls /user/hive/warehouse
Found 1 items
drwxr-xr-x   - prabha supergroup          0 2020-10-03 21:30 /user/hive/warehouse/emp.db

You can change the location of the database where to create by using any of the below commands

jdbc:hive2://>CREATE DATABASE temp LOCATION '/apps/project/hive/warehouse';

You can also change the default location using hive.metastore.warehouse.dir

jdbc:hive2://>set hive.metastore.warehouse.dir='/apps/project/hive/warehouse';
jdbc:hive2://>CREATE DATABASE temp;

Show Database

In order to show the existing databases on Hive, use the SHOW DATABASES command.

jdbc:hive2://>> SHOW DATABASES;
OK
default
emp
Time taken: 0.059 seconds, Fetched: 2 row(s)

Creating a Database from Java & Scala

Let’s explore how to create a database using Java and Scala programs. To connect and execute Hive SQL statements, you’ll require the hive-jdbc dependency and a Hive JDBC connection string. You can obtain the dependency from Maven or include the following dependency in your pom.xml file.

<dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-jdbc</artifactId>
    <version>3.1.2</version>
</dependency>

Below are complete Java and Scala examples of how to create a Database.

In the below Java and Scala examples, I am connecting to the Hive default database and creating a new Hive database emp.

Java Example

package com.sparkbyexamples.hive;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;

public class HiveCreateDatabase {
	public static void main(String[] args) {
		Connection con = null;
		try {
			String conStr = "jdbc:hive2://192.168.1.148:10000/default";
			Class.forName("org.apache.hive.jdbc.HiveDriver");
			con = DriverManager.getConnection(conStr, "", "");
			Statement stmt = con.createStatement();
			stmt.executeQuery("CREATE DATABASE emp");
			System.out.println("Database emp created successfully.");
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				if (con != null)
					con.close();
			} catch (Exception ex) {
			}
		}
	}
}

Scala Example

package com.sparkbyexamples.hive;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;

object HiveCreateDatabase extends App{
	var con = null;
	try {
		val conStr = "jdbc:hive2://192.168.1.148:10000/default";
		Class.forName("org.apache.hive.jdbc.HiveDriver");
		con = DriverManager.getConnection(conStr, "", "");
		val stmt = con.createStatement();
		stmt.executeQuery("CREATE DATABASE emp");
		System.out.println("Database emp created successfully.");
	} catch (Exception ex) {
		ex.printStackTrace();
	} finally {
		try {
			if (con != null)
				con.close();
		} catch (Exception ex) {
		}
	}
}

Conclusion

In this article, you have learned to create a Database using hive shell, java, Scala program and also learned it by default it stores a database at /user/hive/warehouse directory path and finally learned how to change the default location path.

Happy Learning !!

Related Articles

• What are the Different Types of Tables present in Apache Hive
• Hive Create Partition Table Explained
• Hive Create Table Syntax & Usage with Examples
• Hive Temporary Table Usage And How to Create?
• Hive – Create Database from Scala Example
• Hive – Create Database from Java Example
• Hive Relational | Arithmetic | Logical Operators
• Hive Data Types – Primitive | Complex
• Hive Collection Functions with Examples