- Hive Databases are like namespaces/catalogs
- If no database name is specified, ‘default’ database is used
- We can also use the keyword SCHEMA instead of DATABASE in all the database related commands below.
- Hive creates a directory for each of the databases it creates
- The default directory created for the database under a top-level directory specified by the property hive.metastore.warehouse.dir
- You can specify a different directory using the LOCATION option in the CREATE command
- Creating Database
CREATE DATABASE IF NOT EXISTS Mydb1 WITH DBPROPERTIES ( ‘prop1’=‘value1’;
- Listing all databases ( regular expressions also allowed in listing dbs)
SHOW DATABASES;
SHOW DATABASES LIKE ‘M*’;
- Describing Database
DESCRIBE DATABASE Mydb1; // shows DB name, comment & DB directory
DESCRIBE DATABASE EXTENDED <database> // shows DB properties also
- Using Database
USE Mydb1; // sets a database as your working database
- Dropping Database
DROP DATABSE IF EXISTS Mydb1;
DROP DATABASE IF EXISTS MyDb1 CASCADE/RESTRICT; //existing tables in the db must bedropped before dropping the database
- Altering Databases
– DBPROPERTIES can be set using ALTER command– You can not unset or delete a DBPROPERTY
ALTER DATABASE MyDb1 SET DBPROPERTIES (‘edited-by’ = ‘ Infosys ‘);
