EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Hive Tutorial Hive Database
Secondary Sidebar
Hive Tutorial
  • Basics
    • Hive JDBC Driver
    • What is a Hive
    • Hive Architecture
    • Hive Installation
    • How To Install Hive
    • Hive Versions
    • Hive Commands
    • Hive Data Types
    • Hive Built-in Functions
    • Hive Function
    • Hive String Functions
    • Date Functions in Hive
    • Hive Table
    • Hive Drop Table
    • Hive Show Tables
    • Hive Group By
    • Hive Order By
    • Hive Cluster By
    • Joins in Hive
    • Hive Inner Join
    • Map Join in Hive
    • Hive nvl
    • Hive UDF
    • Dynamic Partitioning in Hive
    • HiveQL
    • HiveQL Queries
    • HiveQL Group By
    • Partitioning in Hive
    • Bucketing in Hive
    • Views in Hive
    • Indexes in Hive
    • External Table in Hive
    • Hive TimeStamp
    • Hive Database
    • Hive Interview Questions
    • Hive insert into

Related Courses

Hive Certification Course

Hadoop Course Training

All in One Data Science Course

Hive Database

By Priya PedamkarPriya Pedamkar

Hive Database

Introduction to Hive Database

Hive Databases provides the facility to store and manage the huge records or datasets on top of a distributed Hadoop platform. It is having the capability to store the structure and semi-structured data. With JDBC Driver’s help, the end-user can connect with the Hive database and fetch the data/records with the support of SQL Query. In the Hive, the database framework is developed by Facebook to analyze structured data or semi-structured data. The database framework supports all the regular commands like create, drop, alter, use database.

We can also use the database layer with different Hadoop components like Hbase, Kafka, spark, different streaming tools, etc.

Note: The Hive server or service is containing the default database. The default database name is “default”. While creating the table, we forget to specify which hive database we need to create the database. Then by default, the tables will create in the “default” database.

Syntax:

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database name
[COMMENT ‘database information | database comment’ ] [LOCATION ‘ HDFS Path’ ] [WITH DATABASE PROPERTIES (property name= property value, ...)];

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

How to Create Database in Hive?

We can create the database as similar to the SQL one. But the major thing is whether the tables are created in the internal table format or external table format. If all the tables are created in the internal table format, the database is also called an internal database. If all the tables are created in an external table format, the database is also called an external database. In the database, the data is stored in a tabular manner. The default storage location of the hive database varies from the hive version. From HDP 3.0, we are using version 3.0 and more. The default database location was changed. The location for external hive database is “/warehouse/tablespace/external/hive/” and the location for manage database is “/warehouse/tablespace/managed/hive”.

In the older version of the hive, the hive database’s default storage location is “/apps/hive/warehouse/”.

Query to Create Database

Query:

create database organization;

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,294 ratings)

Output :

Hive Database Example 1

When you need to access the hive DB, we need to establish the connection between the hive client and the hive server. To establish the connection, we need to configure the hive JDBC / ODBC connection string in the hive client and establish the hive server’s connection. With the help of Hive view, Hive CLI, beeline (interactive and non-interactive), or different third party client software, we can connect with the database.

Note:

  1. JDBC and ODBC are two different ways to connect with Hive. Few applications like MSTR support ODCB connection only. We need to configure the MSTR “ini” file. Few applications like Zeppelin, squirrel client, support the JDBC connection.
  2. With the help of Knox gateway, we can able to connect with Database.
  3. Hive View is only available in Ambari 2.6 or less. From version 2.7 and above Hive view is not available.

How to Modify or Alter Database?

We have the facility to alter or modify the existing database. We can also change the existing database comments, database properties, etc., of the database.

We can also alter the database as below.

1. Alter in Database Properties

Example:

ALTER ( DATABASE | SCHEMA ) database name SET database properties  ( property name = property value, ...);

2. Alter the Owner

Example:

ALTER (DATABASE| SCHEMA) database name SET OWNER [USER|ROLE ] user or role;

3. Alter the HDFS Location

Example:

ALTER (DATABASE|SCHEMA ) database name SET LOCATION HDFS Path;

Hive Query to Alter Database

Query:

ALTER DATABASE organization SET DBPROPERTIES ('Date' = '2020-03-31');

Output :

Hive Database Example 2

How to Drop Database in Hive?

In the hive, we can drop or delete the database form from the hive system. By default, there is a restriction on the drop database command. We cannot drop the exiting database with subject to the database is empty. If the database is empty, then only we can drop the database. If we have the tables in the database, then there is a restriction on the drop. There are two ways to drop the database having the tables in it.

  • First Way: We need to drop all the tables that are present in the current database. Then we can drop the current hive database.
  • Second Way: If we want to drop the hive database without dropping the current database tables. Then we can use the “CASCADE” keyword in the drop query. With the help of “CASCADE”, we can drop the hive database without touching the database’s internal tables.

Hive Drop Query with Empty Database

Query:

drop database organization;

Output :

Hive Database Example 3

Screenshot 1

In the above screenshot 1, we can see the hive database “organization” is empty. We can easily drop the database using a hive query.

Hive Drop Query with Table Contain

Query:

drop database company CASCADE;

Output :

Drop Query Example 4

Screenshot 2

As per the above screenshot, we can see the “company” DB has two tables. (table1: products and table2: sales).
Suppose we will directly use the drop command “drop database company;” to drop the company DB. Then the command will not work due to tables are present in it.  Please refer screenshot 3

Hive Database Example 5

Screenshot 3

When we use the “CASCADE” keyword in the query, we can drop the database. Please refer to screenshot 4.

CASCADE Example 6

Screenshot 4

Conclusion

We have seen the uncut concept of “Hive Database” with the proper example, explanation, syntax, SQL Query with different screenshots. This framework resides on top of Hadoop. It will summarize the huge datasets/records and do querying on it. Helpful for easy analysis on top of it. If the datasets increase, we can add the number of machines to it without impacting the performance.

Recommended Articles

This is a guide to Hive Database. Here we discuss the Introduction and how to Create a Hive Database and practical examples and different subquery expressions. You can also go through our related articles to learn more –

  1. What is Hive Data Types?
  2. Hive Alternatives | Find out the Features
  3. Top Components of Hive Commands
  4. Top 10 Hive Interview Questions
Popular Course in this category
Hive Training (2 Courses, 5+ Projects)
  2 Online Courses |  5 Hands-on Projects |  25+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Hadoop Training Program (20 Courses, 14+ Projects, 4 Quizzes)4.9
All in One Data Science Bundle (360+ Courses, 50+ projects)4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more