In this post, we will discuss about Hive Database Commands (Create/Alter/Use/Drop Database) with some examples for each statement. All these commands and their options are from hive-0.14.0 release documentations. So, in order to use these commands with all the options described below we need at least hive-0.14.0 release.
Table of Contents
Hive Database Commands
Note
From Hive-0.14.0 release onwards Hive DATABASE is also called as SCHEMA. So, Both SCHEMA and DATABASE are same in Hive. All the commands discussed below will do the same work for SCHEMA and DATABASE keywords in the syntax.
Introduction to Hive Databases
As given in above note, Either SCHEMA or DATABASE in Hive is just like a Catalog of tables. With the help of database names, users can have same table name in different databases, So thus, in large organizations, teams or users are allowed create same table by creating their own separate DATABASE, to avoid table name collisions.
The default database in hive is default. We do not need to create this database. Any table created without specifying database will be created under this.
Following are the sections on operations on Hive databases.
Create Database
Below is the syntax for creation of Databases in Hive.
Description of Arguments
- IF NOT EXISTS – This optional but recommended to use, so that, if a database with same name already exists, then it will not try to create it again and will not show any error message.
- COMMENT – It is also optional. It can be used for providing short description or any comment to database
- LOCATION – It is also optional. By default all the hive databases will be created under default warehouse directory (set by the property hive.metastore.warehouse.dir) as /user/hive/warehouse/database_name.db . But if we want to specify our own location then this option can be specified.
- DBPROPERTIES – Optional but used to specify any properties of database in the form of (key, value) separated pairs.
Example
Below is the example database creation HiveQL, which covers all the above options.
Simple Syntax for creating database after omitting all the optional arguments is given below.
This uses all the default values for omitted arguments.
Show Databases
Lets verify the creation of these databases in Hive CLI with show databases command. It will list down the databases in hive.
Syntax
By default, SHOW DATABASES lists all of the databases defined in the metastore.
- LIKE – It is optional. But it allows us to filter the database names using a regular expression.
Wild cards in the regular expression can only be ” (single quotes) for any character(s) or ‘|’ for a choice.
Examples are ’employees’, ’emp’, ‘emp*|*ees‘, (emp* or *ees), all of which will match the database named ’employees’.
Examples
Below is the sample output of show databases command after execution above two creation commands.
Describe Databases
We can also verify the properties of databases with Describe command in hive.
Syntax
We can observe the differences between Describe with Extended and without Extended argument in the below examples. Describe extended will also shows database’s properties given under DBPROPERTIES argument at the time of creation.
Describe command will also list down the directory location of the database.