How To Create Hive Database

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.

How to start HiveServer2 & Connect Beeline
Where does Hive store data files in HDFS?

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

Note: when you running SQL statements on Hive or beeline shell terminal, the statement should end with a semicolon.

Hive Create Database Syntax

In Hive, CREATE DATABASE statement is used to create a Database, this takes an optional clause IF NOT EXISTS, using this option, it creates only when database not already exists.

                      CREATE DATABASE [IF NOT EXISTS] <database_name>

Note: Creating a database with already existing name in a database returns an error.

Create Database from Hive Beeline shell

                      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 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.

                      [email protected]:~$ 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 see how to create a Database from Java & Scala program, In order to connect and run Hive SQL you need to have hive-jdbc dependency and Hive JDBC connection string. You can download dependency from Maven or use the below dependency on your pom.xml.

                      <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.

Note: If you are using an older version of Hive, you should use the driver org.apache.hadoop.hive.jdbc.HiveDriver and your connection string should be jdbc:hive://

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 creating a Database using hive shell, java, Scala program and also learned it by default stores a database at /user/hive/warehouse directory path and finally learned how to change the default location path.

Happy Learning !!