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.
|
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.
|
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.
|
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
|
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.
|
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
|
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
|
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
|
1 2 3 |
ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...); ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role; |
Examples
Lets add new property ‘modified by’ to the above created database test_db and see the result in ‘describe extended’.
|
1 2 3 4 5 6 7 8 9 |
hive> ALTER SCHEMA test_db SET DBPROPERTIES ('Modified by' = 'siva'); OK Time taken: 0.414 seconds hive> DESCRIBE DATABASE EXTENDED test_db; OK test_db Test Database created for tutorial hdfs://localhost:9000/user/hive/warehouse/test_db.db user USER {Modified by=siva, Email=bala@somewhere.com, Date=2014-12-03, Creator=Bala G} Time taken: 0.012 seconds, Fetched: 1 row(s) hive> |
In this example, we can also observe that SCHEMA and DATABASE can be used interchangeably, As we have used SCHEMA in Alter command and DATABASE in DESCRIBE command.
Use Databases
We can set the database on which we need to work with USE command in hive. It sets the current database to be used for further hive operations.
As, by default, we enter into default database in Hive CLI, we need to change our database if we need to point to our custom database.
|
1 2 3 4 5 6 7 8 9 |
hive> USE database_name; <strong><span style="text-decoration: underline;" data-mce-style="text-decoration: underline;">Example:</span></strong> hive> USE test_db; OK Time taken: 1.051 seconds hive> |
Note
To know the current database under which we are working we need to the below property in Hive shell. There is no command to show the current database, but this property will keep printing the current database name as suffix in the command line prompt as shown in below examples.
|
1 2 |
set hive.cli.print.current.db=true; |
Examples
|
1 2 3 4 5 6 7 8 9 10 11 |
hive> set hive.cli.print.current.db=true; hive (test_db)> SHOW SCHEMAS; OK default test_db Time taken: 0.523 seconds, Fetched: 2 row(s) hive (test_db)> USE default; OK Time taken: 0.069 seconds hive (default)> |
Updating the conf property via set hive.cli.print.current.db=true will show the current DB for current hive session only, but if we update the .hiverc file with the above property will keep showing the current db for all the sessions.
Drop Databases
When we no longer need a database, we can drop such databases with Drop command in hive.
By default, Hive doesn’t allow us to drop databases that contain at least one or more tables. In this case, we need to either drop the tables first and then drop database or we need to provide CASCADE argument to DROP command.
Syntax
|
1 2 |
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE]; |
- IF EXISTS – It is optional but used to suppresses warnings if database_name doesn’t exist.
- RESTRICT – This is optional and even if it is used, it is same as default hive behavior, i.e. it will not allow database to be dropped until all the tables inside it are dropped.
- CASCADE – This argument allows to drop the non-empty databases with single command. DROP with CASCADE is equivalent to dropping all the tables separately and dropping the database finally in cascading manner.
Examples
Observe the hive error message ” InvalidOperationException(message:Database test_db2 is not empty. One or more tables exist.)” when trying to delete test_db2 which has one test_table with no CASCADE argument.
When a database is dropped, its directory is also deleted
References
https://cwiki.apache.org/confluence/display/Hive/Home
Thanks a lot for providing this article with clear explanation and very easy to understand for beginners.