Apache Hive View and Hive Index

Boost your career with Big Data Get Exclusive Offers on Big Data Course!!

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

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.

Stay updated with latest technology trends
Join DataFlair on Telegram!!

a. What is Hive view?

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

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. 

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

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 Indexes

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)>

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, ...)]
  | STORED BY ...
[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.

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 

These two are Apache Hive Index types:

  • Compact Indexing in Hive
  • Bitmap Indexing in Hive

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


Hence, in this apache hive index, we studied 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 the hive, When to use Indexing in the 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.

7 Responses

  1. Eswar says:

    Does hive support indexing for all file formats or only specific file formats like ORC?

    • Data Flair says:

      Hi Eswar,
      Thanks for Visiting Data-Flair, we are happy you asked your query on this “Apache Hive View and Hive Index” Tutorial. Here, is the solutions –
      Hive supports indexing only for ORC because ORC has built-in Indexes that permits the format to skip blocks of data during reading. Moreover, they also support Bloom filters. Hence, together it replicates what Hive Indexes did and they perform it automatically in the data format even without the need to manage an external table.
      Hope we solve your query. Feel free to explore Hive Tutorial.

  2. Ramakrishna says:


    Can you just help how can we add ‘hplsql’ in hive

  3. Vishakha Sadawarte says:

    Please improve the English grammar in this post.

  4. Rishan says:

    What is the difference between Compact Indexing in Hive & Bitmap Indexing in Hive in Hive

  5. Sai kumar says:

    Compact indexing can be applied only on the column which is unique in table for ex: dept_no, whereas Bitmap can indexing can be applied either on unique or non unique columns,

Leave a Reply

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

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.