How to create a database in Hive CLI?

Introduction

Hadoop is a widely-used framework for big data processing and storage, and Hive is a key component within the Hadoop ecosystem. Hive provides a SQL-like interface, called Hive CLI, that allows you to interact with and manage data stored in the Hadoop Distributed File System (HDFS). In this tutorial, you will learn how to create a database in Hive CLI, as well as how to manage databases and tables for your Hadoop projects.

Skills Graph

Introduction to Hive and Hive CLI

Hive is an open-source data warehousing solution built on top of Apache Hadoop, designed to facilitate data processing, analysis, and management. Hive provides a SQL-like interface, known as Hive CLI (Command-Line Interface), which allows users to interact with data stored in the Hadoop Distributed File System (HDFS) or other compatible data sources.

What is Hive?

Hive is a data warehouse infrastructure built on top of Hadoop for providing data summarization, query, and analysis. It was developed by Facebook and later became an Apache Software Foundation project. Hive allows you to read, write, and manage large datasets residing in distributed storage using a SQL-like language called HiveQL, which is similar to traditional SQL.

What is Hive CLI?

Hive CLI is the command-line interface for interacting with Hive. It provides a shell-like environment where you can execute HiveQL commands, create and manage databases, tables, and perform various data manipulation and analysis tasks.

Key Features of Hive CLI

• SQL-like Syntax: Hive CLI supports a SQL-like language called HiveQL, which makes it easy for users familiar with SQL to work with Hive.
• Data Abstraction: Hive CLI provides a way to abstract complex data stored in HDFS or other data sources, making it easier to query and analyze.
• Scalability: Hive CLI can handle large datasets by leveraging the underlying Hadoop infrastructure, including its distributed processing and storage capabilities.
• Integration with Hadoop: Hive CLI is tightly integrated with the Hadoop ecosystem, allowing seamless interaction with HDFS, MapReduce, and other Hadoop components.
• User-Defined Functions (UDFs): Hive CLI supports the creation and use of custom User-Defined Functions (UDFs) to extend the functionality of HiveQL.

Using Hive CLI

To use Hive CLI, you need to have a Hadoop cluster set up and Hive installed. Once you have the necessary infrastructure in place, you can start the Hive CLI by running the following command in your terminal:

$ hive

This will open the Hive CLI, where you can start executing HiveQL commands to interact with your data.

Creating a Database in Hive CLI

Creating a Database

In Hive CLI, you can create a database using the CREATE DATABASE statement. The basic syntax is as follows:

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

Here's an example of creating a new database called "my_database":

CREATE DATABASE IF NOT EXISTS my_database
COMMENT 'This is my first database'
LOCATION '/user/hive/warehouse/my_database.db'
WITH DBPROPERTIES ('creator'='LabEx', 'created_date'='2023-04-20');

In this example, we:

• Create a database called "my_database" if it doesn't already exist.
• Add a comment to describe the purpose of the database.
• Specify the location of the database in HDFS.
• Set custom database properties, such as the creator and creation date.

Listing Databases

To list all the databases available in Hive, you can use the SHOW DATABASES command:

SHOW DATABASES;

This will display all the databases that have been created in Hive.

Switching to a Database

To switch to a specific database, you can use the USE statement:

USE my_database;

After executing this command, all subsequent Hive operations will be performed within the context of the "my_database" database.

Describing a Database

To get detailed information about a specific database, you can use the DESCRIBE DATABASE command:

DESCRIBE DATABASE my_database;

This will display the database properties, such as the comment, location, and custom properties.

By understanding how to create, list, switch, and describe databases in Hive CLI, you can effectively manage and organize your data within the Hadoop ecosystem.

Managing Databases and Tables in Hive CLI

Managing Databases

In addition to creating databases, Hive CLI also provides commands to manage existing databases. Here are some common database management operations:

Dropping a Database

To drop a database, you can use the DROP DATABASE statement:

DROP DATABASE [IF EXISTS] database_name [CASCADE];

The CASCADE option will also drop all the tables within the database.

Altering a Database

You can modify the properties of an existing database using the ALTER DATABASE statement:

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

This allows you to update the custom properties of the database.

Managing Tables

Hive CLI also provides commands to create, manage, and interact with tables within a database.

Creating a Table

To create a new table, you can use the CREATE TABLE statement:

CREATE TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)];

This statement allows you to define the table structure, including column names, data types, and other table-level properties.

Dropping a Table

To drop an existing table, you can use the DROP TABLE statement:

DROP TABLE [IF EXISTS] table_name;

Altering a Table

Hive CLI also supports table-level alterations, such as adding/dropping columns, changing column data types, and modifying table properties:

ALTER TABLE table_name
ADD COLUMNS (col_name data_type [COMMENT col_comment], ...)
DROP COLUMNS (col_name, ...)
CHANGE COLUMN col_old_name col_new_name column_type
SET TBLPROPERTIES (property_name=property_value, ...)

By understanding how to manage databases and tables in Hive CLI, you can effectively organize and manipulate your data within the Hadoop ecosystem.

Summary

By following this step-by-step guide, you will gain the skills to create a database in Hadoop's Hive CLI, a crucial skill for working with data in the Hadoop ecosystem. You will also learn how to manage databases and tables, empowering you to effectively organize and maintain your Hadoop data infrastructure.