Hive Commands: An Easy Guide For Beginners in 2021


In today’s date and time, there are massive amounts of information available on various platforms. Big data is a word that almost every layman now has a general idea about. Data analysts and scientists take raw data and convert it into content that may be further used to perform a potentially difficult and overwhelming task.

There are certain tools that help in converting data into comprehensible conduct; one such framework is Hadoop that is designed to process and store big data information. 

On the other hand, Hive is a warehouse for data and is an open-source system that processes structured data in Hadoop. It summarizes, analyses and even forms queries on the big data that it stores.

  1. What is Hive
  2. Types of Hive DDL commands

1) What is Hive

Hive Commands: As previously mentioned, the Hive is a warehouse system that has been built to work on Hadoop. Before coming into the hands of Hadoop, Hive was originally a part of Facebook. Apache foundation took it up and developed it further. It now provides a method by which structure is given to data in Hadoop and queries are made regarding the data based on a Structured Query Language (SQL) known as HiveQL (HQL). HQL allows a familiar user to write custom MapReduce framework to perform complex analysis.

In fact, the Hive engine compiles all these queries that are developed into Map- Reduce jobs that are then executed on Hadoop. Hive is accompanied by a command-line shell interface that can be utilized to make a table and execute queries.

Given below are certain features of Hive one must keep in mind-

  • Hive stores raw and processed sets of data in Hadoop.
  • Here, the Tables and databases are created before the data is loaded into the tables.
  • It is designed to facilitate and process high volumes of data in minimal time without dependency on a single server. It is, therefore, perfect for Online Transaction Processing.
  • Unlike SQL database, Hive executes queries on Hadoop’s infrastructure and not on a traditional database.
  • It is reliant, speedy and scalable in nature.
  • Hive is ideal for fast and simple data retrieval as it supports partition and buckets.
  • Hive supports certain user-defined functions for data cleansing and filtering, thus providing the ultimate customer experience according to a programmers’ requirements.
  • HiveQL language that is being used makes ETL and other analytical tasks much easier.

However, it has its limitation. It cannot support subqueries, deletions and updates.

2) Types of Hive DDL commands

Statements used for defining and changing a database’s structure and building or modifying the tables or other objects of a database in a Hive is known as Hive DDL commands. DDL stands for Data Definition Language. It must be noted that all Hive commands are case insensitive. Essentially, CREATE DATABASE is no different than creating a database.

There are several types of Hive DDL commands. A list of basic Hive commands has been enumerated below.


This Hive command creates a new database in the Hive. It must be noted that the DATABASE and SCHEMA are interchangeable. This command can be used with database and table.

  • SHOW

The Show command will provide a view or lists of all databases that are stored in the Hive. It is used with databases, tables, table properties, functions and index as well.


This Hive command shows the name of the database in the Hive, its set comment and even its location on the file system. It is used with Database, Table and view.

  • USE 

The USE command allows one to select a specific database in the Hive for a session in which all subsequent HQL statements can be executed. It is used with a Database.

  • DROP

This Hive command will remove a table from the Hive or delete a database from the Hive. If it is not specifically mentioned that the data is to be moved to Trash, then the data will be lost completely. It can be used with database and table.


This Hive command shall help rename the table or columns of the table. It essentially helps change the metadata that is associated with the database stored in the Hive. The statement used here is the ALTER TABLE. Just like DROP command, it can be used with database and table.


This Hive Table command is used to permanently remove or delete rows of a table or partition in the database. It can be used with Table.


This Hive command helps delete data from a table like the TRUNCATE command. However, unlike the latter, DELETE command allows the data to be restored once deleted.

There are certain intermediate Hive commands as well. For example, changes can be made to the Partition by using commands such as Adding Partition, Renaming Partition, Drop Partition, etc. Furthermore, the Relational Operators command allows fetching necessary information with the help of a consistent set of operators. Arithmetic operators help in executing arithmetic operations, and Logical operators execute logical operations. 

There are certain advanced Hive table commands as well. For example

 View: Similar to the view in SQL command, a view can be created while executing a SELECT statement.

 Loading Data into the table: Such inbuilt functions help fetch results in a better and speedier manner.

 Join: This command helps join two tables with the same column name.


Hive is a more significant level of abstraction on top of HDFS, which in fact provides more adaptable query language. It helps in questioning and handling data in a simpler way.

Hive often tends to be clubbed with other big data components, to bridle its usefulness undeniably.It is an absolute necessity to understand the concept of Hive for someone who wishes to make a career in the field of Big Data. The Global Hadoop market is on the rise. The field has become more rewarding and demanding. Already existing data analyst professionals too need to look into upskilling by understanding software that have entered into the market. An understanding of Hive commands can be a step towards upskilling.

If you are interested in making it big in the world of data and evolve as a Future Leader, you may consider our Integrated Program in Business Analytics, a 10-month online program, in collaboration with IIM Indore!


Related Articles

Please wait while your application is being created.
Request Callback