. Advertisement .
..3..
. Advertisement .
..4..
Hive is one of, if not the most robust database manipulators in the world. However, it’s also among the most difficult to master, as it requires extensive knowledge.
That is why we prepare this guide on how to create database in Hive with examples. We all have to start somewhere, after all.
Create Database In Hive Syntax
The first step you need to take in order to master database manipulation in Hive is to create them. This challenge is actually quite manageable, as the syntax is among the simplest that Hive offers.
CREATE DATABASE [IF NOT EXISTS] <database_name>
As you can see, the main part of this syntax only contains two words and one input parameter. The input is your new database’s name, and you need to actually put in a valid name that doesn’t have a copy. Otherwise, you will get an error message as a result.
Let’s take a small example:
CREATE DATABASE temp
This sentence will prompt the creation of a new, empty database named temp in the Hive warehouse. However, if there is already another database with the same name there, Hive will tell you that “Database temp already exists”.
While you get the error message, sometimes this issue can lead to more troublesome problems. This case is especially dangerous in older versions. That is why you should always throw in the conditional statement [IF NOT EXISTS]
.
Despite producing the same result, the process is completely different when this statement is involved. The code will first check the warehouse and stop the process if it sees a duplicate. On the other hand, the base sentence will create the database first and then check.
Once the database creation process is complete, you can check it out with the SHOW DATABASE prompt. You can further manipulate this database with commands like INSERT INTO and INSERT OVERWRITE.
Of course, some people also want to know where Hive will be storing the newly created database. If you still use the default setting for hdfs, the storage location is at /user/hive/warehouse
.
You can change this location in the case of not having enough memory. All you need to do is put in a new parameter, LOCATION, and add where you want to store the file.
CREATE DATABASE temp LOCATION '/application/projects/hive/storage;’
Create Database In Hive From Java And Scala
It is possible to connect Hive with Scala and Java and start running them. In fact, many people resort to this method as they are much more familiar with how Java and Scala work compared to Hive.
You do need to set up something before you can do so, though. To be more specific, you must have the Hive JDBC connection string and hive-jdbc dependency.
We recommend installing the dependency from Maven, but you can also put into your pom.xml file this dependency:
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>3.1.2</version>
</dependency>
Here is the example in Java:
package com.ittutoria.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) {
}
}
}
}
Here is the example in Scala:
package com.ittutoria.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
This article encompasses all there is to know about how to create database in Hive. As long as you read through it carefully, grasping the specific conditions that the examples show, you will have no problem with this issue anymore.
Do not look down on this technique, as it serves as the basis for many advanced tricks later down the line.
Leave a comment