• Post author:
  • Post category:Apache Hive
  • Post last modified:September 30, 2024
  • Reading time:8 mins read
You are currently viewing 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.

Advertisements

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

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

This Post Has One Comment

  1. Anonymous

    Nice and clear explanation, Thanks for the content. Please post more content in hive like SCD1, SCD2, SCD3 and hive performance tuning techniques so on…

Comments are closed.