Breaking News


Database in hive essentially serves as a catalog or namespace of helps to resolve table name collisions.Used to organize production tables into logical groups.

Show databases – This command shows all the existing databases in hive. The default database is “default”. If no database is specified, all the tables gets created under default database.
Create database: To create a database, use the command
Create database if not exists <database_name>
Hive will create a directory for each database. Tables in that database will be stored in
Subdirectories of the database directory. The exception is tables in the default database,
which doesn’t have its own directory. The database directory is created under a top-level directory specified by the property hive.metastore.warehouse.dir.
Use :To use a specific database, use the command
USE <database_name>

DESCRIBE DATABASE: It shows the directory location for the database.
In the output of DESCRIBE DATABASE, it shows master-server to indicate the URI
authority, in this case a DNS name and optional port number (i.e., server:port) for the
“master node” of the filesystem (i.e., where the NameNode service is running for
HDFS). While running in pseudo-distributed mode, then the master server will be
localhost. For local mode, the path will be a local path, file:///user/hive/warehouse/
If the authority is omitted, Hive uses the master-server name and port defined by
the property in the Hadoop configuration files, found in the
$HADOOP_HOME/conf directory.
drop a database:
The IF EXISTS is optional and suppresses warnings if financials doesn’t exist.
By default, Hive won’t permit you to drop a database if it contains tables. You can either
drop the tables first or append the CASCADE keyword to the command, which will cause the Hive to drop the tables in the database first:
Using the RESTRICT keyword instead of CASCADE is equivalent to the default behavior,where existing tables must be dropped before dropping the database.
When a database is dropped, its directory is also deleted.

 Wherever (Different Directory) I run hive query, it creates new metastore_db, please explain the reason for it?
Whenever you run the hive in embedded mode, it creates the local metastore. And before creating the metastore it looks whether metastore already exist or not. This property is defined in configuration file hive-site.xml. Property is “javax.jdo.option.ConnectionURL” with default value “jdbc:derby:;databaseName=metastore_db;create=true”. So to change the behavior change the location to absolute path, so metastore will be used from that location.
- See more at:
Toggle Footer