Apache Hive View and Hive Index 1

1. Objective

In our last article, we see Hive Built-in Functions. In this Hive index Tutorial, we will learn the whole concept of Hive Views and Indexing in Hive. Also, we will cover how to create Hive Index and hive Views, manage views and Indexing of hive, hive index types, hive index performance, and hive view performance. In addition, we will learn several examples to understand both. We can save any result set data as a view. Whereas Apache Hive Index is a pointer to a particular column of a table. However,  there is much more to learn about Apache Hive View and Indexing in Apache Hive


Hive View and Hive Index

Hive View and Hive Index


2. Apache Hive View and Index

In this Part of a tutorial, we are going to study definition and concept of Apache Hive View and Hive Index.

a. What is Hive view

Basically, Apache Hive View is similar to Hive tables, that are generated on the basis of requirements.

Must Read Hive Internal Tables vs External Tables in detail


Hive View and Hive Index

Apache Hive View on External Table

  • As a Hive view, we can save any result set data.
  • Well, we can say its usage is as same as the use of views in SQL.
  • Although, we can perform all type of DML operations on Hive views.

In other words, Apache Hive View is a searchable object in a database which we can define by the query. However, we can not store data in the view. Still, some refer to as a view as “virtual tables”. Hence, we can query a view like we can a table. Moreover, by using joins it is possible to combine data from or more table. Also, it contains a subset of information. 

Let’s learn Impala vs Hive – Difference Between Hive and Impala

i. Apache Hive View Syntax


ii. Creating a Hive View

However, at the time of executing a SELECT statement, we can create a view. So, to create Hive view Syntax is:

CREATE VIEW [IF NOT EXISTS] view_name [(column_name [COMMENT column_comment], …) ]

[COMMENT table_comment]


iii. Apache Hive View Example

Let’s suppose, an employee table. It includes fields Id, Name, Salary, Designation, and Dept. Now here we are generating a query to retrieve the employee details who earn a salary of more than Rs 35000. So, we store the result in a view named emp_30000.

Table 1- Apache Hive View

1201Michel 45000Technical managerTP
1203Ross40000Technical writer TP
1204Joey40000Hr AdminHR
1205Monika35000Op AdminAdmin

Let’s study Apache Hive Architecture & Components in detail

Hence, using the above scenario here is the following query retrieves the employee details:

hive> CREATE VIEW emp_35000 AS

SELECT * FROM employee

WHERE salary>35000

iv. Dropping a Hive View

However, to drop a Hive view, use the following syntax:

DROP VIEW view_name

The following query drops a view named as emp_35000:

hive> DROP VIEW emp_35000;

b. What is Apache Hive Index

On defining indexing in Hive we can say these are pointers to particular column name of a table.

Hive View and Hive Index

Introduction – Apache Hive Index

  • However, the user has to manually define the Hive index
  • Basically, we are creating the pointer to particular column name of the table, wherever we are creating Hive index.
  • By using the Hive index value created on the column name, any Changes made to the column present in tables are stored.

i. Apache Hive index Syntax

Create INDEX < INDEX_NAME> ON TABLE < TABLE_NAME(column names)>

Let’s Learn Hive Operators – A Complete Tutorial for Hive Built-in Operators

ii. Create an Indexing in Hive

However, creating a Apache Hive index means creating a pointer on a particular column of a table. So, to create an indexing in hive.

Apache Hive Index Syntax is:

CREATE INDEX index_name

ON TABLE base_table_name (col_name, …)

AS ‘index.handler.class.name’


[IDXPROPERTIES (property_name=property_value, …)]

[IN TABLE index_table_name]

[PARTITIONED BY (col_name, …)]





[LOCATION hdfs_path]


iii. Apache Hive Index Example

Let’s suppose the same employee table which we had used earlier with the fields Id, Name, Salary, Designation, and Dept. So, here create an index named index_salary on the salary column of the employee table.

Hence, we use the following query to create a Hive index:

hive> CREATE INDEX inedx_salary ON TABLE employee(salary)

AS ‘org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler’;

However, it is a pointer to the salary column. Basically, the changes are stored using an index value, if the column is modified.

Let’s Know about Difference between Pig and Hive

iv. Dropping an Index

Also, drop indexing in Hive we use the following syntax of Apache Hive Index:

DROP INDEX <index_name> ON <table_name>

Here, is the following query drops a Hive index named index_salary:

hive> DROP INDEX index_salary ON employee;

v. When to use Hive Indexing?

Under the following circumstances, we can use Indexing in Hive:

  • While the dataset is very large.
  • Whenever the query execution is more amount of time than you expected.
  • While we need a speedy query execution.
  • While we build a data model.

Hive Index is maintained in a separate table. Hence, it won’t affect the data inside the table, which contains the data. There is one more advantage of it. That is for indexing in Hive is that index can also be partitioned depending on the size of the data we have.

vi. Types of Hive Index 

This two are Apache Hive Index types:

  • Compact Indexing in Hive
  • Bitmap Indexing in Hive

This was all about Apache Hive Index Tutorial. Hope you like our explanation of Indexing in Hive.

3. Conclusion

In this apache hive index, we study what is Hive View and Hive Index. We have learned the whole concept of Apache Hive view with Syntax, How to create apache hive views with Example, Dropping a Hive view, Hive Index with Syntax, How to create Hive Index with Example, Dropping an Indexing in hive, When to use Indexing in hive, and types of Hive Index. Well also including these features correctly in your Hive implementation will simplify and provide your users with a better experience and Knowing well. Still, if any doubt occurs feel free to ask in the comment section.

See Also- Difference between Hive Partitioning vs Bucketing

For reference

Leave a comment

Your email address will not be published. Required fields are marked *

One thought on “Apache Hive View and Hive Index