access_time 12 months ago languageEnglish
more_vert

Create, Drop, Alter and Use Database - Hive SQL

visibility 468 comment 0

This page shows how to create, drop, alter and use Hive databases via Hive SQL (HQL).

Create database

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

LOCATION is used to specify default HDFS location for external table while MANAGEDLOCATION is the default HDFS location for managed tables.

Example:

CREATE DATABASE IF NOT EXISTS hql;
CREATE SCHEMA IF NOT EXISTS hql;

Output:

OK

Alter database

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

Example:

ALTER DATABASE hql SET DBPROPERTIES('database usage'='Hive SQL tutorials.'); 

Output:

OK

Use database

USE database_name;

Example:

USE hql;

Output:

OK

The above command sets current database to hql.

Drop database

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

The default drop option is RESTRICT which will error out if the database is not empty. Use CASCADE option to drop all the objects in the database too.

Example:

DROP SCHEMA hql CASCADE;

Output:

OK

Install Hive database

Follow the article below to install Hive on Windows 10 via WSL if you don't have available available Hive database to practice Hive SQL:

Examples on this page are based on Hive 3.* syntax.

Run query

All these SQL statements can be run using beeline CLI:

$HIVE_HOME/bin/beeline --silent=true

The above command line connects to the default HiveServer2 service via beeline. Once beeline is loaded, type the following command to connect:

0: jdbc:hive2://localhost:10000> !connect jdbc:hive2://localhost:10000
Enter username for jdbc:hive2://localhost:10000: hive
Enter password for jdbc:hive2://localhost:10000:
1: jdbc:hive2://localhost:10000>

The terminal looks like the following screenshot:


info Last modified by Administrator 12 months ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Follow Kontext

Get our latest updates on LinkedIn.

Want to contribute on Kontext to help others?

Learn more

More from Kontext

visibility 4068
thumb_up 0
access_time 3 years ago
visibility 567
thumb_up 1
access_time 2 years ago
visibility 84
thumb_up 0
access_time 5 months ago