How to Create and Drop Apache Hive databases/schemas

Continuing with our Apache Hive tutorial series now we will see how we can create & drop Hive databases. A database in Hive is a namespace or a collection of tables. The uses of SCHEMA and DATABASE are interchangeable and has the same effect. The base syntax to create a database in Hive is: 

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
  [COMMENT database_comment]
  [LOCATION hdfs_path]
  [WITH DBPROPERTIES (property_name=property_value, ...)];
To create a database in Hive 
hive create database newdb;
OK
Time taken: 2.04 seconds
Create a new Hive Database with custom description and location 
hive CREATE DATABASE newdbTwo
       COMMENT 'new database'
       LOCATION 'hdfs://sandbox.hortonworks.com:8020/apps/hive/warehouse/TwoNew.db'
      ;
OK
Time taken: 1.435 seconds
hive show databases;
OK
default
newdb
newdbtwo
testdb
xademo
Time taken: 0.197 seconds, Fetched: 5 row(s)
hive
To see the database definition/descriptions 
hive describe database newdb;
OK
newdb           hdfs://sandbox.hortonworks.com:8020/apps/hive/warehouse/newdb.db        root    USER
Time taken: 0.335 seconds, Fetched: 1 row(s)
hive describe database newdbtwo;
OK
newdbtwo        new database    hdfs://sandbox.hortonworks.com:8020/apps/hive/warehouse/TwoNew.db       root    USER
Time taken: 0.322 seconds, Fetched: 1 row(s)
Or you can query the MySQL databases that holds the metastore
  • loging to your local MySQL server.
[root@sandbox /]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 490
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql show databases;
+--------------------+

| Database           |
+--------------------+

| information_schema |
| hive               |
| mysql              |
| ranger             |
| ranger_audit       |
| test               |
+--------------------+

6 rows in set (0.00 sec)

mysql use hive;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
Query the DBS table from the hive database 
mysql select * from DBS;
+-------+-----------------------+-------------------------------------------------------------------+----------+------------+------------+

| DB_ID | DESC                  | DB_LOCATION_URI                                                   | NAME     | OWNER_NAME | OWNER_TYPE |
+-------+-----------------------+-------------------------------------------------------------------+----------+------------+------------+

|     1 | Default Hive database | hdfs://sandbox.hortonworks.com:8020/apps/hive/warehouse           | default  | public     | ROLE       |
|     6 | NULL                  | hdfs://sandbox.hortonworks.com:8020/apps/hive/warehouse/xademo.db | xademo   | hive       | USER       |
|    11 | NULL                  | hdfs://sandbox.hortonworks.com:8020/apps/hive/warehouse/testdb.db | testdb   | root       | USER       |
|    16 | NULL                  | hdfs://sandbox.hortonworks.com:8020/apps/hive/warehouse/newdb.db  | newdb    | root       | USER       |
|    17 | new database          | hdfs://sandbox.hortonworks.com:8020/apps/hive/warehouse/TwoNew.db | newdbtwo | root       | USER       |
+-------+-----------------------+-------------------------------------------------------------------+----------+------------+------------+
Drop database syntax:
  • to drop a database use the following syntax:
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
Note:
  • if database is not empty you will get and error
hive drop database testdb;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database testdb is not empty. One or more tables exist.)
  •  To force the drop a Hive database that contains objects you need to use the CASCADE option
hive drop database testdb cascade;
OK
Time taken: 3.363 seconds

Copyright 2020. All Rights Reserved