Hive Database Commands

This entry was posted in Hive and tagged hive alter database examples hive alter database location hive alter database owner hive command to show databases hive create database if not exists example hive create database location example hive create database with db properties example hive database db properties hive database owner hive ddl database commands with examples hive default database location hive default database name hive describe database extended example hive describe database extended examples hive drop database cascade examples hive drop database force hive drop database restrict hive drop database with tables example hive set default database hive show current database hive show database location with describe hive show database owner with describe hive show databases like examples hive use database command example hive use multiple databases examples How to identify the database being used in hive CLI Show current database in hive prompt on December 3, 2014 by Siva

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.

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.

MySQL
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
  [COMMENT database_comment]
  [LOCATION hdfs_path]
  [WITH DBPROPERTIES (property_name=property_value, ...)];

				1
2
3
4
5

						 
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
  [COMMENT database_comment]
  [LOCATION hdfs_path]
  [WITH DBPROPERTIES (property_name=property_value, ...)];

					

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.

create database in hive
MySQL
CREATE DATABASE IF NOT EXISTS test_db
	COMMENT "Test Database created for tutorial"
	WITH DBPROPERTIES(
		'Date' = '2014-12-03',
		'Creator' = 'Bala G',
		'Email' = 'bala@somewhere.com'
		);

				1
2
3
4
5
6
7
8

						 
CREATE DATABASE IF NOT EXISTS test_db
	COMMENT "Test Database created for tutorial"
	WITH DBPROPERTIES(
		'Date' = '2014-12-03',
		'Creator' = 'Bala G',
		'Email' = 'bala@somewhere.com'
		);

					

Simple Syntax for creating database after omitting all the optional arguments is given below.

MySQL
CREATE DATABASE test_db2;

				1
2

CREATE DATABASE test_db2;

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

MySQL
SHOW (DATABASES|SCHEMAS) [LIKE identifier_with_wildcards];

				1
2

SHOW (DATABASES|SCHEMAS) [LIKE identifier_with_wildcards];

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.

MySQL
hive> show databases;
OK
default
test_db
test_db2
Time taken: 0.072 seconds, Fetched: 3 row(s)
hive> SHOW DATABASES LIKE '*db*';
OK
test_db
test_db2
Time taken: 0.014 seconds, Fetched: 2 row(s)
hive> 

				1
2
3
4
5
6
7
8
9
10
11
12
13

						 
hive> show databases;
OK
default
test_db
test_db2
Time taken: 0.072 seconds, Fetched: 3 row(s)
hive> SHOW DATABASES LIKE '*db*';
OK
test_db
test_db2
Time taken: 0.014 seconds, Fetched: 2 row(s)
hive> 

					

Describe Databases

We can also verify the properties of databases with Describe command in hive.

Syntax

MySQL
hive> (DESCRIBE|DESC) (DATABASE|SCHEMA) [EXTENDED] database_name

				1
2

hive> (DESCRIBE|DESC) (DATABASE|SCHEMA) [EXTENDED] database_name

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.

Examples

MySQL
hive> DESCRIBE DATABASE test_db;          
OK
test_db	Test Database created for tutorial	hdfs://localhost:9000/user/hive/test_db	user	USER	
Time taken: 0.016 seconds, Fetched: 1 row(s)
hive> DESCRIBE DATABASE EXTENDED test_db;
OK
test_db	Test Database created for tutorial	hdfs://localhost:9000/user/hive/test_db	user	USER	{Email=bala@somewhere.com, Date=2014-12-03, Creator=Bala G}
Time taken: 0.008 seconds, Fetched: 1 row(s)
hive> DESC DATABASE test_db2;
OK
test_db2		hdfs://localhost:9000/user/hive/warehouse/test_db2.db	user	USER	
Time taken: 0.019 seconds, Fetched: 1 row(s)

				1
2
3
4
5
6
7
8
9
10
11
12
13

						 
hive> DESCRIBE DATABASE test_db;          
OK
test_db	Test Database created for tutorial	hdfs://localhost:9000/user/hive/test_db	user	USER	
Time taken: 0.016 seconds, Fetched: 1 row(s)
hive> DESCRIBE DATABASE EXTENDED test_db;
OK
test_db	Test Database created for tutorial	hdfs://localhost:9000/user/hive/test_db	user	USER	{Email=bala@somewhere.com, Date=2014-12-03, Creator=Bala G}
Time taken: 0.008 seconds, Fetched: 1 row(s)
hive> DESC DATABASE test_db2;
OK
test_db2		hdfs://localhost:9000/user/hive/warehouse/test_db2.db	user	USER	
Time taken: 0.019 seconds, Fetched: 1 row(s)

					

Observe the locations of test_db (created with our own LOCATION in CREATE statement) and test_db2 (without location argument while creating it).

Alter Databases

We can alter the databases with Alter command in hive but it provides very minimal alterations.

We can

Assign any new (key, value) pairs into DBPROPERTIES
Set owner user or role to the Database

But below are its limitations

We can’t unset any property using Alter command
No other meta-data about the database can be changed, including its name and directory location.

Syntax

MySQL
ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;

				1
2
3

						 
ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);
ALTER (DATABASE