Apache Hive View and Hive Index

1. Objective – Hive View

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

Hadoop Quiz

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

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

If these professionals can make a switch to Big Data, so can you:
Rahul Doddamani Story - DataFlair
Rahul Doddamani
Java → Big Data Consultant, JDA
Follow on
Mritunjay Singh Success Story - DataFlair
Mritunjay Singh
PeopleSoft → Big Data Architect, Hexaware
Follow on
Rahul Doddamani Success Story - DataFlair
Rahul Doddamani
Big Data Consultant, JDA
Follow on
I got placed, scored 100% hike, and transformed my career with DataFlair
Enroll now
Deepika Khadri Success Story - DataFlair
Deepika Khadri
SQL → Big Data Engineer, IBM
Follow on
DataFlair Web Services
You could be next!
Enroll now

3. Conclusion

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.
See Also- Difference between Hive Partitioning vs Bucketing
For reference

5 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.

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.