This is the fourth post in the series about Hive, this article let’s use learn about how to manipulate the database in Hive ^^
1. Create database
Hive is a database technology that can identify databases and tables for structured data analysis. The topic for structured data analysis is to store data in a tabular manner and to transfer queries to analyze data. Hive contains a default database called default.
A database in Hive is a namespace or a collection of tables. The syntax is as follows:
1 2 | CREATE DATABASE|SCHEMA [IF NOT EXISTS] <database name> |
Here, IF NOT EXISTS is an optional clause, informing the user that a database with the same name already exists. We can use SCHEMA instead of DATABASE in this command. The following query is executed to create a database named userdb:
1 2 | hive> CREATE DATABASE [IF NOT EXISTS] userdb; |
or
1 2 | hive> CREATE SCHEMA userdb; |
The following query is used to verify the database listing:
1 2 3 4 | hive> SHOW DATABASES; default userdb |
JDBC Program
JDBC to create the database is given below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | import java.sql.SQLException; import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; import java.sql.DriverManager; public class HiveCreateDb { private static String driverName = "org.apache.hadoop.hive.jdbc.HiveDriver"; public static void main(String[] args) throws SQLException { // Register driver and create driver instance Class.forName(driverName); // get connection Connection con = DriverManager.getConnection("jdbc:hive://localhost:10000/default", "", ""); Statement stmt = con.createStatement(); stmt.executeQuery("CREATE DATABASE userdb"); System.out.println(“Database userdb created successfully.”); con.close(); } } |
Save the program in a file named HiveCreateDb.java. The following commands are used to compile and execute this program.
1 2 3 | $ javac HiveCreateDb.java $ java HiveCreateDb |
Output:
1 2 | Database userdb created successfully. |
2. Delete the database
Drop Database is a command that removes all tables and deletes the database. Its syntax is as follows:
1 2 3 | DROP DATABASE StatementDROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE]; |
The following queries are used to delete the database. Let’s assume that the database name is userdb.
1 2 | hive> DROP DATABASE IF EXISTS userdb; |
The following query removes the database using CASCADE. It means dropping the corresponding tables before dropping the database.
1 2 | hive> DROP DATABASE IF EXISTS userdb CASCADE; |
The following queries remove the database using SCHEMA.
1 2 | hive> DROP SCHEMA userdb; |
This statement has been added in Hive 0.6.
JDBC Program
JDBC program to remove the database is given below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | import java.sql.SQLException; import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; import java.sql.DriverManager; public class HiveDropDb { private static String driverName = "org.apache.hadoop.hive.jdbc.HiveDriver"; public static void main(String[] args) throws SQLException { // Register driver and create driver instance Class.forName(driverName); // get connection Connection con = DriverManager.getConnection("jdbc:hive://localhost:10000/default", "", ""); Statement stmt = con.createStatement(); stmt.executeQuery("DROP DATABASE userdb"); System.out.println(“Drop userdb database successful.”); con.close(); } } |
Save the program in a file named HiveDropDb.java. Given below are the commands to compile and execute this program.
1 2 3 | $ javac HiveDropDb.java $ java HiveDropDb |
Output:
1 2 | Drop userdb database successful. |
Conclude
How to manipulate Database in Hive is also simple, isn’t it, in the next article, I will present the next article on how to manipulate tables in Hive’s database, see ya !!